473,545 Members | 319 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to group date periods together ?

[DB2 UDB LUW 8.2.4] I've got bookings per day in a table:

bkgdate bookings company
1/1/06 50 A
2/1/06 45 A
3/1/06 55 A
4/1/06 40 A
5/1/06 30 A
6/1/06 45 A
7/1/06 35 A
....

I want to segment this into blocks of days where there are at least 100
bookings in each block.

So the solution I want is along the lines of

date_segment startdate enddate (bookings)
1 1/1/06 3/1/06 150
2 4/1/06 6/1/06 115
....

So far I've managed to do

SELECT bkgdate, company,
sum(bookings) over (partition by company order by bkgdate) as
cume_bookings
FROM table1

which gives me

bkgdate cume_bookings
1/1/06 50
2/1/06 95
3/1/06 150
4/1/06 190
....

I was thinking about joining this result set to itself, but that
doesn't quite give me the results I want, and the SQL is getting more
and more convoluted. Is there a more obvious way to do this that I'm
missing?

Thanks
JCSJF

Jun 29 '06 #1
7 1431
I got the result this way. But, I feel it too complex. Althogh, I tried
to simlify or modify some part of this code. I failed to simplify any
more. Are there anyone who could show more simple or easily
understandable way?

WITH Cum_bookings AS (
SELECT bkgdate
, bookings
, SUM(bookings) OVER(ORDER BY bkgdate
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS bookings_cum
, ROWNUMBER() OVER(ORDER BY bkgdate) AS rn
FROM Bookings
)
,Ranges AS (
SELECT
MIN(startdate) AS startdate
, MIN(enddate) AS enddate
, MIN(bookings_cu m) AS bookings_cum
, MIN(Lrn) AS Lrn
, Hrn
FROM (SELECT MIN(L.bkgdate) AS startdate
, MIN(H.bkgdate) AS enddate
, MIN( H.bookings_cum
- L.bookings_cum
+ L.bookings ) AS bookings_cum
, L.rn AS Lrn
, MIN(H.rn) AS Hrn
FROM Cum_bookings L
, Cum_bookings H
WHERE L.rn < H.rn
AND ( H.bookings_cum
- L.bookings_cum
+ L.bookings ) >= 100
GROUP BY
L.rn
) L
GROUP BY Hrn
)
SELECT INT(ROWNUMBER() OVER(ORDER BY startdate)) AS date_segment
, startdate
, enddate
, bookings_cum AS "(bookings) "
FROM Ranges R
WHERE EXISTS
(SELECT *
FROM Ranges E
WHERE E.Lrn = R.Hrn + 1)
OR EXISTS
(SELECT *
FROM Ranges E
WHERE E.Hrn = R.Lrn - 1)
;
--------------------------------------------------------------------

DATE_SEGMENT STARTDATE ENDDATE (bookings)
------------ ---------- ---------- -----------
1 2006-01-01 2006-03-01 150
2 2006-04-01 2006-06-01 115

2 record(s) selected.

Jun 30 '06 #2
Can we start by putting the date in the proper format?
bkgdate bookings company

2006-01-01 50 A
2006-02-01 45 A
2006-03-01 55 A
2006-04-01 40 A
2006-05-01 30 A
2006-06-01 45 A
2006-07-01 35 A
....
I want to segment this into blocks of days where there are at least 100
bookings in each block. <<

What do you do about overlaps and subsets?

2006-01-01 2006-03-01 150
2006-02-01 2006-03-01 100 <== subset of first row
2006-03-01 2006-05-01 125
2006-04-01 2006-06-01 115 <== overlaps following row
2006-05-01 2006-07-01 110
etc.

Likewise, we could get "super blocks" like ('2006-01-01', '2006-04-01')
which meet the criteria, but are not minimal.

This seems like a good candidate for a SUM(bookings)
OVER (PARTITION BY company ORDER BY bkgdate ROWS <<magic range clause>>
FOLLOWING)

but I am having trouble with my magic :) Can we get a better spec?

Jun 30 '06 #3
>> I got the result this way. But, I feel it too complex. <<

I thought it was quite clever myself!

I was playing with the idea of setting up all possible partitions in a
table, then seeing which one (if any) met the summation criteria.
Something like this:

bkgdate part_grp part_nbr
=============== ========
2006-01-01 1 1
2006-02-01 1 1
2006-03-01 1 1
2006-04-01 1 2
2006-05-01 1 2
2006-06-01 1 2
2006-07-01 1 2

2006-01-01 2 1
2006-02-01 2 1
2006-03-01 2 1
2006-04-01 2 1
2006-05-01 2 2
2006-06-01 2 2
2006-07-01 2 3

Wow! That would get big really fast!

Jun 30 '06 #4
Previous my Query was wrong.
Here is right(I hope) Query using Recirsive query.

WITH Nbr_bookings AS (
SELECT bkgdate
, bookings
, INT(ROWNUMBER() OVER(ORDER BY bkgdate)) AS rn
FROM Bookings
)
,Recurse
(rn, date_segment, startdate, enddate, bookings_sum,
next_segment_fl ag) AS (
SELECT rn
, 1
, bkgdate
, bkgdate
, bookings
, 0
FROM Nbr_bookings
WHERE rn = 1
UNION ALL
SELECT new_rn
, CASE
WHEN next_segment_fl ag = 1 THEN
date_segment + 1
ELSE date_segment
END
, CASE
WHEN next_segment_fl ag = 1 THEN
bkgdate
ELSE startdate
END
, bkgdate
, CASE
WHEN next_segment_fl ag = 1 THEN
bookings
ELSE bookings_sum + bookings
END
, new_segment_fla g
FROM (SELECT pre.rn + 1 AS new_rn
, pre.date_segmen t
, pre.startdate
, pre.enddate
, pre.bookings_su m
, pre.next_segmen t_flag
, new.*
, CASE
WHEN pre.next_segmen t_flag = 0
AND ( pre.bookings_su m
+ new.bookings ) >= 100
OR new.bookings >= 100 THEN
1
ELSE 0
END AS new_segment_fla g
FROM Recurse pre
, Nbr_bookings new
WHERE pre.rn < 10000
AND pre.rn + 1 = new.rn
) Q
)
SELECT date_segment
, startdate
, enddate
, bookings_sum AS "(bookings) "
FROM Recurse
WHERE next_segment_fl ag = 1
;
------------------------------------------------------------------------------

DATE_SEGMENT STARTDATE ENDDATE (bookings)
------------ ---------- ---------- -----------
1 2006-01-01 2006-03-01 150
2 2006-04-01 2006-06-01 115

2 record(s) selected.

Jul 1 '06 #5
This also seems work well.

WITH Cum_bookings AS (
SELECT bkgdate
, bookings
, SUM(bookings) OVER(ORDER BY bkgdate
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS bookings_cum
, ROWNUMBER() OVER(ORDER BY bkgdate) AS rn
FROM Bookings
)
,Ranges AS (
SELECT
L.rn AS Lrn
, MIN(H.rn) AS Hrn
FROM Cum_bookings L
, Cum_bookings H
WHERE L.rn <= H.rn
AND ( H.bookings_cum
- L.bookings_cum
+ L.bookings ) >= 100
GROUP BY
L.rn
)
SELECT INT(ROWNUMBER() OVER(ORDER BY Lrn)) AS date_segment
, L.bkgdate AS startdate
, H.bkgdate AS enddate
, ( H.bookings_cum
- L.bookings_cum
+ L.bookings ) AS "(bookings) "
FROM Cum_bookings L
, Cum_bookings H
, (SELECT
MAX(Lrn) AS Lrn
, Hrn
FROM Ranges R
WHERE
(Lrn = 1
OR
EXISTS
(SELECT *
FROM Ranges E
WHERE E.Hrn = R.Lrn - 1)
)
AND
(Hrn = (SELECT MAX(Hrn) FROM Ranges)
OR
EXISTS
(SELECT *
FROM Ranges E
WHERE E.Lrn = R.Hrn + 1)
)
GROUP BY
Hrn
) S
WHERE L.rn = S.Lrn
AND H.rn = S.Hrn
;
--------------------------------------------------------------------

DATE_SEGMENT STARTDATE ENDDATE (bookings)
------------ ---------- ---------- -----------
1 2006-01-01 2006-03-01 150
2 2006-04-01 2006-06-01 115

2 record(s) selected.

Jul 3 '06 #6
What do you do about overlaps and subsets?
>
2006-01-01 2006-03-01 150
2006-02-01 2006-03-01 100 <== subset of first row
2006-03-01 2006-05-01 125
2006-04-01 2006-06-01 115 <== overlaps following row
2006-05-01 2006-07-01 110
etc.

Likewise, we could get "super blocks" like ('2006-01-01', '2006-04-01')
which meet the criteria, but are not minimal.

This seems like a good candidate for a SUM(bookings)
OVER (PARTITION BY company ORDER BY bkgdate ROWS <<magic range clause>>
FOLLOWING)

but I am having trouble with my magic :) Can we get a better spec?
Sorry. As in all things, seemed a bit simpler than it was. I want
disjoint spanning groups, with each group being minimal.

Jul 6 '06 #7
As in all things, seemed a bit simpler than it was.
I agree. At least, my previous posts used a little complex algorithm or
extra code.
I want disjoint spanning groups, with each group being minimal.
But, this problem is not so simple. Because it is neccesary to remove
overlaps, sub-sets as CELKO wrote. And all data(rows) need to be
included in a group except last some rows if sum of bookings in the
group is less than 100.

Here is another example:
WITH Sum_bookings AS (
SELECT L.bkgdate AS startdate
, H.bkgdate AS enddate
, SUM(S.bookings) AS bookings_sum
FROM Bookings L
, Bookings H
, Bookings S
WHERE L.bkgdate <= H.bkgdate
AND S.bkgdate BETWEEN L.bkgdate AND H.bkgdate
GROUP BY
L.bkgdate, H.bkgdate
HAVING SUM(S.bookings) >= 100
)
,Recurse (date_segment, startdate, enddate, bookings_sum) AS (
SELECT 1
, startdate
, enddate
, bookings_sum
FROM Sum_bookings
WHERE startdate
= (SELECT MIN(bkgdate) FROM bookings)
AND enddate
= (SELECT MIN(enddate) FROM Sum_bookings)
UNION ALL
SELECT pre.date_segmen t + 1
, new.startdate
, new.enddate
, new.bookings_su m
FROM Recurse pre
, Sum_bookings new
WHERE pre.date_segmen t < 100000
AND new.startdate
= (SELECT MIN(startdate)
FROM Sum_bookings M
WHERE M.startdate pre.enddate
)
AND new.enddate
= (SELECT MIN(enddate)
FROM Sum_bookings M
WHERE M.startdate pre.enddate
)
)
SELECT * FROM Recurse;

Jul 9 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3204
by: Graeme Longman | last post by:
Hi everyone, I was wondering if anyone has written some Python code which uses a start date and end date and a given interval (day, month or year) and outputs all the time periods for that range and interval. For example you may wish to find all the months between the dates '2004-02-14' and '2004-08-04'. You would maybe use a function...
7
1740
by: LineVoltageHalogen | last post by:
Greetings All, I was hoping that someone might be able to help me with the following issue: table ddl: create table exchange ( exchangefrom varchar(6), exchangeto varchar(6), exchangecode varchar(6),
14
4335
by: Alan | last post by:
Hi everyone! I'm trying to produce a periodic financial report on projects from various departments. My database is set up with the tables tblDepartment, tblProjects, tblPeriods, and tblBudgets -- the first three serve the obvious function; the last table contains foreign keys to tblProjects and tblPeriods, each record corresponding to a...
2
1674
by: mark | last post by:
How can I use "Group By" or a formula to group my query results in 1-year periods from a given date, e.g. 3 groups: 1 Sept 2001 - 1 Sept 2002 1 Sept 2002 - 1 Sept 2003 1 Sept 2003 - 1 Sept 2004 Thanks, Mark
5
3451
by: jnikle | last post by:
I have two completely unrelated tables, one for reviews and another for pay periods. The reviews table has a review date in it, and the pay periods table is just a list of the beginnings of pay periods. What I need to do is take a given review date and find out which date in my pay periods table it's closest to, above or below. Any help...
1
1410
by: lyntonS | last post by:
I need to create a Summary Report which shows the current 6 month periods values, in addition to the perious 6 months periods? Currently I have two separate queries. One I have built using parameters and to return the current periods values. (Between And ) For the previous-period query I have used: Between Date() And...
10
2370
by: kyosohma | last post by:
Hi, I am working on a timesheet application in which I need to to find the first pay period in a month that is entirely contained in that month to calculate vacation time. Below are some example date ranges: December 31, 2006 January 13, 2007 # doesn't earn January 14, 2007 January 27, 2007 # does earn January 28, 2007 February 10,...
1
4889
dlite922
by: dlite922 | last post by:
hey fellas (and ladies) I need help with making a query. My tables are: (Simplified) case: id violatorID number
1
1729
by: guilhermelemmi | last post by:
I'm creating a function that will take a start date, a period duration (in months)and a maximum date, and will calculate the periods start and end dates until reaching the maximum date. For instance, I would give 01/01/2008, period equals 3 months and maximum date equals 01/01/2009, and the method should return periods like...
0
7457
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7391
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7651
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7802
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7410
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7746
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5962
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5320
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3438
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.