[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Merging daily records to range of dates in SQL

Posted on 2014-04-09
12
Medium Priority
?
1,105 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 49

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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 49

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 2000 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 49

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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses

830 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