How to write a SQL query that generate values based on data in the table

Question

I have a table that looks like the table below and I need to make a query that shows the previous price of the Stock

Stock_ID   | Stock_Code | Price | From_date  | To_date
-----------+------------+-------+------------+-----------
1          | XYZ        | 71    | 2013-01-05 | 2013-01-06
1          | XYZ        | 72    | 2013-01-07 | 2013-01-08
1          | XYZ        | 74    | 2013-01-09 | 2013-01-10
2          | QWE        | 24    | 2013-01-05 | 2013-01-06
2          | QWE        | 22    | 2013-01-07 | 2013-01-08
2          | QWE        | 30    | 2013-02-09 | 2013-01-10

The query should resulted in something like this:

Stock_ID   | Stock_Code | Price | From_date  | To_date   | Previous_Price
-----------+------------+-----+--------------+---------------------------
1          | XYZ        | 71    | 2013-01-05 | 2013-01-06| null
1          | XYZ        | 72    | 2013-01-07 | 2013-01-08| 71
1          | XYZ        | 74    | 2013-01-09 | 2013-01-10| 72
2          | QWE        | 24    | 2013-01-05 | 2013-01-06| null
2          | QWE        | 22    | 2013-01-07 | 2013-01-08| 24
2          | QWE        | 30    | 2013-02-09 | 2013-01-10| 22

what I tried:

SELECT *, 
(SELECT Price 
WHERE To_date in (SELECT DATEADD(day, -1, From_date) from StockTable))
FROM StockTable

However, the order is incorrect and the null is showed in the bottom. I suspect this is because it's a totally separate query and the second query does not directly uses the date from the SELECT * table.

I included Order By as well as suggested by one of the comments, but it still doesn't work.

Is it possible to do so without creating a new table and use join? What can I do to obtain the result?

Edit the question and tag the DBMS you're using and its version. — sticky bit
– sticky bit, Commented Jan 17, 2022 at 1:59
You don't have an ORDER BY clause, so you're getting the data in whatever order the DBMS wants to give it to you. To get it sorted in a specific way, provide an ORDER BY clause in your query. I'd suggest you do it using ORDER BY Stock_ID, From_Date, which should give you what you want. — Ken White
– Ken White, Commented Jan 17, 2022 at 2:51
@KenWhite tried Order By, doesn't work... so basically I need a query that allows Null as part of the first result and it seems like using Select is not quite the solution :( — Vincensiu
– Vincensiu, Commented Jan 17, 2022 at 2:58
I don't see any ORDER BY in the SQL you've posted, so there's no way of knowing whether you tried it. It should be in your question as part of your effort, because if it's not in your question, it doesn't exist. You should edit to provide it, as well as to add a tag for the DBMS you're using. — Ken White
– Ken White, Commented Jan 17, 2022 at 2:59
@KenWhite because I just tried it after you told me to, and it still doesn't work. it says: The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. if I tried to use order by in the very end, it still showed me the result where two of the nulls are in the very bottom — Vincensiu
– Vincensiu, Commented Jan 17, 2022 at 3:02

John Cappelletti · Accepted Answer · 2022-01-17 03:38:44Z

6

Perhaps the window function lag() over() would be a good fit here

Select * 
      ,Prev_Price = lag(Price) over (partition by Stock_ID order by From_date)
 from YourTable

Results

answered Jan 17, 2022 at 3:38

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

halfer · Accepted Answer · 2022-01-22 21:07:47Z

A first principles approach is to simply use an inline query to select the previous record. It not a generally efficient solution but if the dates are contiguous and there are not many records for each Stock_Code then the performance might be acceptable:

SELECT s.Stock_ID, s.Stock_Code, s.Price, s.From_date, s.To_date
       , (SELECT Price 
          FROM StockTable lst
          WHERE lst.Stock_ID = s.Stock_ID 
            AND lst.Stock_Code = s.Stock_Code
            AND lst.To_date = (
                SELECT MAX(To_date)
                FROM StockTable mx
                WHERE mx.Stock_ID = s.Stock_ID 
                AND mx.Stock_Code = s.Stock_Code
                AND mx.To_date < s.To_Date
            )
          ) as Previous_Price
FROM StockTable s

You could however simplify this with a simple LAG window query:

SELECT Stock_ID, Stock_Code, Price, From_date, To_date
       , LAG (Price, 1, 0) 
         OVER (PARTITION BY Stock_ID, Stock_Code
               ORDER BY To_Date)
         as Previous_Price
FROM StockTable s

Try this fiddle for proof: http://sqlfiddle.com/#!18/a4372/2

Maxim · Accepted Answer · 2022-01-17 03:33:28Z

1

I'm not sure that is the best solution, but:

create table #temp(
Stock_ID INT,
Stock_Code NChar(5),
Price INT,
From_Date DATE,
To_Date DATE
)

INSERT INTO #temp VALUES
(1, 'XYZ', 71, '2013-01-05', '2013-01-06'),
(1, 'XYZ', 72, '2013-01-07', '2013-01-08'),
(1, 'XYZ', 74, '2013-01-09', '2013-01-10'),
(2, 'QWE', 24, '2013-01-05', '2013-01-06'),
(2, 'QWE', 22, '2013-01-07', '2013-01-08'),
(2, 'QWE', 30, '2013-01-09', '2013-01-10')    
SELECT
        t1.Stock_ID, 
        t1.Stock_Code, 
        t1.Price, 
        t1.From_Date, 
        t1.To_Date,
        (SELECT
            t3.Price [Previouse_Price]
        FROM
            #temp t3
        INNER JOIN
            (SELECT
                MAX(t2.From_Date) [Previouse_Date]
            FROM
                #temp t2 
            WHERE 
                t1.Stock_ID = t2.Stock_ID 
                AND 
                t1.Stock_Code = t2.Stock_Code 
                AND
                t1.From_Date > t2.From_Date
            ) A ON A.Previouse_Date = t3.From_Date
        WHERE
            t1.Stock_ID = t3.Stock_ID 
            AND 
            t1.Stock_Code = t3.Stock_Code)
    FROM #temp t1

answered Jan 17, 2022 at 3:33

Maxim

8691 gold badge8 silver badges18 bronze badges

1 Comment

Vincensiu Over a year ago

This one works, thanks.

Collectives™ on Stack Overflow

How to write a SQL query that generate values based on data in the table

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related