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.
| Author |
Topic |
|
sqlclarify
Yak Posting Veteran
56 Posts |
 Posted - 2009-05-11 : 01:58:42
|
| Ok, I have a query which involves taking counts based on a condition.I want to create subqueries so that the other tables involved in the query won't interfere with the results..I have something like this SELECT incomplete.cnt AS "INCOMPLETES", completes.cnt AS "COMPLETES", notbegun.cnt AS "NOT BEGUN" FROM other tables JOIN join...etc.etc. LEFT JOIN (SELECT COUNT(projectno) AS count, province, person AS worker,month AS m FROM projects JOIN states..ON..etc. WHERE ((startdate <=sysdate) and enddate is null GROUP BY province,person,month )incomplete ON project.province = incomplete.province AND project.person= incomplete.person AND project.month = incomplete.month Similarly, for other conditions too.. where enddate <=sysdate etc.etc. I have other subqueries.Please note that I have changed the names of my tables and fields so I don't expose the details of my project.. But hope you get the point.Problem is that I end up adding like 15 subqueries to get all the total counts. As a result the query takes like 2 hours to run. Is there an easier way to do the same thing without the query taking so long? (I did try doing select count(case when...condition then.. end) etc.. but that doesn't give me correct results.. I have to use a subqueries... so I don't understand what else to do..Hope this information is enough.. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-11 : 10:54:39
|
| are all the tables involved in count calculation the same? else you have to use separate subqueries for them. anyways, if you could post some sample data to illustrate your scenario, then we can suggest more. The current explanation is not enough to understand what exactly you're after. |
 |
|
|
|
|
|
subqueries