How Do I find Current period and YTD spanning over a 2 yr pd

[Goha]

How Do I find Current period and YTD spanning over a 2 yr pd . I need to find the following ..

Cust_ID, Cust_name, Month, Curr_Period, Curr_YTD, Last_Year_Period, Last_Year_YTD

I was thinking of using a view but view but I do not know where to Begin

Thanks....

 

[malexanian]

It doesn't have to be a view; you need an expression that transforms your month into a range of months. It's a common data warehousing concept; read up online if you are interested.

Sample table tblSales:
CustID, Month, Year, Sales

Intermediate Table #temp (to get one line per CustID per month)
SELECT CustID, Month, Year, Sum(Sales) Sales
INTO #temp
FROM tblSales
GROUP BY CustID, Year, Month

Final results:
SELECT CustID, Month, Sales Current_Sales,
(SELECT Sum(Sales) FROM #temp b WHERE
b.Year = a.Year AND b.Month <= a.Month) YTD_Sales,
(SELECT Sum(Sales) FROM #temp b WHERE b.Year
= a.Year - 1 AND b.Month = a.Month) Last_Sales,
(SELECT Sum(Sales) FROM #temp b WHERE b.Year =
a.Year - 1 AND b.Month <= a.Month ) LastYTD_Sales
FROM #temp a

Don't forget to drop #temp.

The technique above uses subqueries; read in Books Online for more.

 

[Goha]

I am looking to do this in a view as stated above the solution above i would need to put it into a stor proc.

Goha

 

[malexanian]

Take the SQL used to generate the temp table. Put it in parentheses and alias it. Use this as a dynamically generated table. Take the final result SQL and put the parenthesized expression into every slot that says #temp. You can see how it gets out of hand, but I do see why you would want this in a view. Good luck.