Kiran.Net Kiran.Net - 3 months ago 12
SQL Question

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 ?

Answer

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.

Comments