Help with query - 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

General SQL Server Forums

New to SQL Server Programming

Help with query

Author

Topic

silvannos
Starting Member

2 Posts

Posted - 2010-04-12 : 14:43:21

Hi, I am pretty new to SQL and so I am having a bit of trouble designing a query for what I want to do. The query I need is meant to give the number of times the reports of a certain company have been executed. I've been able to create a query that returns the report name and the number of times each report has been executed. Thing is, those numbers aren't correct as I have to remove another number from this one. I would like to do something like COUNT(initial_value) - COUNT(what to remove).

Here is the query that gives the incorrect results:

SELECT r.label AS "Report Name",
COUNT(r.id) AS ReportExecutions
FROM a, u, r, f
WHERE a.resource_id = r.id
AND u.id = r.id
AND a.updating = 0
AND r.parent_folder = f.id
GROUP BY r.label
ORDER BY reportexecutions DESC

...And the results:

Report Name...|..REPORTEXECUTIONS
--------------------------------------
report1..........|..178
report2..........|..21
report3..........|..18
report4..........|..12
report5..........|..10

Here is a query I made that gets the amount that needs to be removed from the first COUNT:

SELECT COUNT(a.updating)
FROM a, u, r, f
WHERE a.resource_id = r.id
AND u.id = r.id
AND a.updating = 1
AND r.parent_folder = f.id
GROUP BY a.resource_id

This gives me, where the 20 is the number that needs to be removed for the a certain report(I can get the report id) and 1 is regarding another report:
COUNT(A.UPDATING)
-----------------
20
1

So, I would appreciate it if someone could show me a way to combine the two queries.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 14:46:24

[code]SELECT r.label AS "Report Name",
COUNT(CASE WHEN a.updating = 0 THEN r.id ELSE NULL END) - COUNT(CASE WHEN a.updating = 1 THEN a.updating ELSE NULL END) AS ReportExecutions
FROM a, u, r, f
WHERE a.resource_id = r.id
AND u.id = r.id
AND r.parent_folder = f.id
GROUP BY r.label
ORDER BY reportexecutions DESC
[/code]

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

silvannos
Starting Member

2 Posts

Posted - 2010-04-12 : 14:59:52

Wow! Thanks a lot for the quick response, it works!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-12 : 15:05:02

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources