Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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

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
kdeutsch
Asked:
kdeutsch
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
kdeutschAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LowfatspreadCommented:
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
 
LowfatspreadCommented:
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
 
kdeutschAuthor Commented:
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
 
kdeutschAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
kdeutschAuthor Commented:
Thanks for the help.
0
 
Kevin CrossChief Technology OfficerCommented:
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now