SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Displaying a week numbers as a rows for given date range


Displaying a week numbers as a rows for given date range

Author
Message
phoenix_
phoenix_
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 31
Hi,
I would like to display a week number as a row for given date range. After all I want to join this with another data.
So having a start date and end date as a parameter I would like to receive as many rows as we have weeks in given date range i.e.
week start date = Monday
start date = 2013.01.03
end date = 2013.01.23

expected results:

week_nbr start_date end_date
1 2013-01-03 2013-01-05
2 2013-01-06 2013-01-12
3 2013-01-13 2013-01-19
4 2013-01-20 2013-01-23


Kingston Dhasian
Kingston Dhasian
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11957 Visits: 5339
DECLARE   @start_date DATE, @end_date DATE

SELECT @start_date = '20130103', @end_date = '20130123'

SELECT DATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_date
FROM (
SELECT DATEADD(DAY,sv.number,@start_date) AS Dt
FROM master.dbo.spt_values AS sv -- You can use TALLY table instead of this
WHERE sv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)
) AS Dates
GROUP BY DATEPART(WEEK,Dates.Dt)



You can find the script to generate a TALLY table below
http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/


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/
laurie-789651
laurie-789651
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3870 Visits: 1272
A lot of people set up a Calendar table specifically for this kind of thing -

see http://www.sqlservercentral.com/scripts/Date/68389/ for example.

Using this as a base, I came up with this:



DECLARE @StartDate Date,
@EndDate Date,
@WeekStart TinyInt;

SELECT @StartDate = '03 Jan 2013',
@EndDate = '23 Jan 2013',
@WeekStart = 1;

WITH cte_date_base_table AS
(
SELECT
@StartDate AS calendar_date

UNION ALL

SELECT
DATEADD (DAY, 1, CTE.calendar_date)
FROM
cte_date_base_table CTE
WHERE
DATEADD (DAY, 1, CTE.calendar_date) <= @EndDate
)
, cte2 AS
(
SELECT calendar_date,
DATEPART(dw, calendar_date) AS dow,
DATEPART(week, calendar_date) AS week_nbr
FROM cte_date_base_table
)
SELECT week_nbr,
MIN(calendar_date) as start_date,
MAX(calendar_date) as end_date
FROM cte2
GROUP BY week_nbr



phoenix_
phoenix_
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 31
In the meantime I created something like this...

 declare @StartDate date
set @StartDate = '2013-01-03'
declare @EndDate date
set @EndDate = '2013-02-21'
declare @DateCalc date
declare @WeekStartDate date
set @WeekStartDate = DATEADD(ww, DATEDIFF(ww,0,@StartDate), 0)
set @DateCalc = @StartDate
WHILE (@WeekStartDate <= @EndDate )
begin
select case when @StartDate > @WeekStartDate then DatePart(ww,@StartDate) else DatePart(ww,@WeekStartDate) end as WeekNum, @WeekStartDate as WeekStartDate, DATEADD(dd, 6, @WeekStartDate) as EndDate;
set @WeekStartDate = DATEADD(dd, 7, @WeekStartDate)
set @StartDate = @WeekStartDate
END



Is it possible somehow to convert it to 'one' select statement so I will have only one result?
Kingston Dhasian
Kingston Dhasian
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11957 Visits: 5339
phoenix_ (5/29/2013)
In the meantime I created something like this...

 declare @StartDate date
set @StartDate = '2013-01-03'
declare @EndDate date
set @EndDate = '2013-02-21'
declare @DateCalc date
declare @WeekStartDate date
set @WeekStartDate = DATEADD(ww, DATEDIFF(ww,0,@StartDate), 0)
set @DateCalc = @StartDate
WHILE (@WeekStartDate <= @EndDate )
begin
select case when @StartDate > @WeekStartDate then DatePart(ww,@StartDate) else DatePart(ww,@WeekStartDate) end as WeekNum, @WeekStartDate as WeekStartDate, DATEADD(dd, 6, @WeekStartDate) as EndDate;
set @WeekStartDate = DATEADD(dd, 7, @WeekStartDate)
set @StartDate = @WeekStartDate
END



