I have a table that contains a field called 'Date_Key'. It is a fifteen digit number when I CAST it to BIGINT.

When the data is created, the Date_Key is supposed to increment by five. I'm trying to figure out if, between sequential Date_Keys, if there are any where the difference is more or less than five.

I tried this:

select distinct
cast(c1.Date_key as bigint) as FirstKey
,(cast(c2.Date_key as bigint) -1000) as SecondDateKey
,c1.Day_Number_In_Month as firstDate
,c2.Day_Number_In_Month as SecondDate
,c2.Day_Number_In_Month - c1.Day_Number_In_Month as DateDifference

from dim.Calendar c1
join dim.Calendar c2
on cast(c1.Date_key as bigint) = (cast(c2.Date_key as bigint) -1000)

I'm not exactly sure how the join should be so that I can do math between a Date_Key and the one that comes right after it.

I've seen it done before, but I can't get my hands on an example of how it is supposed to work.

Thank you for your help.

cdun2

If you want to compare rows to the next row where by "next row" you mean the row with the next largest value in some column and if you are on SQL 2012 or later, use the LEAD function, for example

select
cast(c1.Date_key as bigint) as CurrentDateKey
,(cast(LEAD(c1.Date_key) Over(Order By c1.Date_Key) as bigint)NextDateKey
,c1.Day_Number_In_Month as CurrentDate
,LEAD(c1.Day_Number_In_Month) Over(Order By c1.Date_Key) as NextDate
,LEAD(c1.Day_Number_In_Month) Over(Order By c1.Date_Key) - c1.Day_Number_In_Month as DateDifference

from dim.Calendar c1