group by month problem - 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 2005 Forums

Transact-SQL (2005)

group by month problem

Author

Topic

de
Starting Member

4 Posts

Posted - 2009-09-23 : 01:03:46

Hi All,
i have 2 tables
EMPLOYEE
--------
empid empname dateofjoining
-----------------------------------------------
1 karthick 2009-04-07 00:00:00.000
2 ceo 2009-01-01 00:00:00.000
3 ka 2003-08-06 00:00:00.000
4 Rajesh 2006-12-01 00:00:00.000
7 Sam 2009-01-01 00:00:00.000

and
EXITPROCESSMST
--------------
exitprocid empid dateof relieving
---------------------------------------------
1 7 2009-06-05 00:00:00.000
2 3 2008-08-06 00:00:00.000

i want to find out no of employees left per month
i.e
2006 2007 2008
jan 10 20 30
feb 4 25 12
mar -- -- --
apr -- -- --
may -- -- --
jun -- -- --
july -- -- --
aug -- -- --
sep -- -- --
oct -- -- --
nov -- -- --
dec -- -- --

Please help me out ...

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-09-23 : 01:43:44

Hi

try this....


CREATE TABLE #TEMP(EXITPROCID INT IDENTITY(1,1), EMPID INT, DATE DATETIME)


INSERT INTO #TEMP
SELECT 	7 ,'2009-06-05 00:00:00.000' UNION ALL
SELECT 	3 ,'2008-08-06 00:00:00.000' UNION ALL
SELECT 	4 ,'2009-06-05 00:00:00.000' UNION ALL
SELECT 	5 ,'2008-08-06 00:00:00.000' UNION ALL
SELECT 	6 ,'2009-06-05 00:00:00.000' UNION ALL
SELECT 	8 ,'2008-08-06 00:00:00.000' UNION ALL
SELECT 	9 ,'2009-06-05 00:00:00.000' UNION ALL
SELECT 	10 ,'2008-08-06 00:00:00.000' UNION ALL
SELECT 	11 ,'2009-06-05 00:00:00.000' UNION ALL
SELECT 	13 ,'2008-08-06 00:00:00.000' UNION ALL
SELECT 	14 ,'2009-06-05 00:00:00.000' UNION ALL
SELECT 	15 ,'2007-08-06 00:00:00.000'


SELECT DATE,COUNT(EMPID) AS NO_OF_EMPLYEE FROM #TEMP  GROUP BY DATE

DROP TABLE #TEMP

-------------------------
R...

matty
Posting Yak Master

161 Posts

Posted - 2009-09-23 : 01:47:00

[code]
declare @sample table
(
exitprocid int,
empid int,
dateofrelieving datetime
)

insert @sample
SELECT 1, 7, '2009-06-05 00:00:00.000'
UNION ALL
SELECT 2, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 3, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 4, 5, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 5, 6, '2008-09-06 00:00:00.000'
UNION ALL
SELECT 6, 10, '2009-09-06 00:00:00.000'

SELECT Month,[2006],[2007],[2008],[2009]
FROM
(
SELECT empid,CONVERT(varchar(3), dateofrelieving) AS Month,MONTH(dateofrelieving) AS Mth,YEAR (dateofrelieving) AS Yr
froM @sample
) T
PIVOT
(
COUNT(empid)
FOR Yr IN ([2006],[2007], [2008],[2009])
)p
ORDER BY Mth
[/code]

de
Starting Member

4 Posts

Posted - 2009-09-23 : 01:51:08

quote:

Originally posted by matty


declare @sample table
(
exitprocid int,
empid		int,
dateofrelieving	datetime
)

insert @sample
SELECT 1, 7, '2009-06-05 00:00:00.000'
UNION ALL
SELECT 2, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 3, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 4, 5, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 5, 6, '2008-09-06 00:00:00.000'
UNION ALL
SELECT 6, 10, '2009-09-06 00:00:00.000'

SELECT Month,[2006],[2007],[2008],[2009]
FROM
	(
		SELECT empid,CONVERT(varchar(3), dateofrelieving) AS Month,MONTH(dateofrelieving) AS Mth,YEAR (dateofrelieving) AS Yr
		froM @sample
	) T
PIVOT
	(
		COUNT(empid)
		FOR Yr IN ([2006],[2007], [2008],[2009])
	)p
ORDER BY Mth

Thanks for your reply but I want to display all the 12 months even if no emp exited in that month

Udayantha
Starting Member

4 Posts

Posted - 2009-09-23 : 02:38:04

This can be done by a pivot. See if this works

select * from
(select datename(month,dateofrelieving) as monthRetired, year(dateofrelieving) as yearOfLeaving from EXITPROCESSMST)
as SourceTable
pivot(count(yearOfLeaving) for yearOfLeaving IN([2007],[2008],[2009])) p

to learn more abt sql PIVOT visit:
http://sqlshit.blogspot.com/2009/09/sql-pivot-resulting-rows-in-to-columns.html

Bst Rgds,
Udayantha
http://sqlshit.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-23 : 02:54:12

For dynamic PIVOt, refer
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

de
Starting Member

4 Posts

Posted - 2009-09-23 : 02:58:05

quote:
Originally posted by Udayantha

This can be done by a pivot. See if this works

select * from
(select datename(month,dateofrelieving) as monthRetired, year(dateofrelieving) as yearOfLeaving from EXITPROCESSMST)
as SourceTable
pivot(count(yearOfLeaving) for yearOfLeaving IN([2007],[2008],[2009])) p

to learn more abt sql PIVOT visit:
http://sqlshit.blogspot.com/2009/09/sql-pivot-resulting-rows-in-to-columns.html

But I don't want to harcode the years and want to show all the months in a year(can be hard coded)..how to show that

Bst Rgds,
Udayantha
http://sqlshit.blogspot.com/

matty
Posting Yak Master

161 Posts

Posted - 2009-09-23 : 03:19:50

You can do it using a month table ...


declare @sample table
(
exitprocid		int,
empid			int,
dateofrelieving	datetime
)

declare @months table
(
Id	INT IDENTITY(1,1),
MonthNm VARCHAR(50)
)

INSERT @months
SELECT 'Jan'
UNION ALL
SELECT 'Feb'
UNION ALL
SELECT 'Mar'
UNION ALL
SELECT 'Apr'
UNION ALL
SELECT 'May'
UNION ALL
SELECT 'Jun'
UNION ALL
SELECT 'Jul'
UNION ALL
SELECT 'Aug'
UNION ALL
SELECT 'Sep'
UNION ALL
SELECT 'Oct'
UNION ALL
SELECT 'Nov'
UNION ALL
SELECT 'Dec'

insert @sample
SELECT 1, 7, '2009-06-05 00:00:00.000'
UNION ALL
SELECT 2, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 3, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 4, 5, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 5, 6, '2008-09-06 00:00:00.000'
UNION ALL
SELECT 6, 10, '2009-09-06 00:00:00.000'
	
SELECT MonthNm,[2006],[2007],[2008],[2009]
FROM @months m
LEFT JOIN  
	(
		SELECT empid,CONVERT(varchar(3), dateofrelieving) AS Month,MONTH(dateofrelieving) AS Mth,YEAR (dateofrelieving) AS Yr
		froM @sample
	) T
ON m.MonthNm = T.Month
PIVOT
	(
		COUNT(empid)
		FOR Yr IN ([2006],[2007], [2008],[2009])
	)p
ORDER BY Id

quote:

Originally posted by de

quote:

Originally posted by matty


declare @sample table
(
exitprocid int,
empid		int,
dateofrelieving	datetime
)

insert @sample
SELECT 1, 7, '2009-06-05 00:00:00.000'
UNION ALL
SELECT 2, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 3, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 4, 5, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 5, 6, '2008-09-06 00:00:00.000'
UNION ALL
SELECT 6, 10, '2009-09-06 00:00:00.000'

SELECT Month,[2006],[2007],[2008],[2009]
FROM
	(
		SELECT empid,CONVERT(varchar(3), dateofrelieving) AS Month,MONTH(dateofrelieving) AS Mth,YEAR (dateofrelieving) AS Yr
		froM @sample
	) T
PIVOT
	(
		COUNT(empid)
		FOR Yr IN ([2006],[2007], [2008],[2009])
	)p
ORDER BY Mth

Thanks for your reply but I want to display all the 12 months even if no emp exited in that month

Udayantha
Starting Member

4 Posts

Posted - 2009-09-23 : 04:11:58

quote:
Originally posted by de

quote:
Originally posted by Udayantha

This can be done by a pivot. See if this works

select * from
(select datename(month,dateofrelieving) as monthRetired, year(dateofrelieving) as yearOfLeaving from EXITPROCESSMST)
as SourceTable
pivot(count(yearOfLeaving) for yearOfLeaving IN([2007],[2008],[2009])) p

to learn more abt sql PIVOT visit:
http://sqlshit.blogspot.com/2009/09/sql-pivot-resulting-rows-in-to-columns.html

But I don't want to harcode the years and want to show all the months in a year(can be hard coded)..how to show that

Bst Rgds,
Udayantha
http://sqlshit.blogspot.com/

OK..
What you can do is join the table with a temporary table which contains months. Then It;l return all the Months. But I dnt knw how to do this without hard coding the years. Anyway I'll keep on trying.
Hope this will help:

declare @months table
(
Id INT IDENTITY(1,1),
MonthNm VARCHAR(50)
)

INSERT @months
SELECT 'January'
UNION ALL
SELECT 'February'
UNION ALL
SELECT 'March'
UNION ALL
SELECT 'April'
UNION ALL
SELECT 'May'
UNION ALL
SELECT 'June'
UNION ALL
SELECT 'July'
UNION ALL
SELECT 'August'
UNION ALL
SELECT 'September'
UNION ALL
SELECT 'October'
UNION ALL
SELECT 'November'
UNION ALL
SELECT 'December'

select * from
(select m.MonthNm,yearOfLeaving from
(select datename(month,dateofrelieving) as monthRetired, year(dateofrelieving) as yearOfLeaving
from EXITPROCESSMST) as T right join @months m on T.monthRetired=m.MonthNm)
as SourceTable
pivot(count(yearOfLeaving) for yearOfLeaving IN([2007],[2008],[2009])) p

Bst Rgds,
Udayantha
http://sqlshit.blogspot.com/

de
Starting Member

4 Posts

Posted - 2009-09-23 : 04:58:11

quote:

Originally posted by matty

You can do it using a month table ...


declare @sample table
(
exitprocid		int,
empid			int,
dateofrelieving	datetime
)

declare @months table
(
Id	INT IDENTITY(1,1),
MonthNm VARCHAR(50)
)

INSERT @months
SELECT 'Jan'
UNION ALL
SELECT 'Feb'
UNION ALL
SELECT 'Mar'
UNION ALL
SELECT 'Apr'
UNION ALL
SELECT 'May'
UNION ALL
SELECT 'Jun'
UNION ALL
SELECT 'Jul'
UNION ALL
SELECT 'Aug'
UNION ALL
SELECT 'Sep'
UNION ALL
SELECT 'Oct'
UNION ALL
SELECT 'Nov'
UNION ALL
SELECT 'Dec'

insert @sample
SELECT 1, 7, '2009-06-05 00:00:00.000'
UNION ALL
SELECT 2, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 3, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 4, 5, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 5, 6, '2008-09-06 00:00:00.000'
UNION ALL
SELECT 6, 10, '2009-09-06 00:00:00.000'
	
SELECT MonthNm,[2006],[2007],[2008],[2009]
FROM @months m
LEFT JOIN  
	(
		SELECT empid,CONVERT(varchar(3), dateofrelieving) AS Month,MONTH(dateofrelieving) AS Mth,YEAR (dateofrelieving) AS Yr
		froM @sample
	) T
ON m.MonthNm = T.Month
PIVOT
	(
		COUNT(empid)
		FOR Yr IN ([2006],[2007], [2008],[2009])
	)p
ORDER BY Id

quote:

Originally posted by de

quote:

Originally posted by matty


declare @sample table
(
exitprocid int,
empid		int,
dateofrelieving	datetime
)

insert @sample
SELECT 1, 7, '2009-06-05 00:00:00.000'
UNION ALL
SELECT 2, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 3, 3, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 4, 5, '2008-08-06 00:00:00.000'
UNION ALL
SELECT 5, 6, '2008-09-06 00:00:00.000'
UNION ALL
SELECT 6, 10, '2009-09-06 00:00:00.000'

SELECT Month,[2006],[2007],[2008],[2009]
FROM
	(
		SELECT empid,CONVERT(varchar(3), dateofrelieving) AS Month,MONTH(dateofrelieving) AS Mth,YEAR (dateofrelieving) AS Yr
		froM @sample
	) T
PIVOT
	(
		COUNT(empid)
		FOR Yr IN ([2006],[2007], [2008],[2009])
	)p
ORDER BY Mth

Thanks for your reply but I want to display all the 12 months even if no emp exited in that month

HI thanks for working so hard on my query ..but i don't want all the years to come i want only years present in emp table and empexit table.

safderalimd
Starting Member

32 Posts

Posted - 2009-09-23 : 09:42:50

Follow these steps

1) create date_dim table using below code
2) populate date_dim table using below code
3) join date_dim table with your EXITPROCESSMST table ON exit date and then use GROUP BY and PIVOT

--create table
CREATE TABLE dbo.date_dim(
date_id int NOT NULL,
day_date datetime NULL,
day_num tinyint NOT NULL,
month_num tinyint NOT NULL,
year_num smallint NOT NULL,
)
go

--create clsutered primary key
ALTER TABLE [dbo].date_dim ADD CONSTRAINT [date_dim__dateId_CLPK] PRIMARY KEY CLUSTERED
(
date_id ASC
)
GO

--populate date_dim table
declare @x smalldatetime
select @x = '1950-01-01'
while @x < '2051-01-01'
begin
Insert into date_dim
Select cast(convert(char(8),@x,112) as int) --integer date
,@x --calendar date
,datepart(dd,@x) -- day_num
,datepart(mm,@x) --month_num
,datepart(yy,@x) --year_num

Select @x = dateadd(dd,1,@x)
end

matty
Posting Yak Master

161 Posts

Posted - 2009-09-24 : 05:08:29

You have to use dynamic sql.


CREATE TABLE #months 
(
	Id	INT IDENTITY(1,1),
	MonthNm VARCHAR(50)
)

INSERT #months
SELECT 'Jan'
UNION ALL
SELECT 'Feb'
UNION ALL
SELECT 'Mar'
UNION ALL
SELECT 'Apr'
UNION ALL
SELECT 'May'
UNION ALL
SELECT 'Jun'
UNION ALL
SELECT 'Jul'
UNION ALL
SELECT 'Aug'
UNION ALL
SELECT 'Sep'
UNION ALL
SELECT 'Oct'
UNION ALL
SELECT 'Nov'
UNION ALL
SELECT 'Dec'

DECLARE @Year VARCHAR(1000)
DECLARE @Sql  VARCHAR(MAX)

SELECT @Year = COALESCE(@Year + ',' ,'') + '[' + LTRIM(STR(Yr)) + ']'
FROM
	(
	SELECT YEAR(dateofjoining) AS Yr FROM EMPLOYEE
	UNION
	SELECT YEAR(dateofrelieving)
	FROM EXITPROCESSMST
	)y

SET @Sql = 
	'SELECT MonthNm,' + @Year +
	' FROM #months m
	LEFT JOIN  
		(
			SELECT empid,CONVERT(varchar(3), dateofrelieving) AS Month,MONTH(dateofrelieving) AS Mth,YEAR (dateofrelieving) AS Yr
			froM EXITPROCESSMST
		) T
	ON m.MonthNm = T.Month
	PIVOT
		(
			COUNT(empid)
			FOR Yr IN (' + @Year + ')
		)p
	ORDER BY Id'
	
EXEC(@sql)

[/quote]
HI thanks for working so hard on my query ..but i don't want all the years to come i want only years present in emp table and empexit table.
[/quote]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources