GROUP by Date Ranges and get the Sum

Question

0.00/5 (No votes)

See more:

I need a get sum of my Customer Payment in credit Terms. I declare 3 credit Terms

- Over 45 Days
- 45 Days
- 30 Days

I Genarated a Customer Invoice my System and post the customer.then Start Days Count Invoice Create.Same Customer Not Paid last Month of past Invoices.I want to Get sum of The Unpaid Amount Using my Cridet Terms.(Separate Amount Over 45,45 Days And 30 Days).

This Is my Table.

SQL

SELECT [InvoiceNo] ,[cusCode],[InvAmt],[CreateUserID],[CreateDate],[CreateTime],[Confirm] ,[TotalPaid],[TotalDuePaid]
 FROM [tblInvoiceHeader]

I try This One

SQL

SELECT TotalDuePaid,DATEDIFF(DAY, CreateDate, GETDATE()) AS DayDiff FROM tblInvoiceHeader   WHERE Confirm='0' AND cusCode='1'

it's Working But i want to Select All 3 Terms in At a 1 time

EX- CusCode=1 unpaid 2 invoices,this 2 invoices are 2 days I want show this unpaid data given bellow table.

CusCode    Over 45 Days     45 Days      30 Days
    1         500.00           0.00         560.00

How to do it Any one can help me?

Posted 24-Feb-14 1:53am

Hasitha Wijesooriya

Updated 24-Feb-14 2:03am

phil.o

v3

Add a Solution

Comments

King Fisher 24-Feb-14 8:04am

show your Table structure and expected Output.

[no name] 24-Feb-14 8:12am

I show my Table and output in the quetion.it's not Clear.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

govardhan4u · Answer 1 · 2014-02-24T05:07:00

Try with this query.

SQL

select cusCode,SUM(inv1.TotalDuePaid),SUM(inv2.TotalDuePaid),SUM(inv3.ITotalDuePaid) from INVOICES inv
left outer join INVOICES inv1 on inv.INVOICE_ID = inv1.INVOICE_ID and inv1.CreateDate between GETDATE()-30 and GETDATE()
left outer join INVOICES inv2 on inv.INVOICE_ID = inv2.INVOICE_ID and inv2.CreateDate between GETDATE()-34 and GETDATE()-30
left outer join INVOICES inv3 on inv.INVOICE_ID = inv3.INVOICE_ID and inv3.CreateDate > GETDATE()-45
group by cusCode

Maciej Los · Answer 2 · 2014-02-24T07:17:00

Solution 2

There is few ways to achieve that:

using Pivot table[^]

Pivot tables in SQL Server. A simple sample.

^

using CASE WHEN ... THEN ... ELSE ... END[^]

SQL

SELECT CusCode, CASE WHEN DATEDIF(...) BETWEEN 0 AND 30 THEN TotalDuePaid END AS [0-30 days],
CASE WHEN DATEDIF(...) BETWEEN 31 AND 45 THEN TotalDuePaid END AS [31-45 days],
CASE WHEN DATEDIF(...) > 45 THEN TotalDuePaid END AS [over 45 days],  
FROM TableName
WHERE ...

Try!

Posted 24-Feb-14 7:17am

Maciej Los

Comments

[no name] 24-Feb-14 22:54pm

I try,query working but I want TO get sum of this 3 types separately.

SELECT CASE WHEN DATEDIFF(DAY,CreateDate,GETDATE()) BETWEEN 0 AND 30 THEN TotalDuePaid END AS [0-30 days],
CASE WHEN DATEDIFF(DAY,CreateDate,GETDATE()) BETWEEN 31 AND 45 THEN TotalDuePaid END AS [31-45 days],
CASE WHEN DATEDIFF(DAY,CreateDate,GETDATE()) > 45 THEN TotalDuePaid END AS [over 45 days]
FROM tblOutboundInvoiceHeader
WHERE CusCode='00097' AND Confirm='0'

Maciej Los 25-Feb-14 1:35am

SELECT CusCode, SUM([0-30 days]), SUM([31-45 days]), SUM([over 45 days])
FROM ( _here_use_above_query_ ) AS T
GROUP BY CusCode