Post reply

Duplicating Data To Each Saturday That a Promotion Is Live For

  • February 4, 2018 at 12:44 pm

    #329079

    I need help with this currently I do it like this :
    Input :

    CT_PROM_STARTCT_PROM_END
    4-Jan-1819-Jan-18
    3-Feb-1817-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_STARTCT_PROM_ENDPromo Weekending Saturday
    4-Jan-1819-Jan-1806-Jan-18
    4-Jan-1819-Jan-1813-Jan-18
    3-Feb-1817-Feb-1803-Feb-18
    3-Feb-1817-Feb-1810-Feb-18
    3-Feb-1817-Feb-1817-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_STARTCT_PROM_ENDPromo Weekending Saturday
    4-Jan-1819-Jan-1806-Jan-18
    4-Jan-1819-Jan-1813-Jan-18
    4-Jan-1819-Jan-1820-Jan-18  must duplicate to this too because Promotion ends in that week that ends on the 20 Jan 2018
    3-Feb-1817-Feb-1803-Feb-18
    3-Feb-1817-Feb-1810-Feb-18
    3-Feb-1817-Feb-1817-Feb-18

    Please can anyone help.

  • February 5, 2018 at 12:14 am

    #1978503

    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.

    ...

  • February 5, 2018 at 6:29 am

    #1978531

    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

  • February 5, 2018 at 6:44 am

    #1978535

    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.

    ...

  • February 5, 2018 at 7:08 am

    #1978541

    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,

  • February 5, 2018 at 8:07 am

    #1978554

    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

  • February 5, 2018 at 10:51 am

    #1978595

    Okay thanks but how will i get the output I require?

  • February 9, 2018 at 2:00 am

    This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply