I suppose this would be some sort of transpose

[Spud82]

I have 2 tables
Users which is a list of unique users that im interested in.
And my Messages table
Which is a simple structure.
user_id, row_created(date), messages(count of messages the user has sent on that date)

So this table is huge...a user has many rows one for each day they send at least one message.

And what im trying to achieve is...for example
User_id over30Messages sentAMessageinlast15days sentAMessagein16To30days sentAMessagein30To60days
36779 Y N N N
37173 N N N N
37199 Y Y N N
37389 Y N Y N
37408 Y N Y Y
37414 Y Y Y Y
37484 Y N N N

So I wrote this....but it only takes the first result for each user into account.

Select f.User_id, if(sum(f.messages)> 30, 'Y', 'N') as over30Messages,
if((f.row_created) > date_sub(curdate(), interval 15 day),'Y', 'N') as sentAMessageinlast15days,
if((f.row_created) < date_sub(curdate(), interval 15 day) and (f.row_created) >= date_sub(curdate(), interval 30 day),'Y', 'N') as sentAMessagein16To30days,
if((f.row_created) < date_sub(curdate(), interval 30 day) and (f.row_created) >= date_sub(curdate(), interval 60 day),'Y', 'N') as sentAMessagein30To60days
FROM Messages f,
Users s
where s.user_id = f.from_id
and f.row_created > date_sub(curdate(), interval 1 year)
group by 1;

Any ideas....much obligied.

 

[r937]

SELECT f.User_id
     , CASE WHEN SUM(f.messages) > 30
            THEN 'Y' ELSE 'N' END   AS over30Messages
     , MAX(CASE WHEN f.row_created >= CURRENT_DATE - INTERVAL 15 DAY
                THEN 'Y' ELSE 'N' END) AS sentAMessageinlast15DAYs
     , MAX(CASE WHEN f.row_created >= CURRENT_DATE - INTERVAL 30 DAY
                 AND f.row_created  < CURRENT_DATE - INTERVAL 15 DAY
                THEN 'Y' ELSE 'N' END) AS sentAMessagein15To30DAYs
     , MAX(CASE WHEN f.row_created >= CURRENT_DATE - INTERVAL 60 DAY
                 AND f.row_created  < CURRENT_DATE - INTERVAL 30 DAY
                THEN 'Y' ELSE 'N' END) AS sentAMessagein30To60DAYs
  FROM Messages f
INNER
  JOIN Users s
    ON s.user_id = f.from_id
 WHERE f.row_created >= CURRENT_DATE - INTERVAL 1 YEAR
GROUP
    BY f.User_id

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon