RE: Displaying a week numbers as a rows for given date range

Amasallia2012

SSC Enthusiast

Points: 175

February 4, 2018 at 1:27 pm

#1978475

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 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.
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.