By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,237 Members | 1,234 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,237 IT Pros & Developers. It's quick & easy.

QUERY: grouping history of cash dispensers

P: n/a
Hi all,

I do not know if this can be done in standard SQL and if not this is
an answer too!

We have a table that holds the history of our cash dispensers with
their location and number of cassettes.
Please take a look at the last row. The only item that changed here
compared to the row before is the number
of cassettes (except for the dates). The location remains the same.

term from to city cassettes
4711 01.01.2000 31.12.2005 Atown 2
4711 01.01.2006 31.01.2006 Btown 2
4711 01.02.2006 28.02.2006 Atown 2
4711 01.03.2006 31.12.9999 Atown 4

My job is to build a query that gives a compact list of terminals with
their dates and locations. Nobody is
interested in the number of cassettes in this case. Idealy the output
should look like this:

term from to city
4711 01.01.2000 31.12.2005 Atown
4711 01.01.2006 31.01.2006 Btown
4711 01.02.2006 31.12.9999 Atown

A simple 'GROUP BY' won't work:

term from to city
4711 01.01.2000 31.12.9999 Atown
4711 01.01.2006 31.01.2006 Btown

Any Ideas?

thanks for your help
Ralf
---------------------------------------------------------
Ralf Schmoll
Postbank Systems AG
BAW, Competence Center Webtechnologie
Baunscheidstraße 8
53113 Bonn

Tel.: +49(0)228/920-64338

Aug 25 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Ralf,

Take a look at the GROUP BY clause.
For the start date use MIN, for the end date MAX.
That should do it.

Gruess den Rhein von 'nem expat Moselaner
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 25 '06 #2

P: n/a
Hi Serge,

A group by does not work. Look at the last lines of my opening post.
I am now trying to use a recursive query. Seems to come closer to the
correct answer but I am not finished yet.

What is an expat Moselaner? Kommst aus der Gegend wa?

Best Regards
Ralf

Serge Rielau wrote:
Ralf,

Take a look at the GROUP BY clause.
For the start date use MIN, for the end date MAX.
That should do it.

Gruess den Rhein von 'nem expat Moselaner
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 25 '06 #3

P: n/a
ra*****@googlemail.com wrote:
Hi Serge,

A group by does not work. Look at the last lines of my opening post.
I am now trying to use a recursive query. Seems to come closer to the
correct answer but I am not finished yet.
What is the logic behind what you want? The original data has:

4711 01.01.2000 31.12.2005 Atown 2
4711 01.02.2006 28.02.2006 Atown 2
4711 01.03.2006 31.12.9999 Atown 4

(just focusing on Atown), and you want:

4711 01.01.2000 31.12.2005 Atown
4711 01.02.2006 31.12.9999 Atown

Why Atown twice? and/or why combining the two last lines, when the
dates don't overlap? Without knowing that, I don't think we can give
any help.

-Chris

Aug 25 '06 #4

P: n/a
ChrisC wrote:
ra*****@googlemail.com wrote:
>Hi Serge,

A group by does not work. Look at the last lines of my opening post.
I am now trying to use a recursive query. Seems to come closer to the
correct answer but I am not finished yet.

What is the logic behind what you want? The original data has:

4711 01.01.2000 31.12.2005 Atown 2
4711 01.02.2006 28.02.2006 Atown 2
4711 01.03.2006 31.12.9999 Atown 4

(just focusing on Atown), and you want:

4711 01.01.2000 31.12.2005 Atown
4711 01.02.2006 31.12.9999 Atown

Why Atown twice? and/or why combining the two last lines, when the
dates don't overlap? Without knowing that, I don't think we can give
any help.
Oh I get it... He wants to collapse back to back time intervals.
I bet there is a nifty OLAP way of doing it, but it's Friday.

Recursion is the lazy choice here:
WITH rec(term, from, to, city)
AS (SELECT term, from, to, city FROM base
UNION ALL
SELECT rec.term, rec.from, base.to, rec.city
FROM rec, base
WHERE rec.city = base.city
AND rec.to = base.from - 1 day)
SELECT term, from, MAX(to) as to, city
FROM rec
GROUP BY term, from, city;

I did not test this, but it should be enough to get you moving.

Gruss
Serge

PS: expatriot ~ Im Ausland lebend.
http://www.cochem.de/tourism/starten.htm
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 25 '06 #5

P: n/a
Hi,
>
What is the logic behind what you want? The original data has:

4711 01.01.2000 31.12.2005 Atown 2
4711 01.02.2006 28.02.2006 Atown 2
4711 01.03.2006 31.12.9999 Atown 4
Read this like:
ATM 4711 was located in Atown from January 2000 till December 2005.
During that period it was equipped wirh 2 cassettes (Row 1).
>From February 2006 till February 2006 it was again located in Atown
still containing 2 cassettes (Row 2).
You can read between the lines that it was NOT in Atown during January
2006.
In March 2006 the number of cassettes was increased to 4 but the
location did not change (Row 3).
(just focusing on Atown), and you want:

4711 01.01.2000 31.12.2005 Atown
4711 01.02.2006 31.12.9999 Atown
Therefore read the two lines above as:
ATM 4711 was located in Atown from January 2000 till December 2005.
(Row 1)
Since February 2006 stands in Atown again (Row 2), after a pause in
January.
Why Atown twice?
Because there were two distinct periods of installation.
and/or why combining the two last lines, when the
dates don't overlap?
As I told you nobody is interested in the cassettes in this report but
in the times and locations. Just focusing on the locations and
disregarding the cassetttes there is only one period from February
onwards.

A 'GROUP BY' using MIN for the start date and MAX for the END date
gives

4711 01.01.2000 31.12.9999 Atown
4711 01.01.2006 31.01.2006 Btown

This implies that one ATM can be at two locations at the same time
which is physically impossible.

I hope this makes it clearer to you.

Greetings
Ralf

Aug 25 '06 #6

P: n/a
Hi Serge

Serge Rielau wrote:
ChrisC wrote:
Oh I get it... He wants to collapse back to back time intervals.
I bet there is a nifty OLAP way of doing it, but it's Friday.
Thats it !
>
Recursion is the lazy choice here:
WITH rec(term, from, to, city)
AS (SELECT term, from, to, city FROM base
UNION ALL
SELECT rec.term, rec.from, base.to, rec.city
FROM rec, base
WHERE rec.city = base.city
AND rec.to = base.from - 1 day)
SELECT term, from, MAX(to) as to, city
FROM rec
GROUP BY term, from, city;

I did not test this, but it should be enough to get you moving.
It's Friday night here now but I will test this at the end of next
week. Will let you know.

Thank you all very much.
PS: expatriot ~ Im Ausland lebend.
http://www.cochem.de/tourism/starten.htm
NEID (:-))

--
Ralf

Aug 25 '06 #7

P: n/a

ChrisC wrote:
ra*****@googlemail.com wrote:
Hi Serge,

A group by does not work. Look at the last lines of my opening post.
I am now trying to use a recursive query. Seems to come closer to the
correct answer but I am not finished yet.

What is the logic behind what you want? The original data has:

4711 01.01.2000 31.12.2005 Atown 2
4711 01.02.2006 28.02.2006 Atown 2
4711 01.03.2006 31.12.9999 Atown 4

(just focusing on Atown), and you want:

4711 01.01.2000 31.12.2005 Atown
4711 01.02.2006 31.12.9999 Atown

Why Atown twice? and/or why combining the two last lines, when the
dates don't overlap? Without knowing that, I don't think we can give
any help.

-Chris
This TABLE is just a fancy log. Sometimes the location changes,
sometimes it doesn't. He wants to collapse the log entries where the
location did not change.

B.

Aug 25 '06 #8

P: n/a
ra*****@googlemail.com wrote:
Hi Serge

Serge Rielau wrote:
ChrisC wrote:
Oh I get it... He wants to collapse back to back time intervals.
I bet there is a nifty OLAP way of doing it, but it's Friday.

Thats it !

Recursion is the lazy choice here:
WITH rec(term, from, to, city)
AS (SELECT term, from, to, city FROM base
UNION ALL
SELECT rec.term, rec.from, base.to, rec.city
FROM rec, base
WHERE rec.city = base.city
AND rec.to = base.from - 1 day)
SELECT term, from, MAX(to) as to, city
FROM rec
GROUP BY term, from, city;

I did not test this, but it should be enough to get you moving.

It's Friday night here now but I will test this at the end of next
week. Will let you know.

Thank you all very much.

Ralf
Hi,

I like to tell you the result of my test. The query as proposed gives:

term from to city
4711 01.01.2000 31.12.2005 Atown
4711 01.01.2006 31.01.2006 Btown
4711 01.02.2006 31.12.9999 Atown
4711 01.03.2006 31.12.9999 Atown
4711 01.04.2006 31.12.9999 Atown

which is almost what I wanted.

Just for completeness here is what I changed to make it perfect:

WITH rec(term, from, to, city)
AS (SELECT term, from, to, city FROM base
UNION ALL
SELECT rec.term, rec.from, base.to, rec.city
FROM rec, base
WHERE rec.city = base.city
AND rec.to = base.from - 1 day)
SELECT term, MIN(from) as from, to, city
FROM (SELECT term, from, MAX(to) as to, city FROM rec GROUP BY term,
from, city) tmp
GROUP BY term, to, city
ORDER BY 1,2,3;
term from to city
4711 01.01.2000 31.12.2005 Atown
4711 01.01.2006 31.01.2006 Btown
4711 01.02.2006 31.12.9999 Atown
best regards
Ralf

Sep 5 '06 #9

P: n/a
I've been thinking about Serge's comment:
I bet there is a nifty OLAP way of doing it, but it's Friday.
and after reading your corrected SQL, I think I finally know how to fit
OLAP into the query. It might even be useful (i.e., faster) if you
have big enough source data.

WITH rec(term, from, to, city)
AS (SELECT term, from, to, city FROM base
UNION ALL
SELECT rec.term, rec.from, base.to, rec.city
FROM rec, base
WHERE rec.city = base.city
AND rec.to = base.from - 1 day)
SELECT term, from, to, city
FROM (SELECT term, from, to, city, row_number() over(partition by from
order by to desc) as rownum
FROM rec) tmp
WHERE rownum = 1;

-Chris

Sep 11 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.