T-SQL | Group Invoices with concern dates that overide the issue date
-
I have a table with Invoice Documents per Project, in which i need to get statistics per project , year , month with sql. The difficulty is that for some of the invoices the user will have entered concerned dates that must be override the issued month of the Invoice Document and must be splitted proportionally by month according to the days per month concerned.
Consumer data is provided
SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO
CREATE TABLE #Invoices
(
Project Varchar(10) NOT NULL
,DocumentCode Varchar(15) NOT NULL
,IssueDate Date NOT NULL
,Cost Decimal NOT NULL
,ConcernFrom Date NULL
,ConcernTo Date NULL
);
INSERT INTO #Invoices
VALUES ('Prj-001', 'DOC-50001', '20230601', 500 , NULL , NULL),
('Prj-001', 'DOC-50002', '20230615', 1000 , '20230615' , '20230720'),
('Prj-001', 'DOC-50003', '20230625', 300 , NULL , NULL),
('Prj-002', 'DOC-50004', '20230710', 250 , NULL , NULL),
('Prj-002', 'DOC-50005', '20230731', 100 , NULL , NULL),
('Prj-002', 'DOC-50006', '20231015', 500 , '20231215' , '20240131')
What i have Done so far
select *,datediff(DAY,ConcernFrom,ConcernTo) as Days ,
Year(ConcernFrom) as YearFrom,month(ConcernFrom) as MonthFrom, Year(ConcernTo) as YearTo, Month(ConcernTo) as MonthTo,
DAY(EOMONTH(ConcernFrom)) - DATEDIFF(DAY, ConcernFrom,EOMONTH(ConcernFrom)) as RemDaysConcernFrom,
DAY(EOMONTH(ConcernTo)) - DATEDIFF(DAY, ConcernTo,EOMONTH(ConcernTo)) as RemDaysConcernTo
from Invoices
Ι am really stuggling how this can be done. Do you think it is a must for this to use a Stored Procedure for the result .. Any help is welcome because i am really struggling to get the desired result 🙂
-
I don't have time to answer right now, but would like to congratulate you on writing such a clear and detailed question, especially as you are new here … well done! I'm sure that someone will be along to help you.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
This took a while, but was fun! There are a couple of characters in this forum who like to come along and make my code run 17 times faster, with half the lines of code, so I'll await their input 🙂
First of all, I don't agree with your example date calculations. Surely the period from date1 to date2 is inclusive of both dates? In your example, you have 15 June to 30 June as 15 days (wrong), but 1 July to 20 July as 20 days (right). My example is based on the inclusive method.
Let me explain the code structure.
- Create a temporary date table, containing one row per year/month over a large enough date range.
- Create your sample table and data.
- Use a CTE to calculate effective dates and days to be allocated (current month and in total). Join the CTE to the temporary date table to generate rows for those entries where ConcernTo is in a different month from IssueDate
- Based on the various calculations in the CTE, calculate the proportion of Cost to be allocated to the current row
- Select from the CTE to perform the required aggregation (note that I used YYYY-MM format here to easily make the results appear in the correct order)
Note also that I use collapsible code regions, as provided by the SSMS Tools add-on. It means that the top part of my collapsed code looks like this, instead of what you will see in the Code window:
Although not included in your sample data, this code also handles the case where ConcernTo is more than one month after ConcernFrom.
----------------------------------------------------------------------------------
--#region Create a table of month starts (thank you once again for the base code, Jeff Moden!)
DECLARE @StartDate DATETIME --Inclusive
,@EndDate DATETIME --Exclusive
,@Months INT;
SELECT @StartDate = '2020' --Inclusive
,@EndDate = '2030' --Exclusive
,@Months = DATEDIFF (mm, @StartDate, @EndDate);
DROP TABLE IF EXISTS #Mths;
CREATE TABLE #Mths
(
MonthStart DATE NOT NULL PRIMARY KEY CLUSTERED
,MonthEnd DATE NOT NULL
);
INSERT #Mths
(
MonthStart
,MonthEnd
)
SELECT TOP(@Months)
MonthStart = DATEADD (mm, ROW_NUMBER () OVER (ORDER BY(SELECT NULL)) - 1, @StartDate)
,MonthEnd = EOMONTH (DATEADD (mm, ROW_NUMBER () OVER (ORDER BY(SELECT NULL)) - 1, @StartDate))
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2;
--#endregion Create a table of month starts (thank you once again for the base code, Jeff Moden!)
----------------------------------------------------------------------------------
--#region Create sample table and populate with test data
DROP TABLE IF EXISTS #Invoices;
CREATE TABLE #Invoices
(
Project VARCHAR(10) NOT NULL
,DocumentCode VARCHAR(15) NOT NULL
,IssueDate DATE NOT NULL
,Cost DECIMAL(19, 6) NOT NULL
,ConcernFrom DATE NULL
,ConcernTo DATE NULL
);
INSERT #Invoices
VALUES
('Prj-001', 'DOC-50001', '20230601', 500, NULL, NULL)
,('Prj-001', 'DOC-50002', '20230615', 1000, '20230615', '20230720')
,('Prj-001', 'DOC-50003', '20230625', 300, NULL, NULL)
,('Prj-002', 'DOC-50004', '20230710', 250, NULL, NULL)
,('Prj-002', 'DOC-50005', '20230731', 100, NULL, NULL)
,('Prj-002', 'DOC-50006', '20231015', 500, '20231215', '20240131');
--#endregion Create sample table and populate with test data
----------------------------------------------------------------------------------
WITH BaseData
AS (SELECT i.Project
,YrMth = CONVERT (CHAR(7), m.MonthStart, 126)
,AllocatedCost = c3.DaysInAccountingMonth * i.Cost / c2.AllocationDays
FROM #Invoices i
CROSS APPLY
(
SELECT EffectiveStartDate = ISNULL (i.ConcernFrom, i.IssueDate)
,EffectiveEndDate = ISNULL (i.ConcernTo, EOMONTH (i.IssueDate))
) c1
CROSS APPLY
(
SELECT EffectiveStartDateSOM = DATEADD (mm, DATEDIFF (m, 0, c1.EffectiveStartDate), 0)
,EffectiveEndDateSOM = DATEADD (mm, DATEDIFF (m, 0, c1.EffectiveEndDate), 0)
,AllocationDays = DATEDIFF (DAY, c1.EffectiveStartDate, c1.EffectiveEndDate) + 1
) c2
JOIN #Mths m
ON m.MonthStart
BETWEEN c2.EffectiveStartDateSOM AND c2.EffectiveEndDateSOM
CROSS APPLY
(
SELECT DaysInAccountingMonth = DATEDIFF ( DAY
,CASE
WHEN c1.EffectiveStartDate > m.MonthStart THEN
c1.EffectiveStartDate
ELSE
m.MonthStart
END
,CASE
WHEN m.MonthEnd <= c1.EffectiveEndDate THEN
m.MonthEnd
ELSE
c1.EffectiveEndDate
END
) + 1
) c3 )
SELECT BaseData.Project
,BaseData.YrMth
,Cost = CAST (SUM (BaseData.AllocatedCost) AS DECIMAL(19, 2))
FROM BaseData
GROUP BY BaseData.Project
,BaseData.YrMth
ORDER BY BaseData.Project
,BaseData.YrMth;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Ohhh my GOD @phil Parkin !
I have no words to thank you for giving a so detailed answer and such a worthy solution!!!
You are absoluttely correct regarding the inclusive dates and my question..
There is so much info here for someone to go to next level of t-sql
From a first test it works like a charm
I promise to you that your time will not be wasted for me just to give the solution
but I will study this code as much as possible to be able to help others ..
Spread the Word !
- Phil Parkin wrote:
This took a while, but was fun! There are a couple of characters in this forum who like to come along and make my code run 17 times faster, with half the lines of code, so I'll await their input 🙂
Nicely done Phil. It's a great solution and it more than meets the OP's requirements. Why start from scratch when this great solution works? So I shamelessly copy/pasted it into VS and re-jiggered some things. There are 2 instances where your query has a CTE as the only table in its FROM clause. However, there's nothing (afaik) which necessitates using a CTE in these cases. Here I tried to factor out those SELECT's for which the only table in the FROM clause is a CTE and after initial copy/paste:
SELECT i.*
,i.Project
,YrMth = CONVERT (CHAR(7), m.MonthStart, 126)
--,Cost = CAST (SUM (c3.DaysInAccountingMonth * i.Cost / c2.AllocationDays) AS DECIMAL(19, 2)),
,c2.AllocationDays,
DaysInAccountingMonth = DATEDIFF ( DAY
,CASE
WHEN c1.EffectiveStartDate > m.MonthStart THEN
c1.EffectiveStartDate
ELSE
m.MonthStart
END
,CASE
WHEN m.MonthEnd <= c1.EffectiveEndDate THEN
m.MonthEnd
ELSE
c1.EffectiveEndDate
END
) + 1
FROM #Invoices i
CROSS APPLY
(
SELECT EffectiveStartDate = ISNULL (i.ConcernFrom, i.IssueDate)
,EffectiveEndDate = ISNULL (i.ConcernTo, EOMONTH (i.IssueDate))
) c1
CROSS APPLY
(
SELECT EffectiveStartDateSOM = DATEADD (mm, DATEDIFF (m, 0, c1.EffectiveStartDate), 0)
,EffectiveEndDateSOM = DATEADD (mm, DATEDIFF (m, 0, c1.EffectiveEndDate), 0)
,AllocationDays = DATEDIFF (DAY, c1.EffectiveStartDate, c1.EffectiveEndDate) + 1
) c2
JOIN #Mths m
ON m.MonthStart
BETWEEN c2.EffectiveStartDateSOM AND c2.EffectiveEndDateSOM
ORDER BY i.Project
,CONVERT (CHAR(7), m.MonthStart, 126);
/*
ProjectDocumentCodeIssueDateCostConcernFromConcernToProjectYrMthAllocationDaysDaysInAccountingMonth
Prj-001DOC-500032023-06-25300.000000NULLNULLPrj-0012023-0666
Prj-001DOC-500012023-06-01500.000000NULLNULLPrj-0012023-063030
Prj-001DOC-500022023-06-151000.0000002023-06-152023-07-20Prj-0012023-063616
Prj-001DOC-500022023-06-151000.0000002023-06-152023-07-20Prj-0012023-073620
Prj-002DOC-500042023-07-10250.000000NULLNULLPrj-0022023-072222
Prj-002DOC-500052023-07-31100.000000NULLNULLPrj-0022023-0711
Prj-002DOC-500062023-10-15500.0000002023-12-152024-01-31Prj-0022023-124817
Prj-002DOC-500062023-10-15500.0000002023-12-152024-01-31Prj-0022024-014831
*/The last 3 columns show the calculation matches the OP's/Phil's output without the CTE's 🙂 Afaik this query matches the final output
SELECT i.Project
,c3.YrMth
,Cost = CAST(SUM(cast(c3.DaysInAccountingMonth * i.Cost / c2.AllocationDays as money)) AS DECIMAL(19, 2))
FROM #Invoices i
CROSS APPLY
(SELECT EffectiveStartDate = ISNULL (i.ConcernFrom, i.IssueDate)
,EffectiveEndDate = ISNULL (i.ConcernTo, EOMONTH (i.IssueDate))) c1
CROSS APPLY
(SELECT EffectiveStartDateSOM = DATEADD (mm, DATEDIFF (m, 0, c1.EffectiveStartDate), 0)
,EffectiveEndDateSOM = DATEADD (mm, DATEDIFF (m, 0, c1.EffectiveEndDate), 0)
,AllocationDays = DATEDIFF (DAY, c1.EffectiveStartDate, c1.EffectiveEndDate) + 1) c2
JOIN #Mths m ON m.MonthStart BETWEEN c2.EffectiveStartDateSOM AND c2.EffectiveEndDateSOM
CROSS APPLY
(SELECT DaysInAccountingMonth = DATEDIFF(DAY,
CASE WHEN c1.EffectiveStartDate > m.MonthStart
THEN c1.EffectiveStartDate
ELSE m.MonthStart END,
CASE WHEN m.MonthEnd <= c1.EffectiveEndDate
THEN m.MonthEnd
ELSE c1.EffectiveEndDate END)+1
,YrMth = CONVERT (CHAR(7), m.MonthStart, 126)) c3
GROUP BY i.Project
,c3.YrMth
ORDER BY i.Project
,c3.YrMth;
/* Phil's output
Project YrMth Cost
Prj-001 2023-06 1244.44
Prj-001 2023-07 555.56
Prj-002 2023-07 350.00
Prj-002 2023-12 177.08
Prj-002 2024-01 322.92
*/
/* Steve's output
Project YrMth Cost
Prj-001 2023-06 1244.44
Prj-001 2023-07 555.56
Prj-002 2023-07 350.00
Prj-002 2023-12 177.08
Prj-002 2024-01 322.92
*/Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Here is what I came up with. If I had more time, I'd rewrite it to use closed-open intervals rather than open-closed intervals. (That is, I'd include the start date, but not the end date in the range, rather than the other way around.)
I used what I call a mini-tally (only four rows) to create the end-of-month dates. This has the advantage, that there are NO READS for the tally table. This can be expanded by adding more values and/or cross joining to itself as many times as necessary. (Phil, you should update your process to use a similar approach instead of reading the sys.all_columns view.)
I also simplified some of the calculations by converting all intervals to open-closed by subtracting 1 from the start date of the intervals.
WITH miniTally AS
(
SELECT t.n
FROM (VALUES(0), (1), (2), (3)) t(n)
)
, BaseData AS
(
SELECT i.Project
, i.DocumentCode
, i.IssueDate
, i.Cost
, c.ConcernFrom
, c.ConcernTo
, LAG(eor.AccountingTo, 1, c.ConcernFrom) OVER(PARTITION BY i.Project, i.DocumentCode ORDER BY eor.AccountingTo) AS AccountingFrom
, eor.AccountingTo
FROM #Invoices AS i
CROSS APPLY(VALUES(DATEADD(DAY, -1, COALESCE(i.ConcernFrom, i.IssueDate)), COALESCE(i.ConcernTo, i.IssueDate))) c(ConcernFrom,ConcernTo)
CROSS APPLY
(
SELECT EOMONTH(DATEADD(MONTH, mt.n, c.ConcernFrom)) AS AccountingTo
FROM miniTally AS mt
UNION
SELECT c.ConcernTo
) eor
WHERE eor.AccountingTo > c.ConcernFrom
AND eor.AccountingTo <= c.ConcernTo
)
SELECT b.Project
, CONVERT(CHAR(7), EOMONTH(b.AccountingTo), 126) AS YrMth
, SUM(CAST(DATEDIFF(DAY, b.AccountingFrom, b.AccountingTo) * b.Cost/DATEDIFF(DAY, b.ConcernFrom, b.ConcernTo) AS DECIMAL(19,2))) AS Cost
FROM BaseData AS b
GROUP BY b.Project, EOMONTH(b.AccountingTo)Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply