October 3, 2012 at 3:13 pm
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
cStudentID iSchoolCode cGradeCode iMemb Absences
003491714 469 12 5 1
so please help me to build this logic.
Thanks
October 3, 2012 at 3:44 pm
October 3, 2012 at 3:44 pm
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:
October 4, 2012 at 7:29 am
Ray M (10/3/2012)
Something likeSELECT
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
cStudentID iSchoolCode cGradeCode iMemb_5Days Absences_5Days iMemb_10Days Absences_10Days iMemb_TotalDays Absences_TotalDays
003491714 469 12 5 1 10 3 15 3
Then can you please suggests me how to do this?
October 4, 2012 at 10:21 am
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.
October 4, 2012 at 12:54 pm
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? 🙂
October 4, 2012 at 1:20 pm
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
October 5, 2012 at 12:58 pm
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.
October 5, 2012 at 2:02 pm
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