October 24, 2012 at 7:30 am
Hello Every one
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,
[dtEntryDate] [datetime] NOT NULL
)
insert into #x1 values ('004095871',356,'2012-09-21','09','P','2012-10-24')
insert into #x1 values ('004095871',356,'2012-09-22','09','P','2012-10-24')
insert into #x1 values ('004095871',356,'2012-09-23','09','S','2012-10-24')
insert into #x1 values ('004095871',356,'2012-09-23','09','U','2012-10-24')
insert into #x1 values ('004095871',356,'2012-09-24','09','S','2012-10-24')
insert into #x1 values ('004095871',356,'2012-09-24','09','U','2012-10-24')
insert into #x1 values ('004095871',356,'2012-09-25','09','P','2012-10-24')
insert into #x1 values ('004095871',356,'2012-09-26','09','P','2012-10-24')
SELECT
ROW_NUMBER() OVER(PARTITION BY x.cStudentId ORDER BY dtEnrollmentDate desc ) AS DaySeq,
x.*
from #x1 x
I got output as below
DaySeqcStudentIdiSchoolCodedtEnrollmentDatecGradeCodecAbsenceCodedtEntryDate
10040958713562012-09-26 00:00:00.00009P2012-10-24 00:00:00.000
20040958713562012-09-25 00:00:00.00009P2012-10-24 00:00:00.000
30040958713562012-09-24 00:00:00.00009S2012-10-24 00:00:00.000
40040958713562012-09-24 00:00:00.00009U2012-10-24 00:00:00.000
50040958713562012-09-23 00:00:00.00009S2012-10-24 00:00:00.000
60040958713562012-09-23 00:00:00.00009U2012-10-24 00:00:00.000
70040958713562012-09-22 00:00:00.00009P2012-10-24 00:00:00.000
80040958713562012-09-21 00:00:00.00009P2012-10-24 00:00:00.000
but desired output i need as
DaySeqcStudentIdiSchoolCodedtEnrollmentDatecGradeCodecAbsenceCodedtEntryDate
10040958713562012-09-21 00:00:00.00009P2012-10-24 00:00:00.000
20040958713562012-09-22 00:00:00.00009P2012-10-24 00:00:00.000
30040958713562012-09-23 00:00:00.00009S2012-10-24 00:00:00.000
40040958713562012-09-24 00:00:00.00009S2012-10-24 00:00:00.000
50040958713562012-09-25 00:00:00.00009P2012-10-24 00:00:00.000
60040958713562012-09-26 00:00:00.00009P2012-10-24 00:00:00.000
so please help me to develop logic.
Thanks
October 24, 2012 at 7:34 am
Are you saying that you only need to SELECT the data WHERE cAbsenceCode = 'P' OR cAbsenceCode= 'S'? and ORDER the results BY dtEnrollmentDate?
Thomas Rushton
blog: https://thelonedba.wordpress.com
October 24, 2012 at 7:36 am
????
SELECT
ROW_NUMBER() OVER(PARTITION BY x.cStudentId ORDER BY dtEnrollmentDate desc ) AS DaySeq,
x.*
from #x1 x
where cAbsenceCode <> 'u'
order by dtEnrollmentDate
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 24, 2012 at 7:39 am
Change your DESC to ASC for your ROW_NUMBER to get them in the correct order.
Why do you want to get rid of the records that are gone? Is it because of the cAbsenceCode? Or some other reason?
If it is because the cAbsenceCode is U, try this: -
SELECT ROW_NUMBER() OVER (PARTITION BY x.cStudentId ORDER BY dtEnrollmentDate ASC) AS DaySeq,
[cStudentId], [iSchoolCode], [dtEnrollmentDate], [cGradeCode], [cAbsenceCode], [dtEntryDate]
FROM (SELECT [cStudentId], [iSchoolCode], [dtEnrollmentDate],
[cGradeCode], [cAbsenceCode], [dtEntryDate]
FROM #x1
WHERE [cAbsenceCode] <> 'U') x;
Which produces: -
DaySeq cStudentId iSchoolCode dtEnrollmentDate cGradeCode cAbsenceCode dtEntryDate
-------------------- ---------- ----------- ----------------------- ---------- ------------ -----------------------
1 004095871 356 2012-09-21 00:00:00.000 09 P 2012-10-24 00:00:00.000
2 004095871 356 2012-09-22 00:00:00.000 09 P 2012-10-24 00:00:00.000
3 004095871 356 2012-09-23 00:00:00.000 09 S 2012-10-24 00:00:00.000
4 004095871 356 2012-09-24 00:00:00.000 09 S 2012-10-24 00:00:00.000
5 004095871 356 2012-09-25 00:00:00.000 09 P 2012-10-24 00:00:00.000
6 004095871 356 2012-09-26 00:00:00.000 09 P 2012-10-24 00:00:00.000
If it is for some other reason, you'll need to explain
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply