count by each status on latest status sql server

Question

Count by status on latest plan in SSAS DAX
Hi Team,

create sql server query to generate expected output

Logic: We need to calculate count by each status on latest plan.
for example: employee (5245) is having two plans (8869,6988)
when we calculate status for accepted then we have to consider this empid becauase latest planid(8869) staus is accepted .i.e. this eid should consider status: ACCEPTED only because it has the latest planid.

Source Data

Output Expected

181739-image.png

Answer 1

Hi

Please check below code to see if it is helpful.

Define  
MEASURE table[AcceptedCount] =  
COUNTROWS (  
FILTER (  
SUMMARIZE ( table,table[EmployeeNumber],table[status]),  
table[status] = "ACCEPTED"  
))  
MEASURE table[EscalatedCount] =  
COUNTROWS (  
FILTER (  
SUMMARIZE ( table,table[EmployeeNumber],table[status]),  
table[status] = "ESCALATED"  
))  
MEASURE table[overall] =  
COUNTROWS (  
FILTER (  
SUMMARIZE ( table,table[EmployeeNumber],table[status]),  
table[status] in {"ESCALATED","ACCEPTED"}  
))  
VAR status_count =  
Filter(SUMMARIZECOLUMNS (  
table[unit],  
table[EmployeeNumber],  
table[status],  
"AcceptedCount_status", table[AcceptedCount],  
"EscalatedCount_status", table[EscalatedCount],  
"Overallcount",Calcualte(table[overall])  
), table[status]= "ACCEPTED")  
evaluate status_count  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Can you also provide sql query for the same?

Answer 3

Hi @ks ,

Check below sql query:

create table Source  
(unit varchar(100),  
eid int,  
planid int,  
status varchar(100))  
  
  
  
insert into Source values  
('CATEGORY',5245,8869,'ACCEPTED'),  
('CATEGORY',5245,6988,'ESCALATED'),  
('CHANNEL',7676,9898,'ESCALATED'),  
('CHANNEL',7676,9079,'ACCEPTED')  
  
  
  
;WITH CTE AS (  
SELECT A.* FROM Source A  
INNER JOIN (  
SELECT unit,EID,MAX(planid) MAX FROM Source  
GROUP BY unit,EID) B  
ON A.eid=B.eid AND A.planid=B.MAX)  
,CTE1 AS (  
SELECT UNIT,EID,status,CASE WHEN status='ACCEPTED' THEN 1 ELSE 0 END ACCEPTED,  
CASE WHEN status='ESCALATED' THEN 1 ELSE 0 END ESCALATED  
FROM CTE)  
SELECT *,ACCEPTED+ESCALATED OVERALLCOUNT FROM CTE1  

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.