Self Join on variable data

Question

I have a table with the following data:

Fiscal Year | Fiscal Quarter | Fiscal Week | Data
2009        | 2              | 22          | 9.5
2009        | 2              | 24          | 8.8
2009        | 2              | 26          | 8.8
2009        | 3              | 28          | 8.8
2009        | 3              | 31          | 9.1
2009        | 3              | 33          | 8.8

I would like to write a query that would produce the following:

Fiscal Year | Fiscal Quarter | Fiscal Week | Data | Trend
2009        | 2              | 22          | 9.5  | NULL
2009        | 2              | 24          | 8.8  | -0.7
2009        | 2              | 26          | 8.8  | 0
2009        | 3              | 28          | 8.8  | 0
2009        | 3              | 31          | 9.1  | 0.3
2009        | 3              | 33          | 8.8  | -0.3

I know this can be easily achieved by doing a simple join of the table to itself with the previous fiscal week, however this will not always be a simple t1.[Fiscal Week] = t2.[Fiscal Week] - 2 because sometimes the difference is 3 weeks.

I can pull the max record easily with something like this:

SELECT
    MAX(t1.[Fiscal Week]) "LastWeek"
FROM t1
WHERE t1.[Fiscal Year] = 2009
    AND t1.[Fiscal Week] < 31

But I'm at a loss when it comes to abstracting it to make the join work.

How can I do a self join on "the largest fiscal week that is smaller than the current record"?

Answer 1

If you are using SQL Server 2005, you can use CROSS APPLY clause.

Here you could have a table valued function, which will return the row for the fiscal week, prior to the one in current row.

CREATE FUNCTION dbo.fn_GetFiscalWeekBeforeThis(@Year AS int, 
  @CurrentWeek as int)
  RETURNS TABLE
AS
RETURN
  SELECT TOP 1 *
  FROM t1
  WHERE [Fiscal Year] = @Year 
  AND [Fiscal Week] < @CurrentWeek
  ORDER BY [Fiscal Week] DESC
GO

And then,

SELECT A.*,
A.Data - B.Data
FROM
t1 A
CROSS APPLY 
   dbo.fn_GetFiscalWeekBeforeThis(A.[Fiscal Year],  
   A.[Fiscal Week]) AS B

EDIT: Note that I have adapted the SQL by looking at an article & without an IDE.
Also, I don't know - how it will work for the first row.

So, please be kind :)

EDIT2: Let me know, if it doesn't work at all.
This will help me in knowing - not to answer things, without checking the results.

EDIT3: I have removed the need for Quarter parameter from the function.

Answer 2

does this work? The inline query is a bit of a mouthful and there's probably a better way of doing it...

select
  [fiscal year]
, [fiscal quarter]
, [fiscal week]
, [data] 
, (
    select top 1 (i.data - t1.data) from t1 as i
    where i.[fiscal year] >= t1.[fiscal year]
    and i.[fiscal quarter] >= t1.[fiscal quarter]
    and i.[fiscal week] >= t1.[fiscal week]
    and (
          i.[fiscal year] <> t1.[fiscal year]
       or i.[fiscal quarter] <> t1.[fiscal quarter]
       or i.[fiscal week] <> t1.[fiscal week]
    )
    order by [fiscal year] asc, [fiscal quarter] asc, [fiscal week] asc
) as trend
from t1

Answer 3

Easiest way to do this with Sql 2005+ is to use the ranking and windowing functions - simply partition/order your data and assign an appropriate sequence to each record (in this case you are partitioning by fiscalYear and ordering by fiscalWeek over that window) - would look something like this:

with data as
(
 select row_number() over (partition by a.fiscalYear order by a.fiscalWeek) as rn,
   a.fiscalYear, a.fiscalQuarter, a.fiscalWeek, a.Data
 from #TableName a
)
select a.fiscalYear, a.fiscalQuarter, a.fiscalWeek, a.Data, 
  a.Data - b.Data as Trend
from data a
left join data b
on  a.fiscalYear = b.fiscalYear
and  a.rn = b.rn + 1
order by a.fiscalYear, a.rn

This particular query would not allow extension across fiscalYear boundaries - if you wanted to extend to cross these year boundaries you would simply want to drop the "partition by" clause and "fiscalYear" join condition and instead order the set by a combination of the fiscalYear and fiscalWeek, something like this:

with data as
(
 select row_number() over (order by a.fiscalYear + a.fiscalWeek) as rn,
   a.fiscalYear, a.fiscalQuarter, a.fiscalWeek, a.Data
 from #TableName a
)
select a.fiscalYear, a.fiscalQuarter, a.fiscalWeek, a.Data, 
  a.Data - b.Data as Trend
from data a
left join data b
on  a.rn = b.rn + 1
order by a.rn

Answer 4

DECLARE @Fiscal TABLE
  ( 
   FiscalYear int
  ,FiscalQuarter int
  ,FiscalWeek int
  ,[Data] decimal(4,1) 
  )

INSERT INTO @Fiscal
          ( FiscalYear, FiscalQuarter, FiscalWeek, [Data] )
SELECT 2009, 2, 22, 9.5 UNION
SELECT 2009, 2, 24, 8.8 UNION
SELECT 2009, 2, 26, 8.8 UNION
SELECT 2009, 3, 28, 8.8 UNION
SELECT 2009, 3, 31, 9.1 UNION
SELECT 2009, 3, 33, 8.8 UNION
SELECT 2010, 1, 1, 9.0 UNION
SELECT 2010, 1, 2, 9.2

;
WITH  abcd
        AS ( SELECT FiscalYear
                   ,FiscalQuarter
                   ,FiscalWeek
                   ,[Data]
                   ,row_number() OVER ( ORDER BY FiscalYear, FiscalWeek ) AS rn
             FROM   @Fiscal
           )
  SELECT  a.FiscalYear
         ,a.FiscalQuarter
         ,a.FiscalWeek
         ,a.[Data]
         ,a.[Data] - b.[Data] AS [Trend]
  FROM    abcd AS a
          LEFT JOIN abcd AS b ON b.rn = ( a.rn - 1 )
  ORDER BY a.FiscalYear
         ,a.FiscalWeek