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

query

P: n/a
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

Jun 11 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Jun 11 '07 #2

P: n/a
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?

Jun 12 '07 #3

P: n/a
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.
Jun 13 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.