How to use SQL previous records calculated value in current record?
Chaithanya Prabhu
I am trying to use previous records calculated value to perform current record calculations.
In the below image, we have written a small query which calculates the closing stock of all days. The calculated closing stock value of day 1 should be used as Actual stock for day 2 and similarly the calculated closing stock value of day 2 should be used as Actual stock for day 3 and so on till the end of the dates. We would like to perform this logic in a single select query. We tried to use SQL LAG(), BOUNDED Preceding but nothing turned to be positive.
Nguyễn Hải Triều
You can try this:
WITH CTE AS (
SELECT
ROW_NUMBER() OVER(ORDER BY PART_NUMBER, DATE) AS Rownum,
PART_NUMBER, DATE, ACTUALSTOCK, GROSS, BACKLOG, RECEIPT,
SUM(ACTUALSTOCK - (GROSS + BACKLOG) + RECEIPT) OVER (ORDER BY PART_NUMBER, DATE) AS CLOSINGSTOCK
FROM Tbl_PV_Parts_Dtl_MGMT_cur
WHERE PART_NUMBER = '0112107261' AND
DATE BETWEEN '20151021' AND '20151031')
SELECT
PART_NUMBER, DATE,
CASE WHEN Rownum = 1 THEN ACTUALSTOCK ELSE LAG(CLOSINGSTOCK) OVER(ORDER BY Rownum) END,
GROSS, BACKLOG, RECEIPT,
CLOSINGSTOCK
FROM CTE
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
Related
Related Related
- 1
How to minus current and previous value in SQL Server
- 2
Calculated column which references its previous value and current value
- 3
PostgreSQL - How to get the previous(lag) calculated value
- 4
How to use calculated column value to another column in the same SQL Query
- 5
How to use calculated column value to another column in the same SQL Query
- 6
SQL self-join compare current record with the record of the previous date
- 7
SQL self-join compare current record with the record of the previous date
- 8
How to group all records while next record is equal to previous record
- 9
When does a deterministic function use the previous calculated value?
- 10
Get previous record column value in SQL
- 11
How to store previous value and current value in for loop
- 12
spotfire Calculated column for Previous Month Records and Previous Quarter records
- 13
Is there a way in Pandas to use previous row value in dataframe.apply when previous value is also calculated in the apply?
- 14
Oracle SQL Current and previous status in the same output record
- 15
Select value on next date to be calculated on current date SQL
- 16
How to keep the value from previous records
- 17
How To Update The Date Based On Previous Record Value
- 18
How To Update The Date Based On Previous Record Value
- 19
How to select records from mysql along with one previous record
- 20
How to use order by on a calculated field in SQL?
- 21
How to add one to the value of the record based on the previous record
- 22
Get value from previous hour and subtract from current value in SQL
- 23
SQL Server 2000: need to return record ID from a previous record in current query
- 24
Referencing calculated value in SQL
- 25
SQL subtract previous row from record value when no ID exists
- 26
Observable Current and Previous Value
- 27
sql (beginner) - use value calculated from above cell
- 28
JDBC PL/SQL Previous Row (LAG) only returning current value
- 29
How to sum the current column value in row with the previous value?
Comments