Solved

Merging daily records to range of dates in SQL

Posted on 2014-04-09
12
993 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 142

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 142

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 142

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 142

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now