Calculating RSI for Multiple Dates and Tickers in SQL Server 2012

Question

I can calculate RSI for a specific end date:

DECLARE @StartingDate smalldatetime
    DECLARE @EndingDate smalldatetime
    DECLARE @StockID char(15)
    DECLARE @DAYS INT
    DECLARE @AG FLOAT
    DECLARE @AL FLOAT
    DECLARE @RS FLOAT

    SET @StartingDate = '20180301'
    SET @EndingDate = '20180403'
    SET @StockID = 'ACE'
    SET @DAYS = 14

    SET @AG =(
                SELECT SUM([px_close]-[px_open]) 
                FROM [dbo].[daily_data]
                WHERE [Ticker] = @STOCKID
                AND ([Date] BETWEEN @StartingDate AND @EndingDate) 
                AND ([px_close]-[px_open])>0)/@DAYS

    SET @AL =(
                SELECT SUM([px_close]-[px_open])
                FROM [dbo].[daily_data]
                WHERE [Ticker] = @STOCKID 
                AND ([Date] BETWEEN @StartingDate AND @EndingDate) 
                AND ([px_close]-[px_open])<0)/@DAYS
    SET @RS = @AG/ABS(@AL)
    SELECT @StockID AS Ticker, @EndingDate AS Date, 100 - (100/(1+@RS)) RSI

Here's my output:

Ticker  Date        RSI
ACE    2018-04-03   48.7307

How can I calculate RSI for multiple dates and multiple tickers?

Answer 1

You don't need to set all of these to variables. You can just add the date and ticker to the group by and avoid the redundant subqueries... something like:

SELECT 
    [Ticker]
    ,[Date]
    ,AG = SUM(case when (isnull([px_close],0)-isnull([px_open],0))>0 then (isnull([px_close],0)-isnull([px_open],0)) end) / @days
    ,AL = SUM(case when (isnull([px_close],0)-isnull([px_open],0))<0 then (isnull([px_close],0)-isnull([px_open],0)) end) / @days
    ,RS = (SUM(case when (isnull([px_close],0)-isnull([px_open],0))>0 then (isnull([px_close],0)-isnull([px_open],0)) end) / @days) /  ABS(SUM(case when (isnull([px_close],0)-isnull([px_open],0))<0 then (isnull([px_close],0)-isnull([px_open],0)) end) / @days)
    ,RSI = 100 - (100/(1+(SUM(case when (isnull([px_close],0)-isnull([px_open],0))>0 then (isnull([px_close],0)-isnull([px_open],0)) end) / @days) /  ABS(SUM( case when (isnull([px_close],0)-isnull([px_open],0))<0 then (isnull([px_close],0)-isnull([px_open],0)) end) / @days)))
FROM 
    [dbo].[daily_data]
WHERE 
    [Ticker] = @STOCKID
    AND ([Date] BETWEEN @StartingDate AND @EndingDate) 
group by
    [Ticker],[Date]

Remove [Ticker] = @STOCKID from the where clause to return all Tickers