SQL GROUPIONG QUERY - 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 2008 Forums

Transact-SQL (2008)

SQL GROUPIONG QUERY

Author

Topic

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-03 : 04:10:36

Dear All,
i want to find the number of employee joined in month and year
my table strucure is
table name:-Emp
EMPID NAME DOJ LOC
1000 VIP 1/1/2012 MUMBAI
1200 JHA 1/2/2012 MUMBAI
1300 REN 1/2/2012 MUMBAI

I WANT TIO FETCH HOW MANY NUMBER OF EMPLOYEE JOINED IN FEB OR SO MORE

THANKS IN ADVANCE,
REGARDS,
vIPIN JHA

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-03 : 04:20:11

[code]
select dateadd(month, datediff(month, 0, DOJ), 0), count(*)
from EMP
group by dateadd(month, datediff(month, 0, DOJ), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-03 : 06:45:35

i modified my query as u suggested. like below

SELECT A.* FROM (
select left(DATEname(mm,EMP_DATEOFJOINING),3)+'-'+right(convert(varchar,DATEPART(yy,EMP_DATEOFJOINING)),2) "Month-Year",
count(*) as "Number of joined Empl"
from erm_employee_master
group by left(DATEname(mm,EMP_DATEOFJOINING),3)+'-'+right(convert(varchar,DATEPART(yy,EMP_DATEOFJOINING)),2)

UNION ALL
select left(DATEname(mm,ES_LAST_WORKING_DATE),3)+'-'+right(convert(varchar,DATEPART(yy,ES_LAST_WORKING_DATE)),2) "Month-Year",
count(*) as "Number of exit Empl"
from SEP_EMPLOYEE_SEPARATION
group by left(DATEname(mm,ES_LAST_WORKING_DATE),3)+'-'+right(convert(varchar,DATEPART(yy,ES_LAST_WORKING_DATE)),2)

)A

i want to sho output as
month-yaer no.of joined emp no.of exit employe

but i am getting mu output like
Month-Year Number of joined Empl
12-May 16
12-May 19

plesae suggest as its very important

regards,
vipin jha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-03 : 08:18:06

[code]

; with
emp_join as
(
select mth_yr = dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0), cnt = count(*)
from erm_employee_master
group by dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0)
),
emp_exit as
(
select mth_yr = dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0), cnt = count(*)
from SEP_EMPLOYEE_SEPARATION
group by dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0)
)
select mth_yr = left(datename(month, coalesce(j.mth_yr, e.mth_yr)), 3) + '-' + right(datename(year, coalesce(j.mth_yr, e.mth_yr)), 2),
j.cnt as [Number of joined Empl],
e.cnt as [Number of exit Empl]
from emp_join j
full outer join emp_exit e on j.mth_yr = e.mth_yr
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-04 : 00:51:09

THANKX SIR
I GOT THE SOLUTION THANX AGAIN

REGARDS,
VIPIN JHA

vipinjha123
Starting Member

45 Posts

Posted - 2012-05-04 : 02:30:32

Hello sir ,
if i want to add two colmun(location.department) in same query

now i want to see report as monthwise,locationwise,departmentwise

for example in

month-year joinee exit location dept
may-12 12 5 mumbai IT
MAY-12 10 2 MUMBAI ACCOUNTS

what changes are required in above query developed by you

waiting for your prompt response

regards,
Vipin jha

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-04 : 06:12:14

[code]
; with
emp_join as
(
select mth_yr = dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0), location, department, cnt = count(*)
from erm_employee_master
group by dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0), location, department
),
emp_exit as
(
select mth_yr = dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0), location, department, cnt = count(*)
from SEP_EMPLOYEE_SEPARATION
group by dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0), location, department
)
select mth_yr = left(datename(month, coalesce(j.mth_yr, e.mth_yr)), 3) + '-' + right(datename(year, coalesce(j.mth_yr, e.mth_yr)), 2),
location, department,
j.cnt as [Number of joined Empl],
e.cnt as [Number of exit Empl]
from emp_join j
full outer join emp_exit e on j.mth_yr = e.mth_yr
and j.location = e.location
and j.department = e.department
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources