DATEDIFF with GETWEEK worth of data

  • I have such an issue. I have units, on sale date and also how many weeks the unit is on sale. However, my current formula with DATEDIFF grabs only dates that are from '09-20' and some from '09-21' (if we take a week of 1 month example), but I need to change it so it takes units from the whole 7 days.

    I was trying to find some information online, it was somewhat difficult to find smth like this. Does someone know how I can adjust the code to grab all 7 days for a week?

    Thank you!

    SELECT [DMDUNIT], 
    [U_ONSALE_DATE],
    ABS(DATEDIFF(week, GETDATE(), [U_ONSALE_DATE])) AS 'Weeks on sale'
    FROM SCPOMGR.DMDUNIT
  • You don't have a where clause - not sure what the issue is without seeing the full query.  With that said - I have to assume the reason for your problem is that you are using GETDATE() without considering the time component.

    If you run your code at 8am and use GETDATE() - then calculate from that to the previous day (for example) then you get 8am of the previous day, so you will end up missing any data prior to that time.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • /* Setting up test data */
    declare @sales table (
    DMDUNIT char(1),
    U_ONSALE_DATE date
    )
    insert into @sales (DMDUNIT, U_ONSALE_DATE)
    values
    ('A', '20210513'),
    ('B', '20210612'),
    ('C', '20210417'),
    ('D', '20211213'),
    ('E', '20201021'),
    ('F', '20211018'),
    ('G', '20211015')

    declare @todaySimulations table (
    today date
    )
    insert into @todaySimulations (today)
    values
    ('20211011'),
    ('20211012'),
    ('20211013'),
    ('20211014'),
    ('20211015'),
    ('20211016'),
    ('20211017'),
    ('20211018'),
    ('20211019'),
    ('20211020'),
    ('20211021'),
    ('20211021'),
    ('20211022'),
    ('20211023'),
    ('20211024'),
    ('20211025'),
    ('20211026')

    If you simply define a week as a unit of 7 days, then you may be satisfied with something like

    /* Run the simulation.. */
    select ts.today, s.DMDUNIT, s.U_ONSALE_DATE,
    case when s.U_ONSALE_DATE > today then null else datediff(d,s.U_ONSALE_DATE,cast(getdate() as date))/7 end as WeeksOnSale
    from @sales s
    cross join @todaySimulations ts
    order by ts.today, s.DMDUNIT;

    If you on the other hand want to use full calender weeks, it gets a little more involved, but maybe something like this will work for you:

    /* Run the simulation.. */
    select ts.today, s.DMDUNIT, s.U_ONSALE_DATE, sws.SalesFirstCalenderWeekStart, CurrentWeekStart,
    case when s.U_ONSALE_DATE > today then null else datediff(week,sws.SalesFirstCalenderWeekStart,cws.CurrentWeekStart) end as WholeCalenderWeeksOnSale
    from @sales s
    cross join @todaySimulations ts
    cross apply (select cast(dateadd(week,ceiling(DateDiff(day,'20210104',s.U_ONSALE_DATE)/7.0),'20210104') as date) as SalesFirstCalenderWeekStart ) sws
    cross apply (select cast(dateadd(week,DateDiff(day,'20210104',ts.today)/7,'20210104') as date) as CurrentWeekStart ) cws
    order by ts.today, s.DMDUNIT

    I haven't done extensive testing though. 🙂

    Btw. the "magic" date I've used is just an arbitrary monday (if your calender week starts on a different day then just change the date to reflect your week day start date)

  • JeremyU wrote:

    I have such an issue. I have units, on sale date and also how many weeks the unit is on sale. However, my current formula with DATEDIFF grabs only dates that are from '09-20' and some from '09-21' (if we take a week of 1 month example), but I need to change it so it takes units from the whole 7 days.

    I was trying to find some information online, it was somewhat difficult to find smth like this. Does someone know how I can adjust the code to grab all 7 days for a week?

    Thank you!

    SELECT [DMDUNIT], 
    [U_ONSALE_DATE],
    ABS(DATEDIFF(week, GETDATE(), [U_ONSALE_DATE])) AS 'Weeks on sale'
    FROM SCPOMGR.DMDUNIT

    It would really help folks help you if you helped them help you. 😀  Please see the article at the first link in my signature line and then see Kaj's post for another way to provide readily consumable test data .

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

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