Count between two days

Question

Post reply

Count between two days

ramaanujamr

Old Hand

Points: 366
More actions
September 23, 2008 at 8:48 am

#213009

I have student attendance data. I want to calculate total number of school days between startdate and End date .I could write sql for startdate and End Date but eventhough i couldnot get it in the sameline. Startdate takes one line and End Date takes another line. I want to have all in one line. I am blank to calculate count between 2 days because i cant control startdate and enddate in one line.

i want like Like

studentno Startdate EndDate numberofdaysattended

942531 9/10/2007 6/24/2008 178

Startdate indicates in attendance as 'S'

Enddate indicate in attendance as ' E'

178 is only those value marked with characters. '-' is holiday

I have attached xls file for your review

Thanks

Ram

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Quatrei.X SSCrazy Points: 2697 More actions · Answer 1

Hi there,

Hope this helps... By the way, is the excel sample data from Excel or from SQL?

If from SQL, try this one...



DECLARE @EndDate DATETIME, @StartDate DATETIME

SELECT @StartDate=[Column_A] WHERE [Column_C]='S'

SELECT @EndDate=[Column_A] WHERE [Column_C]='E'

SELECT COUNT(1) FROM [Table]

WHERE [Column_C] <> '-'

AND [Column_A] BETWEEN @EndDate AND @StartDate

By the way, you'll be getting an error if there are more than 1 S or E in the table or if one or both of them does not exists. We'll if thats your case, try experementing with this code or ask again how. We'll be here to answer your questions.

Hope it helps...

Please tell me if this was helpful or if it needs some modifications... thanks ^__^

_____________________________________________
[font="Comic Sans MS"]Quatrei Quorizawa[/font]
:):D:P;):w00t::cool::hehe:
MABUHAY PHILIPPINES!

"Press any key...
Where the heck is the any key?
hmmm... Let's see... there's ESC, CTRL, Page Up...
but no any key"
- Homer Simpson

ramaanujamr Old Hand Points: 366 More actions · Answer 2

Hi,

It is from SQL. created a view from 3 tables.

Your sql gives an error. It didnot understand the coulmn name

It produces error .

DECLARE @EndDate DATETIME, @StartDate DATETIME

SELECT @StartDate=Date WHERE Attendance='S'

SELECT @EndDate=Date WHERE Attendance='E'

SELECT COUNT(1) FROM studentattendanceinfo

WHERE Attendance <> '-'

AND Date BETWEEN @EndDate AND @StartDate

It says invalid column name for date,attendance. Actually i did copy and paste

Thanks

Ram

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hi Ram

I'm not exactly sure what you're looking for: whether it's start and end date in the same row to get the days difference between the two, or to perform further processing on your table. The following statement gives start and end date in the same row and then joins back to the original table - this will give you some pointers about how to extract information from your data set. Note the use of MIN and MAX. If there's only one startdate and one end date per student, then the use of MIN and MAX is arbitrary, they are there to allow the aggregate to operate on the event date. If there's more than one start date or end date, then the use of MIN and MAX should be investigated further.

Cheers

ChrisM

[font="Courier New"]DROP TABLE #Attendance



CREATE TABLE #Attendance (EventDate DATETIME, studentno INT, attendance CHAR(1))

INSERT INTO #Attendance

SELECT '05/09/2007', 942531, '-' UNION ALL

SELECT '06/09/2007', 942531, 'D' UNION ALL

SELECT '07/09/2007', 942531, NULL UNION ALL

SELECT '08/09/2007', 942531, '-' UNION ALL

SELECT '09/09/2007', 942531, '-' UNION ALL

SELECT '10/09/2007', 942531, 'S' UNION ALL

SELECT '11/09/2007', 942531, 'P' UNION ALL

SELECT '12/09/2007', 942531, 'P' UNION ALL

SELECT '13/09/2007', 942531, 'P' UNION ALL

SELECT '14/09/2007', 942531, 'X' UNION ALL

SELECT '15/09/2007', 942531, '-' UNION ALL

SELECT '16/09/2007', 942531, '-' UNION ALL

SELECT '23/06/2008', 942531, 'C' UNION ALL

SELECT '24/06/2008', 942531, 'E'

SELECT a.*, '#' AS '#', d.*

FROM #Attendance a

INNER JOIN (

   SELECT studentno, MAX(CASE attendance WHEN 'S' THEN EventDate ELSE NULL END) AS STARTDATE,

       MIN(CASE attendance WHEN 'E' THEN EventDate ELSE NULL END) AS ENDDATE

   FROM #Attendance

   WHERE attendance IN ('S', 'E')

   GROUP BY studentno

) d ON d.studentno = a.studentno

[/font]

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden