query

[Jon.Hakkinen]

Hi all,

I have a table CARS that model cars coming in and out of a dealership.
(This is oversimplified)

create table cars(VIN varchar(50) not null primary key, location_id
int not null, inDate date not null, outDate date)

Every time I buy a new car it's gonna show in the log with the inDate
as today's date, every time i sell it, the outDate gets upated (from
null to current date).
I want to write a query to retrieve the number of cars bought and sold
daily over a period of time grouped by locations
For instance
06/06/07 somelocationid 10 in 8 out
06/06/07 someotherlocationid 2in 3 out
06/07/07 ..........

The results will end up in some sort of a fact table (i'm not sure
here if it's better to keep all days in this fact table - even those
that have 0 out 0 in or just skip the dates i have no flow - the fact
table will be used to compute all sort of trends over time etc)
thanks

[Serge Rielau]

Jo**********@gmail.com wrote:

Hi all,

I have a table CARS that model cars coming in and out of a dealership.
(This is oversimplified)

create table cars(VIN varchar(50) not null primary key, location_id
int not null, inDate date not null, outDate date)

Every time I buy a new car it's gonna show in the log with the inDate
as today's date, every time i sell it, the outDate gets upated (from
null to current date).
I want to write a query to retrieve the number of cars bought and sold
daily over a period of time grouped by locations
For instance
06/06/07 somelocationid 10 in 8 out
06/06/07 someotherlocationid 2in 3 out
06/07/07 ..........

Something like this should do it...

WITH alldates(dt) AS (VALUES (CURRENT DATE)
UNION ALL
SELECT dt - 1 DAY FROM alldates
WHERE dt CURRENT DATE - 1 YEAR)
SELECT dt,
(SELECT count(1) FROM cars WHERE dt = in) as in
(SELECT count(1) FROM cars WHERE dt = out) as out
FROM alldates d

Adding the location is trivial ;-)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Jon.Hakkinen]

thanks Serge,

actually my problem is that I'm not sure what is the best way to join
the CTE with the CARS table so I get access to the other fields I want
to group by (location in my example)

My idea was to do something like

WITH alldates(dt) AS (VALUES (CURRENT DATE)
UNION ALL
SELECT dt - 1 DAY FROM alldates
WHERE dt CURRENT DATE - 1 YEAR)
SELECT dt, location,
sum(case when dt=in then 1 else 0) as in,
sum(case when dt=out then 1 else 0) as out
from alldates join cars on (dt=in or dt=out)
group by dt, location

However, there is something I don;t like about my query and I;m not
sure what.
You say adding location is trivial, how would you do that?

[Brian Tkatch]

On Tue, 12 Jun 2007 21:03:01 -0000, "Jo**********@gmail.com"
<Jo**********@gmail.comwrote:

>thanks Serge,

actually my problem is that I'm not sure what is the best way to join
the CTE with the CARS table so I get access to the other fields I want
to group by (location in my example)

My idea was to do something like

WITH alldates(dt) AS (VALUES (CURRENT DATE)
UNION ALL
SELECT dt - 1 DAY FROM alldates
WHERE dt CURRENT DATE - 1 YEAR)
SELECT dt, location,
sum(case when dt=in then 1 else 0) as in,
sum(case when dt=out then 1 else 0) as out
from alldates join cars on (dt=in or dt=out)
group by dt, location

However, there is something I don;t like about my query and I;m not
sure what.

Perhaps that you are making an INNER JOIN when it should be an OUTER
JOIN on two separate instances of the TABLE?

That's why Serge used a couple subqueries. Like an OUTER JOIN, when
there is no satisfaction of the JOIN criteria, it returns a 0 (like
the JOIN's NULL).

>You say adding location is trivial, how would you do that?

Add a query to pull all unique locations, perhaps in the WITH or in
the FROM, then let a Cartesian join give all locations for all dates.
Once that is done, add location to the sub-qeuries or JOIN clauses.

B.