04-22-2022 09:43 AM
I am working on creating a Reporting Dashboard based on a custom SQL for items in an active unity workflow queue. One of the fields I return in my grid rows is the transdate of when the document entered the queue. I am being asked to build a dashboard item that would show aging based on transdate ( less than 30 day aging bucket, 60 day aging bucket, older than 60 day aging bucket). Has anyone done something similar?
04-22-2022 10:00 AM
You can use a CASE statement combined with a DATEDIFF function to determine age and put into a "bucket". Here is an example where I'm using the revision date of WorkView objects. I've made the important part bold. You will need to replace "revisiondate' with the date field you want to use . I selected revisiondate and ", datediff(day, revisiondate, getdate()) as Age" so you could see the result and how it fits into the values used in the CASE statement.
select top 100
revisiondate
, datediff(day, revisiondate, getdate()) as Age
, case
when datediff(day, revisiondate, getdate()) > 90 then '90+ days'
when datediff(day, revisiondate, getdate()) > 60 then '60 to 90 days'
when datediff(day, revisiondate, getdate()) > 30 then '30 to 60 days'
when datediff(day, revisiondate, getdate()) <= 30 then '30 or less days'
end as TimeBucket
from hsi.rmobjectinstance1018
order by revisiondate desc
You could also do an aggregation like this:
select
case
when datediff(day, revisiondate, getdate()) > 90 then '90+ days'
when datediff(day, revisiondate, getdate()) > 60 then '60 to 90 days'
when datediff(day, revisiondate, getdate()) > 30 then '30 to 60 days'
when datediff(day, revisiondate, getdate()) <= 30 then '30 or less days'
end as TimeBucket
, count(1) as ItemCount
from hsi.rmobjectinstance1018
group by
case
when datediff(day, revisiondate, getdate()) > 90 then '90+ days'
when datediff(day, revisiondate, getdate()) > 60 then '60 to 90 days'
when datediff(day, revisiondate, getdate()) > 30 then '30 to 60 days'
when datediff(day, revisiondate, getdate()) <= 30 then '30 or less days'
end
04-22-2022 10:00 AM
You can use a CASE statement combined with a DATEDIFF function to determine age and put into a "bucket". Here is an example where I'm using the revision date of WorkView objects. I've made the important part bold. You will need to replace "revisiondate' with the date field you want to use . I selected revisiondate and ", datediff(day, revisiondate, getdate()) as Age" so you could see the result and how it fits into the values used in the CASE statement.
select top 100
revisiondate
, datediff(day, revisiondate, getdate()) as Age
, case
when datediff(day, revisiondate, getdate()) > 90 then '90+ days'
when datediff(day, revisiondate, getdate()) > 60 then '60 to 90 days'
when datediff(day, revisiondate, getdate()) > 30 then '30 to 60 days'
when datediff(day, revisiondate, getdate()) <= 30 then '30 or less days'
end as TimeBucket
from hsi.rmobjectinstance1018
order by revisiondate desc
You could also do an aggregation like this:
select
case
when datediff(day, revisiondate, getdate()) > 90 then '90+ days'
when datediff(day, revisiondate, getdate()) > 60 then '60 to 90 days'
when datediff(day, revisiondate, getdate()) > 30 then '30 to 60 days'
when datediff(day, revisiondate, getdate()) <= 30 then '30 or less days'
end as TimeBucket
, count(1) as ItemCount
from hsi.rmobjectinstance1018
group by
case
when datediff(day, revisiondate, getdate()) > 90 then '90+ days'
when datediff(day, revisiondate, getdate()) > 60 then '60 to 90 days'
when datediff(day, revisiondate, getdate()) > 30 then '30 to 60 days'
when datediff(day, revisiondate, getdate()) <= 30 then '30 or less days'
end
04-22-2022 12:07 PM
Alternatively, if you didn't want to include the case statement in the SQL for whatever reason, you can add a calculated field to the dashboard that uses the same logic and date calculations.
04-25-2022 10:04 AM
Thanks Bill!
Find what you came for
We want to make your experience in Hyland Connect as valuable as possible, so we put together some helpful links.