CODEWITHSUNDEEP
sqlsql-serverssms
Jack Wright
Jack Wright

Reputation: 71

SQL: WHERE check on multiple columns (Where only one has to be not null)

Okay,

So I have a report as follows:

WITH   StudentCTEm ([Person Code], [Course Instance Maths], [Course Occurrence Maths], [Course Title], [ULN], [Main Aim])
AS     (SELECT [Person Code],
               [Course Instance],
               [Course Occurrence],
               [Course Title],
               [ULN],
               [Main Aim]
        FROM   EBSWarehouse.dbo.StudentEnrolment cte
        WHERE  cte.[Progress Status] = 'A' AND cte.[Course Instance] LIKE '%REGM%' AND cte.[Course Occurrence] LIKE '1920%'),
StudentCTEe ([Person Code], [Course Instance English], [Course Occurrence English], [Course Title], [ULN], [Main Aim])
AS     (SELECT [Person Code],
               [Course Instance],
               [Course Occurrence],
               [Course Title],
               [ULN],
               [Main Aim]
        FROM   EBSWarehouse.dbo.StudentEnrolment cte
        WHERE  cte.[Progress Status] = 'A' AND cte.[Course Instance] LIKE '%REGE%' AND cte.[Course Occurrence] LIKE '1920%'),
StudentCTEIT ([Person Code], [Course Instance IT], [Course Occurrence IT], [Course Title], [ULN], [Main Aim])
AS     (SELECT [Person Code],
               [Course Instance],
               [Course Occurrence],
               [Course Title],
               [ULN],
               [Main Aim]
        FROM   EBSWarehouse.dbo.StudentEnrolment cte
        WHERE  cte.[Progress Status] = 'A' AND cte.[Course Instance] LIKE '%REGIT%' AND cte.[Course Occurrence] LIKE '1920%'),
pcsCTEfsM ([Learning Aim Ref Maths], [Learning Aim Desc Maths], [PICS ID], [Funding End])
AS     (SELECT [Learning Aim Ref],
               [Learning Aim Desc],
               [PICS ID],
               [Funding End]
        FROM   dbo.ILRComponentAim cte
        WHERE [Learning Aim Desc] like '%Functional Skills qualification in Mathematics%'),
pcsCTEfsE ([Learning Aim Ref English], [Learning Aim Desc English], [PICS ID], [Funding End])
AS     (SELECT [Learning Aim Ref],
               [Learning Aim Desc],
               [PICS ID],
               [Funding End]
        FROM   dbo.ILRComponentAim cte
        WHERE [Learning Aim Desc] like '%Functional Skills qualification in English%'),
pcsCTEfsIT ([Learning Aim Ref IT], [Learning Aim Desc IT], [PICS ID], [Funding End])
AS     (SELECT [Learning Aim Ref],
               [Learning Aim Desc],
               [PICS ID],
               [Funding End]
        FROM   dbo.ILRComponentAim cte
        WHERE [Learning Aim Desc] like '%Functional Skills qualification in Information%')


SELECT DISTINCT
s.[Person Code],
s.ULN,
s.Forenames,
s.Surname,
s.[Episode Start],
s.[Episode Exp End],
s.[Person Code],
icm.[Learning Aim Ref Maths],
ICm.[Learning Aim Desc Maths],
Sem.[Course Instance Maths],
Sem.[Course Occurrence Maths],
SEm.[Course Title],
ice.[Learning Aim Ref English],
ICe.[Learning Aim Desc English],
See.[Course Instance English],
See.[Course Occurrence English],
SEe.[Course Title],
icit.[Learning Aim Ref IT],
icit.[Learning Aim Desc IT],
Seit.[Course Instance IT],
Seit.[Course Occurrence IT],
SEIT.[Course Title]






FROM dbo.Student s
LEFT JOIN           dbo.ILRProgAim AS p ON p.[PICS ID] = S.[PICS ID]
LEFT JOIN       pcsCTEfsM icm ON icm.[PICS ID] = s.[PICS ID]
LEFT JOIN       pcsCTEfsE ice ON ice.[PICS ID] = s.[PICS ID]
LEFT JOIN       pcsCTEfsIT icit ON icit.[PICS ID] = s.[PICS ID]
LEFT JOIN       StudentCTEe see ON see.[ULN] = s.[ULN]
LEFT JOIN       StudentCTEm sem ON sem.[ULN] = s.[ULN]
LEFT JOIN       StudentCTEIT seit ON seit.[ULN] = s.[ULN]
INNER JOIN EBSWarehouse.dbo.StudentEnrolment sen ON sen.[ULN] = s.ULN  

WHERE s.[Episode End] is null AND s.[Status] = 'L' AND [Learning Aim End] is null AND icm.[Funding End] is null AND sen.[Main Aim] <> 1 
ORDER BY s.[Person Code] ASC

Some of the records are showing null across the board for: 

    SELECT 
    icm.[Learning Aim Ref Maths],
    ICm.[Learning Aim Desc Maths],
    Sem.[Course Instance Maths],
    Sem.[Course Occurrence Maths],
    SEm.[Course Title],
    ice.[Learning Aim Ref English],
    ICe.[Learning Aim Desc English],
    See.[Course Instance English],
    See.[Course Occurrence English],
    SEe.[Course Title],
    icit.[Learning Aim Ref IT],
    icit.[Learning Aim Desc IT],
    Seit.[Course Instance IT],
    Seit.[Course Occurrence IT],
    SEIT.[Course Title]
    ...

Which is fine.

How can I put in my WHERE clause to only show the records where at least one of the above columns is not null

thanks

I know it's dirty report writing, but it's a quick job that needs doing

It's telling me to add more details:

If you want context Right, So this is basically mapping across various tables and another database to calculate exactly who is doing a Functional Skills course with their PICS (Internal Software) Apprenticeship and also What information we have on our Internal student management system.

I should add expected results are the same as actual results, For GDPR I can't really post the result set) but really all i need to be able to do is omit any records from the report which have 'null' across all the course options (Basically some people have withdrawn or completed their FS part but not their WBL course, and thus appear as a part of this report, I've tried omitting records using a completion status column, but some people are considered still active and have left since a long time ago - So that was a no go, So really my only options is this duuuurty little view I'm putting together

Upvotes: 0

Views: 47

Answers (1)

Max Zolotenko
Max Zolotenko

Reputation: 1132

COALESCE?

WHERE   COALESCE    (
                        icm.[Learning Aim Ref Maths],
                        ICm.[Learning Aim Desc Maths],
                        Sem.[Course Instance Maths],
                        Sem.[Course Occurrence Maths],
                        SEm.[Course Title],
                        ice.[Learning Aim Ref English],
                        ICe.[Learning Aim Desc English],
                        See.[Course Instance English],
                        See.[Course Occurrence English],
                        SEe.[Course Title],
                        icit.[Learning Aim Ref IT],
                        icit.[Learning Aim Desc IT],
                        Seit.[Course Instance IT],
                        Seit.[Course Occurrence IT],
                        SEIT.[Course Title]
                    ) IS NOT NULL

Upvotes: 4

Related Questions