CODEWITHSUNDEEP
sql
Coopernicus
Coopernicus

Reputation: 95

SQL - How do split data by date into different monthly 'pots'?

New to sql and having trouble figuring the following, though probably straightforward for more experienced users. I have a table of outstanding monies which I need to divide up into monthly pots; the table has the following columns:

Name, amount_due, date_due.

I need to divide the info by 'date_due' into 5 different monthly pots, the current month, last month, 2 months old, 3 months old, > 3 months old.

Upvotes: 1

Views: 2399

Answers (3)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2073

Use case to determine the due_date between months of due

SELECT name,
       amount_due,
       due_date,
       CASE WHEN CAST(due_date AS DATETIME) BETWEEN DATEADD(mm, -1, GETDATE()) AND DATEADD(mm, 0, GETDATE())
            THEN 'this month'
            WHEN CAST(due_date AS DATETIME) BETWEEN DATEADD(mm, -2, GETDATE()) AND DATEADD(mm, -1, GETDATE())
            THEN 'last month'
            WHEN CAST(due_date AS DATETIME) BETWEEN DATEADD(mm, -3, GETDATE()) AND DATEADD(mm, -2, GETDATE())
            THEN '2 months old'
            WHEN CAST(due_date AS DATETIME) BETWEEN DATEADD(mm, -4, GETDATE()) AND DATEADD(mm, -3, GETDATE())
            THEN '3 months old'
            WHEN CAST(due_date AS DATETIME) < DATEADD(mm, -4, GETDATE())
            THEN '> 3 months old'
        END month_of_due

Upvotes: 0

Dai
Dai

Reputation: 155726

I assume you're after summary data that performs a SUM for each month, as opposed to showing all the rows sorted by month.

name   , amount_due, date_due
Alice  ,        100, 2017-09-10
Bob    ,        500, 2017-07-03
Charlie,        300, 2017-07-02
Dan    ,        150, 2017-04-01
Eve    ,        200, 2017-01-01
Faith  ,         50, 2017-09-13

This query converts the date_due value into a "month" value (while still retaining date or datetime type information), then sums them by each month:

Output:

sum_amount_due, month_due
           150, 2017-09-01
           800, 2017-07-01
           150, 2017-04-01
           200, 2017-01-01

SQL:

SELECT
    SUM( amount_due ) AS sum_amount_due,
    DATEADD( month, DATEDIFF( month, 0, date_due ), 0 ) AS month_due
FROM
    your_table
GROUP BY
    DATEADD( month, DATEDIFF( month, 0, date_due ), 0 )
ORDER BY
    month_due

This query does not handle dates older than 3 months specially, so for that we need to change the month_due expression to return '2001-01-01' for dates older than 3 months:

Output:

sum_amount_due, month_due
           150, 2017-09-01
           800, 2017-07-01
           350, 2000-01-01

SQL:

SELECT
    SUM( amount_due ) AS sum_amount_due,
    CASE
        WHEN date_due < DATEADD( month, GETDATE(), -3 ) THEN '2000-01-01'
        ELSE DATEADD( month, DATEDIFF( month, 0, date_due ), 0 )
    END AS month_due
FROM
    your_table
GROUP BY
    CASE
        WHEN date_due < DATEADD( month, GETDATE(), -3 ) THEN '2000-01-01'
        ELSE DATEADD( month, DATEDIFF( month, 0, date_due ), 0 )
    END
ORDER BY
    month_due

Due to how SQL works, you need to repeat the get-month expression in both the SELECT and GROUP BY clauses.

This can be made slightly syntactically simpler by using a subquery to identify records older than 3 months:

SELECT
    SUM( amount_due ) AS sum_amount_due,
    CASE
        WHEN month_due_3_months THEN '2000-01-01'
        ELSE month_due
    END AS month_due
FROM
(
    SELECT
        amount_due,
        DATEADD( month, DATEDIFF( month, 0, date_due ), 0 ) AS month_due,
        CASE
            WHEN DATEADD( month, DATEDIFF( month, 0, date_due ), 0 ) < DATEADD( month, GETDATE(), -3 ) THEN 1
            ELSE 0
        END AS month_due_3_months
    FROM
        your_table
) AS all_months
GROUP BY
    CASE
        WHEN month_due_3_months THEN '2000-01-01'
        ELSE month_due
    END AS month_due
ORDER BY
    month_due

The syntactic complexity is due to some constraints inherent in the SQL language:

  • The SELECT sub-clause ("the projection") is evaluated after the FROM AND GROUP BY sub-clauses, so you cannot reference aliased expressions in GROUP BY: you must either repeat them or specify them in a subquery.
  • SQL does not have a Get month as date value function, surprisingly, you must use DATEADD( month, DATEDIFF( month, 0, @dateValue ), 0 ).
    • Do not use GETMONTH or DATEPART because it returns only the month component and disregards the year value, so it will incorrectly group rows from different years that share the same month.
  • There is no ternary operator in SQL, only the more verbose CASE WHEN x THEN y ELSE z END construct (though there is COALESCE, NULLIF, and ISNULL but those are special-cases).

Upvotes: 2

Horaciux
Horaciux

Reputation: 6487

I assumed all periods are cumulative, if not you need to modify each condition. Let me know if this works as expected or need modification.

select name 
    ,sum( case when datediff(day,getdate(),date_due)<day(date_due) then amount_due end) 'current month'
    ,sum( case when datediff(month,getdate(),date_due)<=1 then amount_due end) 'last month'
    ,sum( case when datediff(month,getdate(),date_due)<=2 then amount_due end) 'last two month'
    ,sum( case when datediff(month,getdate(),date_due)<=3 then amount_due end) 'last three month'
    ,sum( case when datediff(month,getdate(),date_due)>3 then amount_due end) 'more than three month'
from monies
group by name

Upvotes: 1

Related Questions