Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-26
10
Medium Priority
?
273 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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 1000 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 200 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 800 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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

597 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