Previous day (Difference between current date and previous date value)
LAG() (Calculating price difference between dates)
Main key words: Preceding, Following, Unbounded, Current
This is a script for an aggregated running total. We can use framing to further improve the result of the query.
Default behaviour of this query is to use range (RANGE UNBOUNDED PRECEDING) and the range operator is going to use temporally database. By executing this query like this we have 31K scans and 18K reads against that temp DB. We can force this to use memory instead of temp DB by adding framing.
Table 'Worktable'. Scan count 31467, logical reads 189937
This is a default behaviour of this query, using RANGE UNBOUNDED PRECIDING. This is equal to: SUM(totaldue) over (Order by SalesOrderID) and this force the query using temporally database .
ROWS UNBOUNDED PRECEDING says give me all of the rows whiting the current partition up until the current row. The performance is different now, because we forced this query into the memory by using ROWS UNBOUNDED PRECEDING clause.
When you are using RANGE UNBOUNDED PRECEDING you can get inaccurate result back. Range is a default behaviour of this query, that's why you can't see it in the code.
Using to calculate the price difference between the current day and the previous one can fix the problem with the incorrect calculation of the running total and force the query into the memory (no scans and reads on the temp DB). This is one of the ways how to fix the code.
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
The first row in this case is going to be the last row. This kind of notation turns the logic of the running total upside down.
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
What this does is, it takes the current row, 1 previous value, 1 following value and sum them up. In order to do 1 PRECEDING ADN 1 FOLLOWING you must use ROWS option. You can use other aggregation function to achieve different results.
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
FIRST_VALUE
LAST_VALUE
I have some stock data and I want to retrieve the value on the beginning of the month and the value on the end of the month for each row that particular month.
SELECT [StockId]
,[QuoteId]
,[QuoteDay]
,[QuoteClose]
,FIRST_VALUE(QuoteClose) OVER(Partition by StockID, Month(QuoteDay),YEAR(QuoteDay) ORDER BY QuoteDay) FistClose
,LAST_VALUE(QuoteClose) OVER(Partition by StockID, Month(QuoteDay),YEAR(QuoteDay) ORDER BY QuoteDay
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LastClose
FROM [TAdb].[dbo].[Quotes]
Previous Day Closing Price
I want to calculate the price difference between the current day and the previous one. I am using ROW_NUMBER() function and self join. The Stock is closed during the weekends so I do not have a continues set of dates which is a requirement when working with time intelligence functions.
With PreviousDay
as
(
Select ROW_NUMBER() OVER( ORDER BY StockID) [Index],
StockId, QuoteId,QuoteDay,QuoteClose AS _Close
from Quotes
)
Select pd1.QuoteDay, pd1._Close,pd2._Close AS PreviousDayClose,
pd1._Close - pd2._Close As PriceDifference
from PreviousDay pd1
LEFT JOIN PreviousDay pd2
On pd1.[Index] =pd2.[Index] +1
LAG()
Using LAG() function to calculate the price difference between the current day and the previous one
Select
StockId, QuoteId,QuoteDay,QuoteClose AS _Close,
LAG(QuoteClose) OVER(ORDER BY StockID) AS PrevClose,
QuoteClose - LAG(QuoteClose) OVER(ORDER BY StockID) AS PriceDifference
from Quotes
