Split the invoice date as per startdatetime and enddatetime

Transact-SQL
1

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 used datediff to calculate hours manually.

select DATEDIFF(MINUTE,'2022-04-28 22:00:00.000','2022-04-30 23:59:59.000')/60.0
select DATEDIFF(MINUTE,'2022-04-30 23:59:59.000','2022-05-02 12:15:00.000')/60.0

Please help on to make above query.

Thanks & Regards,
Basit.

2

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)

below is the output

original_StartDateTime original_EndDateTime new_StartDateTime new_EndDateTime new_Amount new_Hour getNewHour Per_Hr NewAmountUSD NewInvNo
2022-04-28 22:00:00.000 2022-05-02 12:15:00.000 2022-04-28 22:00:00.000 2022-04-30 22:00:00.000 3000.00 86.25 48.000000 34.78000000000000000000 1669.44000000 1
2022-04-28 22:00:00.000 2022-05-02 12:15:00.000 2022-05-01 22:00:00.000 2022-05-02 12:15:00.000 3000.00 86.25 14.250000 34.78000000000000000000 495.61500000 1
2022-05-21 12:45:00.000 2022-05-24 04:15:00.000 2022-05-21 12:45:00.000 2022-05-24 04:15:00.000 1000.00 63.50 63.500000 15.75000000000000000000 1000.12500000 2
2022-05-21 19:00:00.000 2022-05-24 20:45:00.000 2022-05-21 19:00:00.000 2022-05-24 20:45:00.000 2000.00 73.75 73.750000 27.12000000000000000000 2000.10000000 3
2022-06-28 22:00:00.000 2022-07-02 12:15:00.000 2022-06-28 22:00:00.000 2022-06-30 22:00:00.000 3000.00 86.25 48.000000 34.78000000000000000000 1669.44000000 4
2022-06-28 22:00:00.000 2022-07-02 12:15:00.000 2022-07-01 22:00:00.000 2022-07-02 12:15:00.000 3000.00 86.25 14.250000 34.78000000000000000000 495.61500000 4 
 ;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;