Hi, I am trying to get the results of all dates from the dateout table dynamically as well as performing a join but instead of querying them and listing them in a query one at a time I'd like to do it dynamically where the distinct dates are selected and looped through. How might I do this? The query below is what I'm working on. Thanks, Aaron


select
outdate as 'campaign name',

(select count(*) from outtable ot2 where ot2.outdate =
outtable.outdate) as Total,

count(outdate) as 'match'
from
outtable right join intable on outvalue = invalue
where
outdate IN (select outdate FROM OutTable)
and indate between outdate and ADDDATE(outdate, INTERVAL 1 MONTH)

group by outdate

This result below is what I'd like if the query was dynamically selecting the dates and results.

campaign name total match
2009-01-05 5 3
2009-03-01 4 3
2009-04-01 4 3


Here is the data.

InTable
+-----------+---------+
| InDate | InValue |
+---------------------+
|2009-01-01 | 05 |
|2009-01-02 | 06 |
|2009-01-03 | 07 |
|2009-03-01 | 05 |
|2009-03-02 | 06 |
|2009-03-03 | 07 |
|2009-04-01 | 05 |
|2009-04-02 | 06 |
|2009-04-03 | 07 |
+---------------------+

OutTable
+---------------------+
| OutDate | OutValue|
+---------------------+
|2009-01-05 | 05 |
|2009-01-05 | 06 |
|2009-01-05 | 07 |
|2009-01-05 | 50 |
|2009-01-05 | 51 |
|2009-03-01 | 05 |
|2009-03-01 | 06 |
|2009-03-01 | 07 |
|2009-03-01 | 18 |
|2009-04-01 | 05 |
|2009-04-01 | 06 |
|2009-04-01 | 07 |
|2009-04-01 | 20 |

 

It appears that ADDDATE is not an ANSI standard SQL function (I only find it listed under MySQL), so I'll provide a solution that works in Oracle (probably equally non-ANSI compliant!):
My results are
Note that 05-JAN-09 does not get picked up from outtable because the January indate values precede the outdate.
Hope this gets you closer.

Thanks carp.  I don't understand why the 05-Jan-09 doesn't display?  Also when designing this query what is your approach?  From the inside out?  Do you have a step by step explanation?  I need a better method.  Thanks!

The 1/5 isn't showing up because you're doing a right outer join.  Try a left outer join and see what happens.

As a general rule, if I'm using a subquery or an inline view, I will do them first and make sure my output is what I expect.  Then I go ahead and work on the outer query or the joins between the inline view and other tables.  Also, if (as in your case) something I expect to show up is missing, I might try a full outer join to see if it shows up then.  If it doesn't, then I have a serious problem.  If it does, then I probably went with a left when it should have been a right (or vice versa).

Thanks. That helps a lot.  I noticed you use the subquery to get the count of the totals.  Instead of putting in a subquery could you have put that in the main outer Select portion query?  I'm wondering it would work either way?

Also finding the indate between the outdate works great but I also want to make sure the value is also matched.

So if outtable has a record:
outdate     outvalue
2009-01-05  06

The intable would match if it was within one month of that date and the value was also 06
indate      invalue
2009-01-10  06

How would I do that?
Thanks!