April 4, 2016 at 9:47 am
So, I've a product price table. It can have multiple records for the date range depending on how customer wants to put the price for their product. My goal is find that one record that falls in today's date range. ValidTo can be null and it means this is valid for that entire period.
Here's the sample table structure. Can anybody advise query for this? Thank you so much for your help.
DROP TABLE #temp
CREATE TABLE #temp (ProductCode VARCHAR(10), Price MONEY, ValidFrom DATE NULL, ValidTo DATE NULL)
INSERT INTO #temp
SELECT '123', 20.00, '20140101', NULL
UNION
SELECT '123', 25.00, '20150101', NULL
UNION
SELECT '123', 30.00, '20160101', NULL
UNION
SELECT '123', 35.00, '20170101', NULL
SELECT * FROM #temp
April 4, 2016 at 9:53 am
If I understand correctly, you want to return the third row because the price changed to 30 on 1st January this year and hasn't changed since. Is that correct? Try using the LAG or LEAD functions to fill in the end dates, and then it will be a simple matter to select the correct row.
John
April 4, 2016 at 10:05 am
What happens when you have this scenario?
INSERT INTO #temp
SELECT '123', 20.00, '20140101', '20140131'
UNION
SELECT '123', 25.00, '20140101', '20140131'
Or this:
INSERT INTO #temp
SELECT '123', 20.00, '20140101', NULL
UNION
SELECT '123', 25.00, '20140101', NULL
April 4, 2016 at 10:10 am
Price will never overlap.
April 4, 2016 at 10:17 am
SQL_Surfer (4/4/2016)
Price will never overlap.
Then that's relatively simple,
SELECT * FROM #temp WHERE ValidFrom IS NULL OR (ValidFrom >= getdate() AND (ValidTo IS NULL OR ValidTo <= getdate()))
That's assuming that if ValidFrom is null there is no date range and that the data entry is being properly validated or you'll get odd results(the example you gave has over lapping ranges since validto is null on all of them).
April 4, 2016 at 10:19 am
Ed Wagner (4/4/2016)
What happens when you have this scenario?
INSERT INTO #tempSELECT '123', 20.00, '20140101', '20140131'
UNION
SELECT '123', 25.00, '20140101', '20140131'
Or this:
INSERT INTO #tempSELECT '123', 20.00, '20140101', NULL
UNION
SELECT '123', 25.00, '20140101', NULL
If you know you'll never have any overlaps or missing time periods, then John's suggestion is perfect. Populate missing ones and then pick the right row.
April 4, 2016 at 10:34 am
Try this:
with basedata as (
select
ProductCode,
Price,
ValidFrom,
ValidTo,
rn = row_number() over (partition by ProductCode order by ValidFrom desc)
from
#temp
where
ValidFrom <= getdate()
)
select * from basedata where ProductCode = '123' and rn = 1;
April 4, 2016 at 10:44 am
Codd suggested that there should be two different values for missing data: A-Values and I-Values representing missing and applicable and missing and inapplicable respectively, but SQL only represents a single combined value (NULL). When working with data ranges, it is much better to think of these as "applicable" values and to assign an arbitrary start and end dates where they are unknown. Typical value for start dates are 1900-01-01 for datetime/smalldatetime and 0001-01-01 for date/datetime2. Typical values for end dates are 9000-01-01, 9999-01-01, 9999-12-30, and 9999-12-31.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 6, 2016 at 3:05 am
SELECT TOP 1 * FROM #temp WHERE ValidFrom > getdate() ORDER BY ValidFrom ASC
April 6, 2016 at 8:46 am
kichian (4/6/2016)
SELECT TOP 1 * FROM #temp WHERE ValidFrom > getdate() ORDER BY ValidFrom ASC
You sure about this" If getdate() returns 2016-04-06 and your valid froms include 2014-01-01, 2015-01-01, 2016-01-01, 2017-01-01; don't you want to return 2016-01-01, not 2017-01-01?
April 10, 2016 at 5:34 am
Oh. You are correct. I didn't test :-
The answer would be:
SELECT TOP 1 * FROM #temp WHERE ValidFrom < = getdate() AND ProductCode = '123'
ORDER BY ValidFrom DESC
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply