## Duplicating Data To Each Saturday That a Promotion Is Live For

Author
Message
Amasallia2012
SSC-Enthusiastic

Group: General Forum Members
Points: 127 Visits: 19

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

HappyGeek
SSCoach

Group: General Forum Members
Points: 16088 Visits: 7924
Firstly you did not post DDL and DML for your temp table, that said, based on what you have already done you could simply extend the end date by the number of week days, as in:

`SELECT a.CT_Prom_Start, a.CT_Prom_End, b.CalendarDate Promo_Week_EndFROM #temp_willowton16Jan18 a left join #cal b ON b.CalendarDate>=a.[ct_prom_start] AND b.CalendarDate<=dateadd(day, 5, a.[ct_prom_end])WHERE IsWeekend = 1 AND weekdayno=7`

This may not be an ideal solution but should give you want you want.

...
Amasallia2012
SSC-Enthusiastic

Group: General Forum Members
Points: 127 Visits: 19
hi,
thanks tried it but then out put as follows for this example below :

 CT_Prom_Start CT_Prom_End Promo_Week_End 24-Nov-17 10-Dec-17 25-Nov-17 24-Nov-17 10-Dec-17 2-Dec-17 24-Nov-17 10-Dec-17 9-Dec-17

there also needs to be one more row for 16-Dec-17
HappyGeek
SSCoach

Group: General Forum Members
Points: 16088 Visits: 7924
Amasallia2012 - Monday, February 5, 2018 6:29 AM
hi,
thanks tried it but then out put as follows for this example below :

 CT_Prom_Start CT_Prom_End Promo_Week_End 24-Nov-17 10-Dec-17 25-Nov-17 24-Nov-17 10-Dec-17 2-Dec-17 24-Nov-17 10-Dec-17 9-Dec-17

there also needs to be one more row for 16-Dec-17

Change the dateadd to 6, was not sure whether next week meant Sunday on or Monday on.

...
Amasallia2012
SSC-Enthusiastic

Group: General Forum Members
Points: 127 Visits: 19
thanks so much :

 CT_Prom_Start CT_Prom_End Promo_Week_End 4-Jan-18 19-Jan-18 6-Jan-18 4-Jan-18 19-Jan-18 13-Jan-18 4-Jan-18 19-Jan-18 20-Jan-18 3-Feb-18 17-Feb-18 3-Feb-18

its working for the first set where dates are 4-Jan 18 to 19 Jan 2018 but for the 03 Feb 18 - 18 Feb it is not , for this on it should duplicate to 03,10,17,24 Feb 2018,
drew.allen
SSC Guru

Group: General Forum Members
Points: 68850 Visits: 18321
Why are you testing for both IsWeekend = 1 and weekdayno = 7? If weekdayno = 7, then IsWeekend = 1 will always be true.

Drew

J. Drew Allen
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Amasallia2012
SSC-Enthusiastic

Group: General Forum Members
Points: 127 Visits: 19
Okay thanks but how will i get the output I require?
george_at_sql
SSCommitted

Group: General Forum Members
Points: 1615 Visits: 1927
Based on the examples i think you are looking to get the nearest saturday from the CT_Prom_End

If so you can try this or better still you can tell us how you are computing the column Promo_Week_End?

`select a.*,b.*        from temp_willowton16Jan18 a    join (select * from #cal where weekdayno=6)b   on b.CalendarDate>=a.[ct_prom_start]  --and b.CalendarDate<=a.[ct_prom_end] /* Changing this condition to the one below.*/ and b.CalendarDate<=(case when 6- datepart(weekday,a.ct_prom_end) = 0 then /*If the end_date is on a saturday then allow 1 more week */                                                   dateadd(day,7 ,a.ct_prom_end)                                             else  dateadd(day,6- datepart(weekday,ct_prom_end) ,ct_prom_end) /*Else find the nearest saturday from the end_date*/                                    end)--where IsWeekend = 1 and weekdayno=7 /*This condition was converting that left join to a JOIN in the original query*/`