Group by month/year

Question

Post reply

Group by month/year

Jay@Work

SSCrazy

Points: 2633

June 12, 2019 at 3:55 am

#3652597

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

NameHoursmonthyear
x4.00April2019
x7.00April2019
y7.00August2018
y7.00July2018
x7.00June2019
y5.00May2019
y7.00May2019
x3.00May2019
y14.00October2018

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

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

-- 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 5 years, 5 months ago by ChrisM@Work.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".