Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  SQL Server 2008 Forums  Transact-SQL (2008)  calculate Running Total of two column Reply to Topic  Printer Friendly
Author  Topic

vipin_jha123
Starting Member

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

India
52326 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 MVPhttp://visakhm.blogspot.com/

vipin_jha123
Starting Member

31 Posts

visakh16
Very Important crosS Applying yaK Herder

India
52326 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 MVPhttp://visakhm.blogspot.com/

vipin_jha123
Starting Member

31 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 totalbreakup of 1200 location wise, forgot the exit and joineeregards,Vipin jha

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

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

India
52326 Posts

 Posted - 01/09/2013 :  04:26:07 are location values static?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/

vipin_jha123
Starting Member

31 Posts

 Posted - 01/09/2013 :  04:30:57 no it willl come from Location_master table

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

 Posted - 01/09/2013 :  04:34:16 then see thishttp://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC