CODEWITHSUNDEEP
sql
Arnes
Arnes

Reputation: 13

Adding columns "Previous date"

I have table like this:

PartNr, ProductionSite, ProductionType, PODate, PoPrice, PoCurrency

The first three columns defines unique product. What I would like to do is to add one more column with previous PO date in order to make price valid date range. Here is example:

PartNr....  Site....   Type....    Date

111111      BBBBB       100     2008-06-10

111111      BBBBB       100     2012-01-18

111111      BBBBB       100     2012-01-30

111111      AAAAA       100     2008-06-10

111111      AAAAA       100     2012-01-18

111111      AAAAA       100     2012-01-30

I want to get this

PartNr....   Site ....  Type  ....  Date    .... Added Column

111111  BBBBB       100     2008-06-10  ...       0

111111  BBBBB       100     2012-01-18  ...     2008-06-10

111111  BBBBB       100     2012-01-30  ...     2012-01-18

111111  AAAAA       100     2008-06-10  ...     0

111111  AAAAA       100     2012-01-18  ...     2008-06-10

111111  AAAAA       100     2012-01-30  ...     2012-01-18

Upvotes: 1

Views: 121

Answers (1)

Patashu
Patashu

Reputation: 21773

You want a subquery that selects the greatest date that is below the current row's date (and matches partnr etc), something like:

select PartNr, ProductionSite, ProductionType, PODate, PoPrice, PoCurrency,
(select max(PODate) from table t2 where t2.PODate < t1.PODate and t2.PartNr = t1.PartNr and t2.ProductionSite = t1.ProductionSite and t2.ProductionType = t1.productionType
 as added_column from table t1

You also don't need to have it as a column in your table. Remember, the cardinal rule of database design is - never store what you can compute*.

*Unless your database never or almost never changes and you need it to be really, really fast. This is called a 'data warehouse'.

Upvotes: 1

Related Questions