Count() with a twist - 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)

Count() with a twist

Author

Topic

richardwaugh
Starting Member

36 Posts

Posted - 2011-09-08 : 14:03:13

My apologies if this question has already been asked before and answered.

I am attempting to do a count (which has been successful) but with an added twist. I'll post the code below as well. Basically I have a table that contains a bunch of data with a UserId and I also have a table that I am joined to (Users) that contains the information for the user based on the userid. What I want to do is a return the number of times the userid appears in the first table AND also include the userid (or username) of the users who do NOT appear in the first table.

Any suggestions??

Thanks guys!

Code:

SELECT sup.LastName + ', ' + sup.FirstName AS Supervisor
,Users.LastName + ', ' + Users.FirstName AS WorkerName
,Departments.DepartmentName
,COUNT(Cases.CaseId) AS NumberOfCases
FROM Cases
JOIN Users ON Cases.UserId = Users.UserID
JOIN Departments ON Cases.DepartmentId = Departments.ImmutableID
JOIN Users sup ON Users.SupervisorID = sup.UserID
JOIN LookUpCodes ON (Users.PositionID = LookUpCodes.LookupID)
WHERE (Cases.Closed > '2011-08-31 23:59:59.999' OR Cases.Closed IS NULL)
AND Cases.Entered <= '2011-08-31 23:59:59.999'
GROUP BY Users.FirstName, Users.LastName, Departments.DepartmentName, sup.LastName, sup.FirstName

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 14:24:13

RIGHT JOIN Users ON Cases.UserId = Users.UserID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 03:37:33

quote:
Originally posted by russell

RIGHT JOIN Users ON Cases.UserId = Users.UserID

that wont be enough i guess. he has a few filters on case table fields which might reduce it to an inner join unless he does this


SELECT sup.LastName + ', ' + sup.FirstName AS Supervisor
,Users.LastName + ', ' + Users.FirstName AS WorkerName
,Departments.DepartmentName
,COUNT(Cases.CaseId) AS NumberOfCases
FROM Cases
RIGHT JOIN Users ON Cases.UserId = Users.UserID
AND (Cases.Closed > '2011-08-31 23:59:59.999' OR Cases.Closed IS NULL)
AND Cases.Entered <= '2011-08-31 23:59:59.999'
JOIN Departments ON Cases.DepartmentId = Departments.ImmutableID
JOIN Users sup ON Users.SupervisorID = sup.UserID
JOIN LookUpCodes ON (Users.PositionID = LookUpCodes.LookupID)
GROUP BY Users.FirstName, Users.LastName, Departments.DepartmentName, sup.LastName, sup.FirstName

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

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-09 : 11:09:42

You're right again Visakh

richardwaugh
Starting Member

36 Posts

Posted - 2011-09-09 : 12:00:30

Hi guys. Thanks for the very quick responses. Unfortunately when I update the query and re-run it I get the same results. Non of the users who are not assigned to any cases are returned in my list. Perhaps I am missing something in my query?

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 14:00:06

what about this?


SELECT sup.LastName + ', ' + sup.FirstName AS Supervisor
,Users.LastName + ', ' + Users.FirstName AS WorkerName
,Departments.DepartmentName
,COUNT(Cases.CaseId) AS NumberOfCases
FROM Cases
RIGHT JOIN Users ON Cases.UserId = Users.UserID
AND (Cases.Closed > '2011-08-31 23:59:59.999' OR Cases.Closed IS NULL)
AND Cases.Entered <= '2011-08-31 23:59:59.999'
LEFT JOIN Departments ON Cases.DepartmentId = Departments.ImmutableID
JOIN Users sup ON Users.SupervisorID = sup.UserID
JOIN LookUpCodes ON (Users.PositionID = LookUpCodes.LookupID)
GROUP BY Users.FirstName, Users.LastName, Departments.DepartmentName, sup.LastName, sup.FirstName

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-09-09 : 14:03:33

You'll have to RIGHT join everythihg all those inner joins negate the RIGHT join (reffering to posts before 09/09/2011 : 14:00:06). Alternativly, you could do it some other ways. For example:

SELECT sup.LastName + ', ' + sup.FirstName AS Supervisor
,Users.LastName + ', ' + Users.FirstName AS WorkerName
,Departments.DepartmentName
,COUNT(Cases.CaseId) AS NumberOfCases
FROM 
    Users 
JOIN 
    Departments 
    ON Cases.DepartmentId = Departments.ImmutableID
JOIN 
    Users sup 
    ON Users.SupervisorID = sup.UserID
JOIN 
    LookUpCodes 
    ON Users.PositionID = LookUpCodes.LookupID
LEFT OUTER JOIN
    Cases
    ON Cases.UserId = Users.UserID
    AND (Cases.Closed > '2011-08-31 23:59:59.999' OR Cases.Closed IS NULL)
    AND Cases.Entered <= '2011-08-31 23:59:59.999'
GROUP BY Users.FirstName, Users.LastName, Departments.DepartmentName, sup.LastName, sup.FirstName


--OR


SELECT sup.LastName + ', ' + sup.FirstName AS Supervisor
,Users.LastName + ', ' + Users.FirstName AS WorkerName
,Departments.DepartmentName
,A.NumberOfCases
FROM 
    Users 
JOIN 
    Departments 
    ON Cases.DepartmentId = Departments.ImmutableID
JOIN 
    Users sup 
    ON Users.SupervisorID = sup.UserID
JOIN 
    LookUpCodes 
    ON Users.PositionID = LookUpCodes.LookupID
OUTER APPLY
(
    SELECT
        COUNT(Cases.CaseId) AS NumberOfCases, UserID
    FROM    
        Cases
    WHERE 
        (Cases.Closed > '2011-08-31 23:59:59.999' OR Cases.Closed IS NULL)
        AND Cases.Entered <= '2011-08-31 23:59:59.999'
    GROUP BY
        UserID
) AS A
ON Users.UserID = A.UserID

Completely untested, due to lack for DDL, DML and expected output.

richardwaugh
Starting Member

36 Posts

Posted - 2011-09-09 : 14:46:08

quote:

Originally posted by visakh16

what about this?


SELECT sup.LastName + ', ' + sup.FirstName AS Supervisor
,Users.LastName + ', ' + Users.FirstName AS WorkerName
,Departments.DepartmentName
,COUNT(Cases.CaseId) AS NumberOfCases
FROM Cases
RIGHT JOIN Users ON Cases.UserId = Users.UserID
AND (Cases.Closed > '2011-08-31 23:59:59.999' OR Cases.Closed IS NULL)
AND Cases.Entered <= '2011-08-31 23:59:59.999'
LEFT JOIN Departments ON Cases.DepartmentId = Departments.ImmutableID
JOIN Users sup ON Users.SupervisorID = sup.UserID
JOIN LookUpCodes ON (Users.PositionID = LookUpCodes.LookupID)
GROUP BY Users.FirstName, Users.LastName, Departments.DepartmentName, sup.LastName, sup.FirstName

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

This appears to work and has definitely got me going on the right track! Thank you very much Visakh!!

Lamprey: The first block of code didn't appear to work but I will be testing the second block as soon as I can.

Thanks for everything guys!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-10 : 00:59:46

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources