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

closed as off-topic by Philᵀᴹ, Michael Green, Laurenz Albe, John aka hot2use, McNets Oct 22 at 12:43

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Philᵀᴹ, Laurenz Albe, McNets
If this question can be reworded to fit the rules in the help center, please edit the 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 at 19:50
  • 1
    Please edit the question to remove the images and add minimal reproducible example instead. – Michael Green Oct 22 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

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