date diff, same column diff row

[abenitez77]

I want to get the date difference of a column on 2 different rows. I want to create 2 fields on the fly. 1 - will capture the difference between the date in the current column and the date in the next record. 2 - will capture the date value minus 1 day of the next row and put it in the current row. If the is no item# on the next row related to the current row, then the value should default to '2008/12/31' and if it is an item# that only has 1 item# in that group, then it defaults to '2008/12/31'. I have this query that works but it is not giving me the right results for when the item# changes on the last record set of the items. See the results below:

Code:
SELECT T.[RecNo]
,T.[Vnd#_max]
,T.[Vnd#_min]
,T.[VndName_max]
,T.[VndName_min]
,T.[Item#]
,T.[SKU_max]
,T.[SKU_min]
,T.[Desc_max]
,T.[Dept_max]
,T.[Dept_min]
,T.[RecType_max]
,T.[RealEffDt]
,T.[ActiveDt_Min]
,T.[ActiveDt_Max]
,T.[CreateDt_Min]
,T.[CreateDt_Max]
,T.[CreateID_max]
,T.[ApprovalDt_Min]
,T.[ApprovalID_max]
,T.[Cost]
, DateAdd(Day, DATEDIFF(Day, T.ActiveDt_Min, T1.ActiveDt_Min)-1, T.ActiveDt_Min) As ActiveDT_End
, DATEDIFF(Day, T.ActiveDt_Min, T1.ActiveDt_Min ) As ActiveDT_Diff
, CASE When T1.RealEffDt is null Then cast('12/31/2008' as datetime) Else DateAdd(Day, DATEDIFF(Day, T.RealEffDt, T1.RealEffDt)-1, T.RealEffDt) END As RealEffDt_End
, CASE When T1.RealEffDt is null Then NULL Else DATEDIFF(Day, T.RealEffDt, T1.RealEffDt ) END As RealEffDt_Diff
FROM [Office_Depot_2009].[dbo].[AS_Cost_History_Tmp] T
Inner Join [Office_Depot_2009].[dbo].[AS_Cost_History_Tmp] T1
ON T.RecNo = T1.RecNo - 1 AND T.Item# = T1.Item#
order by item#

( I am putting them in 2 separate result sets and i removed fields from the results here so they fit and you can see them)

Results:
RecNo Item# RealEffDt RealEffDt_End EffDt_Diff
1124 101493 NULL 12/31/2008 NULL
1127 101493 7/5/2007 7/5/2007 1
1129 101493 7/3/2008 9/30/2008 90
1130 101493 10/1/2008 10/17/2008 17
1123 101493 NULL 12/31/2008 NULL
1125 101493 NULL 12/31/2008 NULL
1126 101493 NULL NULL NULL
1128 101493 7/6/2007 7/2/2008 363
1132 101504 NULL 12/31/2008 NULL
1133 101504 NULL 12/31/2008 NULL

(This is from the same result set above)
RecNo Item# ActiveDt_Min ActiveDT_End ADT_Diff
1124 101493 10/1/2006 6/30/2007 273
1127 101493 7/6/2007 7/6/2007 1
1129 101493 7/8/2008 10/8/2008 93
1130 101493 10/9/2008 10/29/2008 21
1123 101493 4/1/2005 9/30/2006 548
1125 101493 7/1/2007 7/22/2008 388
1126 101493 7/23/2008 7/5/2007 -383
1128 101493 7/7/2007 7/7/2008 367
1132 101504 12/23/2005 1/1/2008 740
1133 101504 1/2/2008 7/1/2008 182

 

[ddiamond]

I've sorted your results by RecNo to make them easier to read.

RecNo  Item#   RealEffDt  RealEffDt_End   EffDt_Diff
1123   101493  NULL       12/31/2008      NULL
1124   101493  NULL       12/31/2008      NULL
1125   101493  NULL       12/31/2008      NULL
1126   101493  NULL       NULL            NULL
1127   101493  7/5/2007   7/5/2007        1
1128   101493  7/6/2007   7/2/2008        363
1129   101493  7/3/2008   9/30/2008       90
1130   101493  10/1/2008  10/17/2008      17
1132   101504  NULL       12/31/2008      NULL
1133   101504  NULL       12/31/2008      NULL

RecNo   Item#   ActiveDt_Min    ActiveDT_End    ADT_Diff
1123    101493  4/1/2005        9/30/2006       548
1124    101493  10/1/2006       6/30/2007       273
1125    101493  7/1/2007        7/22/2008       388
1126    101493  7/23/2008       7/5/2007        -383
1127    101493  7/6/2007        7/6/2007        1
1128    101493  7/7/2007        7/7/2008        367
1129    101493  7/8/2008        10/8/2008       93
1130    101493  10/9/2008       10/29/2008      21
1132    101504  12/23/2005      1/1/2008        740
1133    101504  1/2/2008        7/1/2008        182

I think one of the problems with the query is the inner join. It should be a left join. That is why record 1131 is missing. It failed the criteria T.Item# = T1.Item#.

 

[abenitez77]

When I tried a Left Join, it made my table grow and it still did not get the right results.

 

[abenitez77]

Interesting...I made the change below :

, T1.RealEffDt - 1 As RealEffDt_End

and I ordered it by recno, item#

and that gave me the results i wanted.