Solved

Sql Query's not returning a value if 1st value 0 or nothing

Posted on 2011-09-26
10
265 Views
Last Modified: 2012-05-12
my query below returns nothing if the first query is nothing or a zero, but I tried doing it other ways witht eh same results.  I tried adding zeros's intot it but still same result, If I split apart it works fine and return the correct results for counts which should be 0,1,3.  How can I make it return values.  tried to count just one and group them but I get a cloumn with counts and I do not know which ones they are and it does not give me a zero count.


Select		Count(IntPermtaskId) as Dental,
			(Select Count(IntPermtaskId) as Medical from tblPermTask pt LEFT JOIN tblSRPQuestion q on q.intQuestionId = pt.intQuestionId LEFT JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = pt.strSSN Where q.intCategoryID = 2 and strUIC = 'P7TDA' or strAttchUIC = 'P7TDA' AND intSRPAttendId in(Select intSRPAttendId from tblSRPAttendance where intEventId = 147) Group by q.intCategoryID) as Medical,
			(Select Count(IntPermtaskId) as Medical from tblPermTask pt LEFT JOIN tblSRPQuestion q on q.intQuestionId = pt.intQuestionId LEFT JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = pt.strSSN Where q.intCategoryID = 3 and strUIC = 'P7TDA' or strAttchUIC = 'P7TDA' AND intSRPAttendId in(Select intSRPAttendId from tblSRPAttendance where intEventId = 147) Group by q.intCategoryID) as Admin
from		tblPermTask pt LEFT JOIN tblSRPQuestion q on q.intQuestionId = pt.intQuestionId LEFT JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn on mn.strSSN = pt.strSSN Where q.intCategoryID = 1 and strUIC = 'P7TDA' or strAttchUIC = 'P7TDA' AND intSRPAttendId in (Select intSRPAttendId from tblSRPAttendance where intEventId = 147)
Group by q.intCategoryID

Open in new window

0
Comment
Question by:kdeutsch
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 250 total points
ID: 36601124
Wouldn't it be simpler to do something like this?


SELECT  CASE q.intCategoryId
			WHEN 1 THEN 'Dental'
			WHEN 2 THEN 'Medical'
			WHEN 3 THEN 'Admin' 
		END AS CategoryID,
		COUNT(IntPermtaskId) AS Total
FROM    tblPermTask pt
        LEFT JOIN tblSRPQuestion q ON q.intQuestionId = pt.intQuestionId
        LEFT JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn ON mn.strSSN = pt.strSSN
WHERE   q.intCategoryID IN (1,2,3)
        AND strUIC = 'P7TDA'
        OR strAttchUIC = 'P7TDA'
        AND intSRPAttendId IN ( SELECT  intSRPAttendId
                                FROM    tblSRPAttendance
                                WHERE   intEventId = 147 )
GROUP BY q.intCategoryID

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 36601381
dbaduck:

Yes it would, but they also want zeros's to come up if the count = 0. I changed it to the following to get the right counts but I still get no counts for a zero.

SELECT  CASE q.intCategoryId
                  WHEN 1 THEN 'Dental'
                  WHEN 2 THEN 'Medical'
                  WHEN 3 THEN 'Admin'
            END AS CategoryID,
            COUNT(IntPermtaskId) AS Total
FROM    tblPermTask pt
        INNER JOIN tblSRPQuestion q ON q.intQuestionId = pt.intQuestionId
        INNER JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn ON mn.strSSN = pt.strSSN
WHERE   q.intCategoryID IN (1,2,3)
        AND (strUIC = 'P7TDA'
        OR strAttchUIC = 'P7TDA')
        AND intSRPAttendId IN ( SELECT  intSRPAttendId
                                FROM    tblSRPAttendance
                                WHERE   intEventId = 147 )
GROUP BY q.intCategoryID

0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 50 total points
ID: 36601639
Try this using GROUP BY ALL:

SELECT CASE q.intCategoryId
          WHEN 1 THEN 'Dental'
          WHEN 2 THEN 'Medical'
          WHEN 3 THEN 'Admin' 
       END AS CategoryID,
       COUNT(IntPermtaskId) AS Total
FROM tblPermTask pt
INNER JOIN tblSRPQuestion q ON q.intQuestionId = pt.intQuestionId
INNER JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn ON mn.strSSN = pt.strSSN
WHERE q.intCategoryID IN (1,2,3)
   AND (strUIC = 'P7TDA' OR strAttchUIC = 'P7TDA')
   AND intSRPAttendId IN (SELECT intSRPAttendId
                          FROM tblSRPAttendance
                          WHERE intEventId = 147 )
GROUP BY ALL q.intCategoryID 
;

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 200 total points
ID: 36601668
you mean this?

SELECT  sum(CASE q.intCategoryId when 1 then 1 else 0 end) as dental
,sum(CASE q.intCategoryId when 2 then 1 else 0 end) as medical
,sum(CASE q.intCategoryId when 3 then 1 else 0 end) as admin
                  
FROM    tblPermTask pt
        LEFT JOIN tblSRPQuestion q ON q.intQuestionId = pt.intQuestionId
        LEFT JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn ON mn.strSSN = pt.strSSN
WHERE   q.intCategoryID IN (1,2,3)
        AND strUIC = 'P7TDA'
        OR strAttchUIC = 'P7TDA'
        AND intSRPAttendId IN ( SELECT  intSRPAttendId
                                FROM    tblSRPAttendance
                                WHERE   intEventId = 147 )
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36601687
would you like to state the intention of the query in english...

why are you left joining the question table...
  you seem to constrain the answer to specific categories ...
  therfor the question table should be an inner join...

which tables do the other columns derive from?

and what do you actually intend with the OR ?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:kdeutsch
ID: 36601705
mwvisa1:,

Ok that worked to get all of them, I put it into my SSRS report but now it only is able to see the first vale everytime, So now I need to figure a way to do a pivot so it can read all the Dental, medical and admin values.
0
 

Author Comment

by:kdeutsch
ID: 36601739
Lowfatspread:

I am trying to get the counts of taks that where created in tblPermtask per the category and where the task belongs to certian UIC's.

The left joins are really usless and I do not know why I originally put them in here, it should just be INNER JOINS.  I redid the query as such and it gets me exacly what I need in the format I need it for the SSRS report it belongs too.

SELECT  sum(CASE q.intCategoryId when 1 then 1 else 0 end) as dental
,sum(CASE q.intCategoryId when 2 then 1 else 0 end) as medical
,sum(CASE q.intCategoryId when 3 then 1 else 0 end) as admin
                 
FROM    tblPermTask pt
        INNER JOIN tblSRPQuestion q ON q.intQuestionId = pt.intQuestionId
        INNER JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn ON mn.strSSN = pt.strSSN
WHERE   q.intCategoryID IN (1,2,3)
        AND (strUIC = 'P7TDA'
        OR strAttchUIC = 'P7TDA')
        AND intSRPAttendId IN ( SELECT  intSRPAttendId
                                FROM    tblSRPAttendance
                                WHERE   intEventId = 147 )
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36601747
If the GROUP BY ALL does not work, then you can try this:

SELECT c.CategoryID, COUNT(IntPermtaskId) AS Total
FROM (
   VALUES(1, 'Dental'),(2, 'Medical'),(3, 'Admin')
) c(Category, CategoryID)
LEFT OUTER JOIN (
   tblPermTask pt
   INNER JOIN tblSRPQuestion q 
      ON q.intQuestionId = pt.intQuestionId
   INNER JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn 
      ON mn.strSSN = pt.strSSN
) ON c.Category = q.intCategoryID 
  AND (strUIC = 'P7TDA' OR strAttchUIC = 'P7TDA')
  AND intSRPAttendId IN (SELECT intSRPAttendId
                          FROM tblSRPAttendance
                          WHERE intEventId = 147 )
GROUP BY c.CategoryID
;

Open in new window

0
 

Author Closing Comment

by:kdeutsch
ID: 36601778
Thanks for the help.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36601780
I see now it did work. If you need to PIVOT, you can do something similar to LFS's suggestion using conditional aggregates - http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_3527-A-SQL-Tidbit-Conditional-Aggregates.html - or you can use the PIVOT keyword.


;WITH cte AS 
(
SELECT CategoryID, IntPermtaskId
FROM (
   VALUES(1, 'Dental'),(2, 'Medical'),(3, 'Admin')
) c(Category, CategoryID)
LEFT OUTER JOIN (
   tblPermTask pt
   INNER JOIN tblSRPQuestion q 
      ON q.intQuestionId = pt.intQuestionId
   INNER JOIN MnNgPersonnel.dbo.tblMNNatPersonnel mn 
      ON mn.strSSN = pt.strSSN
) ON c.Category = q.intCategoryID 
  AND (strUIC = 'P7TDA' OR strAttchUIC = 'P7TDA')
  AND intSRPAttendId IN (SELECT intSRPAttendId
                          FROM tblSRPAttendance
                          WHERE intEventId = 147 )
)
SELECT [Dental], [Medical], [Admin]
FROM cte
PIVOT (COUNT(IntPermtaskId) FOR CategoryID ([Dental], [Medical], [Admin])) pvt
;

Open in new window

0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now