CODEWITHSUNDEEP
sqlsql-server
Alex Martinez
Alex Martinez

Reputation: 201

Create View table basing column in other table rows

I'm creating a View table in SQL Server out of regular table where I need to add an extra column PURCHASE DATE (X). This will be the outcome and the regular table it's the same just without the Column X.

+----+-------------+----------+--------+-----------+------------+-------------------+ | ID | Item | Quantity | Price | Date | Category | PURCHASE DATE (X) | +----+-------------+----------+--------+-----------+------------+-------------------+ | 12 | BOND A 5% | 40 | $1.25 | 2/20/2012 | BUY | | | 11 | BOND A 5% | 40 | $1.25 | 5/1/2013 | INT INCOME | | | 10 | BOND A 5% | 40 | $1.25 | 9/23/2013 | SELL | | | 9 | BOND A 5% | 100 | $3 | 6/30/2015 | BUY | | | 8 | BOND A 5% | 100 | 0.05 | 2/1/2016 | INT INCOME | | | 7 | BOND A 5% | 100 | 0.05 | 2/1/2017 | INT INCOME | | | 6 | BOND A 5% | 100 | 0.05 | 2/1/2018 | INT INCOME | | | 5 | BOND A 5% | 100 | $5 | 6/30/2018 | SELL | | | 4 | BOND B 7.5% | 500 | $10 | 8/24/2013 | BUY | | | 3 | BOND B 7.5% | 500 | $10 | 3/1/2014 | INT INCOME | | | 2 | BOND B 7.5% | 500 | $10 | 3/2/2015 | INT INCOME | | | 1 | BOND B 7.5% | 500 | $10 | 3/31/2018 | SELL | | +----+-------------+----------+--------+-----------+------------+-------------------+

How can I make the PURCHASE DATE (X) column equal the Date column for the most recent BUY row with a matching Item value (based on Date rather than ID)?

Expected result:

+----+-------------+----------+--------+-----------+------------+-------------------+ | ID | Item | Quantity | Price | Date | Category | PURCHASE DATE (X) | +----+-------------+----------+--------+-----------+------------+-------------------+ | 12 | BOND A 5% | 40 | $1.25 | 2/20/2012 | BUY | 2/20/2012 | | 11 | BOND A 5% | 40 | $1.25 | 5/1/2013 | INT INCOME | 2/20/2012 | | 10 | BOND A 5% | 40 | $1.25 | 9/23/2013 | SELL | 2/20/2012 | | 9 | BOND A 5% | 100 | $3 | 6/30/2015 | BUY | 6/30/2015 | | 8 | BOND A 5% | 100 | 0.05 | 2/1/2016 | INT INCOME | 6/30/2015 | | 7 | BOND A 5% | 100 | 0.05 | 2/1/2017 | INT INCOME | 6/30/2015 | | 6 | BOND A 5% | 100 | 0.05 | 2/1/2018 | INT INCOME | 6/30/2015 | | 5 | BOND A 5% | 100 | $5 | 6/30/2018 | SELL | 6/30/2015 | | 4 | BOND B 7.5% | 500 | $10 | 8/24/2013 | BUY | 8/24/2013 | | 3 | BOND B 7.5% | 500 | $10 | 3/1/2014 | INT INCOME | 8/24/2013 | | 2 | BOND B 7.5% | 500 | $10 | 3/2/2015 | INT INCOME | 8/24/2013 | | 1 | BOND B 7.5% | 500 | $10 | 3/31/2018 | SELL | 8/24/2013 | +----+-------------+----------+--------+-----------+------------+-------------------+

Upvotes: 0

Views: 58

Answers (2)

Alex Martinez
Alex Martinez

Reputation: 201

So, this was pretty much what I did to solve the issue in case in can help someone:

create view A_T as
select A.*,
       (SELECT TOP 1 T.Date FROM MainTable T WHERE T.Item=A.Item AND T.Client=A.Client AND Category='BUY' ORDER BY 1 DESC) AS [PURCHASE DATE (X)]
from MainTable A;

Hope that helps

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION.

Is this what you want?

create view v_t as
    select t.*,
           (case when item = 'Bond A 5%' then '2015-06-30' end) as [PURCHASE DATE (X)]
    from t;

Upvotes: 1

Related Questions