SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2010
    Posts
    192
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    trying to select on date with conditions not sure if possible

    here are 3 rows from result set as an example

    order_no rep cust_id ordered allocated sku date_created
    4220320 Bob 211373 4 4 OTTER4SBK 2012-12-06
    4220320 Bob 211373 4 4 OTTER4SBK 2012-12-07
    4220320 Bob 211373 4 4 OTTER4SBK 2013-01-11


    this is tricky.. i only want to select 1 row per sku and order number combo

    where the date is over 7 days old, unless for this sku / order number pair, there is a date created within the past 7 days. then select nothing.

    so for the data above, all 3 rows would be excluded from the results, because the most recent date_created is within 7 days.

    I think my only concern for the WHERE clause is the date_created column, so how can i make this conditional WHERE clause?

    i.e. WHERE date_created is > 7 unless date_created < 7 is true

    thanks

  2. #2
    SitePoint Zealot
    Join Date
    Aug 2010
    Posts
    192
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    i probably could have explained this better, thanks for looking though.

    not to sure my solution is the best, but I made a sql function to analyze the dates and return a 1 or 0 in an additional column of data.

    then i was able to filter the results based on this new piece of data.

    i feels rather hacky, but it worked

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    something like

    WHERE date_created is > 7
    and 1= case when date_created < 7 then 0
    else 1 end


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
  •