June 21, 2010 at 6:19 am
Hi ! Folks
Please help me in performing a change in the output of a particular stored procedure which I am supplying with this.
THE SP IS ::---
USE IRISDW_B4_FEEDRUN
IF OBJECT_ID('Proc_PerfectJourneyReport_Business02') IS NOT NULL
DROP PROC Proc_PerfectJourneyReport_Business02;
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Subhro Mukherjee
-- Create date: 21/06/10
-- Description: Proc_PerfectJourneyReport_Business02
-- =============================================
CREATE PROC Proc_PerfectJourneyReport_Business02
AS
SET NOCOUNT ON;
BEGIN
SELECT DATEPART(mm, Begin_Journey_Date) AS Month, DATEPART(yyyy, Begin_Journey_Date) As 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)
ORDER BY Month,Year
END
NOW the O/P comming for the I/P :: EXEC Proc_PerfectJourneyReport_Business02 is
Month Year Count %
1 2010481 19
2 2010146 6
3 2010173 7
4 201037 1
6 20094 0
7 20092 0
8 200912 0
9 2009423 17
10 2009158 6
11 2009131 5
12 2009707 29
Here I want the the output of each touple will come LIKE this
Month/Year Count %
1/10 481 19
2/10 146 6
3/10 173 7
4/10 37 1
6/09 4 0
7/09 2 0
8/09 12 0
9/09 423 17
10/09 158 6
11/09 131 5
12/09 707 29
Please give me some idea to modify the query to get this Output
Thanks
Subhro
June 21, 2010 at 6:34 am
--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
*/
June 21, 2010 at 6:34 am
Subhro
This is something that's best done by the presentation layer of your application, rather than the database layer. However, if you insist on using T-SQL, it's a simple piece of string manipulation. Look up CAST, CONCATENATE and RIGHT in Books Online.
John
June 21, 2010 at 11:12 pm
Hi ! Enthu
Thanks for your quick reply, I am sorry I haven't contact you B'cos it was already 6 pm and I want to leave the office, so I can't reply you. It really helped me.Code working fine.
Thanks once more .
Subhro
June 21, 2010 at 11:14 pm
Thanks
John for your suggestion . I will do the same as you told.
Subhro
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply