count by each status on latest status sql server

ks 1 Reputation point
2022-03-10T09:22:12.907+00:00

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

181864-image.png

Output Expected

181865-image.png

181739-image.png

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,899 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
{count} votes

3 answers

Sort by: Most helpful
  1. ZoeHui-MSFT 33,626 Reputation points
    2022-03-11T03:04:27.977+00:00

    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.

    0 comments No comments

  2. ks 1 Reputation point
    2022-03-13T07:50:52.75+00:00

    Can you also provide sql query for the same?

    0 comments No comments

  3. ZoeHui-MSFT 33,626 Reputation points
    2022-03-14T02:48:14.8+00:00

    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  
    

    182557-image.png

    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.