# Invoice Due (30-60 Days / 60-90 Days/ >90 Days) • ### Question

• hi all

I need help to create Statement which display

invoice summary between 30 to 60 Days total / 60 to 90 Days and > 90 Days in SQL

Wednesday, March 2, 2016 2:13 AM

• 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 [30-60 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

Wednesday, March 2, 2016 3:41 AM
• 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,'2015-12-01'),
(150,'2015-12-15'),
(200,'2016-01-01'),
(300,'2016-02-01'),
(350,'2016-02-15'),
(400,'2016-03-01')

;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 Thursday, March 3, 2016 2:20 PM
Wednesday, March 2, 2016 5:03 AM
• ```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```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

• Marked as answer by Thursday, March 3, 2016 2:21 PM
Wednesday, March 2, 2016 6:01 AM

### 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 [30-60 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

Wednesday, March 2, 2016 3:41 AM
• 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,'2015-12-01'),
(150,'2015-12-15'),
(200,'2016-01-01'),
(300,'2016-02-01'),
(350,'2016-02-15'),
(400,'2016-03-01')

;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 Thursday, March 3, 2016 2:20 PM
Wednesday, March 2, 2016 5:03 AM
• ```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```