SQL Server query using a cursor

Question

The query below calculates the percentage of a product between FY2009 vs. FY2010 for California. I am assigned to calculate the remaining states (Please see STATES table). Is there a way in SQL Server that could calculate the states percentages? I am currently manually calculate each state at a time.

(Please note, I must use the states table separately due to data integrity. I was thinking if a cursor would do it, but I am not there yet.)

DECLARE @LOCATION VARCHAR(2)
SET  @state = 'CA' 

SELECT NUMBER1/CAST(NUMBER2 as FLOAT) as PERCENTAGE
FROM
(
    SELECT COUNT (PRODUCT) as NUMBER1
    FROM SOMETABLE1
    WHERE LOCATION = @state
    AND DATE >='10/1/2008'
    AND DATE  <' 10/1/2009'
)X,

(
    SELECT COUNT (PRODUCT) as NUMBER2
    FROM SOMETABLE2
    WHERE LOCATION = @state
    AND DATE >='10/1/2009'
    AND DATE  <' 10/1/2010'
)Y

PERCENTAGE
1.400742


SELECT state
FROM STATES

STATE
CA
AZ
TX
NV
NM
UT
OR
CO
WA

Answer 1

You can join on the STATES table. Something along these lines:

SELECT X.STATE, X.NUMBER1/CAST(Y.NUMBER2 as FLOAT) as PERCENTAGE
FROM
(
    SELECT S.STATE, COUNT(*) as NUMBER1
    FROM SOMETABLE1 AS S1
    INNER JOIN STATES AS S ON S1.LOCATION = S.STATE
    AND S1.DATE >='10/1/2008'
    AND S1.DATE < '10/1/2009'
    GROUP BY S.STATE
)X,

(
    SELECT S.STATE, COUNT (*) as NUMBER2
    FROM SOMETABLE2 AS S2
    INNER JOIN STATES AS S ON S2.LOCATION = S.STATE
    AND S2.DATE >='10/1/2009'
    AND S2.DATE < '10/1/2010'
    GROUP BY S.STATE
)Y
WHERE X.STATE = Y.STATE