0

Table prices:

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

I want the result like this:

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

Note:((prevclose is previousdateclose,change=close-prevclose),change%=(close-prevclose)/100)

  • HIGH, LOW are the values for the most recent records (per ID) ? and why , in the first record of the desired output , for the column Change you have 22.55-22.55 ? maybe 22.550 -23.350 ? – Sabin Bio Dec 11 '18 at 7:48
0

I don't know the logic for columns 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

the desired output:

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

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.