[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 7 1445
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.
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?
>> 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!
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.
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.
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.
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; This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 where you pass
in those starting and ending dates and the interval 'month' and you'd get
back a list...
|
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),
|
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 project's budget for a particular period. Periods are also
grouped by years (a seperate field for...
|
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
|
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 is greatly appreciated.
-Josh
| |
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 DateAdd("m",-6,Date())
How do I show both sets of results in one report????
|
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, 2007 # doesn't
|
by: dlite922 |
last post by:
hey fellas (and ladies)
I need help with making a query.
My tables are: (Simplified)
case:
id
violatorID
number
|
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 "01/01/2008-03/31/2008","04/01/2008-06/30/2008","07/01/2008-09/30/2008","10/01/2008-12/31/2008".
The code looks like:...
|
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 usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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 captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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 launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |