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
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 16

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
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7699 Visits: 5064
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
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 16
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
SSCertifiable
SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)SSCertifiable (7.7K reputation)

Group: General Forum Members
Points: 7699 Visits: 5064
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
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 16
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-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39999 Visits: 14483
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
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 16
Okay thanks but how will i get the output I require?
george-178499
george-178499
Valued Member
Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)Valued Member (55 reputation)

Group: General Forum Members
Points: 55 Visits: 318
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