May 2, 2008 at 2:26 am
Hello everyone, (hope I'm posting this in the right place, apologies if I'm not!)
I'm still very much a novice when it comes to using SQL, and I hope someone can point out my error in the following bit of code I'm trying to use in a stored procedure?
I'm returning 20 values for use in a Crystal report; I've changed the structure of the stored procedure to handle slightly different filtering requirements, and since then every subquery used to set the 20 values returns the error "Subquery returned more than 1 value.. etc". Every value I set follows this format:
SET @Completed_Null_Urgency_INT = (SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating IS NULL)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT))
IF(@Completed_Null_Urgency_INT IS NULL)
BEGIN
SET @Completed_Null_Urgency_INT = 0
END
If I take the subquery out and test it as a query by itself, it returns the expected value; yet when ran as part of this stored procedure, all 20 subquerys error. So I must admit I am slightly confused - if anyone could please point out my error, I would be very grateful. 🙂
Regards,
#
May 2, 2008 at 3:23 am
Now that I think about it, it could be refering to the View that the query is based on? This is said View, in case I've made any errors there?
SELECT Order_Code, Order_No, CONVERT(DATETIME, Order_Date, 102) AS Order_Date, Order_Time, Project_Reference,
Cost_Head_Item_No, Quantity, Rate, Estimated_Cost, Actual_Cost, VAT_Code, VAT, Raised_by, Contractor_Code, Job_Description, Completed,
CONVERT(DATETIME, Completed_Date, 102) AS Completed_Date_DT, Completed_Satisfactorily, Workmen_Tidy_and_Courteous, Comment,
Confirmation_order, Date_Fully_Paid, Authorised, Awaiting_Authorisation, Authorised_By, Status, Order_Type, Health_and_Safety, CONVERT(datetime,
Due_Date, 102) AS Due_Date, Date_Authorised, Include_In_Commitments, SOR_Code, Completion_Comments, Time_Completed, Date_Signed_Off,
Requires_Check, Created_Time, Non_SOR_Order, Recharge, Call_Reference, Discipline_Code, CS_Survey_Completed, CS_Work_Complete,
CS_Standard_of_Work, Urgency_Rating, Appointment_Date, Appointment_Time, Late_Appointment_Reason, Sub_Work_Type_Code, Section_Code,
Master_SOR_code
FROM dbo.Orders
WHERE (Completed_Date IS NULL) OR
(LEFT(Completed_Date, 1) = '2')
ORDER BY CONVERT(datetime, Due_Date, 102)
Btw, this database isn't my doing, I just get the glorious task of writing reports. 😉
May 2, 2008 at 4:05 am
This all looks fine to me.
Are there any lines like the following which don't contain an aggregation (like COUNT)?
SET @Completed_Null_Urgency_INT = (SELECT COUNT(Order_Code) AS Count_INT
The error you're getting is usually because a select query like this returns more than one value, which aggregations can't do, of course.
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 2, 2008 at 9:48 am
I don't see an error in either of these. Can you post the whole proc that's giving you the error?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 2, 2008 at 10:01 am
Thanks for checking that little snippet - Ok, here we go, the whole thing, wonder if theres a character post limit? 😉
ALTER PROCEDURE [dbo].[usp_Calls_Listed_By_Urgency_Summary]
-- Add the parameters for the stored procedure here
@Start_Date_DT DATETIME,
@End_Date_DT DATETIME
AS
BEGIN
DECLARE @Total_Completed_Of_Emergency_Calls_INT INT, @Total_Completed_Within_Target_Of_Emergency_Calls_INT INT, @Total_Not_Completed_Of_Emergency_Calls_INT INT, @Total_Cancelled_Of_Emergency_Calls_INT INT
DECLARE @Total_Completed_Of_24_Hour_Calls_INT INT, @Total_Completed_Within_Target_Of_24_Hour_Calls_INT INT, @Total_Not_Completed_Of_24_Hour_Calls_INT INT, @Total_Cancelled_Of_24_Hour_Calls_INT INT
DECLARE @Total_Completed_Of_5_Day_Calls_INT INT, @Total_Completed_Within_Target_Of_5_Day_Calls_INT INT, @Total_Not_Completed_Of_5_Day_Calls_INT INT, @Total_Cancelled_Of_5_Day_Calls_INT INT
DECLARE @Total_Completed_Of_20_Day_Calls_INT INT, @Total_Completed_Within_Target_Of_20_Day_Calls_INT INT, @Total_Not_Completed_Of_20_Day_Calls_INT INT, @Total_Cancelled_Of_20_Day_Calls_INT INT
DECLARE @Completed_Null_Urgency_INT INT, @Total_Completed_Within_Target_Null_Urgency_Calls_INT INT, @Not_Completed_Null_Urgency_INT INT, @Cancelled_Null_Urgency_INT INT
---------------------------UNKNOWN CALLS
SET @Completed_Null_Urgency_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating IS NULL)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Completed_Null_Urgency_INT IS NULL)
BEGIN
SET @Completed_Null_Urgency_INT = 0
END
SET @Total_Completed_Within_Target_Null_Urgency_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating IS NULL)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
AND
(Completed_Date_DT <= Due_Date)
)
IF(@Total_Completed_Within_Target_Null_Urgency_Calls_INT IS NULL)
BEGIN
SET @Total_Completed_Within_Target_Null_Urgency_Calls_INT = 0
END
SET @Not_Completed_Null_Urgency_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'AA' OR
Status = 'NI' OR
Status = 'INC' OR
Status = 'NPP' OR
Status = 'INP')
AND
(Urgency_Rating IS NULL)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Not_Completed_Null_Urgency_INT IS NULL)
BEGIN
SET @Not_Completed_Null_Urgency_INT = 0
END
SET @Cancelled_Null_Urgency_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'C')
AND
(Urgency_Rating IS NULL)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Cancelled_Null_Urgency_INT IS NULL)
BEGIN
SET @Cancelled_Null_Urgency_INT = 0
END
-------------------------------------END OF UNKNOWN CALLS
---------------------------EMERGENCY CALLS
SET @Total_Completed_Of_Emergency_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating = 1)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Completed_Of_Emergency_Calls_INT IS NULL)
BEGIN
SET @Total_Completed_Of_Emergency_Calls_INT = 0
END
SET @Total_Completed_Within_Target_Of_Emergency_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating = 1)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
AND
(Completed_Date_DT <= Due_Date)
)
IF(@Total_Completed_Within_Target_Of_Emergency_Calls_INT IS NULL)
BEGIN
SET @Total_Completed_Within_Target_Of_Emergency_Calls_INT = 0
END
SET @Total_Not_Completed_Of_Emergency_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'AA' OR
Status = 'NI' OR
Status = 'INC' OR
Status = 'NPP' OR
Status = 'INP')
AND
(Urgency_Rating = 1)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Not_Completed_Of_Emergency_Calls_INT IS NULL)
BEGIN
SET @Total_Not_Completed_Of_Emergency_Calls_INT = 0
END
SET @Total_Cancelled_Of_Emergency_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'C')
AND
(Urgency_Rating = 1)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Cancelled_Of_Emergency_Calls_INT IS NULL)
BEGIN
SET @Total_Cancelled_Of_Emergency_Calls_INT = 0
END
-------------------------------------END OF EMERGENCY CALLS
-------------------------------------24 HOUR CALLS
SET @Total_Completed_Of_24_Hour_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating = 2)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Completed_Of_24_Hour_Calls_INT IS NULL)
BEGIN
SET @Total_Completed_Of_24_Hour_Calls_INT = 0
END
SET @Total_Completed_Within_Target_Of_24_Hour_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating = 2)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
AND
(Completed_Date_DT <= Due_Date)
)
IF(@Total_Completed_Within_Target_Of_24_Hour_Calls_INT IS NULL)
BEGIN
SET @Total_Completed_Within_Target_Of_24_Hour_Calls_INT = 0
END
SET @Total_Not_Completed_Of_24_Hour_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'AA' OR
Status = 'NI' OR
Status = 'INC' OR
Status = 'NPP' OR
Status = 'INP')
AND
(Urgency_Rating = 2)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Not_Completed_Of_24_Hour_Calls_INT IS NULL)
BEGIN
SET @Total_Not_Completed_Of_24_Hour_Calls_INT = 0
END
SET @Total_Cancelled_Of_24_Hour_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'C')
AND
(Urgency_Rating = 2)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Cancelled_Of_24_Hour_Calls_INT IS NULL)
BEGIN
SET @Total_Cancelled_Of_24_Hour_Calls_INT = 0
END
-------------------------------------------END OF 24 HOUR CALLS
-------------------------------------------5 WORKING DAY CALLS
SET @Total_Completed_Of_5_Day_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating = 3)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Completed_Of_5_Day_Calls_INT IS NULL)
BEGIN
SET @Total_Completed_Of_5_Day_Calls_INT = 0
END
SET @Total_Completed_Within_Target_Of_5_Day_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating = 3)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
AND
(Completed_Date_DT <= Due_Date)
)
IF(@Total_Completed_Within_Target_Of_5_Day_Calls_INT IS NULL)
BEGIN
SET @Total_Completed_Within_Target_Of_5_Day_Calls_INT = 0
END
SET @Total_Not_Completed_Of_5_Day_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'AA' OR
Status = 'NI' OR
Status = 'INC' OR
Status = 'NPP' OR
Status = 'INP')
AND
(Urgency_Rating = 3)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Not_Completed_Of_5_Day_Calls_INT IS NULL)
BEGIN
SET @Total_Not_Completed_Of_5_Day_Calls_INT = 0
END
SET @Total_Cancelled_Of_5_Day_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'C')
AND
(Urgency_Rating = 3)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Cancelled_Of_5_Day_Calls_INT IS NULL)
BEGIN
SET @Total_Cancelled_Of_5_Day_Calls_INT = 0
END
---------------------------------------------------END OF 5 WORKING DAY CALLS
---------------------------------------------------20 WORKING DAY CALLS
SET @Total_Completed_Of_20_Day_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating = 4)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Completed_Of_20_Day_Calls_INT IS NULL)
BEGIN
SET @Total_Completed_Of_20_Day_Calls_INT = 0
END
SET @Total_Completed_Within_Target_Of_20_Day_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'CAM' OR
Status = 'CMC' OR
Status = 'CMF' OR
Status = 'CMN' OR
Status = 'CPA' OR
Status = 'CPC' OR
Status = 'CPF' OR
Status = 'CPN' OR
Status = 'SO')
AND
(Urgency_Rating = 4)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
AND
(Completed_Date_DT <= Due_Date)
)
IF(@Total_Completed_Within_Target_Of_20_Day_Calls_INT IS NULL)
BEGIN
SET @Total_Completed_Within_Target_Of_20_Day_Calls_INT = 0
END
SET @Total_Not_Completed_Of_20_Day_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'AA' OR
Status = 'NI' OR
Status = 'INC' OR
Status = 'NPP' OR
Status = 'INP')
AND
(Urgency_Rating = 4)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Not_Completed_Of_20_Day_Calls_INT IS NULL)
BEGIN
SET @Total_Not_Completed_Of_20_Day_Calls_INT = 0
END
SET @Total_Cancelled_Of_20_Day_Calls_INT = (
SELECT COUNT(Order_Code) AS Count_INT
FROM Working_Orders_V
WHERE
(Status = 'C')
AND
(Urgency_Rating = 4)
AND
(Contractor_Code <> 'DUMMY')
AND
(Order_Date <= @End_Date_DT)
AND
(Order_Date >= @Start_Date_DT)
)
IF(@Total_Cancelled_Of_20_Day_Calls_INT IS NULL)
BEGIN
SET @Total_Cancelled_Of_20_Day_Calls_INT = 0
END
DECLARE @Emergency_Percentage_Complete_FL FLOAT, @24_Hours_Percentage_Complete_FL FLOAT, @5_Days_Percentage_Complete_FL FLOAT
DECLARE @20_Days_Percentage_Complete_FL FLOAT, @Null_Urgency_Percentage_Complete_FL FLOAT
IF(@Total_Completed_Of_Emergency_Calls_INT > 0 AND @Total_Completed_Within_Target_Of_Emergency_Calls_INT > 0)
SET @Emergency_Percentage_Complete_FL = (CAST(@Total_Completed_Within_Target_Of_Emergency_Calls_INT AS FLOAT) / CAST(@Total_Completed_Of_Emergency_Calls_INT AS FLOAT)) * 100
ELSE
SET @Emergency_Percentage_Complete_FL = 0
IF(@Total_Completed_Of_24_Hour_Calls_INT > 0 AND @Total_Completed_Within_Target_Of_24_Hour_Calls_INT > 0)
SET @24_Hours_Percentage_Complete_FL = (CAST(@Total_Completed_Within_Target_Of_24_Hour_Calls_INT AS FLOAT) / CAST(@Total_Completed_Of_24_Hour_Calls_INT AS FLOAT)) * 100
ELSE
SET @24_Hours_Percentage_Complete_FL = 0
IF(@Total_Completed_Of_5_Day_Calls_INT > 0 AND @Total_Completed_Within_Target_Of_5_Day_Calls_INT > 0)
SET @5_Days_Percentage_Complete_FL = (CAST(@Total_Completed_Within_Target_Of_5_Day_Calls_INT AS FLOAT) / CAST(@Total_Completed_Of_5_Day_Calls_INT AS FLOAT)) * 100
ELSE
SET @5_Days_Percentage_Complete_FL = 0
IF(@Total_Completed_Of_20_Day_Calls_INT > 0 AND @Total_Completed_Within_Target_Of_20_Day_Calls_INT > 0)
SET @20_Days_Percentage_Complete_FL = (CAST(@Total_Completed_Within_Target_Of_20_Day_Calls_INT AS FLOAT) / CAST(@Total_Completed_Of_20_Day_Calls_INT AS FLOAT)) * 100
ELSE
SET @20_Days_Percentage_Complete_FL = 0
IF(@Completed_Null_Urgency_INT > 0 AND @Total_Completed_Within_Target_Null_Urgency_Calls_INT > 0)
SET @Null_Urgency_Percentage_Complete_FL = (CAST(@Total_Completed_Within_Target_Null_Urgency_Calls_INT AS FLOAT) / CAST(@Completed_Null_Urgency_INT AS FLOAT)) * 100
ELSE
SET @Null_Urgency_Percentage_Complete_FL = 0
---------------------------------------------------END OF 20 WORKING DAY CALLS
SELECT @Total_Completed_Of_Emergency_Calls_INT AS Total_Completed_Of_Emergency_Calls_INT,
@Total_Completed_Within_Target_Of_Emergency_Calls_INT AS Total_Completed_Within_Target_Of_Emergency_Calls_INT,
@Emergency_Percentage_Complete_FL AS Percentage_Emergency_Complete_FL,
@Total_Not_Completed_Of_Emergency_Calls_INT AS Total_Not_Completed_Of_Emergency_Calls_INT,
@Total_Cancelled_Of_Emergency_Calls_INT AS Total_Cancelled_Of_Emergency_Calls_INT,
@Total_Completed_Of_24_Hour_Calls_INT AS Total_Completed_Of_24_Hour_Calls_INT,
@Total_Completed_Within_Target_Of_24_Hour_Calls_INT AS Total_Completed_Within_Target_Of_24_Hour_Calls_INT,
@24_Hours_Percentage_Complete_FL AS Percentage_24_Hours_Complete_FL,
@Total_Not_Completed_Of_24_Hour_Calls_INT AS Total_Not_Completed_Of_24_Hour_Calls_INT,
@Total_Cancelled_Of_24_Hour_Calls_INT AS Total_Cancelled_Of_24_Hour_Calls_INT,
@Total_Completed_Of_5_Day_Calls_INT AS Total_Completed_Of_5_Day_Calls_INT,
@Total_Completed_Within_Target_Of_5_Day_Calls_INT AS Total_Completed_Within_Target_Of_5_Day_Calls_INT,
@5_Days_Percentage_Complete_FL AS Percentage_5_Days_Complete_FL,
@Total_Not_Completed_Of_5_Day_Calls_INT AS Total_Not_Completed_Of_5_Day_Calls_INT,
@Total_Cancelled_Of_5_Day_Calls_INT AS Total_Cancelled_Of_5_Day_Calls_INT,
@Total_Completed_Of_20_Day_Calls_INT AS Total_Completed_Of_20_Day_Calls_INT,
@Total_Completed_Within_Target_Of_20_Day_Calls_INT AS Total_Completed_Within_Target_Of_20_Day_Calls_INT,
@20_Days_Percentage_Complete_FL AS Percentage_20_Days_Complete_FL,
@Total_Not_Completed_Of_20_Day_Calls_INT AS Total_Not_Completed_Of_20_Day_Calls_INT,
@Total_Cancelled_Of_20_Day_Calls_INT AS Total_Cancelled_Of_20_Day_Calls_INT,
@Completed_Null_Urgency_INT AS Completed_Null_Urgency_INT,
@Total_Completed_Within_Target_Null_Urgency_Calls_INT AS Total_Completed_Within_Target_Null_Urgency_Calls_INT,
@Null_Urgency_Percentage_Complete_FL AS Percentage_Null_Urgency_Complete_FL,
@Not_Completed_Null_Urgency_INT AS Not_Complete_Null_Urgency_INT,
@Cancelled_Null_Urgency_INT AS Cancelled_Null_Urgency_INT
END
The formatting looks a little better in Management studio, honest. 😉
May 2, 2008 at 10:04 am
I'll chime in as well that I do not see any issues with the T-SQL code, it should only return 1 row.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 5, 2008 at 7:22 am
It looks okay to me too. I'd try running each sub-query separately. See if they all work on their own.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 4:32 am
Hi Adam
Currently you're doing 20 selects into the same table to get your values. You could consolidate this into a single select and make the first part of your code quite a lot more readable by adopting the following syntax:
SELECT
---------------------------UNKNOWN CALLS
Completed_Null_Urgency_INT = SUM(CASE WHEN Urgency_Rating IS NULL
AND Status IN ('CAM','CMC','CMF','CMN','CPA','CPC','CPF','CPN','SO') THEN [Count_INT] ELSE 0 END),
Total_Completed_Within_Target_Null_Urgency_Calls_INT = SUM(CASE WHEN Urgency_Rating IS NULL
AND [Early] = 1
AND Status IN ('CAM','CMC','CMF','CMN','CPA','CPC','CPF','CPN','SO') THEN [Count_INT] ELSE 0 END),
Not_Completed_Null_Urgency_INT = SUM(CASE WHEN Urgency_Rating IS NULL
AND Status IN ('AA', 'NI', 'INC', 'NPP', 'INP') THEN [Count_INT] ELSE 0 END),
Cancelled_Null_Urgency_INT = SUM(CASE WHEN Urgency_Rating IS NULL
AND Status = 'C' THEN [Count_INT] ELSE 0 END),
---------------------------EMERGENCY CALLS
Total_Completed_Of_Emergency_Calls_INT = SUM(CASE WHEN Urgency_Rating = 1
AND Status IN ('CAM','CMC','CMF','CMN','CPA','CPC','CPF','CPN','SO') THEN [Count_INT] ELSE 0 END) --, <-- COMMA COMMENTED OUT
FROM (
SELECT [Count_INT] = COUNT(Order_Code),
[Status],
[Urgency_Rating],
[Early] = CASE WHEN Completed_Date_DT <= Due_Date THEN 1 ELSE 0 END
FROM Working_Orders_V
WHERE Contractor_Code <> 'DUMMY'
AND Order_Date <= @End_Date_DT
AND Order_Date >= @Start_Date_DT
GROUP BY [Status], [Urgency_Rating], CASE WHEN Completed_Date_DT <= Due_Date THEN 1 ELSE 0 END
) d
This is likely to be much faster than the original.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 6, 2008 at 7:08 am
Thanks for that suggested alternative, worked a treat - I'll rewrite the whole proc in that format as I totally agree that it is a lot more readable. Bit of a mystery why the original code didn't work - while the subqueries worked ok, trying to add the SET element & variable before doing the subquery always seems to cause that error.
May 6, 2008 at 7:38 am
Adam Watson (5/6/2008)
Thanks for that suggested alternative, worked a treat - I'll rewrite the whole proc in that format as I totally agree that it is a lot more readable. Bit of a mystery why the original code didn't work - while the subqueries worked ok, trying to add the SET element & variable before doing the subquery always seems to cause that error.
Like everybody else I couldn't figure out where the error was coming from. If you're happy with the format so far, why not go one stage further? You're down to one row of a few columns - put it into a temporary table and select your final results from there:
.
.
.
Total_Completed_Of_Emergency_Calls_INT = SUM(CASE WHEN Urgency_Rating = 1
AND Status IN ('CAM','CMC','CMF','CMN','CPA','CPC','CPF','CPN','SO') THEN [Count_INT] ELSE 0 END) --, <-- COMMA COMMENTED OUT
INTO #IntermediateResults
FROM (
.
.
.
followed by:
SELECT
Total_Completed_Of_Emergency_Calls_INT,
Total_Completed_Within_Target_Of_Emergency_Calls_INT,
Emergency_Percentage_Complete_FL = CASE
WHEN Total_Completed_Of_Emergency_Calls_INT > 0 AND Total_Completed_Within_Target_Of_Emergency_Calls_INT > 0
THEN (CAST(Total_Completed_Within_Target_Of_Emergency_Calls_INT AS FLOAT) / CAST(Total_Completed_Of_Emergency_Calls_INT AS FLOAT)) * 100
ELSE 0 END
--, <-- COMMA COMMENTED OUT
FROM (
SELECT
---------------------------UNKNOWN CALLS
ISNULL(Completed_Null_Urgency_INT, 0) AS Completed_Null_Urgency_INT,
ISNULL(Total_Completed_Within_Target_Null_Urgency_Calls_INT, 0) AS Total_Completed_Within_Target_Null_Urgency_Calls_INT,
ISNULL(Not_Completed_Null_Urgency_INT, 0) AS Not_Completed_Null_Urgency_INT,
ISNULL(Cancelled_Null_Urgency_INT, 0) AS Cancelled_Null_Urgency_INT,
---------------------------EMERGENCY CALLS
ISNULL(Total_Completed_Of_Emergency_Calls_INT, 0) AS Total_Completed_Of_Emergency_Calls_INT
--, <-- COMMA COMMENTED OUT
FROM #IntermediateResults ) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 6, 2008 at 1:03 pm
Have you tried adding SET NOCOUNT ON at the start of the procedure ?
Some client apps like Crystal get confused by the SQL status messages returning the number of rows affected by each statement.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply