SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 calculate Running Total of two column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vipin_jha123
Starting Member

28 Posts

Posted - 01/08/2013 :  02:36:14  Show Profile  Reply with Quote
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
52325 Posts

Posted - 01/08/2013 :  03:44:55  Show Profile  Reply with Quote

;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/

Go to Top of Page

vipin_jha123
Starting Member

28 Posts

Posted - 01/09/2013 :  01:06:13  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/09/2013 :  01:32:29  Show Profile  Reply with Quote
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

28 Posts

Posted - 01/09/2013 :  01:38:48  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/09/2013 :  03:06:37  Show Profile  Reply with Quote
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

28 Posts

Posted - 01/09/2013 :  03:38:33  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/09/2013 :  04:26:07  Show Profile  Reply with Quote
are location values static?

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

Go to Top of Page

vipin_jha123
Starting Member

28 Posts

Posted - 01/09/2013 :  04:30:57  Show Profile  Reply with Quote
no it willl come from Location_master table
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/09/2013 :  04:34:16  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000