DATEDIFF with GETWEEK worth of data

Question

DATEDIFF with GETWEEK worth of data

JeremyU

SSChasing Mays

Points: 640
More actions
October 20, 2021 at 5:06 pm

#3942528
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
```

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

kaj SSCommitted Points: 1605 More actions · Answer 2

/* 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)

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 3

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)