Grouping by month - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping by month

Author

Topic

danielmcl
Starting Member

4 Posts

Posted - 2005-01-13 : 14:45:11

What I'm trying to achieve is a recordset containing two columns. The first being a list of months starting off from a given date with the following 11 months and the second is the sum of hours worked, grouped by each month.

I have the following statements but as soon as I join them, two things happen. 1) Where a month contains no hours the month is lost (even using an outer join or IsNull). 2) The months get re-ordered by their name.

SELECT CONVERT(varchar(3), DATEADD(m, Months.Mon, Clients.ContractStartDate), 9) AS [Month]
FROM Clients CROSS JOIN
	(SELECT 0 Mon UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
	SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
	SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11) Months
WHERE Clients.ClientID = 1

SELECT SUM(ts.Hours) * 60 + SUM(ts.Minutes) / 60 AS Hours
FROM Clients c INNER JOIN
     TimeSheet ts ON c.ClientID = ts.ClientID AND c.ContractStartDate <= ts.WorkDate
WHERE c.ClientID = 1

I was considering using a Union to add each month to the Timesheet with zero hours (so not to effect the sum) to preserve the months but how about the order?

Or am I going about this all the wrong way?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-13 : 16:40:41

We'll need DDL for your Clients and TimeSheet table as well as INSERT INTO statements for sample data that represents your problem. Then we'll need the expected result set using that sample data. The DDL only needs to includes those columns involved in your query and the INSERT INTO statements should reflect the DDL. Sample data is usually 5-10, whatever makes enough data to illustrate your problem. Then we can copy that information onto our machines and test out our solutions.

Tara

danielmcl
Starting Member

4 Posts

Posted - 2005-01-13 : 18:25:35

Certainly...

CREATE TABLE [dbo].[Clients] (
	[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
	[ContractStartDate] [datetime] NULL
) ON [PRIMARY]
GO

Insert Into Clients (ContractStartDate) Values('2004-06-01')


CREATE TABLE [dbo].[TimeSheet] (
	[ClientID] [int] NOT NULL ,
	[Hours] [int] NOT NULL ,
	[Minutes] [int] NOT NULL ,
	[WorkDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 4, 20, '2004-06-21')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 30, '2004-06-21')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 0 , '2004-07-05')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 3, 20, '2005-07-08')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 6, 0 , '2004-07-09')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 30, '2004-08-05')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 3, 15, '2004-08-17')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 45, '2004-08-27')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 30, '2004-09-03')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 0 , '2004-09-06')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 0 , '2004-09-21')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 0 , '2004-09-24')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 25, '2004-09-27')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 4, 30, '2004-11-04')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 30, '2004-11-04')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 30, '2004-11-09')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 0 , '2005-01-09')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 5, 30, '2005-01-02')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 0 , '2005-01-02')
Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 30, '2005-01-15')

A slight change to the second statement to correctly calculate the hours...

SELECT (SUM(ts.Hours) * 60 + SUM(ts.Minutes)) / 60 AS Hours
FROM Clients c INNER JOIN
     TimeSheet ts ON c.ClientID = ts.ClientID AND c.ContractStartDate <= ts.WorkDate
WHERE c.ClientID = 1

The result set I'm after...

Month	Hours
-----	-----
Jun	5
Jul	10
Aug	7
Sep	6
Oct	0
Nov	9
Dec	0
Jan	10
Feb	0
Mar	0
Apr	0
May	0

danielmcl
Starting Member

4 Posts

Posted - 2005-01-13 : 18:29:55

This is how I was joining the two initially...

SELECT m.[Month], (SUM(ts.Hours) * 60 + SUM(ts.Minutes)) / 60 AS Hours
FROM Clients c INNER JOIN
     TimeSheet ts ON c.ClientID = ts.ClientID AND c.ContractStartDate <= ts.WorkDate

RIGHT OUTER JOIN
(
SELECT CONVERT(varchar(3), DATEADD(m, Months.Mon, Clients.ContractStartDate), 9) AS [Month]
FROM Clients CROSS JOIN
	(SELECT 0 Mon UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
	SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
	SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11) Months
WHERE Clients.ClientID = 1
) m
ON m.[Month] = CONVERT(varchar(3), ts.WorkDate,9)

WHERE c.ClientID = 1
GROUP BY m.[Month]

And here's the unwanted result set (missing months and in the wrong order)...

Month Hours       
----- -----
Aug   7
Jan   10
Jul   10
Jun   5
Nov   9
Sep   6

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-13 : 18:37:23

Your sample data didn't match the result set, but I realized you wanted 2004 for this one:

Insert Into TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 3, 20, '2005-07-08')

Anyway, this solution is missing the zero months, but here you go:

SELECT MONTH(WorkDate) AS Month, (SUM(ts.Hours) * 60 + SUM(ts.Minutes)) / 60 AS Hours
FROM Clients c
INNER JOIN TimeSheet ts
ON c.ClientID = ts.ClientID AND c.ContractStartDate <= ts.WorkDate
GROUP BY YEAR(WorkDate), MONTH(WorkDate)
ORDER BY YEAR(WorkDate), MONTH(WorkDate)

I'll work on the 0 months now.

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-13 : 18:48:46

How's this?


CREATE TABLE #Clients (
	[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
	[ContractStartDate] [datetime] NULL
) ON [PRIMARY]
GO

Insert Into #Clients (ContractStartDate) Values('2004-06-01')

CREATE TABLE #MonthTally(MonthNum INT)

INSERT INTO #MonthTally(MonthNum) VALUES(1)
INSERT INTO #MonthTally(MonthNum) VALUES(2)
INSERT INTO #MonthTally(MonthNum) VALUES(3)
INSERT INTO #MonthTally(MonthNum) VALUES(4)
INSERT INTO #MonthTally(MonthNum) VALUES(5)
INSERT INTO #MonthTally(MonthNum) VALUES(6)
INSERT INTO #MonthTally(MonthNum) VALUES(7)
INSERT INTO #MonthTally(MonthNum) VALUES(8)
INSERT INTO #MonthTally(MonthNum) VALUES(9)
INSERT INTO #MonthTally(MonthNum) VALUES(10)
INSERT INTO #MonthTally(MonthNum) VALUES(11)
INSERT INTO #MonthTally(MonthNum) VALUES(12)

CREATE TABLE  #TimeSheet(
	[ClientID] [int] NOT NULL ,
	[Hours] [int] NOT NULL ,
	[Minutes] [int] NOT NULL ,
	[WorkDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 4, 20, '2004-06-21')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 30, '2004-06-21')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 0 , '2004-07-05')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 3, 20, '2005-07-08')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 6, 0 , '2004-07-09')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 30, '2004-08-05')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 3, 15, '2004-08-17')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 45, '2004-08-27')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 30, '2004-09-03')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 0 , '2004-09-06')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 0 , '2004-09-21')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 0 , '2004-09-24')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 25, '2004-09-27')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 4, 30, '2004-11-04')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 30, '2004-11-04')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 30, '2004-11-09')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 2, 0 , '2005-01-09')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 5, 30, '2005-01-02')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 0 , '2005-01-02')
Insert Into #TimeSheet (ClientID, Hours, Minutes, WorkDate) Values(1, 1, 30, '2005-01-15')


SELECT DATENAME(mm, DATEADD(mm, mn.MonthNum -1, '1/1/1900')), COALESCE(((SUM(ts.Hours) * 60 + SUM(ts.Minutes)) / 60), 0) AS Hours
FROM #MonthTally mn
LEFT JOIN #TimeSheet ts ON DATENAME(mm, ts.WorkDate) = DATENAME(mm, DATEADD(mm, mn.MonthNum -1, '1/1/1900'))
GROUP BY DATENAME(mm, DATEADD(mm, mn.MonthNum -1, '1/1/1900')), mn.MonthNum
ORDER BY mn.MonthNum


DROP TABLE #MonthTally
DROP TABLE #TimeSheet
DROP TABLE #Clients

BTW, It would be nice if you posted TEMP tables. That makes things a bit nicer, for me anyway. I'm not sure if others care. I usually just drop those commands into whatever database I've got open at the time.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-13 : 18:50:59

Oops, I forgot about ClientID. You'll need to add that in like Tara's example.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-13 : 18:53:10

Here you go:



SELECT m.[Month], Hours = CASE WHEN Hours IS NULL THEN 0 ELSE Hours END
FROM 
(
	SELECT YEAR(WorkDate) AS Yr, MONTH(WorkDate) AS [Month], (SUM(ts.Hours) * 60 + SUM(ts.Minutes)) / 60 AS Hours
	FROM Clients c 
	INNER JOIN TimeSheet ts 
	ON c.ClientID = ts.ClientID AND c.ContractStartDate <= ts.WorkDate
	GROUP BY YEAR(WorkDate), MONTH(WorkDate)
) t
RIGHT OUTER JOIN
	(
		SELECT 0 [Month] UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
		SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
		SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
	) m
ON t.[Month] = m.[Month]

The part that isn't correct is the ordering. It has to do with the derived table in the RIGHT OUTER JOIN though.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-13 : 18:58:37

To get the ordering correct, your derived table or the working tally table from Michael's is going to need to include year information. Then join on month and year. Then ORDER BY Year, Month

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-01-13 : 19:01:05

I guess that's what I get for trying to beat Tara to the punch on this one, but I think the basic problem has been solved.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-13 : 19:27:48

Well mine isn't fully functional either. We both have the ordering problem. But I've described what needs to be done to fix it.

Tara

danielmcl
Starting Member

4 Posts

Posted - 2005-01-13 : 20:34:27

Thank you both, that was such a great help. Here's the working statement.

SELECT left(m.[Month],3) as [Month], Hours = CASE WHEN Hours IS NULL THEN 0 ELSE Hours END
FROM 
(
	SELECT YEAR(WorkDate) AS Yr, MONTH(WorkDate) AS [Month], (SUM(ts.Hours) * 60 + SUM(ts.Minutes)) / 60 AS Hours
	FROM Clients c 
	INNER JOIN TimeSheet ts 
	ON c.ClientID = ts.ClientID AND c.ContractStartDate <= ts.WorkDate
	Where c.ClientID = 1
	GROUP BY YEAR(WorkDate), MONTH(WorkDate)
) t
RIGHT OUTER JOIN
	(
		SELECT CONVERT(varchar(11), DATEADD(m, Months.[Month], Clients.ContractStartDate), 9) AS [Month]
		FROM Clients CROSS JOIN
			(
				SELECT 0 [Month] UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
				SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION
				SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11
			) Months
		WHERE Clients.ClientID = 1
	) m
ON t.[Month] = Month(m.[Month]) and t.Yr = Year(m.[Month])

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources