Calculating percent change from one record to next

问题:

I am using this query to calculate percent change of Enrollment total from current semester to the last. What changes do I need to make to this query to avoid nulls ? I have tried using a sub query which is painfully slow.

SELECT E2.Term AS Prev_Term, 
       E2.[Enrollment Total] AS Prev_Enrl_Tot, 
       E1.Term,
       E1.Location, 
       E1.milestone_description, 
       E1.polling_date,
       Round(((E1.[Enrollment Total] - [Prev_Enrl_Tot]) 
                                   / [Prev_Enrl_Tot]) * 100,2) AS Percent_Change
FROM EnrollmentsByLocation E1 
LEFT OUTER JOIN EnrollmentsByLocation E2
ON E1.Location = E2.Location 
AND E1.milestone_description = E2.milestone_description 
AND E1.Term - E2.Term = 1;

回答1:

What flavor SQL? What do you mean by avoid nulls? Are you trying to avoid divide by zero error, or not display nulls? Use ISNULL, but wrap NULLIF around the divisor to avoid divide by zero error. This handles both. Or are you trying to eliminate them from returning? If so, look at your LEFT join or a WHERE clause. You'll need to place your aliases on [Prev_Enrl_Tot].

 ISNULL(Round(((E1.[Enrollment Total] - [Prev_Enrl_Tot]) / NULLIF([Prev_Enrl_Tot],0)) * 100,2),0) AS Percent_Change