May 29, 2013 at 2:57 am
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
May 29, 2013 at 3:46 am
DECLARE@start_date DATE, @end_date DATE
SELECT@start_date = '20130103', @end_date = '20130123'
SELECTDATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_date
FROM(
SELECTDATEADD(DAY,sv.number,@start_date) AS Dt
FROMmaster.dbo.spt_values AS sv -- You can use TALLY table instead of this
WHEREsv.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/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 29, 2013 at 3:52 am
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
May 29, 2013 at 6:26 am
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?
May 29, 2013 at 7:33 am
phoenix_ (5/29/2013)
In the meantime I created something like this...
declare @StartDate dateset @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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 29, 2013 at 7:59 am
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_nbrstart_dateend_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
May 29, 2013 at 8:21 am
You can use it to JOIN with other tables as well
DECLARE@start_date DATETIME, @end_date DATETIME
DECLARE@table TABLE
(
eventNumINT,
startDateDATETIME,
endDateDATETIME
)
SELECT@start_date = '20130103', @end_date = '20130123'
INSERT@table
SELECT1, '20130105', '20130110' UNION ALL
SELECT2, '20130117', '20130122' UNION ALL
SELECT3, '20130108', '20130119'
SELECTD.week_nbr, D.start_date, D.end_date, T.eventNum
FROM(
SELECTDATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_date
FROM(
SELECTDATEADD(DAY,sv.number,@start_date) AS Dt
FROMmaster.dbo.spt_values AS sv -- You can use TALLY table instead of this
WHEREsv.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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 1, 2018 at 12:12 pm
Kingston Dhasian - Wednesday, May 29, 2013 8:21 AMYou can use it to JOIN with other tables as wellDECLARE@start_date DATETIME, @end_date DATETIMEDECLARE@table TABLE(eventNumINT,startDateDATETIME,endDateDATETIME)SELECT@start_date = '20130103', @end_date = '20130123'INSERT@tableSELECT1, '20130105', '20130110' UNION ALLSELECT2, '20130117', '20130122' UNION ALLSELECT3, '20130108', '20130119'SELECTD.week_nbr, D.start_date, D.end_date, T.eventNumFROM(SELECTDATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_dateFROM(SELECTDATEADD(DAY,sv.number,@start_date) AS DtFROMmaster.dbo.spt_values AS sv -- You can use TALLY table instead of thisWHEREsv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)) AS DatesGROUP 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.
February 1, 2018 at 11:14 pm
Amasallia2012 - Thursday, February 1, 2018 12:12 PMKingston Dhasian - Wednesday, May 29, 2013 8:21 AMYou can use it to JOIN with other tables as wellDECLARE@start_date DATETIME, @end_date DATETIMEDECLARE@table TABLE(eventNumINT,startDateDATETIME,endDateDATETIME)SELECT@start_date = '20130103', @end_date = '20130123'INSERT@tableSELECT1, '20130105', '20130110' UNION ALLSELECT2, '20130117', '20130122' UNION ALLSELECT3, '20130108', '20130119'SELECTD.week_nbr, D.start_date, D.end_date, T.eventNumFROM(SELECTDATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_dateFROM(SELECTDATEADD(DAY,sv.number,@start_date) AS DtFROMmaster.dbo.spt_values AS sv -- You can use TALLY table instead of thisWHEREsv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)) AS DatesGROUP 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_dateI 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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 4, 2018 at 1:27 pm
Kingston Dhasian - Thursday, February 1, 2018 11:14 PMAmasallia2012 - Thursday, February 1, 2018 12:12 PMKingston Dhasian - Wednesday, May 29, 2013 8:21 AMYou can use it to JOIN with other tables as wellDECLARE@start_date DATETIME, @end_date DATETIMEDECLARE@table TABLE(eventNumINT,startDateDATETIME,endDateDATETIME)SELECT@start_date = '20130103', @end_date = '20130123'INSERT@tableSELECT1, '20130105', '20130110' UNION ALLSELECT2, '20130117', '20130122' UNION ALLSELECT3, '20130108', '20130119'SELECTD.week_nbr, D.start_date, D.end_date, T.eventNumFROM(SELECTDATEPART(WEEK,Dates.Dt) AS week_nbr, MIN(Dates.Dt) AS start_date, MAX(Dates.Dt) AS end_dateFROM(SELECTDATEADD(DAY,sv.number,@start_date) AS DtFROMmaster.dbo.spt_values AS sv -- You can use TALLY table instead of thisWHEREsv.type = 'P' AND sv.number <= DATEDIFF(DAY,@start_date,@end_date)) AS DatesGROUP 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_dateI 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_START | CT_PROM_END |
| 4-Jan-18 | 19-Jan-18 |
| 3-Feb-18 | 17-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_START | CT_PROM_END | Promo Weekending Saturday |
| 4-Jan-18 | 19-Jan-18 | 06-Jan-18 |
| 4-Jan-18 | 19-Jan-18 | 13-Jan-18 |
| 3-Feb-18 | 17-Feb-18 | 03-Feb-18 |
| 3-Feb-18 | 17-Feb-18 | 10-Feb-18 |
| 3-Feb-18 | 17-Feb-18 | 17-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_START | CT_PROM_END | Promo Weekending Saturday |
| 4-Jan-18 | 19-Jan-18 | 06-Jan-18 |
| 4-Jan-18 | 19-Jan-18 | 13-Jan-18 |
| 4-Jan-18 | 19-Jan-18 | 20-Jan-18 must duplicate to this too because Promotion ends in that week that ends on the 20 Jan 2018 |
| 3-Feb-18 | 17-Feb-18 | 03-Feb-18 |
| 3-Feb-18 | 17-Feb-18 | 10-Feb-18 |
| 3-Feb-18 | 17-Feb-18 | 17-Feb-18 |
Please can anyone help.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply