CODEWITHSUNDEEP
mysqlsqlpowerbidax
Sandeep Mohanty
Sandeep Mohanty

Reputation: 1552

Sql equivalent of this dax

I have a table in power bi which uses the below dax:

need inputs on SQL Query.

powerbi table name: demo,

employee is another table and demo table uses it and KEY IS ALREADY CREATED IN EMPLOYEE TABLE BY CONCATINATING employee'[EMP_CODE]&'employee'[dob]

key=SUMMARIZE( FILTER('employee','employee'[emp_STS]="working"),'employee'[EMP_CODE],'employee'[dob],"Key",'employee'[EMP_CODE]&'employee'[dob])

WORKEDHOURS = CALCULATE(MAX('Employee'[workhrs]), 'Empployee'[NAME]<>BLANK())

employeecode=SUMMARIZE( FILTER('employee','employee'[emp_STS]="working"),'employee'[EMP_CODE],'employee'[dob],"Key",'employee'[EMP_CODE]&'employee'[dob])

employeedob=SUMMARIZE( FILTER('employee','employee'[emp_STS]="working"),'employee'[EMP_CODE],'employee'[dob],"Key",'employee'[EMP_CODE]&'employee'[dob])

corresponding SQL Query I have tried :

select KEY,
CASE
WHEN NAME IS NOT NULL THEN MAX(HOURS) 
END AS WORKEDHOURS,
EMP_CODE,
EMP_DOB
from employee where EMP_STS='WORKING' GROUP BY KEY,QC_NAME,DOB,EMP_CODE

Upvotes: 1

Views: 117

Answers (1)

Mik
Mik

Reputation: 2103

Please, check this part and come with your feedback.

WITH
e1 AS (
    SELECT 
        CONCAT([EMP_CODE],[dob]) AS [Key]
        ,EMP_CODE
        ,EMP_DOB
    FROM employee 
    WHERE [emp_STS]='working'
    GROUP BY [EMP_CODE],[dob]
    )
,e2 AS(
    SELECT 
        CONCAT([EMP_CODE],[dob]) AS [Key]
        ,MAX([workhrs]) AS [WorkedHours]
        FROM employee 
    WHERE [NAME] IS NOT NULL -- ,[emp_STS]='working' ?
    GROUP BY [EMP_CODE] ,[dob]
    )

SELECT     
    e1.[Key]
    ,e1.[EMP_CODE]
    ,e1.[EMP_DOB]
    ,e2.[WorkedHours]
FROM e1
LEFT JOIN e2
ON e1.[Key]=e2.[Key]

Upvotes: 1

Related Questions