SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Month/Year


Month/Year

Author
Message
Shintu
Shintu
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 64
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
Cadavre
Cadavre
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8482 Visits: 8490
--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
*/




Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)SSC-Dedicated (32K reputation)

Group: General Forum Members
Points: 32723 Visits: 16637
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
Shintu
Shintu
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 64
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
Shintu
Shintu
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 64
Thanks
John for your suggestion . I will do the same as you told.

Subhro
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search