CODEWITHSUNDEEP
sql
Shoaib Maroof
Shoaib Maroof

Reputation: 369

Create 2nd column with the result set of existing column shifting up a row

I would like to generate a column called PY Pacing Revenue, where the values from Pacing Revenue are shifted a year up.

This Pacing Revenue can be referred to as the amount of revenue generated at this point last year. i.e. for the Year 2021, gross revenue up until 19/01/2021.

Below you can find the CASE statement that I am using to get the pacing flag. This is then used in another CASE statement to return paced gross revenue


SELECT SUM(z.paced_cy_gross)
  FROM (   SELECT CASE
                       WHEN y.include_in_cy_pacing = 'Y' THEN y.[gross_revenue]
                       ELSE 0 END AS paced_cy_gross
             FROM (   SELECT x.gross_revenue, x.include_in_cy_pacing
                        FROM (   SELECT      a.gross_revenue,
                                             CASE
                                                  WHEN c.booking_date <= CAST(CONCAT(
                                                                                  CAST(d.Year AS CHAR(4)),
                                                                                  '-',
                                                                                  CAST(MONTH(GETDATE()) AS CHAR(2)),
                                                                                  '-',
                                                                                  CAST(DAY(GETDATE()) AS CHAR(2))) AS DATETIME) THEN
                                                      'Y'
                                                  ELSE 'N' END AS include_in_cy_pacing
                                   FROM      dbo.date AS d
                                   JOIN      dbo.factdaily AS a WITH (NOLOCK)
                                     ON d.ID = a.date_id
                                   LEFT JOIN dbo.contract AS c WITH (NOLOCK)
                                     ON c.ID = a.contract_id) x ) y ) z
 GROUP BY z.Year
 ORDER BY z.Year;
Year Pacing Revenue PY Pacing Revenue
2022 20000 25000
2021 25000 18000
2020 18000 22000
2019 22000 [2018 pacing value]

PY Pacing Revenue is my desire field.

Upvotes: 0

Views: 32

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can try to use LAG window function if your DBMS support it.

Query 1:

SELECT *,LAG( [Pacing Revenue] ) OVER(ORDER BY [YEAR])
FROM T 
ORDER BY  [Year] DESC

Results:

| Year | Pacing Revenue |        |
|------|----------------|--------|
| 2022 |          20000 |  25000 |
| 2021 |          25000 |  18000 |
| 2020 |          18000 |  22000 |
| 2019 |          22000 |  11000 |
| 2018 |          11000 | (null) |

Upvotes: 1

Related Questions