How do I make count show 0s?

[rhaas128]

Below is the sql query that I am running. Currently, if a user has 0 open for the number of tickets, their name is removed from my list. I need them to show with a 0. Otherwise, I do not realize they have no work. Thoughts?

I am relatively new to this so any and all help in lamens terms is greatly appreciated!

SELECT Count(dbo_Transactions.TransNbr) AS [# of Tickets], dbo_Transactions.TransStatus AS Status, dbo_Users.FirstName, dbo_Users.LastName
FROM dbo_Users INNER JOIN dbo_Transactions ON dbo_Users.SSN = dbo_Transactions.SecID
GROUP BY dbo_Transactions.TransStatus, dbo_Users.FirstName, dbo_Users.LastName, dbo_Users.FirstName
HAVING (((dbo_Transactions.TransStatus)="Open") AND ((dbo_Users.LastName)="last1" Or (dbo_Users.LastName)="last2" Or (dbo_Users.LastName)="last3" Or (dbo_Users.LastName)="last4" Or (dbo_Users.LastName)="last5"));

 

[Joe4]

So, if a person has both Open and Pending records, then it should show two lines of data (one for each Status type)?
That is actually simpler than the last code I came up with. We just need to update the WHERE clause in the code I posted in post #6 like this:

SELECT Count(Trans.TransNbr) AS [# of Tickets], Trans.TransStatus AS Status, dbo_Users.FirstName, dbo_Users.LastName
FROM dbo_Users
Left Join
(SELECT dbo_Transactions.*
 FROM dbo_Transactions
 WHERE dbo_Transactions.TransStatus in ("Open","Pending")) as Trans
ON dbo_Users.SSN = Trans.SecID
GROUP BY Trans.TransStatus, dbo_Users.FirstName, dbo_Users.LastName, dbo_Users.FirstName
HAVING (((dbo_Users.LastName) = "last1" Or (dbo_Users.LastName) = "last2" Or (dbo_Users.LastName) = "last3" Or (dbo_Users.LastName) = "last4" Or (dbo_Users.LastName) = "last5"))

 

[rhaas128]

This is great! I actually reverted back to the previous version (see below for it). I was wondering how I could also include a ClosedCount that is >=today's date.

It would pull from dbo_Transactions.SecClose I was trying to add something like and And statement of

And ((dbo_Transactions.SecClose)>=Date())

with a new sum for Closed. But I was unsuccessful.




Current Query

SELECT Sum(IIF(Trans.TransStatus="Open",1,0)) as OpenCount, Sum(IIF(Trans.TransStatus="Pending",1,0)) as PendingCount, dbo_Users.FirstName, dbo_Users.LastName
FROM dbo_Users 
LEFT JOIN 
(SELECT dbo_Transactions.* 
 FROM dbo_Transactions
 WHERE dbo_Transactions.TransStatus in ("Open","Pending")) as Trans
ON dbo_Users.SSN = Trans.SecID
GROUP BY dbo_Users.FirstName, dbo_Users.LastName, dbo_Users.FirstName
HAVING (((dbo_Users.LastName)="last1" Or (dbo_Users.LastName)="last2" Or (dbo_Users.LastName)="last3" Or (dbo_Users.LastName)="last4" Or (dbo_Users.LastName)="last5"));

 

[Joe4]

Is "Closed" a different TransStatus value?
Do you only want that criteria of (dbo_Transactions.SecClose)>=Date() applied for the Closed records and not the "Open" or "Pending" ones?

 

[rhaas128]

Closed is a TrasnStatus value. Basically what I am looking to do, is have it show how many open tickets the users have, how many pending tickets the users have, and how many tickets they have closed "today". So I am just looking to add the "closed tickets today" portion to the current query that shows the current amount of open/pending tickets. I have a separate query for requests closed today below.

SELECT dbo_Users.FirstName, dbo_Users.LastName, Count(dbo_Transactions.TransNbr) AS [Tickets closed]
FROM dbo_Transactions, dbo_Users
WHERE (((dbo_Transactions.SecID)=dbo_Users.SSN) And ((dbo_Transactions.TransStatus)="Closed") And ((dbo_Transactions.SecClose)>=Date()) And ((dbo_Users.Role)="lananalyst") And ((dbo_Users.LastName)="test1" Or (dbo_Users.LastName)="test2" Or (dbo_Users.LastName)="test3"))
GROUP BY dbo_Users.FirstName, dbo_Users.LastName
ORDER BY Count(dbo_Transactions.TransNbr) DESC;

 

[Joe4]

The only change to the Criteria you want to make is this:
Change this line:

WHERE dbo_Transactions.TransStatus in ("Open","Pending")) as Trans

to this:

WHERE dbo_Transactions.TransStatus in ("Open","Pending","Closed")) as Trans

Then, I think you just want to add the following calculated field in your first SELECT clause:

Sum(IIF((Trans.TransStatus="Closed") And (dbo_Transactions.SecClose>=Date()),1,0)) as ClosedCount

 

[rhaas128]

This works great, but the query is extremely slow. When I run the closed query by itself, it is instant. Any idea why?

 

[Joe4]

When you keep adding increased complexity to queries, it can slow them down. So sometimes it is best not to try to do too much in a single query.

 

[rhaas128]

Yeah, I completely undertand that. I just took out the open and pending part of that query and ran it only as Closed, and it still took a while to run. But my original "closed" query runs fine. So I am failing to understand that issue as it doesn't seem to be complexity driven.

 

[Joe4]

Nested queries often take longer to run.

Note that performance can also be affected by other factors, especially Indexes. If you make sure all the fields used in JOINs (and maybe CRITERIA) are indexed, you may experience better performance.
You should also regularly run "Compact and Repair" on your database to keep its size from bloating.