Answered by:
Invoice Due (3060 Days / 6090 Days/ >90 Days)
Question

Answers

If you post the table structure, it may be easier to provide a solution.
In general, try
select sum(case when InvoiceDate between dateadd(day,60, CURRENT_TIMESTAMP) and dateadd(day,  30, CURRENT_TIMESTAMP) then Amount end) as [3060 Total], sum(case when InvoiceDate between dateadd(day,90, CURRENT_TIMESTAMP) and dateadd(day,  61, CURRENT_TIMESTAMP) then Amount end) as [60  90 Total], sum(case when InvoiceDate< dateadd(day,90, CURRENT_TIMESTAMP) then Amount end) as [>90 Total]
from Invoices
For every expert, there is an equal and opposite expert.  Becker's Law
My blog
My TechNet articles Edited by Naomi NModerator Wednesday, March 2, 2016 12:49 PM
 Marked as answer by Kalman TothModerator Tuesday, March 8, 2016 4:16 PM

Hi Hitesh1508,
Naomi N has shown you the method using CASE WHEN. To display the result in vertical direction, you may refer to script below.
declare @t table ( InvoiceId int identity(1,1), Amount money, DueDate date ) insert into @t values (100,'20151201'), (150,'20151215'), (200,'20160101'), (300,'20160201'), (350,'20160215'), (400,'20160301') ;with cte as ( SELECT *, CASE WHEN DueDate < DATEADD(DAY, 90, GETDATE()) THEN '>90 Days' WHEN DueDate < DATEADD(DAY, 60, GETDATE()) THEN '(60, 90] Days' WHEN DueDate <= DATEADD(DAY, 30, GETDATE()) THEN '[30, 60] Days' ELSE '<30 Days' END AS interval FROM @t ) select interval, COUNT(InvoiceId) AS cnt, SUM(Amount) AS total from cte group by interval
Sam Zha
TechNet Community Support Marked as answer by Hitesh1508 Thursday, March 3, 2016 2:20 PM

SELECT [30 TO 60 DAYS],[61 TO 90 DAYS],[> 90 DAYS] FROM (SELECT SUM(InvoiceAmount) AS Total, CASE WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) BETWEEN 30 AND 60 THEN '30 TO 60 DAYS' WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) BETWEEN 61 AND 90 THEN '61 TO 90 DAYS' WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) > 90 THEN '> 90 DAYS' END AS Category FROM InvoiceTable GROUP BY CASE WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) BETWEEN 30 AND 60 THEN '30 TO 60 DAYS' WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) BETWEEN 61 AND 90 THEN '61 TO 90 DAYS' WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) > 90 THEN '> 90 DAYS' END )t PIVOT (SUM(Total) FOR Category IN ([30 TO 60 DAYS],[61 TO 90 DAYS],[> 90 DAYS]))p
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page Marked as answer by Hitesh1508 Thursday, March 3, 2016 2:21 PM
All replies

If you post the table structure, it may be easier to provide a solution.
In general, try
select sum(case when InvoiceDate between dateadd(day,60, CURRENT_TIMESTAMP) and dateadd(day,  30, CURRENT_TIMESTAMP) then Amount end) as [3060 Total], sum(case when InvoiceDate between dateadd(day,90, CURRENT_TIMESTAMP) and dateadd(day,  61, CURRENT_TIMESTAMP) then Amount end) as [60  90 Total], sum(case when InvoiceDate< dateadd(day,90, CURRENT_TIMESTAMP) then Amount end) as [>90 Total]
from Invoices
For every expert, there is an equal and opposite expert.  Becker's Law
My blog
My TechNet articles Edited by Naomi NModerator Wednesday, March 2, 2016 12:49 PM
 Marked as answer by Kalman TothModerator Tuesday, March 8, 2016 4:16 PM

Hi Hitesh1508,
Naomi N has shown you the method using CASE WHEN. To display the result in vertical direction, you may refer to script below.
declare @t table ( InvoiceId int identity(1,1), Amount money, DueDate date ) insert into @t values (100,'20151201'), (150,'20151215'), (200,'20160101'), (300,'20160201'), (350,'20160215'), (400,'20160301') ;with cte as ( SELECT *, CASE WHEN DueDate < DATEADD(DAY, 90, GETDATE()) THEN '>90 Days' WHEN DueDate < DATEADD(DAY, 60, GETDATE()) THEN '(60, 90] Days' WHEN DueDate <= DATEADD(DAY, 30, GETDATE()) THEN '[30, 60] Days' ELSE '<30 Days' END AS interval FROM @t ) select interval, COUNT(InvoiceId) AS cnt, SUM(Amount) AS total from cte group by interval
Sam Zha
TechNet Community Support Marked as answer by Hitesh1508 Thursday, March 3, 2016 2:20 PM

SELECT [30 TO 60 DAYS],[61 TO 90 DAYS],[> 90 DAYS] FROM (SELECT SUM(InvoiceAmount) AS Total, CASE WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) BETWEEN 30 AND 60 THEN '30 TO 60 DAYS' WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) BETWEEN 61 AND 90 THEN '61 TO 90 DAYS' WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) > 90 THEN '> 90 DAYS' END AS Category FROM InvoiceTable GROUP BY CASE WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) BETWEEN 30 AND 60 THEN '30 TO 60 DAYS' WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) BETWEEN 61 AND 90 THEN '61 TO 90 DAYS' WHEN DATEDIFF(dd,InvoiceDate,GETDATE()) > 90 THEN '> 90 DAYS' END )t PIVOT (SUM(Total) FOR Category IN ([30 TO 60 DAYS],[61 TO 90 DAYS],[> 90 DAYS]))p
Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page Marked as answer by Hitesh1508 Thursday, March 3, 2016 2:21 PM