Aging Account information

[richardb]

Here some code for a passthrough query:

SELECT f.category, c.FinanceClass, c.ProviderCode, PatDue,
PatDueCurrent =
Case DateDiff(day, service_date, GetDate())
WHEN 60 THEN PatDue ELSE 0 END,
PatDue60 =
Case DateDiff(day, service_date, GetDate())
WHEN 90 THEN PatDue ELSE 0 END
FROM uvwOpenCharges c JOIN FinanceClass f ON c.FinanceClass = f.FinanceClass ;

However, it's not what I really want. What I really want is for
PatDueCurrent to return Patdue when the DateDiff is LESS THAN 60. And I want
PatDue60 to return PatDue when the DateDiff is greater than or equal to 60
but less than 90. I have tried to use "<" operators but keep getting a syntax
error.

Can someone explain how to write my query please. Thanks...

 

[richardb]

Here is my solution...

SELECT f.category, c.FinanceClass, c.ProviderCode, PatDue, InsDue,
DateDiff(day, service_date, GetDate()) AS Age,
PatDueCurrent = Case WHEN DateDiff(day, service_date, GetDate()) < 60 THEN
PatDue ELSE 0 END,
PatDue60 = Case WHEN DateDiff(day, service_date, GetDate()) BETWEEN 61 AND
90 THEN PatDue ELSE 0 END,
InsDueCurrent = Case WHEN DateDiff(day, service_date, GetDate()) < 60 THEN
InsDue ELSE 0 END,
InsDue60 = Case WHEN DateDiff(day, service_date, GetDate()) BETWEEN 61 AND
90 THEN InsDue ELSE 0 END
FROM uvwOpenCharges c JOIN FinanceClass f ON c.FinanceClass = f.FinanceClass ;

Unless someone sees a way to streamline this, I think I've got it figured
out...