Solved

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

Posted on 2011-09-26
10
270 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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 60

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 60

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

688 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