usert4jju7 usert4jju7 - 7 months ago 11
SQL Question

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?

Answer

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