How to Split a Record Based on Dates in SQL Server

How to Split a record based on dates in SQL Server?

You can use cross apply. If I understand correctly:

select e.Employee_Id, t.Name, v.DateType, v.StartDate, v.EndDate
from t cross apply
(values ('FirstReviewDate', FirstReviewDate, FirstReviewDate),
('SecondReviewDate', FirstReviewDate, SecondReviewDate),
('ThirdReviewDate', SecondReviewDate, ThirdReviewDate)
) v(DateType, StartDate, EndDate);

Split single row into multiple rows based on Date and time in SQL Server

Below Query will help you.

CREATE TABLE #test
(
Notifications varchar(50)
,StartDate datetime
,EndDate Datetime
,Id int
)

INSERT into #test
select '001003741915','2018-08-20 07:27:00.000','2018-08-21 16:23:00.000',1
UNION select '001003779670','2018-08-21 03:36:00.000','2018-08-21 04:36:00.000',2
UNION select '001003779830','2018-08-21 04:36:00.000','2018-08-21 21:35:00.000',3
UNION select '001003779835','2018-08-21 04:36:00.000','2018-08-24 21:35:00.000',4

;with cte
As ( SELECT
ID,Notifications,StartDate,dateadd(d, datediff(d, 1, StartDate+1), '06:00') as StartOfDay, EndDate,dateadd(d, datediff(d, 1, EndDate+1), '06:00') as EndDayOfDate
FROM #test
)
, Result
AS (


select Id
,Notifications
,StartDate
,CASE WHEN StartOfDay BETWEEN StartDate AND EndDate THEN StartOfDay
WHEN ENDDate <StartOfDay THEN ENDDate
WHEN ENDDate <EndDayOfDate THEN ENDDate
ELSE EndDayOfDate END AS EndDate
from cte

union ALL
Select T.Id
,T.Notifications
,R.EndDate As StartDate
,CASE WHEN R.EndDate+1 < T.EndDate THEN R.EndDate+1 ELSE T.EndDate END AS EndDate
from cte T
INNER JOIN Result R
ON R.Notifications=T.Notifications
WHERE R.EndDate <T.EndDate

)

SELECT * FROM Result order by id

Split records based on date in sql

You can use a recursive sub-query factoring clause:

WITH split ( ID, EFF_DT, END_DT, MAX_DT ) AS (
SELECT id,
eff_dt,
LEAST(
ADD_MONTHS( TRUNC( SYSDATE, 'YY' ), 12 ) - INTERVAL '1' DAY,
end_dt
),
end_dt
FROM table_name
UNION ALL
SELECT id,
end_dt + INTERVAL '1' DAY,
max_dt,
max_dt
FROM split
WHERE end_dt < max_dt
)
SELECT id,
eff_dt,
end_dt
FROM split;

Which, for your sample data:

CREATE TABLE table_name ( ID, EFF_DT, END_DT ) AS
SELECT 'FLA1', DATE '2018-01-01', DATE '2019-12-31' FROM DUAL UNION ALL
SELECT 'FLA1', DATE '2020-01-01', DATE '9999-12-31' FROM DUAL;

Outputs:


ID | EFF_DT | END_DT
:--- | :------------------ | :------------------
FLA1 | 2018-01-01 00:00:00 | 2019-12-31 00:00:00
FLA1 | 2020-01-01 00:00:00 | 2020-12-31 00:00:00
FLA1 | 2021-01-01 00:00:00 | 9999-12-31 00:00:00

db<>fiddle here

Split a date column, and calculate an amount based on the result

One of the problems you're going to have here is that the outcome of the computed columns you have isn't compatible with the data being stored.
For example you can't build a sum of the quantity for all of the rows in January for each row in January. You need to group by the date and aggregate the quantity.

As such I think this might be an ideal candidate for an indexed view. This will allow you to store the calculated data, whilst preserving the data in the original table.

create table SalesTable (customer_id int, date date, quantity smallint);

insert SalesTable (customer_id, date, quantity)
select 123, '2020-01-01', 6
union
select 124, '2020-02-01', 7
union
select 123, '2021-03-01', 5
union
select 123, '2020-01-15', 4;

select * from SalesTable order by date; /*original data set*/

GO

CREATE VIEW dbo.vwSalesTable /*indexed view*/
WITH SCHEMABINDING
AS
SELECT customer_id, DATEPART(year, date) as year, datepart(MONTH, date) as
month, SUM(quantity) as sum_quantity from dbo.SalesTable group by Customer_Id,
DATEPART(year, date), DATEPART(MONTH, date)
GO

select * from vwSalesTable /*data from your indexed view*/

drop view vwSalesTable;
drop table SalesTable;

Original data set on top and indexed view output below

Split one record into multiple rows based on dates. Procedure in Cursor sent error

EDIT - After understanding what yuo were actually trying to achieve I updated the query I wrote.

The following is your script recreated without a create procedure, and without a cursor. As a general rule, if you ever think you need a cursor in sql server, you are probably wrong 99.9% of the time.

The following query goes through multiple CTEs, its done this way to make it simple to understand, and could no doubt be simplified further.

You will see that I am first expanding your table into 1 row per year, then setting the anniversaries for each row.

I then expand it into one row per date so that I can sort them simply, and then discard any dates that are outside the enrolment range or not required for the year, and finally pair them up into ranges.

;With daterange as (
Select year(min(Entry_date)) as y1, year(max(Termination_date)) as y2 from table1
),
-- Expand the rows into 1 per year
years as
(
Select y1 as y from daterange
union all
Select y + 1
from years
where y <= (select y2 from daterange)
)
-- Now work our all the birthdays, anniversaries etc for each source row and each applicable year
, alldates as
(
Select table1.*,
dateadd(day,90,Entry_date) obsdt,
datefromparts(y,month(DOB),day(DOB)) as birthday,
datefromparts(y,month(Entry_date),day(Entry_date)) as anniversary,
datefromparts(y,1,1) date1,
datefromparts(y,12,31) date2,
y
From table1
join years on y between year(Entry_Date) and year(Termination_date)
)
-- There are 7 possible dates - year start, birthday, anniversay, obsdt, year end, entry, termination
, dates as
(
select d from (values(1),(2),(3),(4),(5),(6),(7)) as starts(d)
)
-- Split it into multiple rows so we can sort the dates
, expanded as
(
select ID, y, entry_date, termination_date,
case d
when 1 then date1
when 2 then birthday
when 3 then anniversary
when 4 then obsdt
when 5 then date2
when 6 then entry_date
when 7 then termination_date
end as dt
from alldates
cross join dates
)
-- Exclude rows that are not from the year - entry, termination, obsdt, or are outside the entry/termination dates
, validrows as
(
select distinct ID, y, dt
from expanded
where year(dt)=y
and dt>=entry_date and dt<=termination_date
)
-- Pair each ro with its next row
, pairs as
(
select *, lead(dt,1) over(partition by ID,Y order by dt) as dt2
from validrows
)
-- The final insert
insert into [dbo].[table3] (ID, Year, Start_date, End_date)
select ID, y, dt, dt2
from pairs where dt2 is not null
order by ID, y, dt

The result of this is:

ID  y       dt          dt2
1 2010 2010-09-01 2010-11-30
1 2010 2010-11-30 2010-12-31
1 2011 2011-01-01 2011-06-01
1 2011 2011-06-01 2011-09-01
1 2011 2011-09-01 2011-12-31
1 2012 2012-01-01 2012-06-01
1 2012 2012-06-01 2012-07-01
2 2011 2011-11-20 2011-12-01
2 2011 2011-12-01 2011-12-31
2 2012 2012-01-01 2012-02-18
2 2012 2012-02-18 2012-11-20
2 2012 2012-11-20 2012-12-01
2 2012 2012-12-01 2012-12-31
2 2013 2013-01-01 2013-02-01


Related Topics



Leave a reply



Submit