June 14, 2004 at 3:03 pm
I have a table
Date |
Car
Type
Count
6/4/2004
Honda
Civic
5
6/4/2004
Honda
Accord
10
6/14/2004
Honda
Civic
15
6/14/2004
Honda
Accord
12
I need a report like
Date |
Car
Type
Count
10 Day Diff
6/14/2004
Honda
Civic
15
10
6/14/2004
Honda
Accord
12
2
10 Day Diff means today count minus 10 days back count.How can i generate this report, please help me.
June 14, 2004 at 3:11 pm
What if you had 3 rows for the civic? Or are you just looking for the lat two rows?
select max(date)
, car, type
from tableA
group by car, type
will give you the last row for each car. From there you can transform this into a subquery to get the row.
select
date
, car, type
from tableA a
where a.date = (select max( b.date)
from TableA b
where a.car = b.car and a.type = b.type)
To get the previous row, you'd need a self join that will get the next most recent date.
group by car, type
June 14, 2004 at 3:29 pm
Thanks Steve,
Is this works
SELECT A.DATE,A.CAR,A.TYPE,A.CNT,A.CNT - B.CNT
FROM
(SELECT MAX(DATE),CAR,TYPE,CNT FROM TABLEA GORUP BY CAR,TYPE,CNT) AS A
(SELECT CAR,TYPE,CNT FROM TABLEA WHERE DATE = CURRENT_TIMESTAMP -10) AS B
WHERE A.CAR = B.CAR AND A.TYPE = B.TYPE
Just i need another column that should today count - 10 (may be 20) day count.
Thanks.
June 14, 2004 at 5:31 pm
Based on the sampling you provided this is how I would do it.
SELECT
A.[Date],
A.Car,
A.Type,
A.[Count] - C.[Count]
FROM
auto_stock A
INNER JOIN
(
select
max([date]) [Date],
Car,
Type
from
auto_stock
Group By
Car,
Type
) B
On
A.[Date] = B.[Date] AND
A.Car = B.Car AND
A.Type = B.Type
INNER JOIN
auto_stock C
ON
A.Car = C.Car AND
A.Type = C.Type AND
A.[DATE] > C.[DATE] AND
C.[DATE] = DATEADD(d,-10,DATEADD(d,datediff(d,-0,GETDATE()),0))
However I feel we need a larger sampling of data and a better understanding of how diff is to work. Are you only concerned with stock today and stock 10 days ago difference or is there somethign else? Also, are going to need to specify a specific date to look at and have 10 day value be 10 days from that point and so on? In addition are these always going to be day end total or can there be more than one value a day?
June 15, 2004 at 8:51 am
Thanks for your reply, i need just today count minus 10 days back count,
i did how you suggested it worked for me. Again thanks for your help on this.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply