SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with a mostly done query!

    Hi all,

    I have an event system that I am trying to change slightly.

    This is the query that i have at the moment that grabs events between now and x days ( i pass it in)

    Code:
    SELECT *
    FROM tbl_Event
    WHERE event_date_end >=#CreateODBCDate(Now())# 
      AND  event_date <=#CreateODBCDate(DateAdd('d',arguments.numdays,Now()))#
      AND event_status='1'
     ORDER BY event_date ASC
     LIMIT 10
    What I am hoping to do is the same but also grab any queries where a field (event_annual) is 1. - These are items submitted as annually occuring. so I will need to change it slightly so that it extracts the day and month, so I guess in written words it will be

    the above but also

    Select all where the day and month fall between no and x days if event_annual = 1, not including any picked above

    Does this make any sense?

    Cheers

    Mike

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sounds like a UNION query

    so for annual events, do you still use the event_date_end and event_date columns? if so, what is the year for those date values?

    or are there separate "day and month" columns for annual events?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    basically at the moment the qery will pull any event on a particular day of a particular year. Now I want to add a tick box on the submission form so that when its ticked it will be marked as an annualy occuring event.

    so for example say im adding this christmas, I want it to be pulled as normal this year. Next year however atm it wouldnt be there. So if the db entry had event_annual as 1 i want to grab it, but effectivly ignore the year - do you get me?

    So yes the event_date column is used but it would need to be modified.

    As I am writing this now, if you suggest its a better idea, i could add new columns if it is an annual event.

    Thanks Rudy for taking the time

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    like this?
    Code:
    WHERE (
          event_date_end >= #CreateODBCDate(Now())# 
      AND event_date <= #CreateODBCDate(DateAdd('d',arguments.numdays,Now()))#
      AND event_status = 1
          )
       OR (
          YEAR(event_date) = #Year(CreateODBCDate(Now()))# 
      AND MONTH(event_date) = #Month(CreateODBCDate(Now()))# 
      AND event_annual = 1
          )
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Aug 2004
    Location
    Swindon
    Posts
    304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Almost - I swapped the YEAR for DAY as i assumed that was just a typo!

    other than that it worked a treat!

    thanks

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    um... yeah... typo, that's what it was, typo
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •