根據目前日期和前一個日期獲取市場數據

餐桌價格：

ID  Date        OPEN    HIGH    Low    CLOSE    
417 9/23/1994   24.399  24.399  24.399  24.399  
417 9/28/1994   23.3    23.3    23.3    23.3    
417 9/29/1994   23.35   23.35   23.35   23.35   
417 9/30/1994   22.55   22.55   22.55   22.55   
418 5/22/2014   47.299  47.299  47.299  47.299  
418 5/23/2014   47.299  47.299  47.299  47.299  
418 5/26/2014   47.1    47.1    47.1    47.1    
418 5/27/2014   47.35   47.35   47.35   47.35

我想要這樣的結果：

id  Open    HIGH    LOW     CLOSE   PervClose   Change     Change%
417 22.55   22.55   22.55   22.55   23.35     22.55-22.55   (22.55-22.55)/100
418 47.35   47.35   47.35   47.35   47.1      47.35-47.1    (47.35-47.1)/100

注意：(((prevclose 是prevdateclose,change=close-prevclose),change%=(close-prevclose)/100)

我不知道列的邏輯HIGH，LOW…

IF OBJECT_ID('tempdb..#tmp') IS NOT NULL 
       DROP TABLE tempdb..#tmp;


CREATE TABLE #tmp
   ([ID] int, [Date] datetime, [OPEN] decimal(6,3), [HIGH] decimal(6,3), [Low] decimal(6,3), [CLOSE] decimal(6,3))
;

INSERT INTO #tmp
   ([ID], [Date], [OPEN], [HIGH], [Low], [CLOSE])
VALUES
   (417, '1994-09-23 00:00:00', 24.399, 24.399, 24.399, 24.399),
   (417, '1994-09-28 00:00:00', 23.3, 23.3, 23.3, 23.3),
   (417, '1994-09-29 00:00:00', 23.35, 23.35, 23.35, 23.35),
   (417, '1994-09-30 00:00:00', 22.55, 22.55, 22.55, 22.55),
   (418, '2014-05-22 00:00:00', 47.299, 47.299, 47.299, 47.299),
   (418, '2014-05-23 00:00:00', 47.299, 47.299, 47.299, 47.299),
   (418, '2014-05-26 00:00:00', 47.1, 47.1, 47.1, 47.1),
   (418, '2014-05-27 00:00:00', 47.35, 47.35, 47.35, 47.35)
;

;WITH cteTmp AS
(
SELECT
   [ID], [Date], [OPEN], [HIGH], [Low], [CLOSE]
   ,ROW_NUMBER()OVER(PARTITION BY [ID] ORDER BY [Date] DESC) AS RN
FROM #tmp as t
)

SELECT [ID],[OPEN]
           ,MAX([OPEN]) as [HIGH]
           ,MIN([OPEN]) as [LOW]
           ,[CLOSE] 
           ,oa.prevClose
           ,[CLOSE] - oa.prevClose as Change
           ,([CLOSE] - oa.prevClose)/100.0 as [Change%]
FROM cteTmp as t    
       OUTER APPLY
       ( SELECT TOP(1) prev.[CLOSE] as prevClose
           FROM cteTmp as prev
           WHERE prev.ID =t.ID
               AND prev.[Date]<t.[Date]
           ORDER By prev.[Date] DESC
       ) oa
WHERE t.rn = 1
GROUP BY  [ID],[OPEN],[CLOSE], oa.prevClose

所需的輸出：

ID    OPEN       HIGH       LOW       CLOSE      prevClose   Change      Change%
417   22.550     22.550     22.550    22.550     23.350     -0.800      -0.00800000
418   47.350     47.350     47.350    47.350     47.100      0.250       0.00250000

引用自：https://dba.stackexchange.com/questions/224620