Solved

Merging daily records to range of dates in SQL

Posted on 2014-04-09
12
1,035 Views
Last Modified: 2014-09-27
Hi Experts.

I have a simple sql query, returning daily records.
select vpt.personnum, vpt.paycodename, 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, vpt.applydate
order by vpt.personnum, vpt.APPLYDATE

Open in new window

This can return I.E. this result set.
pnum	paycodename	applydate
1310	Egenmelding	2014-01-03 
1310	Egenmelding	2014-01-06 
1310	Egenmelding	2014-01-07 
1311	Egenmelding	2014-01-13 
1311	Egenmelding	2014-01-14 
1311	Egenmelding	2014-01-15 
1311	Egenmelding	2014-01-16 
1311	Egenmelding	2014-01-17 
1311	Sykemelding	2014-01-20 
1311	Sykemelding	2014-01-21
1311	Sykemelding	2014-01-22 
1311	Sykemelding	2014-01-23 
1311	Sykemelding	2014-01-24 

Open in new window

My wanted output is to group by personnum and paycodename, and return a start date and an enddate where the dates are adjacent.

Like this:
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

Open in new window


Thoughts on how to solve this?

Regards,
Bjorn
0
Comment
Question by:intimenordic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39988194
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                                 

Open in new window

0
 

Author Comment

by:intimenordic
ID: 39988249
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

Open in new window


Thanks,
Bjorn
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39988261
:( 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.
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39988264
oops, missed that altogether, ignore me please
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39988271
ok, i see, I overlooked that also.
I think my article should help:
http://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
0
 

Author Comment

by:intimenordic
ID: 39988285
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39988337
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

Open in new window

hope this helps
0
 

Author Comment

by:intimenordic
ID: 39988380
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

Open 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

Open in new window

Regards,
Bjorn
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39988437
I see, there where more conditions to be added ...
 
;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 
  and d1.personnum = d2.personnum
  and d1.paycodename = d2.paycodename
 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 where d2.personnum = data.personnum and d2.paycodename = data.paycodename ) 
	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, dc.rn

Open in new window

0
 

Author Closing Comment

by:intimenordic
ID: 39988458
Amazing work!

Thank you ever so much.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39988473
yes, nice job angeliii

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

Author Comment

by:intimenordic
ID: 40347822
PortletPaul - Sorry, didn't see your comment until now. This query was for SQL Server 2008.
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question