April 23, 2005 at 11:54 am
Hello,
I'm trying to compare values between today and a day before.
Here's a example;
table name = MyTable
RecordDate Values
1/1/2005 10
1/2/2005 15
1/3/2005 12
Here, I'd like to return the following result sets;
RecordDate Values Delta
1/1/2005 10 Null
1/2/2005 15 5
1/3/2005 12 -3
Is it possible to get the above result set without using a cursor?
Thanks alot for your help...
April 23, 2005 at 1:58 pm
One way would be:
SET NOCOUNT ON
IF OBJECT_ID('lfdsum_t') IS NOT NULL
DROP TABLE lfdsum_t
GO
CREATE TABLE lfdsum_t
(
thedate DATETIME
, [value] int)
INSERT INTO lfdsum_t values ('20050101', 10);
INSERT INTO lfdsum_t values ('20050102', 15);
INSERT INTO lfdsum_t values ('20050103', 12);
SELECT
a.thedate
, a.value
, a.[value]-b.value AS DELTA
FROM
lfdsum_t a
LEFT JOIN
lfdsum_t b
ON
a.thedate-1=b.thedate
DROP TABLE lfdsum_t
SET NOCOUNT OFF
thedate value DELTA
------------------------------------------------------ ----------- -----------
2005-01-01 00:00:00.000 10 NULL
2005-01-02 00:00:00.000 15 5
2005-01-03 00:00:00.000 12 -3
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 24, 2005 at 1:29 pm
This is super!!!
Thanks for your help.
April 25, 2005 at 2:32 am
Thanks, one thing I've forgotten to consider.
Can there be gaps in your date sequence?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 25, 2005 at 9:35 am
select RecordDate, [Values], [Values] -( select top 1 t2.[Values]
from test t2
Where t2.RecordDate < t1.Recorddate
order by t2.RecordDate desc ) diff
from test t1
* Noel
April 27, 2005 at 1:05 am
This is a nice solution. However, on a larger table you might be better off using an intermediate table like this when you have gaps in your date sequence.
SET NOCOUNT ON
IF OBJECT_ID('lfdsum_t') IS NOT NULL
DROP TABLE lfdsum_t
GO
CREATE TABLE lfdsum_t
(
thedate DATETIME
, [value] int)
INSERT INTO lfdsum_t values ('20050101', 10);
INSERT INTO lfdsum_t values ('20050102', 15);
INSERT INTO lfdsum_t values ('20050104', 12);
CREATE TABLE #workaround
(
wid INT IDENTITY
, thedate DATETIME
, [value] INT
)
INSERT INTO #workaround (thedate, [value])
SELECT thedate, [value] FROM lfdsum_t
ORDER BY thedate
SELECT
a.thedate
, a.value
, a.[value]-b.value AS DELTA
FROM
#workaround a
LEFT JOIN
#workaround b
ON
a.wid-1=b.wid
DROP TABLE lfdsum_t,#workaround
SET NOCOUNT OFF
thedate value DELTA
------------------------------------------------------ ----------- -----------
2005-01-01 00:00:00.000 10 NULL
2005-01-02 00:00:00.000 15 5
2005-01-04 00:00:00.000 12 -3
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
April 27, 2005 at 2:52 am
Try this:
select DT.Thedate, DT.Value, DT.Value - T3.Value as Delta
FROM (Select t1.thedate, T1.Value, max(t2.thedate) as PrevDate
from MyTable T1
left join MyTable T2 on t1.thedate > t2.thedate
group by t1.thedate, T1.Value) DT
left join MyTable T3 on DT.PrevDate = T3.thedate
ORDER BY DT.Thedate
_____________
Code for TallyGenerator
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply