October 8, 2012 at 3:12 pm
Hello Friends
i have one task, in that i need to delete records from tables and keep only 5th rows data.
so please help me to build this logic.
CREATE TABLE #temp1(
[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,
[dtEntryDate] [datetime] NOT NULL
)
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-01')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-02')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Excused',8,'2012-10-03')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-04')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-05')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-06')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-07')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Excused',8,'2012-10-08')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Excused',8,'2012-10-09')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Excused',8,'2012-10-10')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-11')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-12')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-13')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-14')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Excused',8,'2012-10-15')
insert into #temp1 values ('003491714',469,'2012-10-08','12','E','Present',8,'2012-10-16')
Desired output is
cStudentIdiSchoolCodedtEnrollmentDatecGradeCodecAbsenceCodevcAbsenceTypeiSchoolDayCategoryCodedtEntryDate
0034917144692012-10-08 00:00:00.00012EPresent82012-10-05 00:00:00.000
0034917144692012-10-08 00:00:00.00012EExcused82012-10-10 00:00:00.000
0034917144692012-10-08 00:00:00.00012EExcused82012-10-15 00:00:00.000
October 8, 2012 at 3:14 pm
why the requirement not to use a CTE? Sounds like homework or an interview question to me.
EDIT: OP removed the requirement for with out a CTE.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 8, 2012 at 3:25 pm
Well i have an answer but im going to ask what have you tried?
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
October 8, 2012 at 4:17 pm
capnhector (10/8/2012)
Well i have an answer but im going to ask what have you tried?
so far, i did
SELECT
*
FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY x.cStudentId,x.dtEnrollmentDate ORDER BY dtEnrollmentDate asc ) AS DSeq,
x.*
FROM #temp1 x) temp
WHERE DSeq not in (5,10,15)
but when i try to wrote delete syntax then i got an error
so please help me to do this
October 8, 2012 at 4:30 pm
capnhector (10/8/2012)
Well i have an answer but im going to ask what have you tried?
I got it
DELETE temp
FROM (
SELECT
ROW_NUMBER() OVER(PARTITION BY x.cStudentId,x.dtEnrollmentDate ORDER BY dtEnrollmentDate asc ) AS DSeq,
x.*
FROM #temp1 x) temp
WHERE DSeq not in (5,10,15)
October 9, 2012 at 1:41 am
You can use a Modulo operator to remove the hard coding of values
DELETEtemp
FROM(
SELECTROW_NUMBER() OVER(PARTITION BY x.cStudentId,x.dtEnrollmentDate ORDER BY dtEnrollmentDate asc ) AS DSeq, x.*
FROM#temp1 x
) temp
WHERE ( DSeq % 5 ) > 0
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply