Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    3

    Unanswered: PIVOT on month name when field is a timestamp

    Hi all,

    I have a lot of experience with writing queries in Access but it's not helping me at all now that I am trying to query an SQL Server. The table I am trying to query is an incident management system and what I am trying to do is create a crosstab query that will display the number of incidents entered by a user in each month. This is what I have:

    Code:
    SELECT [Assoc Full Name], [January], [February], [March], [April]
    FROM _smdba_.Incident
    PIVOT
    (
    COUNT ([Incident #])
    FOR datename( month, "Open Date & Time" ) IN 
    ( [January], [February], [March], [April] )
    ) AS pvt
    ORDER BY pvt.[Assoc Full Name]
    Can someone help me get this working?

  2. #2
    Join Date
    May 2011
    Location
    Boston, MA area
    Posts
    4

    Possible solution with a derived table

    Hi,

    This query should help. I wasn't able to get the query working having an expression in the pivot statement. So I instead moved the logic to a derived table.

    If my understanding of the data types or columns is incorrect, I can provide a modified solution.

    First, I inserted some dummy data to work with. I wasn't sure how the dates were stored so I assume varchars. If they're stored as datetimes, simply remove the cast in the main query below.
    Code:
    create table Incident([Assoc Full Name] varchar(50), [open date & time] datetime, [incident #] int)
    
    insert into Incident values ('John', '1/1/2011',  1)
    insert into Incident values ('John', '1/3/2011', 2)
    insert into Incident values ('John', '1/3/2011', 3)
    insert into Incident values ('John', '1/3/2011', 4)
    insert into Incident values ('Greg', '1/30/2011', 5)
    insert into Incident values ('Greg', '2/10/2011', 6)
    insert into Incident values ('Bob', '2/20/2011', 7)
    insert into Incident values ('Bob', '1/15/2011', 8)
    insert into Incident values ('Bob', '3/2/2011', 9)
    insert into Incident values ('Bob', '3/25/2011', 10)
    With this data in place, the following sql returns the pivoted result:
    Code:
    SELECT [Assoc Full Name], [1] as 'January', [2] as 'February', [3] as 'March', [4] as 'April'
    -- To make it so we directly have the value we want to pivot on, put the expression in a derived table:
    FROM (
    select [Assoc Full Name], 
    -- cast needed if Open Date & Time is not a datetime field
    datepart(month, cast([Open Date & Time] as datetime)) as monthValue,
    [Incident #] from incident) tbl 
    PIVOT
    (
    COUNT ([Incident #])
    FOR monthValue IN 
    ( [1], [2], [3], [4] ) -- ...
    ) AS pvt
    ORDER BY pvt.[Assoc Full Name]
    I hope this helps

    Andrew Zwicker - Visit A site to talk about SQL. | www.helpwithsql.com
    Last edited by Andrew Zwicker; 05-16-11 at 21:29. Reason: put incrementing incident numbers

  3. #3
    Join Date
    May 2011
    Posts
    3
    Awesome! That worked perfectly.

    Thanks so much for your help!

  4. #4
    Join Date
    May 2011
    Posts
    3

    Follow up

    As a follow up on this one:

    If I want weekly totals instead of monthly totals, is there a better way to create this query than to use:

    Code:
    datepart(wk, [Open Date & Time]) as weekValue
    And then have to list out all the weeks like [1], [2], [3]... etc?

Posting Permissions

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