Results 1 to 7 of 7
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: Pivot table problem

    I've have this following table,

    Date_Taken | Main_ID | Daily_Rainfall
    -------------------------------------------
    5/1/2005 | 194 | 142
    6/1/2005 | 194 | 155
    7/1/2005 | 194 | 132
    8/1/2005 | 194 | 199
    5/1/2005 | 196 | 333
    6/1/2005 | 196 | 544
    7/1/2005 | 196 | 722
    8/1/2005 | 196 | 845
    ...
    ...
    *Combination of Date_Taken and Main_ID generate unique row
    *Above data consist of 1 May 2005 to 1 AUG 2005 data (4 month data for each Main_ID), which is Date_Taken start from 1st day every month.
    *Date_Taken | Main_ID | Daily_Rainfall
    -------------------------------------------
    5/1/2005 | 194 | 142
    ---> Mean, In May 2005 Main_ID=194 having 142 Daily_Rainfall
    6/1/2005 | 196 | 544
    ---> Mean, In Jun 2005 Main_ID=196 having 544 Daily_Rainfall

    Let's say, current month is 8
    I stuck to do the query to display result like as follow

    Date_Taken | Main_ID | CurrentMonth_DR | LastMonth_DR | Last2Month_DR| Last3Month_DR
    --------------------------------------------------------------------------------------
    8/1/2005 | 194 | 199 | 132 | 155 | 142
    8/1/2005 | 196 | 845 | 722 | 544 | 333
    ...
    ...
    *CurrentMonth_DR is 8/1/2005, LastMonth_DR is 7/1/2005, Last2Month_DR is 6/1/2005 and so on

    Plz help me

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Note that in this case if you use the 2005 syntax you'll need to use dynamic sql too. I would go with the 2000 syntax whatever your engine since you can do that without dynamic sql.

  4. #4
    Join Date
    May 2008
    Posts
    3
    i'm using sql server 2005. with Thrasymachus and pootle flump info, i'm trying to apply this info to my query.

    till now, i'm still struggle to solve my problem.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can still use the 2000 syntax.

    Post what you have come up with so far.

  6. #6
    Join Date
    Apr 2007
    Posts
    183

    Cross forum post

    Poster already has had a long discussion about this problem and similar problems here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104572

    Code:
    -- Prepare sample data
    DECLARE	@Sample TABLE (Date_Taken DATETIME, Main_ID INT, Daily_Rainfall SMALLMONEY)
    
    SET DATEFORMAT MDY
    
    INSERT	@Sample
    SELECT	'5/1/2005', 194, 142 UNION ALL
    SELECT	'6/1/2005', 194, 155 UNION ALL
    SELECT	'7/1/2005', 194, 132 UNION ALL
    SELECT	'8/1/2005', 194, 199 UNION ALL
    SELECT	'5/1/2005', 196, 333 UNION ALL
    SELECT	'6/1/2005', 196, 544 UNION ALL
    SELECT	'7/1/2005', 196, 722 UNION ALL
    SELECT	'8/1/2005', 196, 845
    
    -- Initialize variables
    DECLARE	@Today DATETIME,
    	@NextMonth DATETIME,
    	@FirstMonth DATETIME
    
    SELECT	@Today = '8/15/2005',	-- GETDATE(),
    	@NextMonth = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @Today), '19000201'),
    	@FirstMonth = DATEADD(MONTH, -4, @NextMonth)
    
    -- Show the expected output
    SELECT		MAX(CASE DATEDIFF(MONTH, Date_Taken, @Today)
    			WHEN 0 THEN Date_Taken
    			ELSE NULL
    		END) AS Date_Taken,
    		Main_ID,
    		MAX(CASE DATEDIFF(MONTH, Date_Taken, @Today)
    			WHEN 0 THEN Daily_Rainfall
    			ELSE NULL
    		END) AS CurrentMonth_DR,
    		MAX(CASE DATEDIFF(MONTH, Date_Taken, @Today)
    			WHEN 1 THEN Daily_Rainfall
    			ELSE NULL
    		END) AS LastMonth_DR,
    		MAX(CASE DATEDIFF(MONTH, Date_Taken, @Today)
    			WHEN 2 THEN Daily_Rainfall
    			ELSE NULL
    		END) AS Last2Month_DR,
    		MAX(CASE DATEDIFF(MONTH, Date_Taken, @Today)
    			WHEN 3 THEN Daily_Rainfall
    			ELSE NULL
    		END) AS Last3Month_DR
    FROM		@Sample
    WHERE		Date_Taken >= @FirstMonth
    		AND Date_Taken < @NextMonth
    GROUP BY	Main_ID
    ORDER BY	Main_ID

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •