IF...THEN or CASE to create A/R aging distribution.
-
I am trying to write a query in Transact-SQL to create a user view in my SQL database. I am trying to populate columns for each "aging" category (30, 60, 90, etc), so the correct age receives the amount due, but other columns are zero. However, I can't find the correct CASE or IF...THEN syntax. It needs to do something equal to the following:If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else CurrentDue = 0.If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else 30DayDue = 0....etcI would be grateful for help...Thank you.
-
Hi,
there are two forms of CASE statement described in BoL,
Simple CASE, which yields a single column with different values depending on the results of the expression.
SELECT
CASE <expression>
WHEN <condition1> THEN <value1>
WHEN <condition2> THEN <value2>
WHEN <condition3> THEN <value3>
...etc
ELSE <value4>
END
and Searched CASE in which each column is a separate discrete CASE statement, making it suitable for ageing queries such as the one you are trying to construct.
SELECT
CASE WHEN <condition> THEN <value1> ELSE <value2> END,
CASE WHEN <condition> THEN <value1> ELSE <value2> END,
...etc
Notice the lack of the <expression> before the WHEN in the second form of the statement, and that every
column has an END, so is a discrete CASE statement.
So in the example you are trying to create, you might use something similar to
SELECT
CASE WHEN ServiceDateAge BETWEEN 0 AND 29 Then PatientDue Else 0 END 'CurrentDue',
CASE WHEN ServiceDateAge BETWEEN 30 AND 59 THEN PatientDue ELSE 0 END '30DayDue',
...etc
Here is a more complex example I have been using to age RFCs (requests for change) by counting the number of records falling
into each category and displaying the results in separate columns
CREATE view rfcageing as
select
rfc.module,
count(case when datediff( dd,rfc.dateraised, rfc.datecompleted) = 0 then 1 else null end) 'zerodays',
count(case when datediff( dd,rfc.dateraised, rfc.datecompleted) between 1 and 10 then 1 else null end) 'tendays',
count(case when datediff( dd,rfc.dateraised, rfc.datecompleted) between 11 and 20 then 1 else null end) 'twentydays',
count(case when datediff( dd,rfc.dateraised, rfc.datecompleted) between 21 and 30 then 1 else null end) 'thirtydays',
count(case when datediff( dd,rfc.dateraised, rfc.datecompleted) between 31 and 40 then 1 else null end) 'fortydays',
count(case when datediff( dd,rfc.dateraised, rfc.datecompleted) between 41 and 50 then 1 else null end) 'fiftydays',
count(case when datediff( dd,rfc.dateraised, rfc.datecompleted) >50 then 1 else null end) 'loadsadays',
count(rfc.datecompleted) 'total'
from dbo.changerequest rfc with (nolock)
where module is not null
and module>' '
and status='Closed'
group by rfc.module
I hope this helps
David
If it ain't broke, don't fix it...
-
Richard, can you maybe give us the query that you have written so far...it will just make it easier toe either spot the mistake or help with the correct way of doing it.
-
David, thank you for the sample code.
Martin, I got help from another MVP and here is what he came up with. I am using this now, but see if you agree with it. I first wrote a function as follows:
CREATE FUNCTION dbo.AgeAmount (@LowAge int, @HighAge int, @Age int, @Value
int)
RETURNS int AS
BEGIN
RETURN
CASE
WHEN @Age BETWEEN @LowAge AND @HighAge THEN @Value
ELSE 0
END
END
Then I wrote my query this way:
SELECT dbo.uvwOpenChargesWithAge.*,
dbo.AgeAmount(0, 29, ServiceDateAge, InsDue) AS InsCurrentDue, dbo.AgeAmount(30, 59, ServiceDateAge, InsDue) AS Ins30Due, dbo.AgeAmount(60, 89, ServiceDateAge, InsDue) AS Ins60Due, dbo.AgeAmount(90, 119, ServiceDateAge, InsDue) AS Ins90Due,
CASE WHEN ServiceDateAge > 120 THEN InsDue ELSE 0 END AS Ins120Due, dbo.AgeAmount(0, 29, ServiceDateAge, PatDue) AS PatCurrentDue,
dbo.AgeAmount(30, 59, ServiceDateAge, PatDue) AS Pat30Due, dbo.AgeAmount(60, 89, ServiceDateAge, PatDue) AS Pat60Due, dbo.AgeAmount(90, 119, ServiceDateAge, PatDue) AS Pat90Due, CASE WHEN ServiceDateAge > 120 THEN PatDue ELSE 0 END AS Pat120Due
FROM dbo.uvwOpenChargesWithAge
The query uvwOpenChargesWithAge ages each line item, so I don't have to repeat that multiple times in the above query, but now am thinking I might simplify by putting the DATEDIFF into the function. Don't know which way would perform better (two queries, first does DATEDIFF or compute DATEDIFF many times in second query).
As you see, I did find a solution, but any further comments that might teach me something more would be appreciated much.
rb
-
Hi Richard,
Nothing wrong with how you do it here. What I sometimes do in this kind of scenario is to create a temp table, insert all the base data and then update it with the ageing information. You might find that this is a bit better in terms of performance, depending on the amount of records you are going to return.
Functions are very useful, but unfortunately not always the best when it comes to performance. In the end, it just all depends on how you want to use it.
Martin.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply