--Lets create some test data
DECLARE @table AS TABLE(
Begin_Journey_Date DATETIME,
Container_Key INT IDENTITY)
INSERT INTO @table (Begin_Journey_Date)
SELECT '2010-01-01'
UNION ALL SELECT '2010-05-01'
UNION ALL SELECT '2010-05-09'
UNION ALL SELECT '2010-05-10'
UNION ALL SELECT '2010-01-11'
UNION ALL SELECT '2010-02-15'
UNION ALL SELECT '2010-03-20'
--Now we'll modify the query used in your procedure
SELECT CAST(Datepart(mm, begin_journey_date) AS VARCHAR) + '/' + CAST(
RIGHT(Datepart(yyyy, begin_journey_date), 2) AS VARCHAR) AS [Month/Year],
COUNT(container_key) AS [DetentionCount],
100 * COUNT(container_key) / (SELECT COUNT(container_key)
FROM @table) AS [Rate]
FROM @table
WHERE Datediff(m, begin_journey_date, Getdate()) <= 12
GROUP BY Datepart(yyyy, begin_journey_date),
Datepart(MONTH, begin_journey_date)
--Finally, we add this into your procedure
/*
CREATE PROC Proc_perfectjourneyreport_business02
AS
SET nocount ON;
BEGIN
SELECT CAST(Datepart(mm, begin_journey_date) AS VARCHAR) + '/' + CAST(
RIGHT(Datepart(yyyy, begin_journey_date), 2) AS VARCHAR) AS [Month/Year],
COUNT(container_key) AS [DetentionCount],
100 * COUNT(container_key) / (SELECT COUNT(container_key)
FROM dbo.container) AS [Rate]
FROM dbo.container
WHERE Datediff(m, begin_journey_date, Getdate()) <= 12
GROUP BY Datepart(yyyy, begin_journey_date),
Datepart(MONTH, begin_journey_date)
END
*/