Is it possible somehow to convert it to 'one' select statement so I will have only one result?


Yes. It is possible to convert it to single SELECT statement. But, I would advise against it.
We have provided better performing set-based solutions and you have used a WHILE loop which works on a row by row basis.

One more observation after executing your code: Your expected results are different from the results of this query.


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/
phoenix_
phoenix_
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 31
Kingston Dhasian (5/29/2013)
phoenix_ (5/29/2013)


Is it possible somehow to convert it to 'one' select statement so I will have only one result?


Yes. It is possible to convert it to single SELECT statement. But, I would advise against it.
We have provided better performing set-based solutions and you have used a WHILE loop which works on a row by row basis.


Maybe I am wrong but will Group By makes my join with other tables tough work...?
I want now join the results with another table like this:

eventNum startDate endDate
1 2013-01-05 2013-01-10
2 2013-01-17 2013-01-22
3 2013-01-08 2013-01-19



so the result should be the following
week_nbr   start_date   end_date    eventNum
1 2013-01-03 2013-01-05 1
2 2013-01-06 2013-01-12 1
2 2013-01-06 2013-01-12 3
3 2013-01-13 2013-01-19 2
3 2013-01-13 2013-01-19 3
4 2013-01-20 2013-01-23 2



Kingston Dhasian (5/29/2013)

One more observation after executing your code: Your expected results are different from the results of this query.


That's because I changed the date range (more rows) and I added also the case statement (which is showing the first day of the week - and can affect the first row). Same with the last row.

Now the results will be the same:
declare @StartDate date
set @StartDate = '2013-01-03'
declare @EndDate date
set @EndDate = '2013-01-23'
declare @DateCalc date
declare @WeekStartDate date
set @WeekStartDate = DATEADD(ww, DATEDIFF(ww,0,@StartDate), 0)
set @DateCalc = @StartDate
WHILE (@WeekStartDate <= @EndDate )
begin
select case when @StartDate > @WeekStartDate then DatePart(ww,@StartDate) else DatePart(ww,@WeekStartDate) end as WeekNum, @StartDate as StartDate, case when DATEADD(dd, 6, @WeekStartDate) > @EndDate then @EndDate else DATEADD(dd, 6, @WeekStartDate) end as EndDate;
set @WeekStartDate = DATEADD(dd, 7, @WeekStartDate)
set @StartDate = @WeekStartDate
END


Kingston Dhasian
Kingston Dhasian
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11957 Visits: 5339
You can use it to JOIN with other tables as well

DECLARE   @start_date DATETIME, @end_date DATETIME

DECLARE @table TABLE
(
eventNum INT,
startDate DATETIME,
endDate DATETIME
)

SELECT @start_date = '20130103', @end_date = '20130123'

INSERT @table
SELECT 1, '20130105', '20130110' UNION ALL
SELECT 2, '20130117', '20130122' UNION ALL
SELECT 3, '20130108', '20130119'

SELECT D.week_nbr, D.start_date, D.end_date, T.eventNum
FROM (
SELECT DATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_date
FROM (
SELECT DATEADD(DAY,sv.number,@start_date) AS Dt
FROM master.dbo.spt_values AS sv -- You can use TALLY table instead of this
WHERE sv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)
) AS Dates
GROUP BY DATEPART(WEEK,Dates.Dt)
) AS D
INNER JOIN @table AS T ON T.startDate BETWEEN D.start_date AND D.end_date OR T.endDate BETWEEN D.start_date AND D.end_date
ORDER BY D.week_nbr, D.start_date




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/
Amasallia2012
Amasallia2012
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 16
Kingston Dhasian - Wednesday, May 29, 2013 8:21 AM
You can use it to JOIN with other tables as well
DECLARE   @start_date DATETIME, @end_date DATETIMEDECLARE   @table TABLE(   eventNum   INT,   startDate   DATETIME,   endDate      DATETIME)SELECT   @start_date = '20130103', @end_date = '20130123'INSERT   @tableSELECT   1, '20130105', '20130110' UNION ALLSELECT   2, '20130117', '20130122' UNION ALLSELECT   3, '20130108', '20130119'SELECT   D.week_nbr, D.start_date, D.end_date, T.eventNumFROM   (      SELECT   DATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_date      FROM   (            SELECT   DATEADD(DAY,sv.number,@start_date) AS Dt            FROM   master.dbo.spt_values AS sv -- You can use TALLY table instead of this            WHERE   sv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)         ) AS Dates      GROUP BY DATEPART(WEEK,Dates.Dt)   ) AS DINNER JOIN @table AS T ON T.startDate BETWEEN D.start_date AND D.end_date OR T.endDate BETWEEN D.start_date AND D.end_dateORDER BY D.week_nbr, D.start_date

I need help with something similar anyone please help. I have dates same like that but for me it's products advertised for example from 04 Jan 2018 to 20 Jan 2018, I need sql to duplicate my records and create a weekending date make a record with date 6, 13 20 Jan so each Saturday must be there but if it ends on say 25 Jan 2017 it must be joined to 6,13,20 and 27 because the 25th falls within that week that ends on the 27th.
Kingston Dhasian
Kingston Dhasian
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11957 Visits: 5339
Amasallia2012 - Thursday, February 1, 2018 12:12 PM
Kingston Dhasian - Wednesday, May 29, 2013 8:21 AM
You can use it to JOIN with other tables as well
DECLARE   @start_date DATETIME, @end_date DATETIMEDECLARE   @table TABLE(   eventNum   INT,   startDate   DATETIME,   endDate      DATETIME)SELECT   @start_date = '20130103', @end_date = '20130123'INSERT   @tableSELECT   1, '20130105', '20130110' UNION ALLSELECT   2, '20130117', '20130122' UNION ALLSELECT   3, '20130108', '20130119'SELECT   D.week_nbr, D.start_date, D.end_date, T.eventNumFROM   (      SELECT   DATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_date      FROM   (            SELECT   DATEADD(DAY,sv.number,@start_date) AS Dt            FROM   master.dbo.spt_values AS sv -- You can use TALLY table instead of this            WHERE   sv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)         ) AS Dates      GROUP BY DATEPART(WEEK,Dates.Dt)   ) AS DINNER JOIN @table AS T ON T.startDate BETWEEN D.start_date AND D.end_date OR T.endDate BETWEEN D.start_date AND D.end_dateORDER BY D.week_nbr, D.start_date

I need help with something similar anyone please help. I have dates same like that but for me it's products advertised for example from 04 Jan 2018 to 20 Jan 2018, I need sql to duplicate my records and create a weekending date make a record with date 6, 13 20 Jan so each Saturday must be there but if it ends on say 25 Jan 2017 it must be joined to 6,13,20 and 27 because the 25th falls within that week that ends on the 27th.

I would advise you to create a new thread for your doubts instead of posting it in existing threads. This will avoid confusion.
Additionally, I would suggest you to explain the issue with some sample data and expected results in a ready to use format so that people can help with tested solutions.
If you are not sure on how to do this, please follow the article link in my signature and I am sure many people will help you instantly.



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/
Amasallia2012
Amasallia2012
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 16
Kingston Dhasian - Thursday, February 1, 2018 11:14 PM
Amasallia2012 - Thursday, February 1, 2018 12:12 PM
Kingston Dhasian - Wednesday, May 29, 2013 8:21 AM
You can use it to JOIN with other tables as well
DECLARE   @start_date DATETIME, @end_date DATETIMEDECLARE   @table TABLE(   eventNum   INT,   startDate   DATETIME,   endDate      DATETIME)SELECT   @start_date = '20130103', @end_date = '20130123'INSERT   @tableSELECT   1, '20130105', '20130110' UNION ALLSELECT   2, '20130117', '20130122' UNION ALLSELECT   3, '20130108', '20130119'SELECT   D.week_nbr, D.start_date, D.end_date, T.eventNumFROM   (      SELECT   DATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_date      FROM   (            SELECT   DATEADD(DAY,sv.number,@start_date) AS Dt            FROM   master.dbo.spt_values AS sv -- You can use TALLY table instead of this            WHERE   sv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)         ) AS Dates      GROUP BY DATEPART(WEEK,Dates.Dt)   ) AS DINNER JOIN @table AS T ON T.startDate BETWEEN D.start_date AND D.end_date OR T.endDate BETWEEN D.start_date AND D.end_dateORDER BY D.week_nbr, D.start_date

I need help with something similar anyone please help. I have dates same like that but for me it's products advertised for example from 04 Jan 2018 to 20 Jan 2018, I need sql to duplicate my records and create a weekending date make a record with date 6, 13 20 Jan so each Saturday must be there but if it ends on say 25 Jan 2017 it must be joined to 6,13,20 and 27 because the 25th falls within that week that ends on the 27th.

I would advise you to create a new thread for your doubts instead of posting it in existing threads. This will avoid confusion.
Additionally, I would suggest you to explain the issue with some sample data and expected results in a ready to use format so that people can help with tested solutions.
If you are not sure on how to do this, please follow the article link in my signature and I am sure many people will help you instantly.


I need help with this currently I do it like this :
Input :

CT_PROM_STARTCT_PROM_END
4-Jan-1819-Jan-18
3-Feb-1817-Feb-18


SQL:
Declare @beginDate Datetime, @EndDate Datetime
Select @beginDate = '01/03/2015', @EndDate = '31/12/2018'
create Table #cal
(CalendarDate Datetime Primary key, IsWeekend Bit, YearNo SmallInt, QuarterNo TinyInt, MonthNo TinyInt, DayOfYearNo SmallInt, DayNo TinyInt, WeekNo TinyInt, WeekDayNo TinyInt)


While @beginDate <= @endDate
Begin
Insert Into #cal
Select
@beginDate As CalendarDate
,(Case When DATEPART(Weekday, @beginDate) In (7, 1) Then 1 Else 0 End) As IsWeekend
,DATEPART(Year, @beginDate) As YearNo
,DATEPART(QUARTER, @beginDate) As QuarterNo
,DATEPART(MONTH, @beginDate) As MonthNo
,DATEPART(DayOfYear, @beginDate) As DayOfYearNo
,DATEPART(Day, @beginDate) As DayNo
,DATEPART(Week, @beginDate) As WeekNo
,DATEPART(WEEKDAY, @beginDate) As WeekDayNo
Set @beginDate = DateAdd(Day, 1, @beginDate)
End

--drop table #cal


select a.*,b.* from temp_willowton16Jan18 a left join #cal b on b.CalendarDate>=a.[ct_prom_start] and b.CalendarDate<=a.[ct_prom_end]
where IsWeekend = 1 and weekdayno=7
Current Output :

CT_PROM_STARTCT_PROM_ENDPromo Weekending Saturday
4-Jan-1819-Jan-1806-Jan-18
4-Jan-1819-Jan-1813-Jan-18
3-Feb-1817-Feb-1803-Feb-18
3-Feb-1817-Feb-1810-Feb-18
3-Feb-1817-Feb-1817-Feb-18


my code is always either missing the last Saturday or not stopping on the Saturday it should stop on.
Desired Output:

CT_PROM_STARTCT_PROM_ENDPromo Weekending Saturday
4-Jan-1819-Jan-1806-Jan-18
4-Jan-1819-Jan-1813-Jan-18
4-Jan-1819-Jan-1820-Jan-18 must duplicate to this too because Promotion ends in that week that ends on the 20 Jan 2018
3-Feb-1817-Feb-1803-Feb-18
3-Feb-1817-Feb-1810-Feb-18
3-Feb-1817-Feb-1817-Feb-18


Please can anyone help.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search