Select and Grouping Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Select and Grouping Question

Author

Topic

Brian C
Starting Member

10 Posts

Posted - 2008-12-04 : 18:10:40

Hey Everyone,

I was hoping someone can help with an SQL logic problem I can't seem to get past. I am using SQL 2005. I hope this is enough info.

I am returning

IndividualID = Number
Period 1,2,3,4
TrainingRequiredFlag = can be NULL Y or N
PassFailIndicator = can be NULL T or F

Each individual can have multiple entries in our table. For example a person that is a 4 period course will have 4 entries and depending on if they need technical training they will have a Y or N with a corresponding passfailindicator. Below is an example

IndividualID Period TrainingRequiredFlag PassFailIndicator
........1.........1.............N........................NULL
........1.........2.............Y........................T
........1.........3.............Y........................T
........1.........4.............Y........................T

Now what I need to do is select only the IndividualIDs that have all TrainingRequiredFlags = Y and PassFailIndicator = T, If a TrainingRequiredFlag has a NULL or F then do not include. I want to ignore the TrainingRequiredFlag = N as there is no training required. But when I do my select I can only seem to bring back only records where all records for a given IndividualID are Y.

I have a basic select with the following group by and having clauses:

...
group by IndividualID
having TrainingRequiredFlag ='Y' and PassFailIndicator = 'T'

But I don't know how to return the IndividualID where they have either TrainingRequiredFlag ='N' or (TrainingRequiredFlag ='Y' and PassFailIndicator = 'T') for the grouping.

As an extra piece, I would not want this person returned because they have outstanding technicaltraining in period 4.

IndividualID Period TrainingRequiredFlag PassFailIndicator
........2.........1.............N........................NULL
........2.........2.............Y........................T
........2.........3.............Y........................T
........2.........4.............Y........................N

Can anyone please shed some light on this for me? I have spent a couple of days on this already and am stumped,

Thanks for any help

Brian

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-12-04 : 21:16:46

[code]SELECT DISTINCT IndividualID
FROM yourtable Main
WHERE NOT EXISTS (SELECT *
FROM yourtable Filter
WHERE Main.IndividualID = Filter.IndividualID
AND (Filter.TrainingRequiredFlag = 'N'
OR Filter.TrainingRequiredFlag IS NULL
OR Filter.PassFailIndicator = 'F'
OR Filter.PassFailIndicator IS NULL))
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 02:52:28

[code]SELECT IndividualID
FROM yourtable
GROUP BY IndividualID
HAVING COUNT(DISTINCT Period)=SUM(CASE WHEN TrainingRequiredFlag ='Y' THEN 1 ELSE 0 END)
AND COUNT(DISTINCT Period)=SUM(CASE WHEN PassFailIndicator='T' THEN 1 ELSE 0 END)[/code]

Brian C
Starting Member

10 Posts

Posted - 2008-12-05 : 11:31:00

quote:

Originally posted by snSQL

SELECT DISTINCT IndividualID
FROM yourtable Main
WHERE NOT EXISTS (SELECT * 
    FROM yourtable Filter 
    WHERE Main.IndividualID = Filter.IndividualID 
        AND (Filter.TrainingRequiredFlag = 'N' 
            OR Filter.TrainingRequiredFlag IS NULL
            OR Filter.PassFailIndicator = 'F'
            OR Filter.PassFailIndicator IS NULL))

Thanks for the quick reply, I did try this solution last night but valid records were lost in the resultset. A situation where the TrainingRequiredFlag = 'N' and PassFailIndicator = 'F' fails to show up but is a valid result as we want only records where

1. TrainingRequiredFlag = 'N' regardless of PassFailIndicator

2. TrainingRequiredFlag = 'Y', PassFailIndicator = 'T'

I have tried but failed to combine the above 2 logic rules in a where clause but kept getting errors back. The logic has to be able to check for both of the above conditions within the same group of IndividualIDs

Here is a typical situation. There are 3 records that required TechnicalTraining and they have been passed as well as 1 period where no TechnicalTraining is required. This IndividualID of 1 does qualify as NoTechnicalTraining Required and I want to include it in the resulst set.

IndividualID Period TrainingRequiredFlag PassFailIndicator
........1.........1.............N....................F
........1.........2.............Y....................T
........1.........3.............Y....................T
........1.........4.............Y....................T

I can get the code to work when I am looking for all Ys or all Ns but not when I try to combine the two. Is that any clearer. I know it can be difficult to get ideas across in writing.

Thanks for any help

Brian

Brian C
Starting Member

10 Posts

Posted - 2008-12-05 : 11:33:18

quote:

Originally posted by visakh16

SELECT IndividualID
FROM yourtable 
GROUP BY IndividualID 
HAVING COUNT(DISTINCT Period)=SUM(CASE WHEN TrainingRequiredFlag ='Y' THEN 1 ELSE 0 END)
AND COUNT(DISTINCT Period)=SUM(CASE WHEN PassFailIndicator='T' THEN 1 ELSE 0 END)

This solution will only work when all the TrainingRequiredFlags are Y and PassFailIndicator = T. I need to also include groups of IndividualIDs that also have 1 or more periods where TrainingRequiredFlag = N regardless of PassFailIndicator.

Thats the problem that I can't get past.

Thanks

Brian

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 12:05:41

[code]SELECT IndividualID
FROM yourtable
GROUP BY IndividualID
HAVING (COUNT(DISTINCT Period)=SUM(CASE WHEN TrainingRequiredFlag ='Y' AND PassFailIndicator='T' THEN 1 ELSE 0 END)
OR COUNT(DISTINCT Period)=SUM(CASE WHEN TrainingRequiredFlag ='Y' AND PassFailIndicator='T' THEN 1 ELSE 0 END)+SUM(CASE WHEN TrainingRequiredFlag ='N' THEN 1 ELSE 0 END))
[/code]

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-12-05 : 19:25:45

It should be simpler then, although I don't know if you ever said what should happen if TrainingRequiredFlag is NULL. I assumed that means you want it included.

SELECT DISTINCT IndividualID
FROM yourtable Main
WHERE NOT EXISTS (SELECT * 
    FROM yourtable Filter 
    WHERE Main.IndividualID = Filter.IndividualID 
        AND (Filter.TrainingRequiredFlag = 'Y' 
            AND (Filter.PassFailIndicator = 'F' OR Filter.PassFailIndicator IS NULL))
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-06 : 00:02:01

quote:
Originally posted by snSQL

It should be simpler then, although I don't know if you ever said what should happen if TrainingRequiredFlag is NULL. I assumed that means you want it included.
SELECT DISTINCT IndividualID
FROM yourtable Main
WHERE NOT EXISTS (SELECT *
FROM yourtable Filter
WHERE Main.IndividualID = Filter.IndividualID
AND ((Filter.TrainingRequiredFlag = 'N' OR Filter.TrainingRequiredFlag IS NULL) OR
(Filter. TrainingRequiredFlag = 'Y' AND (Filter.PassFailIndicator IS NULL OR Filter.PassFailIndicator ='F'))
)

shouldnt it be above?

Brian C
Starting Member

10 Posts

Posted - 2008-12-06 : 13:44:01

quote:
Originally posted by visakh16

quote:
Originally posted by snSQL

It should be simpler then, although I don't know if you ever said what should happen if TrainingRequiredFlag is NULL. I assumed that means you want it included.
SELECT DISTINCT IndividualID
FROM yourtable Main
WHERE NOT EXISTS (SELECT *
FROM yourtable Filter
WHERE Main.IndividualID = Filter.IndividualID
AND ((Filter.TrainingRequiredFlag = 'N' OR Filter.TrainingRequiredFlag IS NULL) OR
(Filter. TrainingRequiredFlag = 'Y' AND (Filter.PassFailIndicator IS NULL OR Filter.PassFailIndicator ='F'))
)

shouldnt it be above?

I actually finally got it to work with minor alteration:

******************************

HAVING (COUNT( Period)=SUM(CASE WHEN TrainingRequiredFlag ='Y' AND PassFailIndicator='T' THEN 1 ELSE 0 END)+SUM(CASE WHEN TrainingRequiredFlag not in ('Y','N') THEN 1 ELSE 0 END)

OR COUNT( Period)=SUM(CASE WHEN TrainingRequiredFlag ='Y' AND PassFailIndicator='T' THEN 1 ELSE 0 END)+SUM(CASE WHEN TrainingRequiredFlag ='N' AND PassFailIndicator='F' THEN 1 ELSE 0 END)+SUM(CASE WHEN TrainingRequiredFlag ='N' AND PassFailIndicator is null THEN 1 ELSE 0 END))

*******************************

I just added some more conditions and it seemed to catch everything then.

I tested about the bottom 300 records and found that it brought back records when it qualified based on the original logic.

Regarding the simpler code, I would have thought it would have worked but It returned no records in the result set. I was trying the same idea before and couldn't get it to work. I will tinker around with it and see if it can be coaxed into working.

Still I want to thank you for helping me out. I just found this board and thought what the heck its worth a try. I am pretty good with sql in general but still need some experience on the higher end stuff. I was also introduced to ;with as I am using SQL 2005 but I have not done any reading on the changes which I should do.

Thanks again for your help in this problem, its been a huge help and I hope I can give back to the forum sometime,

Brian

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-12-06 : 20:20:42

quote:
Regarding the simpler code, I would have thought it would have worked but It returned no records in the result set. I was trying the same idea before and couldn't get it to work. I will tinker around with it and see if it can be coaxed into working.

Does this return every single IndividualID? If so then your criteria are wrong and there is at least one case for every individual where training is required and not passed. If not, then the last version of the query I gave you should return all individuals not returned by this query.

SELECT DISTINCT IndividualID
    FROM yourtable 
    WHERE TrainingRequiredFlag = 'Y' 
            AND (Filter.PassFailIndicator = 'F' OR Filter.PassFailIndicator IS NULL)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources