Aging report with days parameter

  • 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

     

  • 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.

    • This reply was modified 2 months ago by  ratbak. Reason: Corrected logic to add 30 days to @days for maximum OpenDate
  • 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.

  • scotdg wrote:

    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
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

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

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