Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query Help Expand / Collapse
Author
Message
Posted Monday, October 8, 2012 3:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:30 PM
Points: 120, Visits: 346
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

cStudentId	iSchoolCode	dtEnrollmentDate	cGradeCode	cAbsenceCode	vcAbsenceType	iSchoolDayCategoryCode	dtEntryDate

003491714 469 2012-10-08 00:00:00.000 12 E Present 8 2012-10-05 00:00:00.000
003491714 469 2012-10-08 00:00:00.000 12 E Excused 8 2012-10-10 00:00:00.000
003491714 469 2012-10-08 00:00:00.000 12 E Excused 8 2012-10-15 00:00:00.000


Post #1370076
Posted Monday, October 8, 2012 3:14 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1370077
Posted Monday, October 8, 2012 3:25 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
Well i have an answer but im going to ask what have you tried?


For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1370085
Posted Monday, October 8, 2012 4:17 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:30 PM
Points: 120, Visits: 346
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
Post #1370097
Posted Monday, October 8, 2012 4:30 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 2:30 PM
Points: 120, Visits: 346
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)

Post #1370099
Posted Tuesday, October 9, 2012 1:41 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
You can use a Modulo operator to remove the hard coding of values

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 % 5 ) > 0




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1370200
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse