I have the following table:
PriceId PriceType FromDate ToDate Price ElementId
1 F 2014-01-01 NULL 0.00900 1
3 F 2014-01-01 2014-02-01 0.31470 4
4 F 2014-01-01 NULL 432.00000 3
6 F 2014-02-01 NULL 0.30950 4
7 F 2014-02-01 NULL 0.28990 2
The input is ElementID = 4, a fromDate and a toDate
I want all prices for an element where the from and todate are between the input period. The toDate are to, but not including.
So, 2014-01-28 to 2014-03-01 should return record 3 & 6 2014-01-28 to 2014-02-01 should only return record 3. 2014-02-07 to 2014-03-01 should return record 6.
Can anyone help me complete the WHERE clause:
WHERE ElementId = 4 AND...
TIA...
-- Dag Sunde.
It is not entirely clear what the significance of a NULL ToDate is intended to be. I'll assume it means 'ongoing' or, basically , NOW().
In that case, you need
WHERE whatever
AND FromDate >= ?starting
AND IFNULL(ToDate, NOW()) < ?ending
This will work correctly as long as your FromDate values are always in the past and before your ToDate values, and your ?starting parameters are before your ?ending.
There may be a serious efficiency problem with this design. It's hard to use indexes to search for null values.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments