How to generate week ranges like this picture in sql
-
Hi! I need to generate the week ranges like this format :
Here from date and to date would be picked up from the table but just to make you understand i have hardcoded it but this is the real date which is falling inside the table.
Note : Week should be generated from Monday to Sunday within desired date range
Please help.
-
Just so I'm clear you wish the dates to display as Monday - Sunday, but if the fromDate is not a monday, you wish to start on that day and similar to finish on the toDate if it is not a sunday?
This should get you started
DECLARE@FromDate DATETIME = '20140701',
@ToDate DATETIME = '20140731';
SELECTWeekNum = N + 1,
WeekStart = CASE WHEN CA1.WeekStart < @FromDate THEN @FromDate ELSE CA1.WeekStart END,
WeekEnd = CASE WHEN CA1.WeekEnd > @ToDate THEN @ToDate ELSE CA1.WeekEnd END
FROMdbo.GetNums(0,DATEDIFF(WK,@FromDate,@ToDate))
CROSS
APPLY(
SELECTDATEADD(WEEK,N,DATEADD(DAY,DATEDIFF(DD,0,@FromDate)/7*7,0)),
DATEADD(WEEK,N+1,DATEADD(DAY,DATEDIFF(DD,0,@FromDate)/7*7-1,0))
) AS CA1(WeekStart,WeekEnd)
Last week in your example starts on the 27th, think it should be the 28th.
-
Kindly reply complete solution . Also note i am using SQL 2012
-
I can explain the code sample if you're unsure how it works.
The sample uses a tally or numbers table to help generate the date ranges from the date variables provided (For more information on the tally table it is worth searching this site where there are many articles for it). Assuming you don't have one here is a small example below producing 100 row number table.
--Create Small Numbers TableWITH A(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N)),
B(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM A T1 CROSS JOIN A T2
)
SELECTB.N,
--Date
GETDATE(),
--Date incremented by numbers table
DATEADD(DAY,N,GETDATE())
FROMB
This helps us to create a range of dates. The other part is to find the Monday and Sunday
SELECTGETDATE(),
DATEDIFF(DAY,0,GETDATE()),
--Start of day
DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0),
--Start of week (Monday)
DATEADD(WEEK,0,DATEADD(DAY,DATEDIFF(DD,0,GETDATE())/7*7,0))
More reading on this here http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/.
You can then combine both of these to produce the range of weeks between the two date variables in your original question
-
This is the solution :
DECLARE @startDate date , @endDate date;
SET @startDate = ( SELECT proj_start_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1)
SET @endDate = ( SELECT proj_end_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1);
with tmp(plant_date) as
(
select cast(@startDate as datetime)
union all
select plant_date + 1
from tmp
where plant_date < @endDate
)
SELECT
'Week '+CAST(ROW_NUMBER () OVER (ORDER BY start_of_week) AS VARCHAR(20)),
start_of_week,
DATEADD(day, 6, start_of_week) AS end_of_week
FROM tmp
CROSS APPLY ( VALUES( DATEPART(weekday, DATEADD(day, @@DATEFIRST -1, plant_date)) - 1 ) ) AS A1(dist)
CROSS APPLY ( VALUES( DATEADD(day, -dist, plant_date) ) ) AS A2(start_of_week)
GROUP BY start_of_week
option (maxrecursion 0)
-
emadkhanqai (5/28/2015)
This is the solution :DECLARE @startDate date , @endDate date;
SET @startDate = ( SELECT proj_start_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1)
SET @endDate = ( SELECT proj_end_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1);
with tmp(plant_date) as
(
select cast(@startDate as datetime)
union all
select plant_date + 1
from tmp
where plant_date < @endDate
)
SELECT
'Week '+CAST(ROW_NUMBER () OVER (ORDER BY start_of_week) AS VARCHAR(20)),
start_of_week,
DATEADD(day, 6, start_of_week) AS end_of_week
FROM tmp
CROSS APPLY ( VALUES( DATEPART(weekday, DATEADD(day, @@DATEFIRST -1, plant_date)) - 1 ) ) AS A1(dist)
CROSS APPLY ( VALUES( DATEADD(day, -dist, plant_date) ) ) AS A2(start_of_week)
GROUP BY start_of_week
option (maxrecursion 0)
I've amended by original to include how you set the date variables
DECLARE@FromDate DATETIME = (SELECT proj_start_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1),@ToDate DATETIME = (SELECT proj_end_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1);
SELECTWeekNum = N + 1,
WeekStart = CASE WHEN CA1.WeekStart < @FromDate THEN @FromDate ELSE CA1.WeekStart END,
WeekEnd = CASE WHEN CA1.WeekEnd > @ToDate THEN @ToDate ELSE CA1.WeekEnd END
FROMdbo.GetNums(0,DATEDIFF(WK,@FromDate,@ToDate))
CROSS
APPLY(
SELECTDATEADD(WEEK,N,DATEADD(DAY,DATEDIFF(DD,0,@FromDate)/7*7,0)),
DATEADD(WEEK,N+1,DATEADD(DAY,DATEDIFF(DD,0,@FromDate)/7*7-1,0))
) AS CA1(WeekStart,WeekEnd)
Using a recursive CTE will likely be slower, but the most important thing is you understand how the code works, if you do then best to go with it
-
By using your solution it is giving me error:
-
That's a function I use to generate a numbers table
this is the function below, I imagine some here have one with a few extra performance tweaks
CREATE FUNCTION [dbo].[GetNums]
(
@low AS BIGINT,
@high AS BIGINT
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
FROM L5)
SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
FROM Nums
ORDER BY rownum;
Its use:
SELECT*
FROMdbo.GetNums(1,10)
SELECT*
FROMdbo.GetNums(1,100)
SELECT*
FROMdbo.GetNums(1,1000)
SELECTN,
GETDATE(),
--Incrementing days from todays date
DATEADD(DAY,N,GETDATE())
FROMdbo.GetNums(1,100)
I suggest you read up on this form for the uses of a numbers/tally table, Jeff's original article is a great starter for 10 http://www.sqlservercentral.com/articles/T-SQL/62867/
There are plenty of very good uses for it, very good tool for the toolkit
-
emadkhanqai (5/28/2015)
This is the solution :DECLARE @startDate date , @endDate date;
SET @startDate = ( SELECT proj_start_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1)
SET @endDate = ( SELECT proj_end_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1);
with tmp(plant_date) as
(
select cast(@startDate as datetime)
union all
select plant_date + 1
from tmp
where plant_date < @endDate
)
SELECT
'Week '+CAST(ROW_NUMBER () OVER (ORDER BY start_of_week) AS VARCHAR(20)),
start_of_week,
DATEADD(day, 6, start_of_week) AS end_of_week
FROM tmp
CROSS APPLY ( VALUES( DATEPART(weekday, DATEADD(day, @@DATEFIRST -1, plant_date)) - 1 ) ) AS A1(dist)
CROSS APPLY ( VALUES( DATEADD(day, -dist, plant_date) ) ) AS A2(start_of_week)
GROUP BY start_of_week
option (maxrecursion 0)
suggest that you quote and give credit to those that have helped you
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
emadkhanqai (5/28/2015)
This is the solution :DECLARE @startDate date , @endDate date;
SET @startDate = ( SELECT proj_start_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1)
SET @endDate = ( SELECT proj_end_date FROM [BSD].[dbo].[d_project_p] WHERE proj_id = 1);
with tmp(plant_date) as
(
select cast(@startDate as datetime)
union all
select plant_date + 1
from tmp
where plant_date < @endDate
)
SELECT
'Week '+CAST(ROW_NUMBER () OVER (ORDER BY start_of_week) AS VARCHAR(20)),
start_of_week,
DATEADD(day, 6, start_of_week) AS end_of_week
FROM tmp
CROSS APPLY ( VALUES( DATEPART(weekday, DATEADD(day, @@DATEFIRST -1, plant_date)) - 1 ) ) AS A1(dist)
CROSS APPLY ( VALUES( DATEADD(day, -dist, plant_date) ) ) AS A2(start_of_week)
GROUP BY start_of_week
option (maxrecursion 0)
Actually, not a good solution at all because it uses an rCTE that counts. Please see the following article for why that's such a bad thing.
http://www.sqlservercentral.com/articles/T-SQL/74118/
I also recommend that you do a search for "Calendar Tables" and also lookup ISOWeek in "Books Online".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I wrote a numbers function some time ago that is designed for exactly this type of thing.
IF OBJECT_ID('dbo.GetNumsAB','IF') IS NOT NULL
DROP FUNCTION dbo.GetNumsAB;
GO
CREATE FUNCTION dbo.GetNumsAB(@low int, @high int, @range int)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH
L1(N) AS (SELECT 1 FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL)) t(N)), --5
L4(N) AS (SELECT 1 FROM L1 a CROSS APPLY L1 b CROSS APPLY L1 c CROSS APPLY L1 d), --625
iTally AS
(
SELECT N = CHECKSUM(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)))
FROM L4 a CROSS APPLY L4 b CROSS APPLY L4 c
) --244,140,625
SELECT TOP (ABS((@high-@low)/ISNULL(NULLIF(@range,0),1)+1))
rn = N,
n1 = ((N-1)*@range+@low),
n2 = (( N )*@range+@low)
FROM iTally
WHERE @high >= @low;
GO
-- Examples:
SELECT * FROM dbo.GetNumsAB(1,5,1);
SELECT * FROM dbo.GetNumsAB(0,10,2);
SELECT * FROM dbo.GetNumsAB(0,1000,250);
It's similar to Itzik Ben-Gan's getnums function that dohsan posted but allows you to generate a range of numbers. It also does not blow up if @low > @high. N1 in my function is the same as N in Ben-Gan's. RN is the same as ROW_NUMBER() in that it always starts at 1...n where n is the last row.
Either way this solution or dohsan's solution will blow the doors off the recursive CTE solution above and posted on MSDN.
Using getnumsAB you can do this:
DECLARE
@FromDate Date = '7/1/2014',
@ToDate Date = '7/31/2015';
SELECT
WeekNbr = rn,
StartDay = DATEADD(DAY,n1,@FromDate),
EndDay = DATEADD(DAY,n2-1,@FromDate)
FROM dbo.GetNumsAB(0,DATEDIFF(DAY,@FromDate,@ToDate),7);
To start on Sunday you could update the @StartDate Variable like so:
SELECT @FromDate = DATEADD(DAY,(DATEPART(WEEKDAY,@FromDate)-1)*-1,@FromDate);"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply