CODEWITHSUNDEEP
sqlsql-server-2005t-sql
Mark
Mark

Reputation: 1569

T-SQL Query Self join via Date Table

I am trying to write a query that joins a table to itself via a date table. The date table is populated with a day per row with dates for over 200 years (don't ask I didn't design it). It has a column for the date and one for previous working date (i.e. if its a Monday the prev date will be the previous Friday).

The other table lets call it Prices has a date column and an id to determine the type of price that comes once each day. I need to join Prices against itself via the date table to have each day alongside the previous day using the type column to determine which ones belong with each other.

Todays Date | Todays Price | Previous Working Day Date | Previous Working Day Price | Price Type

Any Ideas?

Upvotes: 0

Views: 723

Answers (2)

Andriy M
Andriy M

Reputation: 77667

Maybe like this:

SELECT
  today.Date,
  today.Price,
  yesterday.Date,
  yesterday.Price,
  today.PriceType
FROM Price today
  INNER JOIN dates d ON today.Date = d.Date
  INNER JOIN Price yesterday
    ON d.YesterdayDate = yesterday.Date AND today.PriceType = yesterday.PriceType

Upvotes: 1

Ed Harper
Ed Harper

Reputation: 21505

Something like:

SELECT today.Date, today.Price, lwd.Date, lwd.Price, today.TypeId
FROM Price AS today
JOIN Date AS d
ON   d.Date = today.Date
JOIN Price AS lwd
ON   lwd.Date = d.PreviousWorkingDate

Upvotes: 1

Related Questions