sum of values between two dates

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i am doing project on student attendance system.i have a database of student attendance with roll no and attendance of each day.
eg. attendance of students in January
|roll_no | 1-01-2015| to |31-01-2015|

each day attendance is recorded with 1 or 0...1-present and 0-absent

to calculate defaulter, teacher will enter start date and end date,and then for each roll no total attendance is calculated.

for eg. attendance between 10-01-2015 to 20-01-2015 for all students or for student roll no =3.

and i want to display that defaulter list in the form of gridview as roll no and total attendance and aslo want to store the defaulter list

i am using asp.net c# and sql server 2008.

Posted 22-Feb-15 1:51am

batman56

Updated 22-Feb-15 1:53am

v2

Add a Solution

Comments

Wendelius 22-Feb-15 7:54am

Few questions: What have you tried so far? Also can you post example data and the desired result

batman56 22-Feb-15 7:59am

select * , ([19-02-2015] + [20-02-2015] + [21-02-2015]) as Sum from GAP where roll_no=3
for this m getting ans as 2...as roll no 3 present only for 2 days out of 3
but i want to use between any two date,and m not getting any clue how do i write a query for that.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-02-22T02:12:00

Solution 1

I'm not sure if I understood the situation correctly but if you want to sum a value between any two dates one wai is:

SQL

SELECT SUM(ColumnToSum)
FROM TableName
WHERE TableName.DateColumn BETWEEN date1 AND date2

Posted 22-Feb-15 2:12am

Wendelius

Comments

batman56 22-Feb-15 8:21am

thank you sir.
my situation is my column name in database is date(01-01-2015) and i have variable number of columns depend on between what two dates is given(between 10-01-2015 to 15-01-2015 or 10-01-2015 to 22-01-2015)
so i don't know what to write in SUM("..........") because of variable numbre of columns.

Wendelius 22-Feb-15 8:25am

I'm sorry but I don't quite understand. A table cannot have variable number of columns. The columns in a table are fixed.

Can you post few rows as an example data so it would be easier to understand the problem.

batman56 22-Feb-15 8:31am

sir,
my database is of attendance so every day a new column is created with today's date
and for that attendance of student is stored
eg
roll_no|01-01-2015|.02.03.04.05...31-01-2015
1 | 1 | 0 | 1 | 1 | 0| .....
2 | 0 | 1 | 1 |....
like that every day a new column is added
and attendance for each roll no is store as 1 as present or 0 as absent

Wendelius 22-Feb-15 8:40am

Okay, I see. I truly believe that you shouldn't use this kind of design. Adding the columns daily is where your problem starts.

Instead of adding new columns, add new rows. So if you change the table to something like:
- roll_no
- attendance_date, date column
- present, 0 or 1

Then you can easily insert and query the data with a query like I wrote in the solution.

The main thing is that relational databases and SQL are designed to work with static structures. This is one of the reasons database normalization focuses on not repeating data and creating fixed entities.

batman56 22-Feb-15 8:42am

thank you sir.
i will try to redesign my database.

Wendelius 22-Feb-15 8:45am

You're welcome :)