SQLTeam.com | Weblogs | Forums

Get data only for specific value


#1

Hi,

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

image


#2

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'

#3

Thanks Jeff, just added WHERE pt.attendance = 'C' AND pt.duration_type in ('H', 'D', 'W') and that did it. :slight_smile:


#4

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')

Thanks.


Here is the modified query:

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')


#5

Doesn't look like you have any that pass for those company ID's. Remove the check for pass/fail - and see what results you get.


#6

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')


#7

That tells me you don't have any data for those company ID's. Try removing the filter for those companies and review what data is returned.


#8

Also - to simplify your code, change 'Complete Sessions' to CompletedSessions (one word). This way you don't have to quote the column name.


#9

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.


#10

will do thanks!


#11

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.


#12

Correct, I had to use another TBL for company ID and its working now.
Thanks for all the help! :fist_left: