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

yeah, only 4 accepted answers to your 15 questions, even when a lot of them are correct....you should accept the answers provided to you — Lamak
– Lamak, Commented Dec 20, 2010 at 21:07

Klaus Byskov Pedersen · Accepted Answer · 2010-12-20 20:19:39Z

2

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

answered Dec 20, 2010 at 20:19

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server query using a cursor

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related