Combining Results from two sql statements into one showing a new result.

[spittingfire]

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.

[spittingfire]

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