| Author |
 Topic  |
|
|
vipinjha123
Starting Member
India
45 Posts |
 Posted - 06/11/2012 : 04:43:11
|
Dear All ,
I am looking for a query where i can get month wise total number of employee
suppose i have 100 employee in mar-12
and 15 new joiend in same month so in april i should have 115 employee
my query is
select SUM(a.total) emp from (
select count(emp_staffid) total,
left(DATEname(mm,EMP_DATEOFJOINING),3)+'-'+right(convert(varchar,DATEPART(yy,EMP_DATEOFJOINING)),2) "Month-Year"
from ERM_EMPLOYEE_MASTER
where EMP_ISACTIVE like '%1%'
group by left(DATEname(mm,EMP_DATEOFJOINING),3)+'-'+right(convert(varchar,DATEPART(yy,EMP_DATEOFJOINING)),2)
)a
where a.[Month-Year]='apr-12'
which is not giving me exact output
regards,
Vipin jha |
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 06/11/2012 : 05:11:43
|
-- Prepare staging table
CREATE TABLE #Stage
(
MonthNumber INT,
Joined INT
)
-- Populate staging table
INSERT #Stage
(
MonthNumber,
Joined
)
SELECT DATEDIFF(MONTH, '19000101', Emp_DateOfJoining) AS MonthNumber,
COUNT(*) AS Joined
FROM dbo.Erm_Employee_Master
WHERE Emp_IsActive = 1
GROUP BY DATEDIFF(MONTH, '19000101', Emp_DateOfJoining)
-- Display the final result
SELECT SUBSTRING(CONVERT(VARCHAR(9), DATEADD(MONTH, s.MonthNumber, '19000101'), 6), 4, 6) AS MonthName,
f.Joined
FROM #Stage AS s
CROSS APPLY (
SELECT SUM(x.Joined)
FROM #Stage AS x
WHERE x.MonthNumber <= s.MonthNumber
) AS f(Joined)
ORDER BY s.MonthNumber
-- Clean up
DROP TABLE #Stage
N 56°04'39.26"
E 12°55'05.63" |
 |
|
|
vipinjha123
Starting Member
India
45 Posts |
Posted - 06/11/2012 : 07:22:56
|
Dear friend thankx for your reply
my query is
SELECT TABA.* FROM (
select a.[Month-Year] "Year-Month of joinee/Exit",
SUM(a.[Number of joined Empl]) "Count of joined Emp" ,
sum(a.[left emp]) as "Count of Exit emp"
from (
select
left(DATEname(mm,EMP_DATEOFJOINING),3)+'-'+right(convert(varchar,DATEPART(yy,EMP_DATEOFJOINING)),2) "Month-Year",
count(EMP_DATEOFJOINING) as "Number of joined Empl",0 as "left emp"
from erm_employee_master left join SEP_EMPLOYEE_SEPARATION on SEP_EMPLOYEE_SEPARATION.ES_EMP_STAFFID=erm_employee_master.EMP_STAFFID
WHERE EMP_ISACTIVE='1'
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",
0,count(*) as "Number of exit Empl"
from SEP_EMPLOYEE_SEPARATION inner join ERM_EMPLOYEE_MASTER on ERM_EMPLOYEE_MASTER.EMP_STAFFID=SEP_EMPLOYEE_SEPARATION.ES_EMP_STAFFID
where ERM_EMPLOYEE_MASTER.EMP_ISACTIVE='0'
group by
dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0),left(DATEname(mm,ES_LAST_WORKING_DATE),3)+'-'+right(convert(varchar,DATEPART(yy,ES_LAST_WORKING_DATE)),2)
)a
where a.[Month-Year] IN ('JAN-12','FEB-12','MAR-12','APR-12','MAY-12')
group by a.[Month-Year]
)TABA
order by TABA.[Year-Month of joinee/Exit] desc
i want to see data on ascenbding order(month-year) wise
like jun-12
may-12
apr-12
mar-12
i am unable to get it
please suggest
regards,
vipin jha |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47962 Posts |
Posted - 06/11/2012 : 15:42:48
|
for getting data in order you want add ORDER BY like
ORDER BY DATEPART(yy,datefield) DESC,DATEPART(mm,datefield) DESC
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
monthwise total employee count
