count number of month

[bbeeharry]

i have a situation where i must count the number of month an employee is active in a year :
Example 1: from Jan to May Active, June to July on leave, August to Sept
Active, so 7 months
Example 2: from Jan to May Active, from June to Sept Terminated..so 5
Months
or the employee can be active at any moment in the year and i must count it all.
can you help me in writing this sql?

thks

 

[LNBruno]

Sample data, please?

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[bbeeharry]

Sample data :

Ebase table : ebid,firstname,lastname, adress,ect..
Employee table:eeideb(foreign key) ,datebeg,dateend,status

when an employee is active, dateend is null
and status ='active'

the query will be run yearly from
Jan. - Dec.


is it clearer for you now?

 

[bborissov]

Something like that:

[COLOR=blue]DECLARE[/color] @SomeYear [COLOR=blue]int[/color]
[COLOR=blue]SET[/color] @SomeYear = 2007
[COLOR=blue]SELECT[/color] eBase.*,
       Tbl1.MntActive
[COLOR=blue]FROM[/color] eBase
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] eeideb, [COLOR=#FF00FF]COUNT[/color](*) [COLOR=blue]AS[/color] MntActive
                   [COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]DISTINCT[/color] eeideb, 
                                         [COLOR=#FF00FF]MONTH[/color](datebeg) [COLOR=blue]AS[/color] Mnt,
                                         [COLOR=#FF00FF]YEAR[/color](datebeg) [COLOR=blue]AS[/color] yr
                          [COLOR=blue]FROM[/color] Employee 
                          [COLOR=blue]WHERE[/color] [COLOR=#FF00FF]YEAR[/color](datebeg) = @SomeYear AND
                                Status        = [COLOR=red]'active'[/color]  AND
                                dateEnd [COLOR=blue]IS[/color] NULL) Tbl1
[COLOR=blue]ON[/color] eBase.ebid = Tbl1.eeideb

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

Are employees always active or on vacation for whole month periods?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[bbeeharry]

yes for whole month

 

[ESquared]

Borislav, what if an employee is active for a whole year and has no begin date or end date in that year? Instead of listing that year with 12 active months your query will just skip it.

Start with a numbers table. I'll call this Numbers0 since it is convenient for it to start with 0 instead of 1:

CREATE TABLE Numbers0 (Num int identity(0, 1) PRIMARY KEY CLUSTERED)
SET NOCOUNT ON
INSERT Numbers0 DEFAULT VALUES
WHILE Scope_Identity() < 9999 INSERT Numbers0 DEFAULT VALUES

And now a query to use that table:

SELECT
   B.*,
   Y2.ActiveYear,
   Y2.ActiveMonthCount,
   InactiveMonthCount = 12 - Y2.ActiveMonthCount
FROM
   eBase B
   INNER JOIN (
      SELECT
         eeideb,
         ActiveYear,
         ActiveMonthCount = Sum(DateDiff(mm,
            CASE WHEN Year(E.datebeg) = ActiveYear THEN E.datebeg ELSE DateAdd(yy, ActiveYear - 2000, '1/1/2000') END,
            CASE WHEN Year(E.dateend) = ActiveYear THEN E.dateend ELSE DateAdd(yy, ActiveYear - 2000, '12/1/2000') END
         ) + 1)
      FROM
         (
            SELECT ActiveYear = Year(GetDate()) - Num
            FROM Numbers0
            WHERE Num <= (SELECT Year(GetDate()) - Min(Year   (datebeg) FROM Employee)
         ) Y
         INNER JOIN Employee E ON Y.ActiveYear BETWEEN Year(E.datebeg) AND Year(E.dateend) AND E.Status = 'active'
      GROUP BY
         eeideb,
         ActiveYear
   ) Y2
   INNER JOIN eBase B ON Y2.eeideb = B.ebid

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[ESquared]

By the way, this query will skip years where an employee did not work at all. I'm guessing that's better than showing every employee that's ever been in the system as 0 each year.

If you need all past employees every year then I can tweak the query for you.

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]