Conditional count

Question

Emplid FunctionId Count

1------- 2

1 ------ 3 ---------- 1

1 ------ 4 ---------- 2

1 ------ 4

1 ------ 5 ---------- 3

1 ------ 6 ---------- 4

1 ------ 3 ---------- 5

2 ------ 3

2 ------ 3

2 ------ 1 ---------- 1

2 ------ 2 ---------- 2

H&R is looking for a measure to count the flexibility/mobility in the company.

When an Employee is changing from job/function a FunctionID is stored in the DWH empl dim. See the example above how this table looks like (6000 employee records with lots of mutations as wel. So I need a count only when an employee is going to do something else (another function). The example above is showing you how the count output should be.

How can do it with T-SqL or in a SSIS package (foreach loop ?)

Hope the light is going to shine again.

Hope to hear from you.

Regards from Amsterdam,

Arthur

Answer 1

Hi Arthur,

is the functionid increased always by one ?

If yes, just need to calculate the difference between the min and the max of functionid  grouped by an EmployeeId.

Else a count on distinct value of functionid  for an employeeid.

 

Regards.

 

 

Answer 2

Hi Arthur,

You can use an aggregate query in combination with a derived query. The derived query will retrieve unique Emplid/FunctionId combinations, then the aggregate query will do a count by Emplid, removing one (first function doesn't count):

SELECT t.Emplid,
       COUNT(*) - 1
FROM (SELECT Emplid, FunctionId
      FROM TableName
      GROUP BY Emlid, FunctionId) AS t
GROUP BY t.Emplid

Hope this helps.

Cheers, Fokko

---

ALM/TFS Consultant, Software Developer C#.NET, SQL Server specialist at Delta-N BV (http://www.delta-n.nl)

Answer 3

I don't particulary like this and I feel like I've done something wrong or missed something, but unfortunately I have to go; sorry for just dumping this:

declare @test table
( recId int identity, emplId int, FunctionId int)
insert into @test
select 1, 2 union all select 1, 3 union all
select 1, 4 union all select 1, 4 union all
select 1, 5 union all select 1, 6 union all 
select 1, 3 union all
select 2, 3 union all select 2, 3 union all
select 2, 1 union all select 2, 2

;with cte as
( select 
    *,
    row_number() over
    ( partition by emplId
      order by recId
    ) as Rn
  from @test
), cte2 as
( select 
    a.RecId,
    a.emplId,
    a.functionId,
    a.Rn,
    case when b.emplId is null then 0 else 1 
    end as empGroup,
    row_Number() over
    ( partition by a.emplId, case when b.emplId is null then 0 else 1 end
      order by a.rn
    ) as Raw_Count
  from cte a
  left join cte b
    on b.emplId = a.emplId
   and b.functionId = a.functionId
   and b.rn = a.rn - 1
)
select
  emplId,
  functionId,
  case when empGroup = 0 then Raw_Count 
  end as [Count] 
from cte2
order by emplId, Rn

/* -------- Output: --------
emplId      functionId  Count
----------- ----------- --------------------
1           2           1
1           3           2
1           4           3
1           4           NULL
1           5           4
1           6           5
1           3           6
2           3           1
2           3           NULL
2           1           2
2           2           3

(11 row(s) affected)
*/

.

Answer 4

I guess I misunderstood this one, when re-reading carefully. My query just outputs the total function change count per employee... Looks like Kent has a working solution.

Cheers,

Fokko

---

ALM/TFS Consultant, Software Developer C#.NET, SQL Server specialist at Delta-N BV (http://www.delta-n.nl)

Answer 5

Assuming there is an order criteria in DWH table?

Answer 6

create table #t (empid int, functid int)

insert into #t values (1,2)

insert into #t values (1,3)

insert into #t values (1,4)

insert into #t values (1,4)

insert into #t values (1,5)

insert into #t values (1,6)

insert into #t values (1,3)

insert into #t values (2,3)

insert into #t values (2,3)

insert into #t values (2,1)

insert into #t values (2,2)

with cte

as

(

select *,row_number() over (partition by empid order by empid) rn

 from #t

),cte1

as

( select cte.*,c.functid f,case when cte.functid<>c.functid then 1 else 0 end col

 from cte join cte c on cte.empid=c.empid

and cte.rn=c.rn-1

) select empid,functid,case when row_number() over(partition by empid,col order by empid,functid)=1 then 0

  else row_number() over(partition by empid,col order by empid,functid)-1 end cn

 

 from cte1

order by empid,functid

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/