Hi,
Just want to ask how to GROUP by maintaining date periods. basically this:
TIA
hi
one idea that comes to mind is
min(valid_from) max(valid_to)
group by primary 1 , 0
hope it helps
hi
i have posted the drop create data script
drop create data
drop table #raw
go
create table #raw
(
row int ,
company_id varchar(100),
position varchar(100),
emp_no varchar(100),
org_code varchar(100),
valid_from date ,
valid_to date,
primary1 int
)
go
insert into #raw
select 1,
'CORP',
'Executive Secreteray',
'003999',
'CORP-TEST2',
'2019-06-13',
'9999-12-31',
1
insert into #raw
select 2,
'CORP',
'Secreteray',
'003999',
'CORP-TEST2',
'2019-06-11',
'2019-06-12',
0
insert into #raw
select 3,
'CORP',
'HR Officer',
'003999',
'CORP-COR',
'2019-06-09',
'2019-06-10',
0
insert into #raw
select 4,
'CORP',
'Secretary',
'003999',
'CORP-TEST2',
'2019-06-06',
'2019-06-08',
0
select * from #raw
go
hi
i tried to come up with a solution
i used recursive cte for the grouping
may not be a good idea .. for performance on large data
hope it helps
i love any feedback
drop create data ...
drop table #raw
go
create table #raw
(
row int ,
company_id varchar(100),
position varchar(100),
emp_no varchar(100),
org_code varchar(100),
valid_from date ,
valid_to date,
primary1 int
)
go
insert into #raw
select 1,
'CORP',
'Executive Secreteray',
'003999',
'CORP-TEST2',
'2019-06-13',
'9999-12-31',
1
insert into #raw
select 2,
'CORP',
'Secreteray',
'003999',
'CORP-TEST2',
'2019-06-11',
'2019-06-12',
0
insert into #raw
select 3,
'CORP',
'HR Officer',
'003999',
'CORP-COR',
'2019-06-09',
'2019-06-10',
0
insert into #raw
select 4,
'CORP',
'Secretary',
'003999',
'CORP-TEST2',
'2019-06-06',
'2019-06-08',
0
select * from #raw
go
SQL ...
;WITH cte
AS (SELECT row,
primary1,
company_id,
emp_no,
org_code,
valid_from,
valid_to
FROM #raw),
rec_cte
AS (SELECT 1 AS grp,
*
FROM cte
WHERE row = 1
UNION ALL
SELECT CASE
WHEN a.primary1 <> b.primary1 THEN grp
ELSE grp + 1
END AS grp,
a.*
FROM cte a
JOIN rec_cte b
ON a.row = b.row + 1)
SELECT grp,
company_id,
emp_no,
org_code,
Min(valid_from),
Max(valid_to)
FROM rec_cte
GROUP BY grp,
company_id,
emp_no,
org_code
go
This worked great!
Cheers
hi
i know this post from a long time ago
i seem to be on a roll trying to come up with easy SQL instead of recursive CTE
recursive CTE is very bad for performance !!!
Here is an easy way SQL to do it ..simple math
Planets have again aligned ...
please click the arrow to the left for drop create data ...
drop table #raw
go
create table #raw
(
row int ,
company_id varchar(100),
position varchar(100),
emp_no varchar(100),
org_code varchar(100),
valid_from date ,
valid_to date,
primary1 int
)
go
insert into #raw
select 1,
'CORP',
'Executive Secreteray',
'003999',
'CORP-TEST2',
'2019-06-13',
'9999-12-31',
1
insert into #raw
select 2,
'CORP',
'Secreteray',
'003999',
'CORP-TEST2',
'2019-06-11',
'2019-06-12',
0
insert into #raw
select 3,
'CORP',
'HR Officer',
'003999',
'CORP-COR',
'2019-06-09',
'2019-06-10',
0
insert into #raw
select 4,
'CORP',
'Secretary',
'003999',
'CORP-TEST2',
'2019-06-06',
'2019-06-08',
0
please click arrow to the left for Simple Easy Math SQL
; WITH cte
AS (SELECT row + primary1 AS grp,
*
FROM #raw),
cte_grp
AS (SELECT Row_number()
OVER(
ORDER BY (SELECT NULL)) AS rn,
grp,
company_id,
emp_no,
org_code,
Min(valid_from) AS minvalid,
Max(valid_to) AS maxvalid
FROM cte
GROUP BY grp,
company_id,
emp_no,
org_code)
SELECT rn,
company_id,
emp_no,
org_code,
minvalid,
maxvalid
FROM cte_grp
