SQL Parameter group by 30,60,90 120 days...

plucenko
plucenko used Ask the Experts™
on
How would I implement this with Parameters where the query set the date, and 30,60,90 and 120 are parameters that would have to go off of the date set by the query?


select Billing.Bill.AccountNo,
       sum(case when DATEDIFF(DD, Billing.Bill.DueDate, GETDATE()) < 30 then Billing.Bill.CurrAmt else 0 end) ['0 - 30'],
       sum(case when DATEDIFF(DD, Billing.Bill.DueDate, GETDATE()) > 30 and DATEDIFF(DD, Billing.Bill.DueDate, GETDATE()) < 60 then Billing.Bill.CurrAmt else 0 end) ['31 - 60'],
       sum(case when DATEDIFF(DD, Billing.Bill.DueDate, GETDATE()) > 60 and DATEDIFF(DD, Billing.Bill.DueDate, GETDATE()) < 90 then Billing.Bill.CurrAmt else 0 end) ['61 - 90'],
       sum(case when DATEDIFF(DD, Billing.Bill.DueDate, GETDATE()) > 90 and DATEDIFF(DD, Billing.Bill.DueDate, GETDATE()) < 120 then Billing.Bill.CurrAmt else 0 end) ['91 - 120'],      
       sum(case when DATEDIFF(DD, Billing.Bill.DueDate, GETDATE()) > 120 then Billing.Bill.CurrAmt else 0 end) ['Over 120']
from Billing.Bill
group by Billing.Bill.AccountNo
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
What problem are you having?  Outside of the minor hassle of your column names (!) you'd just plug the parameters in where your comparisons are, like @Aging1 instead of 30, @Aging2 instead of 60, etc.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial