Thread: Create History Table / Outcome results

Hmmm...that might be a really ugly query. You might want to split this into two parts and first create a view that aggregates the tools by year and quarter -- that should make the report easier.

Code:

Select  ToolID
         , datepart(yyyy, CurrentMoYr) as Yr
         , datepart(q, CurrentMoYr) as Qtr
         , sum(ShippedQty) as ShippedQty
 From  myTable
 Group By ToolID, datepart(yyyy, CurrentMoYr), datepart(q, CurrnetMoYr)

Given that, you should be able to pivot the table into the results you want.

However, PIVOT is an SQL 2005 feature -- in SQL 2000 you're stuck with ugly case statements. Using the view above:

Code:

Select  ToolID
       ,  Case When Yr = 2008 and q = 1 Then ShippedQty End as 'Year2008-Q1'
       ,  Case When Yr = 2008 and q = 2 Then ShippedQty End as 'Year2008-Q2'
       ,  Case When Yr = 2008 and q = 3 Then ShippedQty End as 'year2008-Q3'
From myView

Yes, you'll need to add a Case statement for each Quarter.

Or, do the Pivot transform in code from the view and build the output table at reporting time