Calculate MonthlyVolume - PIVOT SQL

[jayesh222705]

I am using SQL server 2008 and I have following Table with millions of rows...Here are few sample records


Serial_Num ReadingDate M_Counter Dyn_Counter
XYZ 3/15/2014 100 190
XYZ 4/18/2014 140 240
XYZ 5/18/2014 200 380
ABC 3/12/2014 45 40
ABC 4/19/2014 120 110
ABC 5/21/2014 130 155


and I would like calculate M_Counter and Dyn_Counter values for each month, For an example XYZ -> May month calculated counter value should be 60 = 200 (05/18/2014 value) - 140 (04/18/2014 value). I would like to insert data into another table in following way.

CalculatedYear CalculatedMonth Serial_Num M_Counter_Calc Dyn_Counter_Calc
2014 4 XYZ 40 50
2014 5 XYZ 60 140
2014 4 ABC 75 70
2014 5 ABC 10 45


Someone told me the best way to do it would be use Pivot SQL but never used before!! Can someone help me write this sql? I would really appreciate any help!

 

[gmmastros]

Will there always be exactly 1 reading from each month, or could there be missing months and/or multiple readings within the same month?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jayesh222705]

It will always have only one reading for each month and no missing months....

 

[jayesh222705]

After processing data I just found today that there are some cases when prior month's reading is not there!! In that case sql should go back previous month to get the reading and do calculation. I will put this in store procedure and Calculation needed for last 4 months only (CurrentMonth + 3 Previous Months).

For an example

Current Month (Aug) - Calculated_M_Counter =

IF M_Counter (Month Of July Value) > 0 Then
M_Counter (Aug) - M_Counter (July)
Else
IF M_Counter (Month of June Value) > 0 Then
M_Counter (Aug) - M_Counter (June)
If M_Counter(Month Of May Value) > 0 Then
M_Counter (Aug) - M_Counter (May)
End If

 

[jayesh222705]

I tried something like this but it's not working the way I want.

declare @Temp1 table
(
RowID int,
Serial_Num varchar(3),
ReadingDate datetime,
M_Counter int,
Dyn_Counter int
)

insert into @Temp1
select ROW_NUMBER() over (order by Serial_Num, ReadingDate), *
from MyTable T

select
Year(T1.ReadingDate) As CalculatedYear,
Month(T1.ReadingDate) as CalculatedMonth,
T1.Serial_Num,
T1.M_Counter - ISNULL(T2.M_Counter,0) as Calculated_M_Counter,
T1.Dyn_Counter - isnull(T2.Dyn_Counter,0) as Calculated_Dyn_Counter
from @Temp1 T1
left outer join @Temp1 T2 on T1.RowID = T2.RowID + 1 and T1.Serial_Num = T2.Serial_Num
order by T1.Serial_Num, Year(T1.ReadingDate), Month(T1.ReadingDate)

The concept is to sort the dataset based on Serial_Num and ReadingDate. Add a sequential Row ID and store into a temp table. Join the table onto itself such that you match up the current row with the previous row where the serial numbers still match. This approach not working if previous month value is not there! Can someone please help me?

Hope this explain in more details...