Search code examples
sqlcasessms-2012

Add case Statement to large query to allow result set to be more dynamic

I have the following MS SQL query using SSMS 2012. I've been fine tuning and tweaking this for a while. You can learn more about it here.

CASE statement in monster query

WITH Data AS
(   SELECT b.ReportHeading1, 
            p.DisplayOrder, 
            p.MemberCode,
            m.PortfolioGroupCode as DistGroup,
            m.PortfolioGroupCode as DistCode,
            m.MemberCode as Packgroup,
            g.purpose,
            gg.purpose as purpose2,
            c.DeliveryName,
            p.PortfolioGroupID as pid,
            m.PortfolioGroupID as mid,
            convert(varchar(max),lb.value) as repset,
                        RowNumber = ROW_NUMBER() OVER(PARTITION BY m.portfoliogroupid, m.membercode ORDER BY p.DisplayOrder)
    FROM    [APXFirm].[AdvApp].[vPortfolioGroupMemberFlattened] p
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBase] b
                ON b.PortfolioBaseID = p.PortfolioGroupID
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroupMember] m
                 on m.MemberID = p.PortfolioGroupID
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroup] g
                ON g.PortfolioGroupID = m.PortfolioGroupID
                   LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroup] gg
                ON m.MemberCode = gg.PortfolioGroupCode
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBaseLabels] lb
                on p.MemberID = lb.PortfolioBaseID
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vContact] c
                on c.ContactCode = g.Purpose
                WHERE     m.PortfolioGroupCode like '%_Dist%'
                and (g.Purpose like '%_ind' or g.Purpose like '%group')
                and lb.Label = '$repset'
                --and m.membercode like '%pack%'
                       )
SELECT
t.ContentSetName,
'' as 'DistributionDesc',
CASE    WHEN ISNULL(d.purpose2,'.') <> ISNULL(d.purpose,'.')
    THEN CAST(d.ReportHeading1+'_ - '+d.DeliveryName AS NVARCHAR(MAX))
    ELSE d.ReportHeading1+'_' END AS DistributionCustom,
    CONCAT(d.mid,'.',d.pid,'_',d.DisplayOrder,'_', 

    CASE 
    WHEN t.ContentSetName LIKE 'Cover%' THEN 'Cover'
    WHEN t.ContentSetName = 'Separator_docx' THEN 'Separator'
    WHEN t.ContentSetName LIKE '%Report%' THEN 'Report'
    END
    ,'_',d.purpose) as DistributionName,
            'False' as IsForFunctionalGroup,
    'True' as IsLandscapePageNum,
    1 as NumOfCopies,
    d.purpose as RecipientCode,
    d.ReportHeading1 as RecipientFullName,
    d.MemberCode as ReportingEntityCode,
    'Quarterly' as RunEvent

    FROM    Data d
        CROSS APPLY 
        (   VALUES
                ('Cover_SSRS'),
                ('Separator_docx'),
                 (d.repset) 
                        ) t (ContentSetName)
WHERE   d.RowNumber = 1
OR      t.ContentSetNAme != 'Cover_SSRS'

This is the result set it produces..

table

As you may note in the content set Cover_SSRS is hard coded in the CROSS APPLY portion of the query. However I would like a CASE statement or some type of boolean logic that references the Contact View used in the query

[APXFirm].[AdvApp].[vContact]

Contact View

In the contact view noted above is the column Email3. If the value of Email3 is [email protected] I would like instead of Cover_SSRS in the contentsetname column for Cover_EmailSSRS to be alternatively used. Thus in the same result set if the value of Email3 was [email protected] the result set would look as follows:

Result2

Thank you

Solution

  • Since you're already referencing the [APXFirm].[AdvApp].[vContact] view in your CTE, why not simply add the Email3-column as an output column of the CTE, and then do a simple CASE in the main part of your query?

    WITH Data AS
(   SELECT  c.Email3,             -- <-- Add Email3 column from vContact here.
            b.ReportHeading1, 
            p.DisplayOrder, 
            p.MemberCode,
            m.PortfolioGroupCode as DistGroup,
            m.PortfolioGroupCode as DistCode,
            m.MemberCode as Packgroup,
            g.purpose,
            gg.purpose as purpose2,
            c.DeliveryName,
            p.PortfolioGroupID as pid,
            m.PortfolioGroupID as mid,
            convert(varchar(max),lb.value) as repset,
                        RowNumber = ROW_NUMBER() OVER(PARTITION BY m.portfoliogroupid, m.membercode ORDER BY p.DisplayOrder)
    FROM    [APXFirm].[AdvApp].[vPortfolioGroupMemberFlattened] p
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBase] b
                ON b.PortfolioBaseID = p.PortfolioGroupID
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroupMember] m
                 on m.MemberID = p.PortfolioGroupID
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroup] g
                ON g.PortfolioGroupID = m.PortfolioGroupID
                   LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioGroup] gg
                ON m.MemberCode = gg.PortfolioGroupCode
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vPortfolioBaseLabels] lb
                on p.MemberID = lb.PortfolioBaseID
                LEFT OUTER JOIN [APXFirm].[AdvApp].[vContact] c
                on c.ContactCode = g.Purpose
                WHERE     m.PortfolioGroupCode like '%_Dist%'
                and (g.Purpose like '%_ind' or g.Purpose like '%group')
                and lb.Label = '$repset'
                --and m.membercode like '%pack%'
                       )
SELECT
    -- Add CASE statement here --:
    CASE 
        WHEN d.Email3 = '[email protected]' AND t.ContentSetName = 'Cover_SSRS' THEN 'Cover_EmailSSRS'
        ELSE t.ContentSetName
    END AS ContentSetName,
    ------------------------------
    '' as 'DistributionDesc',
CASE    WHEN ISNULL(d.purpose2,'.') <> ISNULL(d.purpose,'.')
    THEN CAST(d.ReportHeading1+'_ - '+d.DeliveryName AS NVARCHAR(MAX))
    ELSE d.ReportHeading1+'_' END AS DistributionCustom,
    CONCAT(d.mid,'.',d.pid,'_',d.DisplayOrder,'_', 

    CASE 
    WHEN t.ContentSetName LIKE 'Cover%' THEN 'Cover'
    WHEN t.ContentSetName = 'Separator_docx' THEN 'Separator'
    WHEN t.ContentSetName LIKE '%Report%' THEN 'Report'
    END
    ,'_',d.purpose) as DistributionName,
            'False' as IsForFunctionalGroup,
    'True' as IsLandscapePageNum,
    1 as NumOfCopies,
    d.purpose as RecipientCode,
    d.ReportHeading1 as RecipientFullName,
    d.MemberCode as ReportingEntityCode,
    'Quarterly' as RunEvent

    FROM    Data d
        CROSS APPLY 
        (   VALUES
                ('Cover_SSRS'),
                ('Separator_docx'),
                 (d.repset) 
                        ) t (ContentSetName)
WHERE   d.RowNumber = 1
OR      t.ContentSetNAme != 'Cover_SSRS'