October 31, 2012 at 10:35 am
Hello
I am stuck in one place, need help
CREATE TABLE #x1(
[iSchoolCode] [int] NOT NULL,
[iSchoolYearCode] [int] NOT NULL,
[dtCalendarDay] [datetime] NOT NULL,
[DaySeq] [int] NOT NULL
)
insert into #x1 values (356,2012,'2012-10-02 00:00:00.000',21)
insert into #x1 values (356,2012,'2012-10-03 00:00:00.000',22)
insert into #x1 values (356,2012,'2012-10-04 00:00:00.000',23)
insert into #x1 values (356,2012,'2012-10-05 00:00:00.000',24)
insert into #x1 values (356,2012,'2012-10-08 00:00:00.000',25)
insert into #x1 values (356,2012,'2012-10-09 00:00:00.000',26)
insert into #x1 values (356,2012,'2012-10-10 00:00:00.000',27)
insert into #x1 values (356,2012,'2012-10-11 00:00:00.000',28)
insert into #x1 values (234,2012,'2012-10-12 00:00:00.000',28)
insert into #x1 values (234,2012,'2012-10-15 00:00:00.000',29)
insert into #x1 values (234,2012,'2012-10-16 00:00:00.000',30)
insert into #x1 values (234,2012,'2012-10-17 00:00:00.000',31)
insert into #x1 values (234,2012,'2012-10-18 00:00:00.000',32)
insert into #x1 values (234,2012,'2012-10-19 00:00:00.000',33)
insert into #x1 values (234,2012,'2012-10-22 00:00:00.000',34)
insert into #x1 values (234,2012,'2012-10-23 00:00:00.000',35)
insert into #x1 values (234,2012,'2012-10-24 00:00:00.000',36)
insert into #x1 values (234,2012,'2012-10-25 00:00:00.000',37)
insert into #x1 values (234,2012,'2012-10-26 00:00:00.000',38)
insert into #x1 values (234,2012,'2012-10-29 00:00:00.000',39)
insert into #x1 values (234,2012,'2012-10-30 00:00:00.000',40)
CREATE TABLE #x2(
[cStudentId] [char](9) NOT NULL,
[iSchoolCode] [int] NOT NULL,
[dtEnrollmentDate] [datetime] NOT NULL,
[cGradeCode] [char](2) NOT NULL,
[cAbsenceCode] [char](1) NOT NULL,
) ON [PRIMARY]
insert into #x2 values ('004005600',356,'2012-10-02 00:00:00.000','07','P')
insert into #x2 values ('004005600',356,'2012-10-03 00:00:00.000','07','P')
insert into #x2 values ('004005600',356,'2012-10-04 00:00:00.000','07','P')
insert into #x2 values ('004005600',356,'2012-10-05 00:00:00.000','07','P')
insert into #x2 values ('004005600',356,'2012-10-08 00:00:00.000','07','P')
insert into #x2 values ('004005600',356,'2012-10-09 00:00:00.000','07','P')
insert into #x2 values ('004005600',356,'2012-10-10 00:00:00.000','07','P')
insert into #x2 values ('004005600',356,'2012-10-11 00:00:00.000','07','P')
insert into #x2 values ('004005600',234,'2012-10-12 00:00:00.000','07','P')
insert into #x2 values ('004005600',234,'2012-10-15 00:00:00.000','07','E')
insert into #x2 values ('004005600',234,'2012-10-16 00:00:00.000','07','E')
insert into #x2 values ('004005600',234,'2012-10-17 00:00:00.000','07','E')
insert into #x2 values ('004005600',234,'2012-10-18 00:00:00.000','07','U')
insert into #x2 values ('004005600',234,'2012-10-19 00:00:00.000','07','P')
insert into #x2 values ('004005600',234,'2012-10-22 00:00:00.000','07','P')
insert into #x2 values ('004005600',234,'2012-10-23 00:00:00.000','07','P')
insert into #x2 values ('004005600',234,'2012-10-24 00:00:00.000','07','P')
insert into #x2 values ('004005600',234,'2012-10-25 00:00:00.000','07','P')
insert into #x2 values ('004005600',234,'2012-10-26 00:00:00.000','07','P')
insert into #x2 values ('004005600',234,'2012-10-29 00:00:00.000','07','P')
insert into #x2 values ('004005600',234,'2012-10-30 00:00:00.000','07','P')
i develop the logic as
select distinct
E.cStudentId,
E.iSchoolCode,
E.dtEnrollmentDate,
E.cGradeCode,
E.cAbsenceCode,
sc.DaySeq
from #x2 E
join #x1 sc
on E.iSchoolCode = sc.iSchoolCode
and E.dtEnrollmentDate = sc.dtCalendarDay
where sc.DaySeq between 21 and 40
order by E.dtEnrollmentDate
in this case schoolcode 356 and 254 has same DaySeq number as 28
so i have trouble to find date range for last 20 school days
but it will give me out put as
cStudentIdiSchoolCodedtEnrollmentDatecGradeCodecAbsenceCodeDaySeq
0040056003562012-10-02 00:00:00.00007P21
0040056003562012-10-03 00:00:00.00007P22
0040056003562012-10-04 00:00:00.00007P23
0040056003562012-10-05 00:00:00.00007P24
0040056003562012-10-08 00:00:00.00007P25
0040056003562012-10-09 00:00:00.00007P26
0040056003562012-10-10 00:00:00.00007P27
0040056003562012-10-11 00:00:00.00007P28
0040056002342012-10-12 00:00:00.00007P28
0040056002342012-10-15 00:00:00.00007E29
0040056002342012-10-16 00:00:00.00007E30
0040056002342012-10-17 00:00:00.00007E31
0040056002342012-10-18 00:00:00.00007U32
0040056002342012-10-19 00:00:00.00007P33
0040056002342012-10-22 00:00:00.00007P34
0040056002342012-10-23 00:00:00.00007P35
0040056002342012-10-24 00:00:00.00007P36
0040056002342012-10-25 00:00:00.00007P37
0040056002342012-10-26 00:00:00.00007P38
0040056002342012-10-29 00:00:00.00007P39
0040056002342012-10-30 00:00:00.00007P40
but desired output like
cStudentIdiSchoolCodedtEnrollmentDatecGradeCodecAbsenceCodeDaySeq
0040056003562012-10-03 00:00:00.00007P22
0040056003562012-10-04 00:00:00.00007P23
0040056003562012-10-05 00:00:00.00007P24
0040056003562012-10-08 00:00:00.00007P25
0040056003562012-10-09 00:00:00.00007P26
0040056003562012-10-10 00:00:00.00007P27
0040056003562012-10-11 00:00:00.00007P28
0040056002342012-10-12 00:00:00.00007P28
0040056002342012-10-15 00:00:00.00007E29
0040056002342012-10-16 00:00:00.00007E30
0040056002342012-10-17 00:00:00.00007E31
0040056002342012-10-18 00:00:00.00007U32
0040056002342012-10-19 00:00:00.00007P33
0040056002342012-10-22 00:00:00.00007P34
0040056002342012-10-23 00:00:00.00007P35
0040056002342012-10-24 00:00:00.00007P36
0040056002342012-10-25 00:00:00.00007P37
0040056002342012-10-26 00:00:00.00007P38
0040056002342012-10-29 00:00:00.00007P39
0040056002342012-10-30 00:00:00.00007P40
in my logic the filter sc.DaySeq between 21 and 40 has remain same.
because for other case it works fine.
Please guide me to do this.
Thanks
October 31, 2012 at 11:14 am
Pull your top 20 dates with a subquery first.
SELECT E.cStudentId ,
E.iSchoolCode ,
E.dtEnrollmentDate ,
E.cGradeCode ,
E.cAbsenceCode ,
sc.DaySeq
FROM ( SELECT TOP 20
sc.dtCalendarDay ,
sc.DaySeq
FROM #x1 SC
WHERE SC.DaySeq BETWEEN 21 AND 40
ORDER BY dtCalendarDay DESC
) SC
INNER JOIN #X2 E ON sc.dtCalendarDay = E.dtEnrollmentDate
ORDER BY E.dtEnrollmentDate
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply