Merging daily records to range of dates in SQL

this should do:

select vpt.personnum, vpt.paycodename
, min(vpt.applydate)
, max(vpt.applydate)
from VP_TOTALS vpt
where vpt.applydate between '2014-01-01' and '2014-01-31'
and vpt.PAYCODENAME in ('Sykemelding', 'Egenmelding')
group by vpt.personnum, vpt.paycodename
order by vpt.personnum                                 

Select allOpen in new window

Not quite I'm afraid, because it does not consider whether the applydate is adjacent  or not. There can be several date ranges for the same paycodename in the same period.

For pnum 1310 I only get one line, while I need two, as in my original example,

1310	Egenmelding	2014-01-03 00:00:00.000	2014-01-07 00:00:00.000
1311	Egenmelding	2014-01-13 00:00:00.000	2014-01-17 00:00:00.000
1311	Sykemelding	2014-01-20 00:00:00.000	2014-01-24 00:00:00.000

Select allOpen in new window

Thanks,
Bjorn

:( too slow

just one tip, if you do not use "between" date ranges can be easier (and more accurate) e.g.

select vpt.personnum, vpt.paycodename
, min(vpt.applydate)
, max(vpt.applydate)
from VP_TOTALS vpt
where ( vpt.applydate >= '2014-01-01' and vpt.applydate < '2014-02-01' )
and vpt.PAYCODENAME in ('Sykemelding', 'Egenmelding')
group by vpt.personnum, vpt.paycodename
order by vpt.personnum    

Now you don't need to wonder what the last day of Jan is or Feb and so on

no points pl.

oops, missed that altogether, ignore me please

ok, i see, I overlooked that also.
I think my article should help:
https://www.experts-exchange.com/Database/Miscellaneous/A_3952-ranges-gaps-overlaps-for-numbers-and-date-ranges.html

if not, please tell me so I can try to write the sql

Looked at you article, but your SQL skills exceeds mine by far. I'd really appreciate some  help with the sample SQL provided.

I tried converting your code, and have also looked on a few other examples, but I'm not quite there yet.

Thanks,
Bjorn

I took the code of "find date ranges"  as input, and put this code together (tested)

;with data as ( select row_number() over (partition by personnum, paycodename  order by applydate) id 
	 ,personnum, paycodename
	 , applydate start_date, dateadd(day, 1,applydate ) end_date
  from vo_totals
  )
, d1 as (
select m.end_date, personnum, paycodename 
     , row_number() over (partition by personnum, paycodename order by m.end_date ) rn
  from data m
 where not exists ( select null 
                  from data o 
                 where o.start_date <= m.end_date
                   and o.end_date >= m.end_date
                   and o.id <> m.id 
				   and o.personnum = m.personnum
				   and o.paycodename = m.paycodename
              )
  group by m.end_date, m.personnum, m.paycodename
)
, d2 as (
select m.personnum, m.paycodename, m.start_date
     , row_number() over (partition by m.personnum, m.paycodename order by m.start_date ) rn
  from data m
 where not exists ( select null 
                  from data o 
                 where o.start_date <= m.start_date
                   and o.end_date >= m.start_date
                   and o.id <> m.id 
				   and o.personnum = m.personnum
				   and o.paycodename = m.paycodename
              )
  group by m.personnum, m.paycodename, m.start_date
)
, d3 as (
select d1.personnum, d1.paycodename
    , d1.end_date gap_start, d2.start_date gap_end, d1.rn
  from d1
  join d2
    on d1.rn = d2.rn - 1 
 union all
select data.personnum, data.paycodename
	, null, min(start_date), 0 
	from data
	group by data.personnum, data.paycodename
 union all
select data.personnum, data.paycodename
	, max(end_date), null, (select max(rn) from d2) 
	from data
	group by data.personnum, data.paycodename
)
select dc.personnum, dc.paycodename
	--, dc.rn + 1 rn
	, dc.gap_end range_start, dateadd(day,-1,dn.gap_start ) range_end
  from d3 dc
  join d3 dn
    on dn.rn = dc.rn + 1
   and dn.personnum = dc.personnum
   and dn.paycodename = dc.paycodename
order by dc.personnum, dc.paycodename, dn.rn

Select allOpen in new window

hope this helps

Really appreciate your effort, but this gives the following output.
pnum 1310 is correct, while pnum 1311 is off.

1310	Egenmelding	2014-01-03 00:00:00.000	2014-01-03 00:00:00.000
1310	Egenmelding	2014-01-06 00:00:00.000	2014-01-07 00:00:00.000
1311	Egenmelding	2014-01-13 00:00:00.000	2014-01-17 00:00:00.000
1311	Egenmelding	2014-01-06 00:00:00.000	2014-01-17 00:00:00.000
1311	Sykemelding	2014-01-20 00:00:00.000	2014-01-24 00:00:00.000
1311	Sykemelding	2014-01-06 00:00:00.000	2014-01-24 00:00:00.000

Select allOpen in new window

wanted output is (Never mind the date format)

pnum	Paycodename Startdate	Enddate
1310	Egenmelding 2014-01-03	2014-01-03
1310	Egenmelding 2014-01-06	2014-01-07
1311	Egenmelding 2014-01-13 	2014-01-17
1311	Sykemelding 2014-01-20	2014-01-24

Select allOpen in new window

Regards,
Bjorn

Amazing work!

Thank you ever so much.

yes, nice job angeliii

out of pure interest; intimenordic, are you using SQL Server 2012 by any chance?

PortletPaul - Sorry, didn't see your comment until now. This query was for SQL Server 2008.