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


Duplicating Data To Each Saturday That a Promotion Is Live For


Duplicating Data To Each Saturday That a Promotion Is Live For

Author
Message
Amasallia2012
Amasallia2012
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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


Please can anyone help.


HappyGeek
HappyGeek
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14730 Visits: 7190
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_End

FROM #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
Amasallia2012
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

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

CT_Prom_StartCT_Prom_EndPromo_Week_End
24-Nov-1710-Dec-1725-Nov-17
24-Nov-1710-Dec-172-Dec-17
24-Nov-1710-Dec-179-Dec-17

there also needs to be one more row for 16-Dec-17
HappyGeek
HappyGeek
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

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

CT_Prom_StartCT_Prom_EndPromo_Week_End
24-Nov-1710-Dec-1725-Nov-17
24-Nov-1710-Dec-172-Dec-17
24-Nov-1710-Dec-179-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
Amasallia2012
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

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


CT_Prom_StartCT_Prom_EndPromo_Week_End
4-Jan-1819-Jan-186-Jan-18
4-Jan-1819-Jan-1813-Jan-18
4-Jan-1819-Jan-1820-Jan-18
3-Feb-1817-Feb-183-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
drew.allen
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63767 Visits: 16962
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
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
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
Amasallia2012
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 Visits: 19
Okay thanks but how will i get the output I require?
george_at_sql
george_at_sql
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1359 Visits: 1798
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*/


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