-
Combining Results from two sql statements into one showing a new result.
Hi All,
I have the below two SQL statements
SELECT EMP_CLASS_1
,SUM(TOT_MI/60) AS [Absent hours]
FROM [TestData].[dbo].[Absenteeism and SLWP TCS Data]
WHERE CODE = 'MLSKU' OR CODE = 'UNPDAB' OR CODE = 'UNPADH'
GROUP BY
EMP_CLASS_1
ORDER BY EMP_CLASS_1
and the second
SELECT EMP_CLASS_1
,SUM(TOT_MI/60) AS [Schedule Hours]
FROM [TestData].[dbo].[Absenteeism and SLWP TCS Data]
WHERE code = 'attend'
GROUP BY EMP_CLASS_1
ORDER BY EMP_CLASS_1
What I am trying to do is take the results from the first statement and divide by the results of the second statement.
So far I am not getting any luck.
I am very green at this so please be patient with me.
Thanks in advance for any help you can provide.
-
After some more tinkering I came up with the below solution that gave me what I needed
SELECT EMP_CLASS_1
,SUM(IIF(CODE IN('MLSKU' ,'UNPDAB' , 'UNPADH' ,'PDAB'), TOT_MI, 0) /60) AS [Absent hours]
,SUM(IIF(CODE ='attend', TOT_MI, 0) /60) AS [Schedule Hours]
,[Absent%] = SUM( IIF( CODE IN('MLSKU' ,'UNPDAB' , 'UNPADH' , 'PDAB'), TOT_MI, 0) /60)
/ SUM( IIF( CODE ='attend', TOT_MI, 0) /60) * 100
FROM [TestData].[dbo].[Absenteeism and SLWP TCS Data]
WHERE CODE IN('MLSKU' ,'UNPDAB' , 'UNPADH', 'PDAB' ,'attend')
GROUP BY EMP_CLASS_1
ORDER BY EMP_CLASS_1
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules