CODEWITHSUNDEEP
mysqlsql
usert4jju7
usert4jju7

Reputation: 1813

SQL - Get count of new events for a month & display of only increased values

I use MySQL. I have a table with event history. I'm trying to acheive the following

1) Get those events in March 2015 that lasted longer than those in Feb 2015 provided the event did occur in Feb 2015. An event may occur any number of times in a month. SO, for starters, I'm just taking average duration. later will consider percentile.

2) Get a list of events in march 2015 that are unique to March (The comparision is with Feb currently. However in future, I may have a requirement on the lines of - Get events unique to March compared over the last 6 months - Basically trying to find out when a new event was introduced)

OUTPUT

I'd like the output for (1) to look something like below

MONTH,EVENT,DURATION
2015-03,Event1,1030
2015-03,Event2,365
2015-03,Event9,1010

For (2), I'd like the event list to look as below

MONTH,EVENT
2015-03,Event7
2015-03,Event2

With repect to (1) above, I'm trying the below query but can't get it right. There are 6 columns in the output obviously due to the nature of the query I've written.

select 
    * 
from
(
    (
        select
            month(EVE_DATE) as mon,
            EVE_ID as task,
            truncate(AVG(EVE_DURATION)/1000,2) as eve_dur
        from
            EVE_BOOKINGS
        where
            EVE_DATE >= '2015-02-01'
            and EVE_DATE <= '2015-02-31'
            and dayofweek(EVE_DATE) not in (1,7)
            and EVE_BOOKER = 'organizer'
            and EVE_ID <> ''
        group by
            mon,
            task
        order by 
            eve_dur desc
    ) t1,
    (    
        select
            month(EVE_DATE) as mon,
            EVE_ID as task,
            truncate(AVG(EVE_DURATION)/1000,2) as eve_dur
        from
            EVE_BOOKINGS
        where
            EVE_DATE >= '2015-03-01'
            and EVE_DATE <= '2015-03-31'
            and dayofweek(EVE_DATE) not in (1,7)
            and EVE_BOOKER = 'organizer'
            and CONFIDENCE = 'PROD'
            and EVE_ID <> ''
        group by
            mon,
            task
        order by 
            eve_dur desc
    ) t2   
)
where
        t2.eve_dur > t1.eve_dur

Could I please request guidance to get this query right?

Upvotes: 0

Views: 43

Answers (1)

Hogan
Hogan

Reputation: 70529

You don't have non-cross joins in your example -- I expect you don't know how to do them. For example to get item that are in march and you could take your sub-query construct and join them like this (I'm using a more standard indenting style)

SELECT *
FROM (
  SELECT month(EVE_DATE) as mon,      EVE_ID as task, truncate(AVG(EVE_DURATION)/1000,2) as eve_dur
  FROM EVE_BOOKINGS
  WHERE EVE_DATE >= '2015-02-01' and EVE_DATE <= '2015-02-31'
    AND dayofweek(EVE_DATE) not in (1,7)
    AND EVE_ID <> ''
  GROUP BY mon, task
) FEB
JOIN (    
  SELECT month(EVE_DATE) as mon,      EVE_ID as task, truncate(AVG(EVE_DURATION)/1000,2) as eve_dur
  FROM EVE_BOOKINGS
  WHERE EVE_DATE >= '2015-03-01' and EVE_DATE <= '2015-03-31'
    AND dayofweek(EVE_DATE) not in (1,7)
    AND EVE_ID <> ''
  GROUP BY mon, task
) MAR ON FEB.EVE_ID = MAR.EVE_ID

To find the ones in march that are not in feb select from march but left join to feb and find the ones that are "missing"

Like this 

SELECT *
FROM (
  SELECT EVE_DATE, EVE_ID 
  FROM EVE_BOOKINGS
  WHERE EVE_DATE >= '2015-03-01' and EVE_DATE <= '2015-03-31'
    AND dayofweek(EVE_DATE) not in (1,7)
    AND EVE_ID <> ''
) MAR 
LEFT JOIN (    
  SELECT EVE_DATE, EVE_ID 
  FROM EVE_BOOKINGS
  WHERE EVE_DATE >= '2015-02-01' and EVE_DATE <= '2015-02-31'
    AND dayofweek(EVE_DATE) not in (1,7)
    AND EVE_ID <> ''
  GROUP BY mon, task
) FEB ON FEB.EVE_ID = MAR.EVE_ID
WHERE FEB.EVE_ID is null

Upvotes: 2

Related Questions