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
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
WHERE date_created is > 7
and 1= case when date_created < 7 then 0
else 1 end