kdeutsch
asked on
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ?
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 ?
ASKER
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.
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.
ASKER
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.tblMNNat Personnel 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 )
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.tblMNNat
WHERE q.intCategoryID IN (1,2,3)
AND (strUIC = 'P7TDA'
OR strAttchUIC = 'P7TDA')
AND intSRPAttendId IN ( SELECT intSRPAttendId
FROM tblSRPAttendance
WHERE intEventId = 147 )
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
;
ASKER
Thanks for the help.
I see now it did work. If you need to PIVOT, you can do something similar to LFS's suggestion using conditional aggregates - https://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
;
ASKER
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.tblMNNat
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