Month/Year

  • 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

  • --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
    My blog - http://www.cadavre.co.uk/
    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/

  • 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

  • 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

  • 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