help me to solve the query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help me to solve the query

Author

Topic

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-11-03 : 04:28:08

Hello all,
following are my table data


Sr. No	Year
1	2003
2	2004
3	2005
4	2006
5	2007
6	2008
7	2009
8	2010
9	2011
10	2012
11	2013
12	2014
13	2015
14	2016
15	2017
16	2018
17	2019
18	2020
19	2021
20	2022
21	2023
22	2024
23	2025
24	2026
25	2027
26	2028
27	2029
28	2030
29	2031



and i want output like follow

Sr. No	Year
1	2003
2	2008
3	2013
4	2018
5	2023
6	2028

i want to delete years column records where i want to delete those records which is multiple of 5 means
if min(year) is 2003 then first year should be 2003 and next year will be 2003 + 5 = 2008 , next will be 2008 + 5 =2013 like that
how can i do that

Pankaj

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-11-03 : 05:02:45

Use a number/tally table:


SELECT ROW_NUMBER() OVER (ORDER BY T.[Year]) AS SrNo
	,T.[Year]
FROM YourTable T
	CROSS JOIN
	(
		SELECT MIN(Year) AS MinYear
		FROM YourTable
	) D
	JOIN master.dbo.spt_values N
		ON T.[Year] = D.MinYear + (N.Number * 5)
			AND N.[Type] = 'P'

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-03 : 05:07:00

delete from #temp where s_no in(
select s_no
from #temp where (year_no-(select min(year_no) from #temp))%5>0)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-11-03 : 05:11:34

quote:
Originally posted by senthil_nagore

delete from #temp where s_no in(
select s_no
from #temp where (year_no-(select min(year_no) from #temp))%5>0)

Thank you very much.

Pankaj

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-11-03 : 05:16:28

quote:
Originally posted by pmotewar

quote:
Originally posted by senthil_nagore

delete from #temp where s_no in(
select s_no
from #temp where (year_no-(select min(year_no) from #temp))%5>0)

Thank you very much.

Pankaj

Welcome

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources