Hi,
I have a table with time dependent values for each key. I need to figure out value for each range and chain them together. Sometimes ranges are overlapping and sometimes there is default value with seasonal changes. The query has to resolve
Here is simplified data, hope it explains this a bit:
DECLARE @tbl TABLE
(
[Id] INT IDENTITY( 1, 1 ) PRIMARY KEY,
FK varchar(20) NOT NULL,
Start int NOT NULL,
[Expiration] int NOT NULL,
Value FLOAT NOT NULL
)
insert into @tbl(FK,Start,[Expiration],Value) values ('key1',1,10,1.1)
insert into @tbl(FK,Start,[Expiration],Value) values ('key1',3,99,2.2)
--insert into @tbl(FK,Start,[Expiration],Value) values ('key1',99,99,0)
insert into @tbl(FK,Start,[Expiration],Value) values ('key2',1,99,3.3)
insert into @tbl(FK,Start,[Expiration],Value) values ('key2',2,5,5.5)
--insert into @tbl(FK,Start,[Expiration],Value) values ('key2',99,99,0)
SELECT S.FK, S.Start,
CASE WHEN S.Expiration < E.Start THEN S.Expiration ELSE E.Start END as [End],S.Value
FROM @tbl S INNER JOIN @tbl E
ON S.FK=E.FK AND S.[Id]=E.[Id] - 1
Expected result is:
FK Start End Value
key1 1 3 1.1
key1 3 99 2.2
key2 1 2 3.3
key2 2 5 5.5
key2 5 99 3.3
The 2 commented lines in the INSERT part are additional records I added and run the query provided. Now I am getting these 4 records:
FK Start End Value
key1 1 3 1.1
key1 3 99 2.2
key2 1 2 3.3
key2 2 5 5.5
It is almost good except that I lost the last records where value returns to default after previous Start/End time range expired.
Please help me to modify the query to get the correct result.
Thanks.