Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Month/Year Expand / Collapse
Author
Message
Posted Monday, June 21, 2010 6:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 11:24 PM
Points: 35, Visits: 53
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 2010 481 19
2 2010 146 6
3 2010 173 7
4 2010 37 1
6 2009 4 0
7 2009 2 0
8 2009 12 0
9 2009 423 17
10 2009 158 6
11 2009 131 5
12 2009 707 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
Post #940264
Posted Monday, June 21, 2010 6:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
--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
*/




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #940276
Posted Monday, June 21, 2010 6:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:58 AM
Points: 5,077, Visits: 8,918
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
Post #940277
Posted Monday, June 21, 2010 11:12 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 11:24 PM
Points: 35, Visits: 53
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
Post #940750
Posted Monday, June 21, 2010 11:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 17, 2014 11:24 PM
Points: 35, Visits: 53
Thanks
John for your suggestion . I will do the same as you told.

Subhro
Post #940751
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse