June 27, 2012 at 2:08 pm
I'm trying to get the below query to print 0 if no results are returned. I need this process because I'm using the query in SSRS and if results aren't returned my tablix disappears. I've tried ISNULL in the SELECT and FROM clause, but it didn't work. I think that's because PIVOT simply counts the values for the IN clause and returns what it finds. (Which in my case means nothing).
I'll be glad to create a table if needed. I thought since I'm dealing with NULLs one might not be needed. Thanks!
DECLARE @ProgramVersionCode varchar(25)
SET @ProgramVersionCode = 'DO-M-2017'
SELECT
[ROMA - Acceptance Fee] AS ROMA_Accept_Fee
,[ROMA - Pre-Registration Fee] AS ROMA_Pre_Reg_Fee
FROM
(
SELECT
ger_Activities.Activity AS [Admissions Fee]
,COUNT(Distinct Stu.Student_ID) AS Count
FROM Enroll
JOIN Stu ON Enroll.Student_ID = Stu.Student_ID
JOIN ger_Activities ON Stu.Student_ID = ger_Activities.Student_ID
WHERE
Enroll.Program_Version_Code_Enrollment = @ProgramVersionCode
AND Enroll.School_Status_Enrollment IN ('Accept', 'Conditional Accept', 'Future Start')
AND ger_Activities.Activity IN('ROMA - Acceptance Fee', 'ROMA - Pre-Registration Fee')
AND ger_Activities.Activity_Status = 'Closed'
GROUP BY ger_Activities.Activity
) pt
PIVOT
(
SUM(pt.Count) FOR pt.[Admissions Fee]
IN ([ROMA - Acceptance Fee], [ROMA - Pre-Registration Fee])
) as pvt
June 27, 2012 at 2:48 pm
...I went ahead and setup an example (see below). Switching the SUM to COUNT in the below query will print a 0. However, from some reason that isn't working on my original query. And after testing it I don't think switching to COUNT is going to be the best route. So any help is appreciated! 🙂
CREATE TABLE TestingPivot
(
PivotIDCHAR(10)NOT NULL,
ActivityCHAR(25)NOT NULL,
);
INSERT INTO TestingPivot
(
PivotID,
Activity
)
Values
('1', 'ROMA - Pre-Mat Fee'),
('2', 'ROMA - Pre-Mat Fee'),
('3', 'ROMA - Pre-Art Fee')
SELECT
[ROMA - Acceptance Fee] AS ROMA_Accept_Fee
,[ROMA - Pre-Registration Fee] AS ROMA_Pre_Reg_Fee
FROM
(
SELECT
Activity AS Activity
,COUNT(DISTINCT PivotID) AS COUNT
FROM TestingPivot
GROUP BY Activity
)pt
PIVOT
(
SUM(pt.Count) FOR pt.[Activity]
IN ([ROMA - Acceptance Fee], [ROMA - Pre-Registration Fee])
) as pvt
June 27, 2012 at 4:21 pm
What is the expected result?
-- Gianluca Sartori
June 27, 2012 at 4:36 pm
I would like to return the value in the PIVOT. If no rows are returned I would like to print 0. If I need to give more details please let me know. Thanks very much.
June 28, 2012 at 4:05 am
Your current query, based on the sample data you posted, returns these results:
ROMA_Accept_Fee ROMA_Pre_Reg_Fee
--------------- ----------------
NULL NULL
What would you like it to be?
ROMA_Accept_Fee ROMA_Pre_Reg_Fee
--------------- ----------------
0 0
The one above?
-- Gianluca Sartori
June 28, 2012 at 7:12 am
Yes, I would like the one above. (The one which returned 0's). Thanks so much for the help.
June 28, 2012 at 7:21 am
Also - and this is a side question - sometimes SSMS will print 1 row with NULLs when no results are returned. Other times it prints 0 rows. Does anyone know what makes the difference? (Or whether it matters!)
June 28, 2012 at 7:35 am
Hi,
Try this or use a dynamic pivot solution that is more flexible
;
WITH c AS ( SELECT [ROMA - Acceptance Fee] AS ROMA_Accept_Fee ,
[ROMA - Pre-Registration Fee] AS ROMA_Pre_Reg_Fee
FROM ( SELECT Activity AS Activity ,
COUNT(DISTINCT PivotID) AS COUNT
FROM TestingPivot
GROUP BY Activity
) pt PIVOT
( SUM(pt.Count) FOR pt.[Activity] IN ( [ROMA - Acceptance Fee],
[ROMA - Pre-Registration Fee] ) ) as pvt
)
SELECT CASE WHEN [ROMA_Accept_Fee] IS NULL THEN 0
ELSE [ROMA_Accept_Fee]
END AS ROMA_Accept_Fee ,
CASE WHEN [ROMA_Pre_Reg_Fee] IS NULL THEN 0
ELSE [ROMA_Pre_Reg_Fee]
END AS ROMA_Pre_Reg_Fee
FROM c
June 28, 2012 at 9:13 am
I think that will work. The only variable between the table/query I supplied and the other query I'm working with is the data. For some reason, the cte with a case check doesn't work on my other query. So I'm thinking it must be something in our data. The only difference I've noticed between the query I created for this post and the other query is this - The query I posted above would return one row with "NULL" under the two column headers. The other query doesn't return any rows.
I apologize this is so vague, but does anyone have any thoughts?
June 28, 2012 at 3:52 pm
...I just wanted to bump my last question one time to see if anyone had suggestions. Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply