Group by month/year

  • Jay@Work

    SSCrazy

    Points: 2424

    Having some problems trying to combine values where the month/year are the same.

    Here is my current output (below) , what I'm trying to do is to combine the value of hours and only show a single row where the month/year is the same for each person.

    So this would change the first 2 rows below into a single row which would read

    x - 11 - April - 2019

    Name	Hours	month	year
    x 4.00 April 2019
    x 7.00 April 2019
    y 7.00 August 2018
    y 7.00 July 2018
    x 7.00 June 2019
    y 5.00 May 2019
    y 7.00 May 2019
    x 3.00 May 2019
    y 14.00 October 2018

    This is the t-sql with sensitive stuff removed

    select RTRIM(COALESCE (E.PreferredName, E.FirstNames)) + ' ' + E.LastName AS Name, sum(H.Quantity) as 'Hours', datename(MONTH,H.startdate) as month, datepart(YEAR,H.startdate) as year
    from table1 H
    inner join table2 E
    on e.Code = H.Code
    where E.pcode in ('C','L')
    and H.AllCode like 'z%'
    and H.startdate > '2018-06-30'
    group by datename(MONTH,H.startdate), datepart(YEAR,H.startdate)-, RTRIM(COALESCE (E.PreferredName, E.FirstNames)) + ' ' + E.LastName, h.Quantity
  • ChrisM@Work

    SSC Guru

    Points: 186034

    -- Use EOMONTH(H.startdate) instead of datename(MONTH,H.startdate), datepart(YEAR,H.startdate)
    -- and remove h.Quantity from the GROUP BY
    SELECT
    RTRIM(COALESCE (E.PreferredName, E.FirstNames)) + ' ' + E.LastName AS [Name],
    sum(H.Quantity) as [Hours],
    datename(MONTH,x.YearMonth) as [month],
    datepart(YEAR,x.YearMonth) as [year]
    FROM table1 H
    INNER JOIN table2 E
    ON e.Code = H.Code
    CROSS APPLY (
    SELECT YearMonth = EOMONTH(H.startdate)
    ) x
    WHERE E.pcode in ('C','L')
    and H.AllCode like 'z%'
    and H.startdate > '2018-06-30'
    GROUP BY x.YearMonth, RTRIM(COALESCE (E.PreferredName, E.FirstNames)) + ' ' + E.LastName --, h.Quantity

    • This reply was modified 6 days, 3 hours ago by  ChrisM@Work.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • ScottPletcher

    SSC Guru

    Points: 97970

    Use just one date for each month; in keeping with "standard" practice, I use the first of the month.

    Also, you must remove H.Quantity from the GROUP BY.

    select RTRIM(COALESCE (E.PreferredName, E.FirstNames)) + ' ' + E.LastName AS Name, 
    sum(H.Quantity) as 'Hours',
    datename(MONTH,ca1.startmonth) as month, datepart(YEAR,ca1.startmonth) as year
    from table1 H
    cross apply (
    select dateadd(MONTH, DATEDIFF(MONTH, 0, H.startdate), cast(0 as date)) AS startmonth
    ) as ca1
    inner join table2 E
    on e.Code = H.Code
    where E.pcode in ('C','L')
    and H.AllCode like 'z%'
    and H.startdate > '2018-06-30'
    group by ca1.startmonth, RTRIM(COALESCE (E.PreferredName, E.FirstNames)) + ' ' + E.LastName

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply