Get Same Quarter Revenue From Previous Year

Question

I have a set of data that is divided by the department, fiscal year, fiscal quarter and finally total amount, similar to below.

+---------------------------------+------------+---------------+-------------+
|            Department           | FISCALYEAR | FISCALQUARTER | TotalAmount |
+---------------------------------+------------+---------------+-------------+
| Internal Medicine - Dermatology |       2018 |             2 | 50.00       |
| Internal Medicine - Dermatology |       2018 |             4 | 75.00       |
| Internal Medicine - Dermatology |       2019 |             1 | 135.00      |
| Internal Medicine - Dermatology |       2019 |             2 | 75.00       |
| Internal Medicine - Dermatology |       2019 |             3 | 185.00      |
| Internal Medicine - Dermatology |       2019 |             4 | 84.00       |
| Internal Medicine - Dermatology |       2020 |             1 | 85.00       |
| Internal Medicine - Dermatology |       2020 |             2 | 10.00       |
+---------------------------------+------------+---------------+-------------+

How would I add a column to get the total amount from the previous year/quarter? For instance fiscal year 2020, fiscal quarter 2 would show 75.00.

The tough part is that some quarters don't have any data, so there are gaps.

I have attempted a LAG() window function, but have difficulties on specifying the offset since it is not a standard offset.

Any help/ideas would be great.

Answer 1

How about this?

IF OBJECT_ID('tempdb.dbo.#YourTable', 'U') IS NOT NULL DROP TABLE #YourTable; 

CREATE TABLE #YourTable(
   Department    VARCHAR(33) NOT NULL
  ,FISCALYEAR    INTEGER  NOT NULL
  ,FISCALQUARTER INTEGER  NOT NULL
  ,TotalAmount   NUMERIC(7,2) NOT NULL
);

INSERT INTO #YourTable(Department,FISCALYEAR,FISCALQUARTER,TotalAmount) VALUES ('Internal Medicine - Dermatology',2018,2,50.00);
INSERT INTO #YourTable(Department,FISCALYEAR,FISCALQUARTER,TotalAmount) VALUES ('Internal Medicine - Dermatology',2018,4,75.00);
INSERT INTO #YourTable(Department,FISCALYEAR,FISCALQUARTER,TotalAmount) VALUES ('Internal Medicine - Dermatology',2019,1,135.00);
INSERT INTO #YourTable(Department,FISCALYEAR,FISCALQUARTER,TotalAmount) VALUES ('Internal Medicine - Dermatology',2019,2,75.00);
INSERT INTO #YourTable(Department,FISCALYEAR,FISCALQUARTER,TotalAmount) VALUES ('Internal Medicine - Dermatology',2019,3,185.00);
INSERT INTO #YourTable(Department,FISCALYEAR,FISCALQUARTER,TotalAmount) VALUES ('Internal Medicine - Dermatology',2019,4,84.00);
INSERT INTO #YourTable(Department,FISCALYEAR,FISCALQUARTER,TotalAmount) VALUES ('Internal Medicine - Dermatology',2020,1,85.00);
INSERT INTO #YourTable(Department,FISCALYEAR,FISCALQUARTER,TotalAmount) VALUES ('Internal Medicine - Dermatology',2020,2,10.00);

SELECT a.Department, a.FISCALYEAR, a.FISCALQUARTER, 
       a.TotalAmount, b.TotalAmount AS PriorYearQuarterTotalAmount
FROM #YourTable a
LEFT JOIN #YourTable b ON a.Department = b.Department
                     AND a.FISCALYEAR - 1 = b.FISCALYEAR
                     AND a.FISCALQUARTER = b.FISCALQUARTER