1

enter image description here

I am having a billing table like as above.

I need to get a billing age wise report by using distinct of Branch, UserId, Customer, BillType.

I need to wirte a Stored procedure for getting the following result.

How to write a query for this

enter image description here

| improve this question | | | | |
  • 1
    Hello. How far have you got with writing the query yourself? Can you please post what you have so far? – Philᵀᴹ Oct 21 '19 at 19:50
  • 1
    Please edit the question to remove the images and add minimal reproducible example instead. – Michael Green Oct 22 '19 at 5:04
0

You'll want to check out the datediff function, see below example.

select branch, 
       userid,
       customer,
       billtype,
       case when datediff(d,billdate,getdate()) >= 0 and datediff(d,billdate,getdate()) <= 30 then billingamount else 0 end [0 - 30 days],
       case when datediff(d,billdate,getdate()) >= 31 and datediff(d,billdate,getdate()) <= 60 then billingamount else 0 end [31 - 60 days],
       case when datediff(d,billdate,getdate()) >= 61 and datediff(d,billdate,getdate()) <= 90 then billingamount else 0 end [61 - 90 days],
       case when datediff(d,billdate,getdate()) >= 91 and datediff(d,billdate,getdate()) <= 120 then billingamount else 0 end [91 - 120 days],
       case when datediff(d,billdate,getdate()) >= 121 then billingamount else 0 end [121+ days],
       billingamount as total
  from #test
| improve this answer | | | | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.