Solved

Merging daily records to range of dates in SQL

Posted on 2014-04-09
12
1,011 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
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.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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