CODEWITHSUNDEEP
sqlsql-server
Kiran.Net
Kiran.Net

Reputation: 33

SQL Query to get the monthly change percentage from daily quotes

I have the below table and I would like to get the Monthly change % from it

Data Image

I am using the below query for getting the monthly change:

SELECT 
    ct.Quote_Date, ht.Quote_Date AS htDate, ct.Quote_Price, 
    ht.Quote_Price AS [htPrice], 
    ((ct.Quote_Price - ht.Quote_Price) / ht.Quote_Price) * 100 AS ChangePerc
FROM 
    #TempStock ct 
LEFT JOIN 
    #TempStock ht ON CONVERT(DATE, CAST(ct.Quote_Date AS VARCHAR), 101) = DATEADD(MM, 1, CONVERT(DATE, CAST(ht.Quote_Date AS VARCHAR), 101))
ORDER BY  
    ct.Quote_Date DESC

Result of this query:

Result Image

Everything working fine except when the ht.Quote_Date is Sunday or Saturday or a holiday for which the record is missing in the table. In this case the available date before the holiday should be considered so that i don't get the NULLs as shown in the result image above.

Could you please let me know the correct query to get the required result ?

Upvotes: 1

Views: 209

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

I would suggest outer apply:

SELECT ct.Quote_Date, ht.Quote_Date AS htDate, ct.Quote_Price,
       ht.Quote_Price AS [htPrice],
       ((ct.Quote_Price - ht.Quote_Price)/ht.Quote_Price)*100 AS ChangePerc
FROM #TempStock ct OUTER APPLY
     (SELECT TOP 1 ht.*
      FROM #TempStock ht
      WHERE ht.Quote_Date <= DATEADD(month, -1, ct.Quote_Date)
      ORDER BY ht.Quoate_Date DESC
     ) ht
ORDER BY ct.Quote_Date DESC;

Notes:

  • You should do date arithmetic using dates. There is no reason to cast back and forth to strings.
  • If the dates have time components, use cast(<col> as date) to get rid of the time component.

Upvotes: 2

gofr1
gofr1

Reputation: 15997

OUTER APPLY could help you:

SELECT  ct.Quote_Date, 
        ht.Quote_Date AS htDate, 
        ct.Quote_Price, 
        ht.Quote_Price AS [htPrice], 
        ((ct.Quote_Price - ht.Quote_Price)/ht.Quote_Price)*100 AS ChangePerc
FROM #TempStock ct 
OUTER APPLY (
    SELECT TOP 1 *
    FROM #TempStock 
    WHERE CONVERT(DATE,CAST(ct.Quote_Date AS VARCHAR),101) >= DATEADD(MM,1, CONVERT(DATE,CAST(Quote_Date AS VARCHAR),101))
    ORDER BY Quote_Date DESC
    ) ht
ORDER BY  ct.Quote_Date DESC

WHERE clause in OUTER APPLY will bring first record with same or lesser date.

Upvotes: 0

Related Questions