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)
 calculate Running Total of two column

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_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

52326 Posts

Posted - 2013-01-08 : 03:44:55
[code]
;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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 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/

Go to Top of Page

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 total
breakup of 1200 location wise, forgot the exit and joinee

regards,
Vipin jha
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

vipin_jha123
Starting Member

31 Posts

Posted - 2013-01-09 : 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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 04:26:07
are location values static?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

vipin_jha123
Starting Member

31 Posts

Posted - 2013-01-09 : 04:30:57
no it willl come from Location_master table
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-09 : 04:34:16
then see this

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -