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
Solved

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

Posted on 2011-09-26
10
268 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

860 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