Invoice Table Split date and calculate

Transact-SQL
1

Hi,

I have a table called Invoice.

Below is the structure.

CREATE TABLE [dbo].[Invoice](
	[Inv_No] [int] NULL,
	[Inv_StartDt] [datetime] NULL,
	[Inv_EndDt] [datetime] NULL,
	[Inv_SEAmt] [numeric](18, 2) NULL,
	[Inv_HrTotal] [numeric](18, 2) NULL
) ON [PRIMARY]

GO

Pass two values.

INSERT INTO [dbo].[Invoice]
           ([Inv_No]
           ,[Inv_StartDt]
           ,[Inv_EndDt]
           ,[Inv_SEAmt]
           ,[Inv_HrTotal])
     VALUES
           (1
           ,'2013-02-28 15:00:00.000'
           ,'2013-03-01 07:30:00.000'
           ,8515.80
           ,111.00)
GO

INSERT INTO [dbo].[Invoice]
           ([Inv_No]
           ,[Inv_StartDt]
           ,[Inv_EndDt]
           ,[Inv_SEAmt]
           ,[Inv_HrTotal])
     VALUES
           (2
           ,'2022-10-21 06:00:00.000'
           ,'2023-01-09 14:30:00.000'
           ,8515.80
           ,114.00)
GO

For split date in between month i wrote below 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, start_month, start_year) AS
(
SELECT TOP 100 PERCENT
n.n,
d .Inv_StartDt,
d .Inv_EndDt,
DATEDIFF(MONTH, d .Inv_StartDt, d .Inv_EndDt) + (CASE WHEN DAY(d .Inv_EndDt) > DAY(d .Inv_StartDt) THEN 1 ELSE 0 END),
DATEADD(MONTH, DATEDIFF(MONTH, 0, Inv_EndDt), 0),
DATEADD(SECOND, - 1, DATEADD(MONTH, 1, DATEADD(MONTH, DATEDIFF(MONTH, 0, Inv_StartDt), 0))),
d .Inv_SEAmt,
d .Inv_HrTotal,
d .Inv_No,
MONTH(d .Inv_StartDt) AS start_month,
YEAR(d .Inv_StartDt) AS start_year
FROM n
INNER JOIN Invoice AS d
ON d .Inv_EndDt >= DATEADD(MONTH, n.n - 1, d .Inv_StartDt)
)
SELECT TOP 100 PERCENT
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 = round(am / hr, 2),
NewAmountUSD = round(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), 2), 2),
NewInvNo = InVNo,
start_month,
start_year
FROM d
WHERE md >= n
ORDER BY start_year, start_month, original_StartDateTime, new_StartDateTime;

But with Inv_No=1 result is coming perfect, but with Inv_No result is not coming, because its goes to another year.

below are the result. Inv_No=2 result its coming in negative.

original_StartDateTime original_EndDateTime new_StartDateTime new_EndDateTime new_Amount new_Hour getNewHour Per_Hr NewAmountUSD NewInvNo start_month start_year
28-02-2013 15:00 01-03-2013 07:30 28-02-2013 15:00 28-02-2013 23:59 8515.8 111 9 76.72 690.45 1 2 2013
28-02-2013 15:00 01-03-2013 07:30 01-03-2013 00:00 01-03-2013 07:30 8515.8 111 7.5 76.72 575.39 1 2 2013
21-10-2022 06:00 09-01-2023 14:30 21-10-2022 06:00 31-10-2022 23:59 8515.8 114 258 74.7 19272.58 2 10 2022
21-10-2022 06:00 09-01-2023 14:30 01-01-2023 00:00 31-10-2022 23:59 8515.8 114 -1464 74.7 -109360.82 2 10 2022
21-10-2022 06:00 09-01-2023 14:30 01-01-2023 00:00 31-10-2022 23:59 8515.8 114 -1464 74.7 -109360.82 2 10 2022
21-10-2022 06:00 09-01-2023 14:30 01-01-2023 00:00 09-01-2023 14:30 8515.8 114 206.5 74.7 15425.55 2 10 2022

Kindly help on this.

regards,
Basit.

2

hi

i hope this helps

; with tally_cte as 
(
SELECT N=number FROM master..spt_values WHERE type = 'P'
)
select Inv_StartDt from [dbo].[Invoice] where Inv_No = 2 
   UNION ALL
select cast(cast(eomonth(DATEADD(month, DATEDIFF(month, 0, Inv_StartDt), 0),N) as varchar)+' 23:59:59' as datetime) 
  from tally_cte a , dbo.Invoice b
where Inv_No = 2 and cast(cast(eomonth(DATEADD(month, DATEDIFF(month, 0, Inv_StartDt), 0),N) as varchar)+' 23:59:59' as datetime)  < Inv_EndDt
   UNION ALL
select Inv_EndDt from [dbo].[Invoice] where Inv_No = 2

image

3

Many thanks for your reply, im using sql express 2008 R2 where 'eomonth' is not a recognized built-in function name.

Thanks once again.

4

hi

this is using 2008 without EOMONTH

; with tally_cte as 
(
SELECT N=number FROM master..spt_values WHERE type = 'P'
)
select Inv_StartDt from [dbo].[Invoice] where Inv_No = 2 
   UNION ALL
select cast(cast(cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, Inv_StartDt) + N+1, 0)) as date) as varchar)+' 23:59:59' as datetime) from tally_cte a , dbo.Invoice b
where Inv_No = 2 
and cast(cast(cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, Inv_StartDt) + N+1, 0)) as date) as varchar)+' 23:59:59' as datetime)  < Inv_EndDt
   UNION ALL
select Inv_EndDt from [dbo].[Invoice] where Inv_No = 2

image

5

Many Thanks for your query, but looking for below result.

original_StartDateTime original_EndDateTime new_StartDateTime new_EndDateTime new_Amount new_Hour getNewHour Per_Hr NewAmountUSD NewInvNo start_month start_year
28-02-2013 15:00 01-03-2013 07:30 28-02-2013 15:00 28-02-2013 23:59 8515.8 111 9 76.72 690.45 1 2 2013
28-02-2013 15:00 01-03-2013 07:30 01-03-2013 00:00 01-03-2013 07:30 8515.8 111 7.5 76.72 575.39 1 2 2013
21-10-2022 06:00 09-01-2023 14:30 21-10-2022 06:00 31-10-2022 23:59 8515.8 114 258 74.7 19272.58 2 10 2022
21-10-2022 06:00 09-01-2023 14:30 01-01-2023 00:00 31-10-2022 23:59 8515.8 114 -1464 74.7 -109360.82 2 10 2022
21-10-2022 06:00 09-01-2023 14:30 01-01-2023 00:00 31-10-2022 23:59 8515.8 114 -1464 74.7 -109360.82 2 10 2022
21-10-2022 06:00 09-01-2023 14:30 01-01-2023 00:00 09-01-2023 14:30 8515.8 114 206.5 74.7 15425.55 2 10 2022
6

image
image772×315 7.29 KB

7

Thanks for your reply, i have image file.

below data is correct
original_StartDateTime original_EndDateTime new_StartDateTime new_EndDateTime new_Amount new_Hour getNewHour Per_Hr NewAmountUSD NewInvNo start_month start_year
28-02-2013 15:00 01-03-2013 07:30 28-02-2013 15:00 28-02-2013 23:59 8515.8 111.00 9.00 76.72 690.45 1 2 2013
28-02-2013 15:00 01-03-2013 07:30 01-03-2013 00:00 01-03-2013 07:30 8515.8 111.00 7.50 76.72 575.39 1 2 2013
21-10-2022 06:00 09-01-2023 14:30 21-10-2022 06:00 31-10-2022 23:59 8515.8 104.00 258.00 81.88 21,125.71 2 10 2022
21-10-2022 06:00 09-01-2023 14:30 01-11-2022 00:00 30-11-2022 23:59 8515.8 104.00 720.00 81.88 58,955.52 2 10 2022
21-10-2022 06:00 09-01-2023 14:30 01-12-2022 00:00 31-12-2022 23:59 8515.8 104.00 744.00 81.88 60,920.70 2 10 2022
21-10-2022 06:00 09-01-2023 14:30 01-01-2023 00:00 09-01-2023 14:30 8515.8 104.00 206.50 81.88 16,908.78 2 10 2022

calculations
calculations1254×498 19.2 KB

9

hi

please see below .. hope this helps

; with tally_cte as 
( SELECT N=number FROM master..spt_values WHERE type = 'P') 
, cte_xx as 
(select inv_no,datediff(mm,Inv_StartDt,Inv_EndDt ) as N123 from dbo.Invoice) 
, cte_create_calendar as
(select a.inv_no, case when N=0 then Inv_StartDt  else  DATEADD(month, DATEDIFF(month, 0, dateadd(mm,N,inv_startdt)), 0) end as ok 
              , case when N=N123 then inv_enddt else cast(cast(cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, dateadd(mm,N,inv_startdt)) + 1, 0)) as date) as varchar)+' 23:59:59' as datetime) end as ok1 
 from dbo.Invoice a , tally_cte , cte_xx b  where
  N <= N123 and a.Inv_No = b.Inv_No
  )
select * from cte_create_calendar

image

10

My recommendation is that SQL Express is free and you should seriously consider an upgrade.

1 Like