SQLTeam.com | Weblogs | Forums

GROUPING by maintaining date periods

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
:slight_smile: :slight_smile:

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 :slight_smile: :slight_smile:

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

image

This worked great!
Cheers