Count between two days

  • 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

  • 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
  • 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

  • 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

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

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