SQLTeam.com | Weblogs | Forums

Need you help constructing Looping SQL in my SELECT statement


#1

I have this SQL that I want to transform into different result in my output desired below.
SQL = "SELECT Pond, [Week No], ABW FROM table1
ORDER BY Pond,Week"

  • Calculate the weekly growth per Pond

RESULT:
Pond Week No ABW Week Growth to Calculate
03PA01 1 0.45 1st weekly growth will pick up from week 1 of each pond
03PA01 2 1.02 2nd week = 1.02 - 0.45 = 0.57
03PA01 3 2.01 3rd week = 2.01 - 1.02 = 1.08
03PA02 1 0.5 same calculation from pond 03PA01 for this new pond.
03PA02 2 1.23
03PA03 1 0.71
03PA03 2 1.39
03PA03 3 2.43

OUTPUT DESIRED:
Pond Week No ABW Week Growth
03PA01 1 0.45 0.45
03PA01 2 1.02 0.57
03PA01 3 2.01 1.08
03PA02 1 0.5 0.5
03PA02 2 1.23 0.73
03PA03 1 0.71 0.71
03PA03 2 1.39 0.68
03PA03 3 2.43 1.04

Thank you in advance.


#2

If you are on SQL 2012 or later, use LAG function:

ABW-LAG(ABW,1,0) over (partition by Pond order by Week) as WeekGrowth