1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Extension on an existing query

Discussion in 'Databases' started by johnberman, Feb 5, 2017.

  1. #1
    So i was helped before with this


    I have a table of events (they are actually radio meteor events)
    This table has amongst other things

    event_id which is unique
    Date
    time: Event time is in a time field HH:MM:SS
    contributor

    in practical terms the event may be repeated several times for any given event as different contributors may have records the same event.

    So im looking for a query that will return all records were the date and time match


    and the answer was

    select event_id, eventdate, eventtime, contributor_id
    , (select count(event_id)
    from rme as r2
    where rme.eventdate = r2.eventdate
    and rme.eventtime = r2.eventtime
    group by rme.eventdate, rme.eventtime) as counter
    from rme
    having counter > 1



    I want a query that will bring back where the date matches but the time can be plus or minus 30 seconds for a match

    so imagine the following events were recorded on the 27 Jan 2017

    02:35:00
    02:35:15
    02:36:00
    02:38:00
    02:39:15

    I would want it to return
    SEMrush
    02:35:00
    02:35:15


    Hope this makes sense

    John B
     
    johnberman, Feb 5, 2017 IP
    SEMrush
  2. Zoidrums

    Zoidrums Greenhorn

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    1
    Trophy Points:
    11
    #2
    You don't really need any count. If I were you, I will do like this :

    select eventtime
    from rme R
    where exists (select 1
    from rme R2
    where R.eventdate = r2.eventdate
    and R.eventtime between DATE_ADD(r2.eventtime, INTERVAL -30 SECOND)
    and DATE_ADD(r2.eventtime, INTERVAL +30 SECOND))
     
    Zoidrums, Feb 11, 2017 IP
  3. johnberman

    johnberman Peon

    Messages:
    10
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3
    Sorry for the delay, i tried this but it failed , however I want to customise even further if possible ?

    Now this works fine

    SELECT
    meteor_events.event_id AS event_id,
    meteor_events.date AS date,
    meteor_events.Time AS time,
    (select count(`r2`.`event_id`) AS `count(event_id)`
    from `meteor_events` `r2` where ((`meteor_events`.`date` = `r2`.`date`) and
    (`meteor_events`.`Time` = `r2`.`Time`)) group by `meteor_events`.`date`,`meteor_events`.`Time`) AS counter
    from `meteor_events`
    having (`counter` > 1)
    order by `meteor_events`.`Time`


    I want to update it to do 2 things

    1 - only bring back events with a specific date range
    2 - were the time varies by 30 seconds as per the orginal question

    Regards
    John B
     
    johnberman, Feb 14, 2017 IP
  4. kodyaz

    kodyaz Greenhorn

    Messages:
    6
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #4
    Hi John,
    You need to add WHERE clause containing the filter criteria like date range
    After FROM clause add
    WHERE
    meteor_events.date between @date1 and @date2
    Code (SQL):
     
    kodyaz, Mar 2, 2017 IP