From: Jonathan Gardner <jgardner@jonathangardner.net>
To: "Ali Adams" <aliadams@doit4u.com>, <pgsql-sql@postgresql.org>
Subject: Re: help yourself by helping others
Date: Thu, 10 Jul 2003 12:14:22 -0700
User-Agent: KMail/1.5
References: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
In-Reply-To: <001c01c2fa9b$5b91ec80$03000000@DELLPC>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Content-Description: clearsigned data
Content-Disposition: inline
Message-Id: <200307101214.23951.jgardner@jonathangardner.net>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Friday 04 April 2003 03:10, Ali Adams wrote:
> Dear All,
>
> I am new to Relational Databases and SQL and my background in ODBs is
> clouding my way to solving what seems to be a simple problem. I am sure
> many of you have met it many times.
>
> OK, I have a table as follows:
>
>
> ID Machine   Date             Withdrawals
> 1  1              01/01/2003    1101
> 2  2              01/01/2003    2101
> 3  3              01/01/2003    3101
>
> 4  1              02/01/2003    1102
> 5  2              02/01/2003    2102
>
> 6  1              03/01/2003    1103
> 7  3              03/01/2003    3103
>
> 8  2              04/01/2003    2104
> 9  4              04/01/2003    4104
>
> And i would like to create a monthly withdrawals report as follows:
>
> Machine   Day1  Day2  Day3  Day4
> .............................................. Day31 1             11=20=
=20=20=20=20
> 12      13       0                                                      0=
 2
>             21      22      0        24=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20
>                 0 3             31      0        33       0=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20
>                                      0 4              0        0       0=
=20=20=20
>      0                                                     0
>
> Can you please help?
>

Doing this purely in SQL is difficult. The reason is because there is no wa=
y=20
to say things like "all the dates in a month, eg, 7/1, 7/2, 7/3, ... 7/31" =
in=20
SQL.

What I would do is select the data using aggregates and "group by" the=20
machine. You can specify how many days or months to aggregate across by=20
"group by date_trunc("day", Date)" or "date_trunc("month", Date)" or whatev=
er=20
you like. You can get some interesting behavior by combining that with=20
date_part() (IE, grouping by day of the week, to compare Mondays with=20
Tuesdays.)

The data you get back will look something like this:

Machine | Date | Withdrawals
- ---
1 | June 1 | 50
1 | June 7 | 65
1 | June 8 | 12
2 | June 2 | 14
2 | June 9 | 19

When you go to display the code, you populate a list of dates and withdrawa=
ls=20
for each machine. You fill in '0' for the missing dates. Then displaying it=
=20
is easy. You can also probably iterate over the results if everything is=20
sorted, and fill in the missing dates as you encounter them.

- --=20
Jonathan Gardner <jgardner@jonathangardner.net>
(was jgardn@alumni.washington.edu)
Live Free, Use Linux!
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/DbsOWgwF3QvpWNwRAqUiAKCDxyDbzRrIqzLDcZ6jOMESLvooAwCgvlOU
HYWoH/6iRlqhgDB5/KaoYJA=3D
=3DrhPR
-----END PGP SIGNATURE-----