[thelist] Complex SQL query

[Luther, Ron]

Hi Jay,

Is that for Access?  {Oracle has some functions to make things like this a little easier.}

Any chance you are mixing data types?  e.g. comparing a *date* dueDate value against a text '2001-12-01' in the SQL?  [I quite often run into issues where I have to recast data types to get the comparisons I want to use working.]

That would be easy enough to check by running an equivalence "dueDate == 2001-12-01" and seeing what comes back.


HTH,

RonL.

-----Original Message-----
From: Jay Blanchard [mailto:jay.blanchard at niicommunications.com]

I have the following table;
+----+--------------+------------+--------+
| id | wtn          | dueDate    | ageAmt |
+----+--------------+------------+--------+
|  1 | 210-212-3871 | 2001-12-01 |   0.51 |
|  2 | 210-212-3871 | 2001-12-01 |   0.49 |
|  3 | 210-212-3871 | 2001-12-01 |   0.50 |
where I would like to to this;
select wtn,
sum(if(dueDate between 2001-12-01 and 2001-12-30, ageAmt, 0)) AS "+150",
sum(if(dueDate between 2002-01-01 and 2001-01-30, ageAmt, 0)) AS "+120",
sum(if(dueDate between 2002-02-01 and 2001-02-28, ageAmt, 0)) AS "+90",
sum(if(dueDate between 2002-03-01 and 2001-03-30, ageAmt, 0)) AS "+60",
sum(if(dueDate between 2002-04-01 and 2001-04-30, ageAmt, 0)) AS "+30"
from tblAcctRec
group by wtn;

it returns;
+--------------+------+------+------+------+------+
| wtn          | +150 | +120 | +90  | +60  | +30  |
+--------------+------+------+------+------+------+
| 210-212-3871 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
+--------------+------+------+------+------+------+

which is not right. Can anyone see what I'm doing wrong here?