Changes to a Query Based on Year

Question

    select datepart(month,s1.Timeperiod) as monthofaum, 
           datepart(YEAR,s1.Timeperiod) as Yearofaum,
           ISNULL(s2.endingAum,0) as Starting_Aum, 
           s1.endingAum as Ending_Aum
    from #temp_1 s1
    left outer join (select * from #temp_1)s2
    on month(s1.Timeperiod) = dateadd(D,1,month(s2.Timeperiod))

This work perfectly for the Monthly basis, but supoose if i need to change the query to obtain the result based on the year also - where should i make changes?

Example
    monthofaum  Yearofaum   Starting_Aum          Ending_Aum
    ----------- ----------- --------------------- ---------------------
    11          2009        0.00                  0.00
    12          2009        0.00                  1059594254.86
    1           2010        0.00                  1083195051.98
    2           2010        1083195051.98         1125314638.64
    3           2010        1125314638.64         1212355911.70
    4           2010        1212355911.70         1270374634.62
    5           2010        1270374634.62         1265193377.27
    6           2010        1265193377.27         1260776179.02
    7           2010        1260776179.02         2599205697.44
    8           2010        2599205697.44         1323838670.57

If you look at the data can see that for 2010 the Ending Aum value of previous month will be equal to Starting Aum of next month, but when it comes to year 2009 the dec Ending Aum is not assigned to Jan 2010 Starting Aum.

This is the bug which i need to fix.

Answer 1

This is assuming you don't care about the time I think this will work...

select datepart(month,s1.Timeperiod) as monthofaum,  
   datepart(YEAR,s1.Timeperiod) as Yearofaum, 
   ISNULL(s2.endingAum,0) as Starting_Aum,  
   s1.endingAum as Ending_Aum 
from #temp_1 s1 
left outer join (select * from #temp_1) s2 
on s1.TimePeriod = DateAdd(year,1,s2.TimePeriod)

EDIT:
Or if you do care about time you could try this (I have a function for convenience)...

1.Create dateonly function

CREATE FUNCTION [dbo].[fn_DateOnly](@DateTime DATETIME)
-- Returns @DateTime at midnight; i.e., it removes the time portion of a DateTime value.
RETURNS  DATETIME
AS
BEGIN
RETURN DATEADD(dd,0, DATEDIFF(dd,0,@DateTime))
END

2. This allows you to:

select datepart(month,s1.Timeperiod) as monthofaum,  
   datepart(YEAR,s1.Timeperiod) as Yearofaum, 
   ISNULL(s2.endingAum,0) as Starting_Aum,  
   s1.endingAum as Ending_Aum 
from #temp_1 s1 
left outer join (select * from #temp_1) s2 
on dbo.fn_DateOnly(s1.TimePeriod) = DateAdd(year,1,dbo.fn_DateOnly(s2.TimePeriod))

Answer 2

To adjust it for year, compare just the month and year. For example,

select convert(varchar(7), getdate(), 120)

Prints 2010-10. Applied to your query, you could rewrite the on like:

on convert(varchar(7), s1.TimePeriod, 120) = 
   convert(varchar(7), DateAdd(year, 1, s2.TimePeriod), 120)

P.S. the subquery is not required. This line:

 left outer join (select * from #temp_1)s2

is exactly the same as:

 left outer join  #temp_1 s2

Answer 3

select datepart(month,s1.Timeperiod) as monthofaum, 
           datepart(YEAR,s1.Timeperiod) as Yearofaum,
           ISNULL(s2.endingAum,0) as Starting_Aum,  
           s1.endingAum as Ending_Aum 
    from #temp_1 s1 
    left outer join (select * from #temp_1)s2 
    on (month(s1.Timeperiod)-1 = month(s2.Timeperiod) or (month(s1.Timeperiod)=1 and month(s2.Timeperiod)=12))

The above Query works if the table contains only data for 2 years.
i will keep you all posted if i figure out the problem for above quey