I have each entity data over three different [periods]. These are: 2022-03-31, 2021-09-30 and 2021-03-31
I want to alter the dataset and have
2022-03-31 AS [curr_period], 2021-09-30 AS [prev_period] and 2021-03-31 AS [prev2_period].
This can as such easily done by case statements.
The problem is, I want that data to be aligned, so that [entity] appears and then on the same line [curr_period], [prev_period], [prev2_period]
How can this be achieved?
Row (over) ? But I don’t think it would actually be doing what I want. What other method is there?
Advertisement
Answer
When you say alter database, do you mean to create a new table for this entity or are you just looking to have a select query? Do you know those dates for certain or do you also need to sort them by date and get first, second and third?
If you know these dates for certain and they don’t change then why do you use them as hard-coded like this
SELECT entity, '2022-03-31' AS [curr_period], '2021-09-30' AS [prev_period], '2021-03-31' AS [prev2_period] FROM entities Group by entity
I might be missing something but it’s hard to know for sure without example data.
This works for me without hard-coded dates.
SELECT e1.entity, e1.date AS [curr_period], e2.date AS [prev_period], e3.date AS [prev2_period] FROM entities e1 join entities e2 on e2.entity = e1.entity and e1.date > e2.date join entities e3 on e3.entity = e1.entity and e2.date > e3.date order by e1.entity, e1.date desc