April 3, 2012 at 2:02 pm
I have a UNION ALL query that brings together 18 statements. It mirrors a dashboard I built. However, after 13 results (which are 13 COUNT queries) Management Studio splits the results by putting and header and restarting the row count. So the last 5 rows (which again, are 5 COUNT queries) are split from the first 13. Any thoughts?
April 3, 2012 at 2:05 pm
Please post the query. See how to do this appropriately in my signature; the article by Jeff Moden.
Jared
CE - Microsoft
April 3, 2012 at 2:13 pm
Thanks, Jared. Here she is...
/*Primary Applications Received*/
SELECT 'Primary Applications Received' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Individual_Status_Changes ON Enrollment.Enrollment_ID = Individual_Status_Changes.Enrollment_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Individual_Status_Changes.Individual_New_Entity_Status = 'Primary Application Received'
UNION ALL
/*Low GPA*/
SELECT 'Low GPA' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Entity_Status_Enrollment = 'Low GPA'
UNION ALL
/*Secondary Applications Sent*/
SELECT 'Secondary Applications Sent' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Contact_Manager_Activities ON Enrollment.Individual_ID = Contact_Manager_Activities.Individual_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Activity = 'KCOM - Secondary Application Invitation'
AND Activity_Status = 'Closed'
AND Due_Date_CM_Activity >= '2011-06-01'
UNION ALL
/*Secondary Applications Received*/
SELECT 'Secondary Applications Received' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Individual_Status_Changes ON Enrollment.Enrollment_ID = Individual_Status_Changes.Enrollment_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Individual_Status_Changes.Individual_New_Entity_Status = 'Application Received'
UNION ALL
/*Applications Completed*/
SELECT 'Applications Complete' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Individual_Status_Changes ON Enrollment.Enrollment_ID = Individual_Status_Changes.Enrollment_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Individual_Status_Changes.Individual_New_Entity_Status = 'Application Completed'
UNION ALL
/*Prescreen*/
SELECT 'Prescreen Reject' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - Prescreen'
UNION ALL
/*Cancelled Files*/
SELECT 'Cancelled Files' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CB-Cancel Before Accept'
UNION ALL
/*Cancelled Interviews*/
SELECT 'Cancelled Interviews' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CI-Cancel Interview'
UNION ALL
/*Applicants Interviewed*/
SELECT 'Applicants Interviewed' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Contact_Manager_Activities ON Enrollment.Individual_ID = Contact_Manager_Activities.Individual_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Activity = 'KCOM - Interview One'
AND Activity_Status = 'Closed'
AND Due_Date_CM_Activity BETWEEN '2011-09-01' AND '2012-06-01'
UNION ALL
/*Applicants Rejected by Admissions Committee*/
SELECT 'Applicants Rejected by Admissions Committee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - Adm Committee'
UNION ALL
/*Applicants Accepted by Admissions Committee*/
SELECT 'Applicants Accepted by Admissions Committee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Individual_Status_Changes ON Enrollment.Enrollment_ID = Individual_Status_Changes.Enrollment_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Individual_Status_Changes.Individual_New_Entity_Status IN ('Accept', 'Conditional Accept')
UNION ALL
/*Cancel Before Paying Acceptance Fee*/
SELECT 'Cancel Before Paying Acceptance Fee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment IN ('AM - CA-Cancel After Accept', 'AM - CN-Cancel Not Heard')
UNION ALL
/*Cancel After Paying Acceptance Fee*/
SELECT 'Cancel After Paying Acceptance Fee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CP-Cancel After Acc Fee Pd'
/*Cancel After Paying Pre-Registration Fee*/
SELECT 'Cancel After Paying Pre-Registration Fee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CR-Cancel After Reg Fe Pd'
UNION ALL
/*Cancel After Paying Pre-Matriculation Fee*/
SELECT 'Cancel After Paying Pre-Matriculation Fee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CM-Cancel After Mat Fe Pd'
UNION ALL
/*Deferred Applicants*/
SELECT 'Deferred Applicants' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Entity_Status_Enrollment = 'Accept Deferred'
UNION ALL
/*Alternates*/
SELECT 'Alternates' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Entity_Status_Enrollment = 'Alternate'
UNION ALL
/*Current Entering Class*/
SELECT 'Alternates' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Entity_Status_Enrollment IN ('Accept', 'Conditional Accept')
April 3, 2012 at 2:16 pm
Ok... Add a UNION ALL between the 13th and 14th SELECT statements 😉
Jared
CE - Microsoft
April 3, 2012 at 2:26 pm
Thanks for pointing out the obvious 🙂 I was looking for something technical, and should have checked the syntax again!
April 3, 2012 at 2:29 pm
Won't be the last time it happens to you or me 😀 Sometimes second quick pair of eyes (or brain) gets the job done!
Jared
CE - Microsoft
April 10, 2012 at 12:38 pm
One more question on this particular query. Notice I have to put "WHERE holder_Version_Code_Enrollment = 'AR-46'" in every SELECT statement. Is there any way to do this once for the whole query? (Using a subquery, cte, or something along these lines?) I've tried some stuff, but nothing has worked to date. Thanks!
/*Primary Applications Received*/
SELECT 'Primary Applications Received' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Individual_Status_Changes ON Enrollment.Enrollment_ID = Individual_Status_Changes.Enrollment_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Individual_Status_Changes.Individual_New_Entity_Status = 'Primary Application Received'
UNION ALL
/*Low GPA*/
SELECT 'Low GPA' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Entity_Status_Enrollment = 'Low GPA'
UNION ALL
/*Secondary Applications Sent*/
SELECT 'Secondary Applications Sent' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Contact_Manager_Activities ON Enrollment.Individual_ID = Contact_Manager_Activities.Individual_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Activity = 'KCOM - Secondary Application Invitation'
AND Activity_Status = 'Closed'
AND Due_Date_CM_Activity >= '2011-06-01'
UNION ALL
/*Secondary Applications Received*/
SELECT 'Secondary Applications Received' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Individual_Status_Changes ON Enrollment.Enrollment_ID = Individual_Status_Changes.Enrollment_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Individual_Status_Changes.Individual_New_Entity_Status = 'Application Received'
UNION ALL
/*Applications Completed*/
SELECT 'Applications Complete' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Individual_Status_Changes ON Enrollment.Enrollment_ID = Individual_Status_Changes.Enrollment_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Individual_Status_Changes.Individual_New_Entity_Status = 'Application Completed'
UNION ALL
/*Prescreen*/
SELECT 'Prescreen Reject' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - Prescreen'
UNION ALL
/*Cancelled Files*/
SELECT 'Cancelled Files' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CB-Cancel Before Accept'
UNION ALL
/*Cancelled Interviews*/
SELECT 'Cancelled Interviews' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CI-Cancel Interview'
UNION ALL
/*Applicants Interviewed*/
SELECT 'Applicants Interviewed' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Contact_Manager_Activities ON Enrollment.Individual_ID = Contact_Manager_Activities.Individual_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Activity = 'KCOM - Interview One'
AND Activity_Status = 'Closed'
AND Due_Date_CM_Activity BETWEEN '2011-09-01' AND '2012-06-01'
UNION ALL
/*Applicants Rejected by Admissions Committee*/
SELECT 'Applicants Rejected by Admissions Committee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - Adm Committee'
UNION ALL
/*Applicants Accepted by Admissions Committee*/
SELECT 'Applicants Accepted by Admissions Committee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment JOIN Individual_Status_Changes ON Enrollment.Enrollment_ID = Individual_Status_Changes.Enrollment_ID
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Individual_Status_Changes.Individual_New_Entity_Status IN ('Accept', 'Conditional Accept')
UNION ALL
/*Cancel Before Paying Acceptance Fee*/
SELECT 'Cancel Before Paying Acceptance Fee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment IN ('AM - CA-Cancel After Accept', 'AM - CN-Cancel Not Heard')
UNION ALL
/*Cancel After Paying Acceptance Fee*/
SELECT 'Cancel After Paying Acceptance Fee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CP-Cancel After Acc Fee Pd'
/*Cancel After Paying Pre-Registration Fee*/
SELECT 'Cancel After Paying Pre-Registration Fee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CR-Cancel After Reg Fe Pd'
UNION ALL
/*Cancel After Paying Pre-Matriculation Fee*/
SELECT 'Cancel After Paying Pre-Matriculation Fee' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Enrollment.Reason_Enrollment = 'AM - CM-Cancel After Mat Fe Pd'
UNION ALL
/*Deferred Applicants*/
SELECT 'Deferred Applicants' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Entity_Status_Enrollment = 'Accept Deferred'
UNION ALL
/*Alternates*/
SELECT 'Alternates' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Entity_Status_Enrollment = 'Alternate'
UNION ALL
/*Current Entering Class*/
SELECT 'Alternates' AS 'Current Application Cycle', COUNT(DISTINCT Enrollment.Individual_ID) AS Count
FROM Enrollment
WHERE holder_Version_Code_Enrollment = 'AR-46'
AND Entity_Status_Enrollment IN ('Accept', 'Conditional Accept')
April 10, 2012 at 12:47 pm
You could use a parameter:
DECLARE @parameterName varchar(25)
SET @parameterName = 'AR-46'
Then replace all your where's to WHERE holder_Version_Code_Enrollment = @parameterName
Jared
CE - Microsoft
April 10, 2012 at 1:08 pm
Thanks Jared. That did just what I needed.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply