Split the invoice date as per startdatetime and enddatetime

Question

0.00/5 (No votes)

See more:

Hi,

Thanks for help.

I want to seprate invoice data as per month wise.

below i have table.

SQL

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.

SQL

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

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

Thanks & Regards,
Basit.

What I have tried:

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

SQL

;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;

The out put is given below.

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

Posted 2-Aug-22 18:24pm

basitsar

Updated 3-Aug-22 8:57am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2022-08-03T08:57:00

Well...

I'm not getting an idea about amount calculations. So, i decided to show you the first part of the job - how to "split" period of time on months (+ something extra). See:

SQL

;WITH CTE AS
(
  -- initial part
  SELECT InvoiceNo, StartDateTime, EndDateTime AS RealEndDateTime,
    CASE
      WHEN EndDateTime < EOMONTH(StartDateTime, 0) THEN EndDateTime
      ELSE EOMONTH(StartDateTime, 0)
    END AS CalculatedEndOfTime, InvHours, Amount,
    DATEDIFF(dd, StartDateTime, EndDateTime) + 1 AS WorkedDays
  FROM Invoice
  --recursive part
  UNION ALL
  SELECT InvoiceNo, DATEADD(dd, 1, CalculatedEndOfTime) AS StartDateTime, 
    RealEndDateTime,
    RealEndDateTime AS CalculatedEndOfTime, InvHours, Amount, WorkedDays
  FROM CTE 
  WHERE CalculatedEndOfTime < RealEndDateTime
)
--final query
SELECT InvoiceNo, 
    StartDateTime,
    CalculatedEndOfTime AS EndDateTime,
    InvHours,
    PayDays,
    WorkedDays,
    Amount
FROM
(
  -- subquery
  SELECT *,
    DATEDIFF(dd, StartDateTime, CalculatedEndOfTime) + 1 AS PayDays
  FROM CTE
) AS T  
ORDER BY T.InvoiceNo, T.StartDateTime;

SQL Server 2019 | db<>fiddle[^]

Result:

InvoiceNo 	StartDateTime 	EndDateTime 	InvHours 	PayDays 	WorkedDays 	Amount
1 	2022-04-28 22:00:00.000 	2022-04-30 00:00:00.000 	86.25 	3 	5 	3000.00
1 	2022-05-01 00:00:00.000 	2022-05-02 12:15:00.000 	86.25 	2 	5 	3000.00
2 	2022-05-21 12:45:00.000 	2022-05-24 04:15:00.000 	63.50 	4 	4 	1000.00
3 	2022-05-21 19:00:00.000 	2022-05-24 20:45:00.000 	73.75 	4 	4 	2000.00

Note: above example will return proper dates, if a difference between start date and end date is not greater than 1 month.

Good luck!