query joined off sums and counts

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 2005 Forums

Transact-SQL (2005)

query joined off sums and counts

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2012-05-07 : 11:50:31

Hi,

I am trying to come with a query based on 2 other queries.

Qry1 is pulling back planid, planname, status, sumoflives, type
Qry2 is pulling back planname, type, countofplanname

What I am trying to pull together in 1 query is -

planname, type, status (where some of lives is greatest), expression ("Some may differ") if count is >1.

At the end I would want 1 line item per plan/type combination. Could anyone help me out with this?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-07 : 17:27:27

[code]
SELECT q1.planname,q2.type,q1.status,CASE WHEN q2.countofplanname>1 THEN 'Some may differ' ELSE CAST(q2.countofplanname AS char(1)) END
FROM Qry1 q1
INNER JOIN Qry2 q2
ON q2.planname = q1.planname
INNER JOIN (SELECT planname,MAX(sumoflives) AS MaxLives
FROM Qry1
GROUP BY planname) q21
On q21.planname = q2.planname
AND q21.MaxLives = q2.sumoflives
GROUP BY q1.planname,q2.type
[/code]

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

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2012-05-08 : 10:37:00

This looks good but I keep getting a syntax error. When I pull individual sections out and run it separately it seems to work but I cannot seem to find the error here.

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2012-05-08 : 14:00:42

Hi, I finally got this to work. I am actually testing in access so I had to change the case statement to a iif statement.

Could someone take a look and let me know if this is the best way to do this? I added in my additional fields and query names once I knew I had something that worked.


SELECT q1.PBM, q1.Parentname, q1.Providername, q1.planname, q1.plantype, q1.formularystatus & IIf(q2.countofplanname>1," - plan by plan may differ","") AS Formulary, q1.drugname
FROM tblMarketMatching 
INNER JOIN ((qrymaxcounts AS q1 INNER JOIN qrycounts AS q2 
ON (q1.drugname = q2.DrugName) AND (q1.planname = q2.planname)) 
INNER JOIN 
              (SELECT planname, MAX(sumoflives) AS MaxLives, drugname FROM qrymaxcounts GROUP BY planname, drugname)  AS q21 
ON (q1.drugname = q21.drugname) AND (q1.planname = q21.planname) AND (q1.sumoflives = q21.MaxLives)) ON tblMarketMatching.Product = q1.drugname
GROUP BY q1.PBM, q1.Parentname, q1.Providername, q1.planname, q1.plantype, q1.drugname, [q1].[formularystatus] & IIf([q2].[countofplanname]>1," - plan by plan may differ","");

What each query returns -
qryMaxCounts - sum of lives grouped by multiple attributes.
qryCounts - count of combinations of drug and plan. If greater than 1 tells me that there are differences amongst plans.
tblMarketMatching - limits which product group to return.

Goal of query is to return the status of the plan with the greats live count. Then check the plan against the plan account. If > 1 place concatenate text to status.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 16:29:31

You might be better off asking this in Access forum if you're using Access. though majority of syntax is similar, there are few changes in Access so it might be probably worth putting it across to an Access expert!

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

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2012-05-09 : 09:36:31

I agree Access and SQL server are different in certain ways. Though I tend to prefer to learn how to make something work in SQL and adjust it if needed.

The above query works. And I have also made it work by making multiple queries to capture in each piece and then combining them at the end. I am just trying to figure out if there is a better way to do this. The case statement, or iif, is interchangeable depending on where it is ultimately used.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources