November 7, 2006 at 9:58 pm
Hi,
Say I have a temp table like below, I want a script that can return a result-set like the one placed at the bottom. I have been trying with various ways but with no luck. Can somebody help on this?
create table #temp ( date datetime, value int)
insert into #temp values ('2006-12-10',5);
insert into #temp values ('2006-12-11',5);
insert into #temp values ('2006-12-12',10);
insert into #temp values ('2006-12-13',5);
effectivedate expirydate value
2006-12-10 2006-12-11 5
2006-12-12 2006-12-12 10
2006-12-13 2006-12-13 5
November 7, 2006 at 10:04 pm
Can you explain in plain English the logic behind effectivedate and expirydate?
_____________
Code for TallyGenerator
November 7, 2006 at 10:30 pm
I think the point is to display one value while it's effective, and then display the new value once the data changes. In this case, rows one and two would be combined into the same row, and then rows 3 and 4 would be displayed separately. Something like:
RowDate as effectivedate,
NextHighestRowDate as expiry_date,
ValueFromRow as Value
Except that consecutive rows with the same value should be combined. I might be wrong, but I don't think this can be done without a cursor. A very short script could take care of this, thought I don't think it could be done in a single SELECT statement.
Ryan
November 7, 2006 at 10:47 pm
What Ryan says is right. I need to return a set of rates within a date range. The db stores rate for each date. So I'm supposed to return rate variations as mentioned above. Would be nice if can be done in fewer steps because I might have to integrate the logic to a bigger select statement used in SP
November 7, 2006 at 10:51 pm
Actually, after giving it some thought, it could be done using a subquery. It would take a long time, as it would have to look up through the table for each row, which would be like running 50 queries if the table has 50 rows in it.
Would something like this work?
SELECT DateColumn as EffectiveDate,
(SELECT MIN(EffectiveDate)
FROM #Table t2
WHERE t2.DateColumn > t1.DateColumn
AND t2.Value t1.Value)
FROM #Table t1
ORDER BY DateColumn
DISCLAIMER: I haven't run this through a SQL Parser, so I can't vouch for the syntax being perfect. Also, I don't think it will handle multiple same-day changes to the rate correctly - you'd have to tweak the subquery for that. I think this might accomplish what you're looking for, though.
Ryan
November 8, 2006 at 12:00 am
SELECT date as EffectiveDate,(SELECT MIN(date)
FROM #temp t2 WHERE t2.date > t1.date AND t2.Value <> t1.Value) as ExpiryDate, value FROM #temp t1
ORDER BY date
This query returns
EffectiveDate ExpiryDate value
----------------------- ----------------------- -----------
2006-12-10 00:00:00.000 2006-12-12 00:00:00.000 5
2006-12-11 00:00:00.000 2006-12-12 00:00:00.000 5
2006-12-12 00:00:00.000 2006-12-13 00:00:00.000 10
2006-12-13 00:00:00.000 NULL 5
But I want something that I have mentioned above
November 8, 2006 at 8:05 am
You could take care of the NULL by using an ISNULL, and replacing a NULL value with the date from the first column. As far as the multiple consecutive rows with the same value, you could do a WHERE clause to exclude rows from the main query where the value hasn't changed. If you're not sure how to do this, I can try to roll it into the statement for you.
Ryan
November 8, 2006 at 11:08 am
The following should work but, like all triangular joins, if you have a lot of rows it would be better to use a cursor. Somewhere between 5000 and 10000 rows is usually the cut-off point but you will have to test it yourself.
SELECT D.EffectiveDate
,MAX(D.ExpiryDate) AS ExpiryDate
,D.Value
FROM (
SELECT MIN(T1.[date]) AS EffectiveDate
,T2.[date] AS ExpiryDate
,T1.value
FROM #temp T1
JOIN #temp T2 ON T1.[date] <= T2.[date]
WHERE NOT EXISTS (
SELECT *
FROM #temp T3
WHERE T3.[date] BETWEEN T1.[date] AND T2.[date]
AND T3.value <> T1.value )
GROUP BY T2.[date], T1.value ) D
GROUP BY D.EffectiveDate, D.Value
ORDER BY D.EffectiveDate
or
SELECT D.EffectiveDate
,MAX(D.ExpiryDate) AS ExpiryDate
,D.Value
FROM (
SELECT MIN(T1.[date]) AS EffectiveDate
,T2.[date] AS ExpiryDate
,T1.value
FROM #temp T1
JOIN #temp T2 ON T1.[date] <= T2.[date]
WHERE T1.value = (
SELECT AVG(T3.value)
FROM #temp T3
WHERE T3.[date] BETWEEN T1.[date] AND T2.[date] )
GROUP BY T2.[date], T1.value ) D
GROUP BY D.EffectiveDate, D.Value
ORDER BY D.EffectiveDate
[Edit] On second thoughts, AVG may not be a good idea as there could be different values with the same AVG. Best to use the NOT EXISTS version.
November 8, 2006 at 11:31 pm
Thanks Ken and Ryan. I don;t think we will be operating on that much of data. I will try to integrate this with the procedure
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply