August 26, 2009 at 1:23 pm
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
August 26, 2009 at 2:14 pm
You could use DATEDIFF and the Modulus operator.
Something like, DATEDIFF(ww, CurrentDate, StartDate) % SkipWeek
So, if you wanted to get the data every 3 weeks, what you would have is
DATEDIFF(ww, CurrentDate, StartDate) % 3
EG:
PRINT DATEDIFF(ww, GETDATE(), GETDATE()) % 3
-- 0
PRINT DATEDIFF(ww, GETDATE(), GETDATE() - 7) % 3
-- 1
PRINT DATEDIFF(ww, GETDATE(), GETDATE() - 14) % 3
-- 2
PRINT DATEDIFF(ww, GETDATE(), GETDATE() - 21) % 3
-- 0
DECLARE @MaxDate SMALLDATETIME
SET @MaxDate = 'SomeDate'
So, SELECT * FROM TABLE WHERE DATEDIFF(ww, CurrentDate, @MaxDate) % 3 = 0
would give you back all the records seperated by a 3 week range
August 27, 2009 at 2:24 am
Here's some code that should give you what you want if I understood your question properly.
You'll want to wrap this up in a stored procedure. Unfortunately doesn't look like the posting's respecting my carriage returns properly, so added comment lines to force blank lines.
begin
-- these would be input params for your stored procedure
declare
@week_day tinyint,
@weeks_between tinyint,
@start_date smalldatetime,
@end_date smalldatetime
--
-- params would normally be passed into your stored proc
select
@week_day = 2, -- 1 for Sunday, 2 for Monday, ... 7 for Saturday
@weeks_between = 3,
@start_date = '2008-01-01',
@end_date = '2009-01-01'
--
-- declare table variable to hold all rows for desired week_day
declare @mydates table (
id int IDENTITY(1,1) NOT NULL, -- this ID column is used later to determine which rows to keep
day_date smalldatetime,
week_day tinyint
)
--
insert into @mydates
(
day_date, week_day
)
(
select day_date, week_day
from date_table
where day_date between @start_date and @end_date -- specifies date range
and week_day = @week_day -- specifies weekday of interest
)
--
declare
@id int,
@max_id int
-- set @id to 1 because the first record is always retained
-- @Max_id holds the total number of @week_day days in the date range
select @id = 1, @max_id = max(id) from @mydates
--
-- another table variable to hold the dates that meet requirements
declare @myfinaldates table (
id int,
day_date smalldatetime,
week_day tinyint
)
--
-- always insert the first row from table @mydates
insert into @myfinaldates
(
id, day_date, week_day
)
(
select id, day_date, week_day from @mydates where id = @id
)
--
-- while there are still dates we haven't looked at
while @id < @max_id
begin
-- determine the next row that we want to keep
-- Formula is always current row + 1 row + @weeks_between rows
-- For example, if @weeks_between = 2, rows to keep are row 1, row 4, row 7...
set @id = @id + 1 + @weeks_between
--
-- insert the next row that we want to keep into the second table variable
insert into @myfinaldates
(
id, day_date, week_day
)
(
select id, day_date, week_day from @mydates where id = @id
)
end
--
-- retrieve all inserted rows
select * from @myfinaldates
end
Riz
August 27, 2009 at 3:26 am
Heres a good resource on calendar tables
Its just a simple query DayMask = 1 Means Mondays ,WeekNo is encoded as YYYYWW
with cteWeeks(dte,WeekNo,RowN)
as
(
Select dte,WeekNo,row_number() over (order by dte)
from cal
where dte between '01jan2009' and '30dec2009'
and DayMask = 1
)
Select * from cteWeeks where Rown %3 = 0
August 27, 2009 at 11:54 am
Thanks for all of your efforts. I got what I was looking for.
August 27, 2009 at 2:22 pm
Hi Dave,
Great article, thanks for pointing it out.
Can you explain your code a bit please? See comments in the code.
with cteWeeks(dte,WeekNo,RowN)
as
(
Select dte,WeekNo,row_number() over (order by dte) -- what does row_number() over (order by dte) do?
from cal
where dte between '01jan2009' and '30dec2009'
and DayMask = 1 -- Is this a column in your calendar table?
)
Select * from cteWeeks where Rown %3 = 0
Your code returns every row number that's evenly divisible by 3 correct?
What if I was looking for the first Monday in the range, then skip 3 Mondays, then get the next Monday, then skip 3 Mondays....
Thanks!
Riz
Riz
August 27, 2009 at 2:42 pm
Hi Riz,
row_number() is documented in bol and returns an incrementing number.
DayMask is a column in my table 1=Monday , 2=Tuesday , 4 = Wednesday , 8 =Thurs etc....
and as for your skip question then that would be "rown % 4 =0"? , again the modulous operator (%) is documented in bol.
August 28, 2009 at 12:42 am
Thanks Dave 🙂 Learned a few things.
Riz
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply