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

Question

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 yes@yes.com 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 yes@yes.com the result set would look as follows:

Result2

Thank you

I don't see a question in there? Have you tried adding cases where you're interested in them? Or perhaps you can just build the entire query dynamically with the proper replacement and execute the string? Or use paramaters/variables? — Allan S. Hansen, Apr 04 '14 at 05:55
I did previously try to add a case statement to the lower part of the query beginning with FROM data d. This is the one part of the query I don't really understand and I did not have success. The question is how to get the result set to show Cover_EmailSSRS in the contentsetname row if the value of Email3 is yes@yes.com — Jay C, Apr 04 '14 at 06:06
I do also suspect this entire query could be built more simply but not sure how. — Jay C, Apr 04 '14 at 06:09

Dan · Accepted Answer · 2014-04-04T06:34:57.593

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 = 'yes@yes.com' 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'

Could you show where these editions would be placed using the entire query? — Jay C, Apr 04 '14 at 06:16
The query works but it makes the Cover_EmailSSRS value repeat too much see more here http://i.imgur.com/oLo2cKV.jpg — Jay C, Apr 04 '14 at 06:29
If you look in the DistributionName column you can see this should be alternating between Cover Separator Report within groups. Cover should only appear once per group. Maybe the CRoss apply section needs adjustment? — Jay C, Apr 04 '14 at 06:30
What was the complete output of the query before you added my changes? I don't believe that my additions should change the number of records in the query. — Dan, Apr 04 '14 at 06:31
It doesn't change the row count. It changes the value of contentsetname for more than just the cover value — Jay C, Apr 04 '14 at 06:32
Well in that case, change the CASE statement to something like this: `CASE WHEN d.Email3 = 'yes@yes.com' AND t.ContentSetName = 'Cover_SSRS' THEN 'Cover_EmailSSRS' ELSE t.ContentSetName END` — Dan, Apr 04 '14 at 06:34

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

1 Answers1