November 5, 2009 at 12:33 pm
Hi all,
I have a table that gets items added to it everyday through a SQL AGent Job. I would like to be able to create a report that shows the difference between todays item and yesterdays item. I'm not sure how to write the query.
The table has a Type, Cnt and Date column.
So the records would like this
Type Cnt Date
Foo 206 11/04/2009
Foo 509 11/05/2009
Basically what I want the query to tell me the difference between todays count and yesterdays. (303). I'm really just learning tSQL so the syntax on this one has me a little stumped. Addition I can do but subtraction not so much.
Any help would be greatly appreciated.
Thanks,
Rhonda
November 5, 2009 at 12:50 pm
Would something along those lines do it?
DECLARE @t table(Type char(3),Cnt int, Date datetime)
INSERT INTO @t values('Foo' ,206, '11/04/2009')
INSERT INTO @t values('Foo' ,509, '11/05/2009')
SELECT
t1.type,
t1.date,
t1.Cnt,
t2.cnt as CntYesterday,
t1.cnt-t2.cnt as Diff
FROM @t t1
INNER JOIN @t t2
ON t1.date = dateadd(dd,1,t2.date)
AND t1.type = t2.type
/* result set:
typedateCntCntYesterdayDiff
Foo2009-11-05 00:00:00.000509206303
*/
November 5, 2009 at 2:43 pm
Perfect. Thank you very much. One more thing learned this week. 🙂
Rhonda
November 5, 2009 at 4:14 pm
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply