-
I'm running a query that is grabbing 2 groups of people that meet one criteria or another, ie:
SELECT a.user_id, n.mailerdate, n.mailername, p.datepurchased
FROM userdb a, purchase p, mailerlists n, (select max(u.dt) as maxdate, u.user_id FROM uservisit u group by u.user_id ) z
WHERE a.user_id = p.user_id(+)
AND a.user_id = z.user_id(+)
AND ((z.maxdate > n.mailerdate) or (p.datepurchased > n.mailerdate))
The 2 criteria are 1: if the last visit (maxdate) is GT the mailerdate OR 2: They have a purchase (datepurchased) GT the mailerdate. The query runs fine, my only issue is being able to differentiate the 2 groups in the output, and defining WHY they are displayed (which criteria they met).
Any help is greatly appreciated.
-
Try this (without the -'s, of course)
SELECT
---a.user_id,
---n.mailerdate,
---n.mailername,
---p.datepurchased,
---DECODE (
------GREATEST ( z.maxdate, n.mailerdate ),
------z.maxdate,
------DECODE ( z.maxdate, n.mailerdate, 0, 1),
------0 )
------AS BecauseMaxDate---,
---DECODE (
------GREATEST ( p.datepurchased, n.mailerdate ),
------p.datepurchased,
------DECODE ( p.datepurchased, n.mailerdate, 0, 1),
------0 )
------AS BecauseDatePurchased
FROM userdb a, purchase p, mailerlists n, (select max(u.dt) as maxdate, u.user_id FROM uservisit u group by u.user_id ) z
WHERE a.user_id = p.user_id(+)
AND a.user_id = z.user_id(+)
AND ((z.maxdate > n.mailerdate) or (p.datepurchased > n.mailerdate))
So we:
- Check to see if z.maxdate >= n.mailerdate
--- If true, then we need to check if z.maxdate = n.mailerdate
...then do the same thing for the other field.
Let me know if you need it explained further.
Hope this helps,
- Chris
-
hmmm.. seems doable.. thanks for the help !
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|