June 28, 2009 at 10:45 pm
Hi guys,
I have a table with structure as following:
ID, Stock, RiQi, ShouShi
For each Name, there are many records,
ID Stock RiQi ShouShi
1 000001 2004-12-30 00:00:00.000 6.59
2 000001 2005-01-03 00:00:00.000 6.52
3 000001 2004-12-30 00:00:00.000 6.59
4 000001 2005-01-03 00:00:00.000 6.52
5 000001 2005-01-04 00:00:00.000 6.46
6 000001 2005-01-05 00:00:00.000 6.52
7 000001 2005-01-06 00:00:00.000 6.51
8 000001 2005-01-09 00:00:00.000 6.59
...
1017 000002 2003-05-12 00:00:00.000 13.05
1018 000002 2003-05-13 00:00:00.000 13.09
1019 000002 2003-05-14 00:00:00.000 13.28
1020 000002 2003-05-15 00:00:00.000 13.73
1021 000002 2003-05-16 00:00:00.000 13.88
1022 000002 2003-05-19 00:00:00.000 13.90
1023 000002 2003-05-20 00:00:00.000 13.76
1024 000002 2003-05-21 00:00:00.000 13.72
What I want to do is I need to get result like the following:
000001, Max, Min, Avg, Current, Change1, Change2
000002, Max, Min, Avg, Current, Change1, Change2
...
where:
Max is the maximum value for 0000001,
Minis the minimum value for 0000001,
Avgis the average value for 0000001,
Current is the most current value for 0000001,
Change1 is the difference between Current and Max, in percentage
Change2 is the difference between Current and Max, in percentage
There is no problem to get Max, Min, and Avg, problem is how do I get the Current value and Change1 and Change2, is it possible to use only query to do this? I don't want to use cursor although I could.
Thanks very much in advance.
June 29, 2009 at 12:56 am
This?
DECLARE@Sample TABLE
(
theStock CHAR(6),
theDate DATETIME,
theValue MONEY
)
INSERT@Sample
SELECT'000001', '20041230', 6.59 UNION ALL
SELECT'000001', '20050103', 6.52 UNION ALL
SELECT'000001', '20041230', 6.59 UNION ALL
SELECT'000001', '20050103', 6.52 UNION ALL
SELECT'000001', '20050104', 6.46 UNION ALL
SELECT'000001', '20050105', 6.52 UNION ALL
SELECT'000001', '20050106', 6.51 UNION ALL
SELECT'000001', '20050109', 6.59 UNION ALL
SELECT'000002', '20030512', 13.05 UNION ALL
SELECT'000002', '20030513', 13.09 UNION ALL
SELECT'000002', '20030514', 13.28 UNION ALL
SELECT'000002', '20030515', 13.73 UNION ALL
SELECT'000002', '20030516', 13.88 UNION ALL
SELECT'000002', '20030519', 13.90 UNION ALL
SELECT'000002', '20030520', 13.76 UNION ALL
SELECT'000002', '20030521', 13.72
;WITH theYak (theStock, theValue, theMin, theMax, theAvg,recID)
AS (
SELECTtheStock,
theValue,
MIN(theValue) OVER (PARTITION BY theStock) AS theMin,
MAX(theValue) OVER (PARTITION BY theStock) AS theMax,
AVG(theValue) OVER (PARTITION BY theStock) AS theAvg,
ROW_NUMBER() OVER (PARTITION BY theStock ORDER BY theDate DESC) AS recID
FROM@Sample
)
SELECTtheStock,
theMin,
theAvg,
theMax,
theValue AS theCurrent,
100 * (theValue - theMax) / theMax AS [Change1 %],
100 * (theValue - theMin) / theMin AS [Change2 %]
FROMtheYak
WHERErecID = 1
N 56°04'39.16"
E 12°55'05.25"
June 29, 2009 at 7:49 am
Thank you very much, it works perfectly like I expect.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply