none
Invoice Due (30-60 Days / 60-90 Days/ >90 Days) RRS feed

  • 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

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 [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
    Moderator
  • 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 Hitesh1508 Thursday, March 3, 2016 2:20 PM
    Wednesday, March 2, 2016 5:03 AM
    Moderator
  • 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
    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
    Moderator
  • 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 Hitesh1508 Thursday, March 3, 2016 2:20 PM
    Wednesday, March 2, 2016 5:03 AM
    Moderator
  • 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
    Wednesday, March 2, 2016 6:01 AM