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.
Author |
Topic |
vipin_jha123
Starting Member
31 Posts |
Posted - 2013-01-08 : 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_STAFFIDWHERE EMP_ISACTIVE='1'group by convert(varchar(4),EMP_DATEOFJOINING,100) + convert(varchar(4),year(EMP_DATEOFJOINING))UNION ALLselect 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_STAFFIDwhere 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)))agroup by a.[Month-Year])TABAorder by convert(datetime, '1-' + TABA.Year_Month_of_joinee_Exit) descoutput of above query isYear_Month joined_Emp Exit_empJan 2013 7 0Dec 2012 78 42Nov 2012 61 65Oct 2012 21 87Sep 2012 27 46Aug 2012 44 58Jul 2012 29 72Jun 2012 26 64May 2012 33 53Apr 2012 22 68Mar 2012 50 93Feb 2012 41 74Please help me in that .Thankx in advanceRegards,Vipin jha |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-08 : 03:44:55
|
[code];With CTEAS(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_STAFFIDWHERE 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 ALLselect 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_STAFFIDwhere 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)))agroup by a.MonthYearID,a.[Month-Year])SELECT c1.*,RunningFROM CTE c1CROSS APPLY (SELECT SUM(Count_of_joined_Emp) - SUM(Count_of_Exit_emp) AS Running FROM CTE WHERE MonthYearID <=c1.MonthYearID)c2[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
vipin_jha123
Starting Member
31 Posts |
Posted - 2013-01-09 : 01:06:13
|
Hi Vikash ,Thankx for your post , which is very usefull.I did some changes in your query;With CTEAS(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_IDWHERE 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_NAMEUNION ALLselect 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)aWHERE A.MonthYearID IS NOT NULL group by a.MonthYearID,a.[Month-Year],A.LOCATION_NAME)SELECT c1.*,RunningFROM CTE c1CROSS 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 DESCby using above query I am getting location wise breakup of exit and joinee.See I am looking this query for dashboardsuppose in dec 2012 my headcount is 1200. i need location wise breakup of 1200 in runnable column.like mumbai-400,chennai-400,ahmedaba-400Please suggest the some logicthankx in advanceVipin jha |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 01:32:29
|
make last select like...SELECT c1.*,RunningFROM CTE c1CROSS APPLY (SELECT SUM(Count_of_joined_Emp) - SUM(Count_of_Exit_emp)+1147 AS Running FROM CTEWHERE MonthYearID <=c1.MonthYearIDAND LOCATION_NAME = c1.LOCATION_NAME)c2ORDER BY MonthYearID DESC ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
vipin_jha123
Starting Member
31 Posts |
Posted - 2013-01-09 : 01:38:48
|
Hi Vikash , After chnage suggested by you I am not getting data as per my requirment.I should get in ruiining totalbreakup of 1200 location wise, forgot the exit and joineeregards,Vipin jha |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 03:06:37
|
can you show data sample and then explain your output?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
vipin_jha123
Starting Member
31 Posts |
Posted - 2013-01-09 : 03:38:33
|
Hi Vikash,Below is my latest query;With CTEAS(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_IDWHERE 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 ALLselect 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)))aWHERE A.MonthYearID IS NOT NULL group by a.MonthYearID,a.[Month-Year])SELECT c1.*,RunningFROM CTE c1CROSS 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 DESCOut put is :-MonthYearID Year_Month_of_joinee_Exit Count_of_joined_Emp Count_of_Exit_emp Running201301 Jan 2013 12 0 1086201212 Dec 2012 67 42 1074201211 Nov 2012 70 65 1049201210 Oct 2012 24 87 1044201209 Sep 2012 41 46 1107201208 Aug 2012 64 58 1112201207 Jul 2012 38 72 1106201206 Jun 2012 36 64 1140201205 May 2012 68 53 1168201204 Apr 2012 60 68 1153201203 Mar 2012 107 93 1161Now if you see the running total value .I am looking a breakup of Runing valuefor example.suppose in jan 2013 1086 employee is there . then I need to have breakup of 1086 location wise.like mumbai have 400chennai have 300delhi have 250ahmedabada 136 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-09 : 04:26:07
|
are location values static?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
vipin_jha123
Starting Member
31 Posts |
Posted - 2013-01-09 : 04:30:57
|
no it willl come from Location_master table |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|