| Author |
 Topic  |
|
|
vipin_jha123
Starting Member
26 Posts |
 Posted - 01/08/2013 : 02:36:14
|
Dear All,
I am looking for a query where I should get the running existing total count of active employee.
Suppose I have a default employee count 1000 till jan2012...now in feb 2012 41 employees joined and 74 left.so the head_count shoud be 967
MY query is
SELECT Top 12 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 convert(varchar(4),EMP_DATEOFJOINING,100) + convert(varchar(4),year(EMP_DATEOFJOINING)) "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 convert(varchar(4),EMP_DATEOFJOINING,100) + convert(varchar(4),year(EMP_DATEOFJOINING))
UNION ALL
select convert(varchar(4),ES_LAST_WORKING_DATE,100) + convert(varchar(4),year(ES_LAST_WORKING_DATE)) "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),convert(varchar(4),ES_LAST_WORKING_DATE,100) + convert(varchar(4),year(ES_LAST_WORKING_DATE))
)a
group by a.[Month-Year]
)TABA
order by convert(datetime, '1-' + TABA.Year_Month_of_joinee_Exit) desc
output of above query is
Year_Month joined_Emp Exit_emp
Jan 2013 7 0
Dec 2012 78 42
Nov 2012 61 65
Oct 2012 21 87
Sep 2012 27 46
Aug 2012 44 58
Jul 2012 29 72
Jun 2012 26 64
May 2012 33 53
Apr 2012 22 68
Mar 2012 50 93
Feb 2012 41 74
Please help me in that .
Thankx in advance
Regards,
Vipin jha |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/08/2013 : 03:44:55
|
;With CTE
AS
(
select a.MonthYearID,
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 CAST(DATENAME(yy,dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0)) + RIGHT('0' + CAST(MONTH(dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0)) AS varchar(2)),2) AS int) AS MonthYearID,
convert(varchar(4),EMP_DATEOFJOINING,100) + convert(varchar(4),year(EMP_DATEOFJOINING)) "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 dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0),convert(varchar(4),EMP_DATEOFJOINING,100) + convert(varchar(4),year(EMP_DATEOFJOINING))
UNION ALL
select CAST(DATENAME(yy,dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0)) + RIGHT('0' + CAST(MONTH(dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0)) AS varchar(2)),2) AS int),
convert(varchar(4),ES_LAST_WORKING_DATE,100) + convert(varchar(4),year(ES_LAST_WORKING_DATE)) "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),convert(varchar(4),ES_LAST_WORKING_DATE,100) + convert(varchar(4),year(ES_LAST_WORKING_DATE))
)a
group by a.MonthYearID,a.[Month-Year]
)
SELECT c1.*,Running
FROM CTE c1
CROSS APPLY (SELECT SUM(Count_of_joined_Emp) - SUM(Count_of_Exit_emp) AS Running
FROM CTE
WHERE MonthYearID <=c1.MonthYearID)c2
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
vipin_jha123
Starting Member
26 Posts |
Posted - 01/09/2013 : 01:06:13
|
Hi Vikash ,
Thankx for your post , which is very usefull.
I did some changes in your query
;With CTE
AS
(
select a.MonthYearID,
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",
A.LOCATION_NAME
from (
select CAST(DATENAME(yy,dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0)) + RIGHT('0' + CAST(MONTH(dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0)) AS varchar(2)),2) AS int) AS MonthYearID,
convert(varchar(4),EMP_DATEOFJOINING,100) + convert(varchar(4),year(EMP_DATEOFJOINING)) "Month-Year",
L.LOCATION_NAME,
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
LEFT JOIN ERM_LOCATION_MASTER L ON L.LOCATION_ID=erm_employee_master.EMP_LOCATION_ID
WHERE EMP_DATEOFJOINING>='01-mar-2012'
and EMP_STAFFID NOT IN ('1','US011','M0586','M0256','AA01','Admin01','ChennaiHelpdesk','Ctest','ITA01','MumbaiHelpdesk','709','Adre1','AH0001','M01','M010')
AND EMP_STAFFID NOT LIKE 'M0T%'
AND EMP_STAFFID NOT LIKE 'GUE%'
AND EMP_STAFFID NOT LIKE 'KUM%'
AND EMP_STAFFID NOT LIKE 'MT%'
AND EMP_STAFFID NOT LIKE 'AT%'
AND EMP_STAFFID NOT LIKE 'CT%'
AND EMP_STAFFID NOT LIKE 'T%'
AND EMP_STAFFID NOT LIKE 'IT H%'
AND Isnull(ES_COMMENTS,'') Not Like 'Lost%'
group by dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0),convert(varchar(4),EMP_DATEOFJOINING,100) + convert(varchar(4),year(EMP_DATEOFJOINING))
,L.LOCATION_NAME
UNION ALL
select CAST(DATENAME(yy,dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0)) + RIGHT('0' + CAST(MONTH(dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0)) AS varchar(2)),2) AS int),
convert(varchar(4),ES_LAST_WORKING_DATE,100) + convert(varchar(4),year(ES_LAST_WORKING_DATE)) "Month-Year",
L.LOCATION_NAME,
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
LEFT JOIN ERM_LOCATION_MASTER L ON L.LOCATION_ID=erm_employee_master.EMP_LOCATION_ID
where ERM_EMPLOYEE_MASTER.EMP_ISACTIVE='0'
and ES_LAST_WORKING_DATE>='01-mar-2012'
group by dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0),convert(varchar(4),ES_LAST_WORKING_DATE,100) + convert(varchar(4),year(ES_LAST_WORKING_DATE))
,L.LOCATION_NAME
)a
WHERE A.MonthYearID IS NOT NULL
group by a.MonthYearID,a.[Month-Year],A.LOCATION_NAME
)
SELECT c1.*,Running
FROM CTE c1
CROSS APPLY (SELECT SUM(Count_of_joined_Emp) - SUM(Count_of_Exit_emp)+1147 AS Running
FROM CTE
WHERE MonthYearID <=c1.MonthYearID)c2
ORDER BY MonthYearID DESC
by using above query I am getting location wise breakup of exit and joinee.
See I am looking this query for dashboard
suppose in dec 2012 my headcount is 1200. i need location wise breakup of 1200 in runnable column.
like mumbai-400,
chennai-400,
ahmedaba-400
Please suggest the some logic
thankx in advance
Vipin jha
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/09/2013 : 01:32:29
|
make last select like
...
SELECT c1.*,Running
FROM CTE c1
CROSS APPLY (SELECT SUM(Count_of_joined_Emp) - SUM(Count_of_Exit_emp)+1147 AS Running
FROM CTE
WHERE MonthYearID <=c1.MonthYearID
AND LOCATION_NAME = c1.LOCATION_NAME)c2
ORDER BY MonthYearID DESC
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
vipin_jha123
Starting Member
26 Posts |
Posted - 01/09/2013 : 01:38:48
|
Hi Vikash , After chnage suggested by you I am not getting data as per my requirment.
I should get in ruiining total
breakup of 1200 location wise, forgot the exit and joinee
regards,
Vipin jha |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/09/2013 : 03:06:37
|
can you show data sample and then explain your output?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
vipin_jha123
Starting Member
26 Posts |
Posted - 01/09/2013 : 03:38:33
|
Hi Vikash,
Below is my latest query
;With CTE
AS
(
select a.MonthYearID,
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 CAST(DATENAME(yy,dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0)) + RIGHT('0' + CAST(MONTH(dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0)) AS varchar(2)),2) AS int) AS MonthYearID,
convert(varchar(4),EMP_DATEOFJOINING,100) + convert(varchar(4),year(EMP_DATEOFJOINING)) "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
LEFT JOIN ERM_LOCATION_MASTER L ON L.LOCATION_ID=erm_employee_master.EMP_LOCATION_ID
WHERE EMP_DATEOFJOINING>='01-mar-2012'
and EMP_STAFFID NOT IN ('1','US011','M0586','M0256','AA01','Admin01','ChennaiHelpdesk','Ctest','ITA01','MumbaiHelpdesk','709','Adre1','AH0001','M01','M010')
AND EMP_STAFFID NOT LIKE 'M0T%'
AND EMP_STAFFID NOT LIKE 'GUE%'
AND EMP_STAFFID NOT LIKE 'KUM%'
AND EMP_STAFFID NOT LIKE 'MT%'
AND EMP_STAFFID NOT LIKE 'AT%'
AND EMP_STAFFID NOT LIKE 'CT%'
AND EMP_STAFFID NOT LIKE 'T%'
AND EMP_STAFFID NOT LIKE 'IT H%'
AND Isnull(ES_COMMENTS,'') Not Like 'Lost%'
group by dateadd(month, datediff(month, 0, EMP_DATEOFJOINING), 0),convert(varchar(4),EMP_DATEOFJOINING,100) + convert(varchar(4),year(EMP_DATEOFJOINING))
UNION ALL
select CAST(DATENAME(yy,dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0)) + RIGHT('0' + CAST(MONTH(dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0)) AS varchar(2)),2) AS int),
convert(varchar(4),ES_LAST_WORKING_DATE,100) + convert(varchar(4),year(ES_LAST_WORKING_DATE)) "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
LEFT JOIN ERM_LOCATION_MASTER L ON L.LOCATION_ID=erm_employee_master.EMP_LOCATION_ID
where ERM_EMPLOYEE_MASTER.EMP_ISACTIVE='0' and ES_LAST_WORKING_DATE>='01-mar-2012'
group by dateadd(month, datediff(month, 0, ES_LAST_WORKING_DATE), 0),convert(varchar(4),ES_LAST_WORKING_DATE,100) + convert(varchar(4),year(ES_LAST_WORKING_DATE))
)a
WHERE A.MonthYearID IS NOT NULL
group by a.MonthYearID,a.[Month-Year]
)
SELECT c1.*,Running
FROM CTE c1
CROSS APPLY (SELECT SUM(Count_of_joined_Emp) - SUM(Count_of_Exit_emp)+1147 AS Running
FROM CTE
WHERE MonthYearID <=c1.MonthYearID)c2
ORDER BY MonthYearID DESC
Out put is :-
MonthYearID Year_Month_of_joinee_Exit Count_of_joined_Emp Count_of_Exit_emp Running
201301 Jan 2013 12 0 1086
201212 Dec 2012 67 42 1074
201211 Nov 2012 70 65 1049
201210 Oct 2012 24 87 1044
201209 Sep 2012 41 46 1107
201208 Aug 2012 64 58 1112
201207 Jul 2012 38 72 1106
201206 Jun 2012 36 64 1140
201205 May 2012 68 53 1168
201204 Apr 2012 60 68 1153
201203 Mar 2012 107 93 1161
Now if you see the running total value .
I am looking a breakup of Runing value
for example.
suppose in jan 2013 1086 employee is there . then I need to have breakup of 1086 location wise.
like mumbai have 400
chennai have 300
delhi have 250
ahmedabada 136
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 01/09/2013 : 04:26:07
|
are location values static?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
vipin_jha123
Starting Member
26 Posts |
Posted - 01/09/2013 : 04:30:57
|
| no it willl come from Location_master table |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
|
| |
Topic |
|
|
|
calculate Running Total of two column
