Split the invoice date as per startdatetime and enddatetime

Hi,

Thanks for help.

I want to seprate invoice data as per month wise.

below i have table.

CREATE TABLE [dbo].[Invoice]( [InvoiceNo] [numeric](18, 0) NULL, [StartDateTime] [datetime] NULL, [EndDateTime] [datetime] NULL, [InvHours] [numeric](18, 2) NULL, [Amount] [numeric](18, 2) NULL ) ON [PRIMARY] GO

and the data is given below.

INSERT INTO Invoice ( InvoiceNo, StartDateTime,EndDateTime,InvHours,Amount ) VALUES (1,'2022-04-28 22:00:00.000','2022-05-02 12:15:00.000', 86.25,3000), (2,'2022-05-21 12:45:00.000','2022-05-24 04:15:00.000', 63.5,1000), (3,'2022-05-21 19:00:00.000','2022-05-24 20:45:00.000', 73.75,2000)

if run the query select * from Invoice below is the result.

InvoiceNo	StartDateTime	EndDateTime	InvHours	Amount
1	28-04-2022 22:00	02-05-2022 12:15	86.25	3000
2	21-05-2022 12:45	24-05-2022 04:15	63.5	1000
3	21-05-2022 19:00	24-05-2022 20:45	73.75	2000

Now the invoiceNo 1 start from 28-04-2022 to 02-05-2022 now i'm looking for query to split the InvoiceNo 1 StartDate 28-04-2022 and end 30-04-2022 and calculate hours and amount.

below result i'm looking for. Amount 3000/86.25=34.78

InvoiceNo	StartDateTime	EndDateTime	InvHours	Amount	This column shows calculation
1	28-04-2022 22:00	30-04-2022 23:59	49.98	1738.550713	=49.98*34.78=1738.551
1	30-04-2022 23:59	02-05-2022 12:15	36.27	1261.449252	=36.24*34.78=1261.449
2	21-05-2022 12:45	24-05-2022 04:15	63.5	1000
3	21-05-2022 19:00	24-05-2022 20:45	73.75	2000

I wrote the below code but new_enddate timing is not coming, Its coming 2022-04-30 22:00:00.000 It should come 2022-04-30 11:59:59 and also repeate same in new_StartDateTime. ( if the startDate and EndDate month is different)

;WITH n(n) AS ( SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns ), d(n,f,t,md,bp,ep,am,hr,InVNo) AS ( SELECT n.n, d.StartDateTime, d.EndDateTime, DATEDIFF(MONTH, d.StartDateTime, d.EndDateTime), --new start date & Time DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(StartDateTime), StartDateTime)), --new End Date & Time DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n,DATEADD(DAY, 1-DAY(StartDateTime), StartDateTime)))), --DATEADD(month, ((YEAR(StartDateTime) - 1900) * 12) + MONTH(StartDateTime), -1), d.Amount,d.InvHours,d.InvoiceNo FROM n INNER JOIN Invoice AS d ON d.EndDateTime >= DATEADD(MONTH, n.n-1, d.StartDateTime) ) SELECT original_StartDateTime = f, original_EndDateTime = t, new_StartDateTime = CASE n WHEN 0 THEN f ELSE bp END, new_EndDateTime = CASE n WHEN md THEN t ELSE ep END, new_Amount = am, new_Hour = hr, getNewHour=DATEDIFF(MINUTE,CASE n WHEN 0 THEN f ELSE bp END,CASE n WHEN md THEN t ELSE ep END)/60.0, Per_Hr=round(am/hr,2), NewAmountUSD=(DATEDIFF(MINUTE,CASE n WHEN 0 THEN f ELSE bp END,CASE n WHEN md THEN t ELSE ep END)/60.0)*round(am/hr,2), NewInvNo=InVNo FROM d WHERE md >= n ORDER BY original_StartDateTime, new_StartDateTime;

original_StartDateTime	original_EndDateTime	new_StartDateTime	new_EndDateTime	new_Amount	new_Hour	getNewHour	Per_Hr	NewAmountUSD	NewInvNo
28-04-2022 22:00	02-05-2022 12:15	28-04-2022 22:00	30-04-2022 22:00	3000	86.25	48	34.78	1669.44	1
28-04-2022 22:00	02-05-2022 12:15	01-05-2022 22:00	02-05-2022 12:15	3000	86.25	14.25	34.78	495.615	1
21-05-2022 12:45	24-05-2022 04:15	21-05-2022 12:45	24-05-2022 04:15	1000	63.5	63.5	15.75	1000.125	2
21-05-2022 19:00	24-05-2022 20:45	21-05-2022 19:00	24-05-2022 20:45	2000	73.75	73.75	27.12	2000.1	3

Thanks

Basit.

Check the query

;WITH n(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns ), d(n,f,t,md,bp,ep,am,hr,InVNo) AS (
SELECT n.n, d.StartDateTime, d.EndDateTime, DATEDIFF(MONTH, d.StartDateTime, d.EndDateTime), --new start date & Time
DATEADD(MONTH, DATEDIFF(MONTH, 0, EndDateTime), 0), --new End Date & Time
DATEADD(SECOND, -1, DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDateTime) , 0))),
d.Amount,d.InvHours,d.InvoiceNo FROM n INNER JOIN Invoice AS d ON d.EndDateTime >= DATEADD(MONTH, n.n-1, d.StartDateTime) )
SELECT original_StartDateTime = f, original_EndDateTime = t, new_StartDateTime = CASE n WHEN 0 THEN f ELSE bp END,
new_EndDateTime = CASE n WHEN md THEN t ELSE ep END, new_Amount = am, new_Hour = hr,
getNewHour=CAST(DATEDIFF(SECOND,CASE n WHEN 0 THEN f ELSE bp END,CASE n WHEN md THEN t ELSE ep END)/3600.0 AS DECIMAL(10,2)),
Per_Hr = FORMAT(am/hr, 'N2'),
NewAmountUSD=FORMAT(ROUND((DATEDIFF(SECOND,CASE n WHEN 0 THEN f ELSE bp END,CASE n WHEN md THEN t ELSE ep END)/3600.0) * (am/hr), 0), 'N2'),
NewInvNo=InVNo FROM d WHERE md >= n ORDER BY original_StartDateTime, new_StartDateTime;

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty4y ago
Accepted answer
Check the query
;WITH n(n) AS (
SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns ), d(n,f,t,md,bp,ep,am,hr,InVNo) AS (
SELECT n.n, d.StartDateTime, d.EndDateTime, DATEDIFF(MONTH, d.StartDateTime, d.EndDateTime), --new start date & Time
DATEADD(MONTH, DATEDIFF(MONTH, 0, EndDateTime), 0), --new End Date & Time
DATEADD(SECOND, -1, DATEADD(MONTH, 1,  DATEADD(MONTH, DATEDIFF(MONTH, 0, StartDateTime) , 0))),
d.Amount,d.InvHours,d.InvoiceNo FROM n INNER JOIN Invoice AS d ON d.EndDateTime >= DATEADD(MONTH, n.n-1, d.StartDateTime) )
SELECT original_StartDateTime = f, original_EndDateTime = t, new_StartDateTime = CASE n WHEN 0 THEN f ELSE bp END,
new_EndDateTime = CASE n WHEN md THEN t ELSE ep END, new_Amount = am, new_Hour = hr,
getNewHour=CAST(DATEDIFF(SECOND,CASE n WHEN 0 THEN f ELSE bp END,CASE n WHEN md THEN t ELSE ep END)/3600.0 AS DECIMAL(10,2)),
Per_Hr = FORMAT(am/hr, 'N2'),
NewAmountUSD=FORMAT(ROUND((DATEDIFF(SECOND,CASE n WHEN 0 THEN f ELSE bp END,CASE n WHEN md THEN t ELSE ep END)/3600.0) * (am/hr), 0), 'N2'),
NewInvNo=InVNo FROM d WHERE md >= n ORDER BY original_StartDateTime, new_StartDateTime;
Basit Khan4y ago
Many Many Thanks