SQL Statement. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Statement.

Author

Topic

viperbyte
Posting Yak Master

132 Posts

Posted - 2011-12-15 : 09:02:45

Good Morning all. I have 32 queries to do that look very similar to the following SQL statement. They are Access queries that I need to rewrite to MSSL queries. But they use subqueries and I'm not a strong sql guy. If someone would be so kind to show me how to do this query I promise not bug you ppl on this forum for the rest of the queries. The remaining 31 queries tie into SubQuery SMLNSHRDATA so once someone saves this soul with this once re-write I'll be able to use it as a template. Someone on this forum did one of these rewrites and it works perfect. The forum member rewrote the Access subQuery SMLNSHRDATA. Can someone please show me how to do this?

/*SMLNRAGE*/
SELECT [CountyDemographics].[MedianAgeDecile], Avg(SMLNSHRDATA.MillennialShare) AS AvgOfMillennialShare
FROM [CountyDemographics] INNER JOIN SMLNSHRDATA ON [CountyDemographics].FIPS=SMLNSHRDATA.FIPS
GROUP BY [CountyDemographics].[MedianAgeDecile];

/* the following sql statement is the definition of the SMLNSHRDATA subquery as T-SQL*/
/*this query was supposed to return 'as SMLNSHRDATA.MillennialShare' in the the header but its not happening */
SELECT m.FIPS,n.Millennials*1.0/m.CountOfRULEDATE
FROM
(
SELECT CountyTbl.FIPS, Count([t2009ArtsNonProfits].RULEDATE) AS CountOfRULEDATE, Sum([t2009ArtsNonProfits].TOTREV2) AS AllRev
FROM t2009ArtsNonProfits INNER JOIN CountyTbl ON [t2009ArtsNonProfits].FIPS=CountyTbl.FIPS
GROUP BY CountyTbl.FIPS
HAVING (((Count([t2009ArtsNonProfits].RULEDATE))>19))
)m
INNER JOIN
(
SELECT CountyTbl.FIPS, Count(t2009ArtsNonProfits.RULEDATE) AS Millennials, Sum(t2009ArtsNonProfits.TOTREV2) AS MillenialRev
FROM t2009ArtsNonProfits INNER JOIN CountyTbl ON [t2009ArtsNonProfits].FIPS=CountyTbl.FIPS
WHERE ((([t2009ArtsNonProfits].RULEDATE)>199912))
GROUP BY CountyTbl.FIPS
)n
ON n.FIPS = m.FIPS

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-15 : 09:38:18

Here's the easy answer, if it's actually what you're looking for.


;with SMLNSHRDATA
AS

(

SELECT m.FIPS,n.Millennials*1.0/m.CountOfRULEDATE as MillennialShare
FROM
(
SELECT CountyTbl.FIPS, Count([t2009ArtsNonProfits].RULEDATE) AS CountOfRULEDATE, Sum([t2009ArtsNonProfits].TOTREV2) AS AllRev
FROM t2009ArtsNonProfits INNER JOIN CountyTbl ON [t2009ArtsNonProfits].FIPS=CountyTbl.FIPS
GROUP BY CountyTbl.FIPS
HAVING (((Count([t2009ArtsNonProfits].RULEDATE))>19))
)m
INNER JOIN
(
SELECT CountyTbl.FIPS, Count(t2009ArtsNonProfits.RULEDATE) AS Millennials, Sum(t2009ArtsNonProfits.TOTREV2) AS MillenialRev
FROM t2009ArtsNonProfits INNER JOIN CountyTbl ON [t2009ArtsNonProfits].FIPS=CountyTbl.FIPS
WHERE ((([t2009ArtsNonProfits].RULEDATE)>199912))
GROUP BY CountyTbl.FIPS
)n
ON n.FIPS = m.FIPS
)

SELECT [CountyDemographics].[MedianAgeDecile], Avg(SMLNSHRDATA.MillennialShare) AS AvgOfMillennialShare
FROM [CountyDemographics] INNER JOIN SMLNSHRDATA ON [CountyDemographics].FIPS=SMLNSHRDATA.FIPS
GROUP BY [CountyDemographics].[MedianAgeDecile];

Jim

Everyday I learn something that somebody else already knew

viperbyte
Posting Yak Master

132 Posts

Posted - 2011-12-15 : 11:50:00

Hi Jim. Thank you very much, you da best.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources