July 29, 2021 at 1:59 pm
I created an aging report for a case management system with a single parameter @days with values 30,60,90 etc. and the following where clause
WHERE DateOpened <= Getdate() - @days
This worked fine but the user rather than return rows for the last 60 or 90 days wants cases between 30-60 days or 60-90 days.
Is there any way to get this with a single @days parameter? I am trying to avoid any additional parameter if possible.
Thanks,
Scott
July 29, 2021 at 2:52 pm
You could make the parameter that window/time range.
Obviously your underlying will have to change.
If it's always 30 days windows like your two examples suggest (is that sample really representative, or do they still want ability to do 60, 90,other day ranges?) , than you're lucky in that you can present the parameter text as "30-60 days" or "60-90 days", but still use an integer parameter value. If you treat @days as the minimum date, you could use something like
WHERE
-- Start Date (Datetime since we're using GETDATE)
DateOpened >= DATEADD(DAY,-@days,GETDATE()) AND
-- End Date (Datetime since we're using GETDATE)
DateOpened < DATEADD(DAY,-@days+30,GETDATE())
Question: Is logic supposed to be based on exact time, or just date? Aging reports tend to be based on whole days.
GetDate is datetime. Is DateOpened a date or a datetime? For a 30-60 day report, do you want to exclude everything on 05/30 because 2021-05-30 00:00 is less than 2021-05-30 09:45:44.730?
Or is the intent to base it on date only? If the latter, then you need to convert the current time to a date -- e.g., CAST(GETDATE() AS DATE). If DateOpened is a date, this could help ensure more efficient queries by avoiding type conversion & enabling index on DateOpened to be used.
July 29, 2021 at 5:31 pm
First - thanks for the response.
In the example you give the date is the same for both start and finish so it will never return any records. I can just use getDate() for the end date and that would give me the last 30 days but I am not sure how I would change the query for 30-60 or 60-90, etc. with the single parameter.
To answer your questions:
Yes they timeframe is always 30 day period (30-60, 60-90, 90-120, 120-180, 180 and older)
The logic is only based on the date, not time.
DateOpened is datetime but the time is not being saved.
July 29, 2021 at 7:46 pm
First - thanks for the response.
In the example you give the date is the same for both start and finish so it will never return any records. I can just use getDate() for the end date and that would give me the last 30 days but I am not sure how I would change the query for 30-60 or 60-90, etc. with the single parameter.
To answer your questions:
Yes they timeframe is always 30 day period (30-60, 60-90, 90-120, 120-180, 180 and older)
The logic is only based on the date, not time.
DateOpened is datetime but the time is not being saved.
If you have control over the days parameter - then it is simply a matter of taking the input and converting that input to the actual date range. I would set this up using parameters:
Declare @days int = 90; -- input parameters
Declare @startDate datetime = dateadd(day, datediff(day, 0, getdate()) - @days - 30, 0) -- 0/30/60/90/120/180 days back
, @endDate datetime = dateadd(day, datediff(day, 0, getdate()) - @days, 0); -- Midnight today - YYYY-MM-DD 00:00:00.000
Select @startDate, @endDate;
Select ...
From ...
Where DateOpened >= @startDate
And DateOpened < @endDate --Notice the less than only, include everything up to but not including today
You may need to adjust the calculation - but the idea is the same. Calculate the start date of the range - the end date of the range and use that in the query to return that range of data. We use an open-interval range here which takes into account all of the times that are possible up to but not including current date.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply