skip rows from result set - 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)

skip rows from result set

Author

Topic

safderalimd
Starting Member

32 Posts

Posted - 2009-08-26 : 15:21:03

I am looking for a SQL that can fetch me rows for all mondays with in a date range from date_table by skipping 1 week at a time. The SQL must be generic to accept integer parameter to skip 1 week or 2 week or 3 weeks.

Here is SQL script to create and populate date_table.

--create table
CREATE TABLE [dbo].[date_table](
day_date smalldatetime NULL,
week_day tinyint)

--populate data
declare @x smalldatetime

select @x = '2008-01-01'
while @x < '2009-01-01'
begin
Insert into dbo.date_table
Select @x
,case when DATENAME(dw , @x) = 'sunday' then 1
when DATENAME(dw , @x) = 'monday' then 2
when DATENAME(dw , @x) = 'tuesday' then 3
when DATENAME(dw , @x) = 'wednesday' then 4
when DATENAME(dw , @x) = 'thursday' then 5
when DATENAME(dw , @x) = 'friday' then 6
when DATENAME(dw , @x) = 'saturday' then 7
end

select @x = dateadd(dd,1,@x)
end

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2009-08-27 : 11:10:20

I find it useful to have a generic "Calendar" table. It just comes in handy for a variety of applications. If you assume the existence of this table your query becomes simple:

insert into date_table
select DayDate
from Calendar
where DayOfWeek = 'Monday
and '2008-01-01' <= DayDate
and DayDate < '2009-01-01'

BTW, a generic Numbers table is also useful.

=======================================
Men build too many walls and not enough bridges. -Isaac Newton, philosopher and mathematician (1642-1727)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources