What is the best way to get data only if attendance is 'C' and is a pass? I am getting data for C, E, W,X. for attendance column. Its mostly because of the duration type, H, D, W.
select t.LEARN_ID TRNG_ID
,pt.DURATION_NBR
,pt.ATTENDANCE
,pt.DURATION_TYPE
,Case
--when pt.DURATION_TYPE ='H' or pt.DURATION_TYPE ='D' or pt.DURATION_TYPE ='W' and pt.attendance = 'C' Then 'PASS'
when pt.DURATION_TYPE in('H' ,'D', 'W', '') and pt.attendance ='C' then 'PASS'--not in( 'I','E', 'N','W', 'X', 'S') Then 'PASS'
Thanks!
Pasi
Else 'FAIL'
End as 'Completed Session'
from SESSN_TBL t
inner join TRAINING pt on pt.SESSION_ID =t.SESSION_ID
Just add WHERE pt.attendance = 'C' AND pt.duration_type in ('H', 'D', 'W')
Assuming there can be other values for duration_type with attendance = 'C' that are not a PASS, then you have to include the full condition. If there are no other duration types - then you only need the attendance = 'C' (in fact - it wouldn't be needed in the CASE expression either).
If you want to be able to define PASS/FAIL and then filter based on PASS/FAIL and not on the specific conditions (in other words, this is just one pass condition and you are going to add other WHEN statements to define passing) - put the query into a CTE and then in the WHERE clause of the outer select you can select for PASS/FAIL.
with passfail
as (
select t.LEARN_ID TRNG_ID
,pt.DURATION_NBR
,pt.ATTENDANCE
,pt.DURATION_TYPE
,Case
--when pt.DURATION_TYPE ='H' or pt.DURATION_TYPE ='D' or pt.DURATION_TYPE ='W' and pt.attendance = 'C' Then 'PASS'
when pt.DURATION_TYPE in('H' ,'D', 'W', '') and pt.attendance ='C' then 'PASS'--not in( 'I','E', 'N','W', 'X', 'S') Then 'PASS'
Else 'FAIL'
End as 'Completed Session'
from SESSN_TBL t
inner join TRAINING pt on pt.SESSION_ID =t.SESSION_I
)
SELECT *
FROM passfail pf
WHERE pf.[Completed Session] = 'PASS' -- 'FAIL'
Jeff, I was using your suggestion with CTE ( added more columns and joined tables) and everything works good but as soon as I add companyID to the query I get no results, cant figure out why? I am adding it to AFTER WHERE clause:
WHERE pf.[Completed Session] = 'PASS' -- 'FAIL' and companyId in ('02','11')
with passfail
as (
select
pt.LEARN_ACT_ID TRNG_CLS_ID
,pt.SESSION_ID SESS_ID
,pt.emplid EMPNO
,pt.COURSE_TITLE TRNG_CLS_NM
,f.Descr LOC_NM
,pt.ATTENDANCE
,pt.PASS_IND
,t.SESSION_START_TIME STR_TM
,t.SESSION_START_DT
,t.SESSION_END_DT
,pt.emplid ,LA.CompanyId
,SF.FACILTATR_ID
,Case
when pt.DURATION_TYPE in ('H' ,'D', 'W', '') and pt.attendance ='C' then 'PASS'--
Else 'FAIL'
End as 'Completed Session'
,Case
When pt.DURATION_TYPE ='D' Then pt.DURATION_NBR * 8
When pt.DURATION_TYPE ='W' Then pt.DURATION_NBR * 40
End as 'Session Duration'
from SESSN_TBL t
inner join TRAINING pt on pt.SESSION_ID =t.SESSION_ID
inner join FACILITY_TBL f on f.facility_id = t.FACILITY_ID
inner join LEARN_ACT_TBL LA on LA.LEARN_ACT_ID = t.LEARN_ACT_ID
inner join SESSN_FACILTATR SF on SF.SESSION_ID =pt.SESSION_ID
)
SELECT *
FROM passfail pf
WHERE pf.[Completed Session] = 'PASS' -- 'FAIL' and companyId in ('02','11')
Not following? I removed the pass/fail from the Where clause: still the same? no rows
)
SELECT *
FROM passfail pf
--WHERE --pf.[Completed Session] = 'PASS' -- 'FAIL' WHERE companyId in ('02','11')
Yes, I do see company id from a table I joined but it looks like it wont pick up the company, when I leave the company out of filter, I get all the required data. I was trying to just build a regular query and join tables just like the one in CTE but I get so many records for just one employee. like I have one employee with only 434 records but when I run my other query without CTE I get about 200k records??? All I am trying to do is to get training records for each employees.
Pasi.
It sounds like you have a problem with the joins - and where you are looking for the company ID. In this query (with the CTE) - all you are doing with the CTE is encapsulating the PASS/FAIL logic so you can then filter on those that pass.
The problems you are having are not related to the CTE - rather they are related to the tables you are querying, the joins - and the available data in those tables.
