Reputation: 95
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
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
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:
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.Get month as date value
function, surprisingly, you must use DATEADD( month, DATEDIFF( month, 0, @dateValue ), 0 )
.
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.CASE WHEN x THEN y ELSE z END
construct (though there is COALESCE
, NULLIF
, and ISNULL
but those are special-cases).Upvotes: 2
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