Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2016
    Posts
    1

    Unanswered: Missing month group by query

    My data is like below currently- 7 columns below in first table.
    Date Country P1 P2 C1 C2 R1

    2017-03-01 IN 0 10 20 0 3

    2017-05-01 IN 4 20 10 10 0

    2017-03-01 US 2 10 5 2 1

    2017-05-01 US 2 0 5 20 1

    2017-07-01 US 2 8 15 2 10

    I am looking for query that will make the data like below in table 2.It will do the running total by country ,add the missing months and invert the dates to column .It will also accept the start and end month .

    Country Mar 2017 Apr 2017 May 2017 June 2017 July 2017
    P1 IN 0 0 4 4 4
    P2 IN 10 10 30 30 30
    C1 IN 20 20 30 30 30
    C2 IN 0 0 10 10 10
    R1 IN 3 3 3 3 3


    P1 US 2 2 4 4 6
    P2 US 10 10 10 10 18
    C1 US 5 5 10 10 25
    C2 US 2 2 22 22 24
    R1 US 1 1 2 2 12

    pls help.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,842
    Provided Answers: 6
    you want to have a table of dates that you can left outer join from. a table of dates and a table of numbers are invaluable in any datawarehousing environment. then you will also want to use the PIVOT SQL statement. You can look up PIVOT in the SQL Server Books Online documentation readily available online and sometimes with your local install.
    “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.

Tags for this Thread

Posting Permissions

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