Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Proper syntax for a lookup table and 3 additional tables
Message
From
25/04/2001 12:42:12
 
 
To
24/04/2001 23:59:20
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Miscellaneous
Thread ID:
00499418
Message ID:
00499687
Views:
12
I don't know that you have to reference the table again as long as all the data is within the same table. You should be able to do a cross-tab and get the desired results.
DECLARE @CurrentYear integer
SET @CurrentYear = year(getdate())

SELECT
g.name,
SUM(CASE WHEN i.year_date = @currentyear THEN i.total_inf ELSE 0 END) AS currentyear,
SUM(CASE WHEN i.year_date = @currentyear-1 THEN i.total_inf ELSE 0 END) AS previousyear,
SUM(CASE WHEN i.year_date = @currentyear-2 THEN i.total_inf ELSE 0 END) AS secpreviousyear,
FROM geographic g
LEFT OUTER JOIN inforce_state i ON g.geo_code = i.geo_code
WHERE
g.operations_ind = 'Y' AND
g.company_code = '1' AND
i.lob_code = '00' AND
i.premium_policy_ind = 'P'
GROUP BY g.name
ORDER BY i.total_inf DESC
That should get you started.

-Mike
Michael Levy
MCSD, MCDBA
ma_levy@hotmail.com
Previous
Reply
Map
View

Click here to load this message in the networking platform