locked
How to calculate the number of employees in each month RRS feed

  • Question

  • Hi All,

    I have a table which has name,Speciality,start date and end date. So each person may have 1/more rows .They will have more if they change their specialities. For example if you look at the data below.

    AdjusterName Specialty Datestart DateEnd Test Inside Property 2009-08-29 2010-07-31 Test Management 2010-08-01 2012-07-31

    If we see at the data above Test has 2 rows because he changed his specialty in the middle.My requirement is to calculate the total number of employees in each month for last 2 years in each speciality. For example if we look at the example above, Test was in Inside property from 2009 Aug to 2010 Aug but if i use just the date start and take the month for each adjuster it gives me the number of adjusters started in that year and month but what i want is Test should be counted in all the months for Inside property until 2010 07 month. 

    Which means i want to have the total number of adjusters present by each speciality for each month of last 2 years .

    Can someone please help me with any suggestions on this?Please let me know if i am unclear or if you have any questions.

    Thanks.

    Wednesday, June 17, 2015 5:54 PM

Answers

  • try this

    CREATE TABLE ##Speciality
    ( AdjusterName VARCHAR(30)
     ,Speciality VARCHAR(50)
     ,DateStart DATE
     ,DateEnd DATE 
    )
    
    INSERT INTO ##Speciality
            ( AdjusterName ,
              Speciality ,
              DateStart ,
              DateEnd
            )
    VALUES  ( 'Test' , -- AdjusterName - varchar(30)
              'InsideProperty' , -- Speciality - varchar(50)
              '20090829' , -- DateStart - date
              '20100731'  -- DateEnd - date
            ),
    		( 'Test' , -- AdjusterName - varchar(30)
              'Management' , -- Speciality - varchar(50)
              '20100801' , -- DateStart - date
              '20120731'  -- DateEnd - date
            )
    
    
    ;WITH cte AS (
    SELECT AdjusterName
    	  ,Speciality
    	  ,DateStart
    	  ,DateEnd
    	  ,DSC.CalendarMonthName
    	  ,DSC.CalendarYear
    	  ,1 "TotalCount"  
    FROM ##Speciality
    	INNER JOIN dbo.DimStandardCalendar DSC
    			ON DSC.CalendarDate BETWEEN DateStart AND DateEnd
    GROUP BY AdjusterName, Speciality, DateStart, DateEnd,DSC.CalendarMonthName, DSC.CalendarYear
    )
    SELECT Speciality, CalendarYear, CalendarMonthName, SUM(TotalCount) "TotalCount"
    FROM cte
    GROUP BY Speciality, CalendarYear, CalendarMonthName

    This will work with any standard calendar table. 

    let me know if any questions.


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.

    • Marked as answer by SqlDev12 Wednesday, June 17, 2015 7:32 PM
    Wednesday, June 17, 2015 6:24 PM

All replies

  • try this

    CREATE TABLE ##Speciality
    ( AdjusterName VARCHAR(30)
     ,Speciality VARCHAR(50)
     ,DateStart DATE
     ,DateEnd DATE 
    )
    
    INSERT INTO ##Speciality
            ( AdjusterName ,
              Speciality ,
              DateStart ,
              DateEnd
            )
    VALUES  ( 'Test' , -- AdjusterName - varchar(30)
              'InsideProperty' , -- Speciality - varchar(50)
              '20090829' , -- DateStart - date
              '20100731'  -- DateEnd - date
            ),
    		( 'Test' , -- AdjusterName - varchar(30)
              'Management' , -- Speciality - varchar(50)
              '20100801' , -- DateStart - date
              '20120731'  -- DateEnd - date
            )
    
    
    ;WITH cte AS (
    SELECT AdjusterName
    	  ,Speciality
    	  ,DateStart
    	  ,DateEnd
    	  ,DSC.CalendarMonthName
    	  ,DSC.CalendarYear
    	  ,1 "TotalCount"  
    FROM ##Speciality
    	INNER JOIN dbo.DimStandardCalendar DSC
    			ON DSC.CalendarDate BETWEEN DateStart AND DateEnd
    GROUP BY AdjusterName, Speciality, DateStart, DateEnd,DSC.CalendarMonthName, DSC.CalendarYear
    )
    SELECT Speciality, CalendarYear, CalendarMonthName, SUM(TotalCount) "TotalCount"
    FROM cte
    GROUP BY Speciality, CalendarYear, CalendarMonthName

    This will work with any standard calendar table. 

    let me know if any questions.


    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.

    • Marked as answer by SqlDev12 Wednesday, June 17, 2015 7:32 PM
    Wednesday, June 17, 2015 6:24 PM
  • Hi VS_SQL,

    This is what exactly what i was looking for except some minor corrections which i made. Thanks a lot for your help on this.

    Wednesday, June 17, 2015 7:32 PM