tough 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)

tough sql statement

Author

Topic

viperbyte
Posting Yak Master

132 Posts

Posted - 2011-12-13 : 13:12:02

Hi guys it's me again. Still doing this Access to MSSQL project that's making me sad. The Access db has querie that use sub queies that I'm super struggling with. I'll post it here and I know it's alot to ask to ask someone to show me but for now its just beyond my capabilities and skill. Maybe I'll get lucky and someone will come through? How do I do this?

SELECT CountyTbl.FIPS, [Millennials]/[CountOfRULEDATE] AS MillennialShare
FROM (NPODATE1Qry INNER JOIN CountyTbl ON NPODATE1Qry.FIPS=CountyTbl.FIPS)
INNER JOIN NPODATE2Qry ON CountyTbl.FIPS=NPODATE2Qry.FIPS
GROUP BY CountyTbl.FIPS, [Millennials]/[CountOfRULEDATE]
ORDER BY CountyTbl.FIPS;

/*this is me trying to make sense of it*/
M = select count(ruledate) from 2009 where ruledate > 199912
C = select COUNT(ruledate) from 2009 where ruledate > 19
M/C

/*npodate1qry..In Accees this is called npodate1.qry
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))
*/

/*npodate2qry...In Access this is called npodate2qry
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;
*/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 13:41:16

do you mean this?


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

viperbyte
Posting Yak Master

132 Posts

Posted - 2011-12-13 : 15:37:39

Thank you very much. I'm not sure it it works though I'm having trouble running the original query in access to compare against because the access db is corrupt. But from what you wrote I'm hoping that after I analyse it to death I can get the jist of it to do the others. I really appreciate that you took the time to write that statement. How do i get the MillennialShare from AS MillennialShare to show in the query? AS it is when I run this query I get "column unknown" as a header for where MillennialShare should be. Also as far as me learning this m.*** and n.*** stuff what topic is it that I need to look up?

viperbyte
Posting Yak Master

132 Posts

Posted - 2011-12-13 : 15:51:28

bth Visakk; your statement works great. Just found out. Super thanks :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-13 : 23:36:12

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources