T-SQL help

  • Hello

    I have one task

    CREATE TABLE #x1(

    [cStudentId] [char](9) NOT NULL,

    [iSchoolCode] [int] NOT NULL,

    [dtEnrollmentDate] [datetime] NOT NULL,

    [cGradeCode] [char](2) NOT NULL,

    [cAbsenceCode] [char](1) NOT NULL,

    [vcAbsenceType] [varchar](20) NOT NULL,

    [iSchoolDayCategoryCode] [int] NOT NULL

    )

    insert into #x1 values ('003491714',469,'2012-08-30 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-08-31 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-09-04 00:00:00.000',12,'A','',8)

    insert into #x1 values ('003491714',469,'2012-09-05 00:00:00.000',12,'A','',8)

    insert into #x1 values ('003491714',469,'2012-09-06 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-09-07 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-09-10 00:00:00.000',12,'E','Excused',8)

    insert into #x1 values ('003491714',469,'2012-09-11 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-09-12 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-09-13 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-09-14 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-09-18 00:00:00.000',12,'P','Excused',8)

    insert into #x1 values ('003491714',469,'2012-09-19 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-09-20 00:00:00.000',12,'P','',8)

    insert into #x1 values ('003491714',469,'2012-09-21 00:00:00.000',12,'U','UnExcused',8)

    so here is dtEnrollmentDate for one student and date represent schoolDates(means not actual date).

    so the requirement is,

    Calculating Most Recent 5 day of Membership Days, Absences (if it is in 'E' and 'U' Absence code)

    when i use enddate in filter as '2012-09-21' and do date difference as -5, it calculate

    Membership Days and Absences between '2012-09-21' and '2012-09-17'.

    But as per requirement it most recent 5 dates between '2012-09-21' and '2012-09-14'

    Desired Output

    cStudentIDiSchoolCodecGradeCodeiMembAbsences

    0034917144691251

    so please help me to build this logic.

    Thanks

  • Looks like you want someone to do your homework.

    If you want help, post what you're trying and specifics of how your efforts are not getting you the data you're expecting.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Something like

    SELECT

    temp.cStudentId,

    temp.iSchoolCode,

    temp.cGradeCode,

    COUNT(1) AS iMemb,

    SUM(CASE WHEN temp.cAbsenceCode IN ('E','U') THEN 1 ELSE 0 END) AS Absences

    FROM (

    SELECT

    ROW_NUMBER() OVER(PARTITION BY x.cStudentId ORDER BY dtEnrollmentDate Desc ) AS DaySeq,

    x.*

    FROM #x1 x) temp

    WHERE DaySeq < 6

    GROUP BY temp.cStudentId,

    temp.iSchoolCode,

    temp.cGradeCode

    What!!!!???? Homework, crap screwed that one. :crazy:

  • Ray M (10/3/2012)


    Something like

    SELECT

    temp.cStudentId,

    temp.iSchoolCode,

    temp.cGradeCode,

    COUNT(1) AS iMemb,

    SUM(CASE WHEN temp.cAbsenceCode IN ('E','U') THEN 1 ELSE 0 END) AS Absences

    FROM (

    SELECT

    ROW_NUMBER() OVER(PARTITION BY x.cStudentId ORDER BY dtEnrollmentDate Desc ) AS DaySeq,

    x.*

    FROM #x1 x) temp

    WHERE DaySeq < 6

    GROUP BY temp.cStudentId,

    temp.iSchoolCode,

    temp.cGradeCode

    What!!!!???? Homework, crap screwed that one. :crazy:

    Thanks for Your reply.

    Now if the time window is expand and desired output like

    Desired Output

    cStudentIDiSchoolCodecGradeCodeiMemb_5DaysAbsences_5DaysiMemb_10DaysAbsences_10DaysiMemb_TotalDaysAbsences_TotalDays

    0034917144691251103153

    Then can you please suggests me how to do this?

  • Remove the < 6 from the where clause,

    Then use the case statement to create the new derived columns you need.

    Try it and get as far as you can, then post what you have and we'll help from there.

  • Ray M (10/4/2012)


    Remove the < 6 from the where clause,

    Then use the case statement to create the new derived columns you need.

    Try it and get as far as you can, then post what you have and we'll help from there.

    Not able to get it, can you help me out? 🙂

  • Ray M (10/4/2012)


    Remove the < 6 from the where clause,

    Then use the case statement to create the new derived columns you need.

    Try it and get as far as you can, then post what you have and we'll help from there.

    Will this work? (I know its messy):-)

    SELECT

    temp.cStudentId,

    temp.iSchoolCode,

    temp.cGradeCode,

    sum(case when temp.dayseq <6 then 1 else 0 end) AS iMemb_5days,

    sum(case

    when temp.dayseq < 6 and temp.cabsencecode in ('e','u')THEN 1 ELSE 0 END) AS Absence_5days,

    sum(case when temp.dayseq <11 then 1 else 0 end) AS iMemb_10days,

    sum(case

    when temp.dayseq < 11 and temp.cabsencecode in ('e','u')THEN 1 ELSE 0 END) AS Absence_10days,

    sum(case when temp.dayseq <16 then 1 else 0 end) AS iMemb_15days,

    sum(case

    when temp.dayseq < 16 and temp.cabsencecode in ('e','u')THEN 1 ELSE 0 END) AS Absence_15days,

    COUNT(1) as total_days,

    sum(case

    when temp.cabsencecode in ('e','u')THEN 1 ELSE 0 END) AS Absence_total

    FROM (

    SELECT

    ROW_NUMBER() OVER(PARTITION BY x.cStudentId ORDER BY dtEnrollmentDate Desc ) AS DaySeq,

    x.*

    FROM #x1 x) temp

    GROUP BY temp.cStudentId,

    temp.iSchoolCode,

    temp.cGradeCode

    Please suggest for any improvement.

    The number of days could be directly mentioned without the calculations but did it the opp way:-D

  • Ya, looks like it is satisfying your request. Is it the output you want?

    Now, when using the query on your "Real" Table you may need to look at the table to make sure it performs well.

  • Ray M (10/5/2012)


    Ya, looks like it is satisfying your request. Is it the output you want?

    Now, when using the query on your "Real" Table you may need to look at the table to make sure it performs well.

    Thank You so much.

    Sorry for Late reply. I use this logic in real-time and it works fine.

Viewing 9 posts - 1 through 8 (of 8 total)

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