CODEWITHSUNDEEP
sql-servert-sql
David Tunnell
David Tunnell

Reputation: 7532

Adding conditionals to a UNION

I am working on a long complex query:

DECLARE @userParam VARCHAR(100)
    ,@startDateParam DATETIME
    ,@endDateParam DATETIME
    ,@orgTeamPK VARCHAR(100)
    ,@teamId VARCHAR(100)
    ,@productId VARCHAR(100)

SET @userParam = '%'
SET @startDateParam = '2013-09-02 00:00:00'
SET @endDateParam = '2013-09-08 00:00:00'
SET @orgTeamPK = '%'
SET @teamId = '%'
SET @productId = '%'

SELECT '3' AS RowType
    ,DTH.PointPerson AS Person
    ,'' AS [Status]
    ,'' AS OrganizationalTeam
    ,'' AS Project
    ,'' AS Sprint
    ,'' AS Story
    ,'Subtotal:' AS Task
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 2
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS MondayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 3
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS TuesdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 4
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS WednesdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 5
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS ThursdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 6
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS FridayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 7
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS SaturdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 1
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS SundayHours
    ,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS TotalHours
FROM DailyTaskHours DTH
LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
LEFT JOIN NonScrumStory NSS ON DTH.NonScrumStoryId = NSS.PK_NonScrumStory
LEFT JOIN Sprint SPT ON STY.SprintId = SPT.PK_Sprint
LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
LEFT JOIN ProductTeamUser PTU ON TSK.ProductTeamUserId = PTU.PK_ProductTeamUser
LEFT JOIN [User] USR ON PTU.UserId = USR.PK_User
LEFT JOIN OrganizationalTeam OLT ON USR.OrganizationalTeamId = OLT.PK_OrganizationalTeam
LEFT JOIN UserAuthorization UAN2 ON (PDT.PK_Product = UAN2.ProductId AND UAN2.Deleted IS NULL)
WHERE DTH.PointPerson LIKE @userParam
    AND ActivityDate >= @startDateParam
    AND ActivityDate <= @endDateParam
    AND COALESCE(OLT.PK_OrganizationalTeam,'') LIKE @orgTeamPK 
    AND UAN2.ProductId LIKE @productId
GROUP BY DTH.PointPerson
HAVING SUM(DTH.[Hours]) > 0
) AS My_View
--

UNION

--
SELECT '3' AS RowType
    ,DTH.PointPerson AS Person
    ,'' AS [Status]
    ,'' AS OrganizationalTeam
    ,'' AS Project
    ,'' AS Sprint
    ,'' AS Story
    ,'Subtotal:' AS Task
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 2
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS MondayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 3
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS TuesdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 4
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS WednesdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 5
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS ThursdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 6
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS FridayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 7
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS SaturdayHours
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 1
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS SundayHours
    ,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS TotalHours
FROM DailyTaskHours DTH
LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
LEFT JOIN NonScrumStory NSS ON DTH.NonScrumStoryId = NSS.PK_NonScrumStory
LEFT JOIN Sprint SPT ON STY.SprintId = SPT.PK_Sprint
LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
LEFT JOIN ProductTeamUser PTU ON TSK.ProductTeamUserId = PTU.PK_ProductTeamUser
LEFT JOIN [User] USR ON PTU.UserId = USR.PK_User
LEFT JOIN OrganizationalTeam OLT ON USR.OrganizationalTeamId = OLT.PK_OrganizationalTeam
LEFT JOIN UserAuthorization UAN1 ON (USR.PK_User = UAN1.UserId AND UAN1.Deleted IS NULL)
WHERE DTH.PointPerson LIKE @userParam
    AND ActivityDate >= @startDateParam
    AND ActivityDate <= @endDateParam
    AND COALESCE(OLT.PK_OrganizationalTeam,'') LIKE @orgTeamPK 
    AND UAN1.OrganizationalTeamId LIKE @teamId
GROUP BY DTH.PointPerson
HAVING SUM(DTH.[Hours]) > 0

There are two rows that can be generated and put in rowtype 3. They have a UNION between them so both results will be shown and but duplicates will not. This is working as expected, however there are cases that because not all of the cells add up between rows, it shows two versions of the row. If this happens I want to just show the row there the last cell is larger (Total column).

How do I achieve this?

Edit (an attempt to clarify):

I have a union that sometimes results in two rows for the same person:

If this happens I just want to show the row with the higher total instead of both. How do I do that? enter image description here

Edit2:

Updated with suggestions.. still having the same results. What is wrong?

SELECT RowType
    ,Person
    ,[Status] 
    ,OrganizationalTeam 
    ,Project
    ,Sprint
    ,Story 
    ,[Task] 
    ,MondayHours 
    ,TuesdayHours 
    ,WednesdayHours
    ,ThursdayHours
    ,FridayHours
    ,SaturdayHours
    ,SundayHours
    ,MAX(DerivedTable.TotalHours)
    FROM (
    SELECT '3' AS RowType
        ,DTH.PointPerson AS Person
        ,'' AS [Status]
        ,'' AS OrganizationalTeam
        ,'' AS Project
        ,'' AS Sprint
        ,'' AS Story
        ,'Subtotal:' AS Task
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 2
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS MondayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 3
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS TuesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 4
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS WednesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 5
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS ThursdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 6
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS FridayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 7
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SaturdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 1
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SundayHours
        ,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS TotalHours
    FROM DailyTaskHours DTH
    LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
    LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
    LEFT JOIN NonScrumStory NSS ON DTH.NonScrumStoryId = NSS.PK_NonScrumStory
    LEFT JOIN Sprint SPT ON STY.SprintId = SPT.PK_Sprint
    LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
    LEFT JOIN ProductTeamUser PTU ON TSK.ProductTeamUserId = PTU.PK_ProductTeamUser
    LEFT JOIN [User] USR ON PTU.UserId = USR.PK_User
    LEFT JOIN OrganizationalTeam OLT ON USR.OrganizationalTeamId = OLT.PK_OrganizationalTeam
    LEFT JOIN UserAuthorization UAN2 ON (
            PDT.PK_Product = UAN2.ProductId
            AND UAN2.Deleted IS NULL
            )
    WHERE DTH.PointPerson LIKE @userParam
        AND ActivityDate >= @startDateParam
        AND ActivityDate <= @endDateParam
        AND COALESCE(OLT.PK_OrganizationalTeam, '') LIKE @orgTeamPK
        AND UAN2.ProductId LIKE @productId
    GROUP BY DTH.PointPerson
    HAVING SUM(DTH.[Hours]) > 0
    --

    UNION

    --
    SELECT '3' AS RowType
        ,DTH.PointPerson AS Person
        ,'' AS [Status]
        ,'' AS OrganizationalTeam
        ,'' AS Project
        ,'' AS Sprint
        ,'' AS Story
        ,'Subtotal:' AS Task
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 2
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS MondayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 3
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS TuesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 4
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS WednesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 5
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS ThursdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 6
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS FridayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 7
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SaturdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 1
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SundayHours
        ,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS TotalHours
    FROM DailyTaskHours DTH
    LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
    LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
    LEFT JOIN NonScrumStory NSS ON DTH.NonScrumStoryId = NSS.PK_NonScrumStory
    LEFT JOIN Sprint SPT ON STY.SprintId = SPT.PK_Sprint
    LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
    LEFT JOIN ProductTeamUser PTU ON TSK.ProductTeamUserId = PTU.PK_ProductTeamUser
    LEFT JOIN [User] USR ON PTU.UserId = USR.PK_User
    LEFT JOIN OrganizationalTeam OLT ON USR.OrganizationalTeamId = OLT.PK_OrganizationalTeam
    LEFT JOIN UserAuthorization UAN1 ON (
            USR.PK_User = UAN1.UserId
            AND UAN1.Deleted IS NULL
            )
    WHERE DTH.PointPerson LIKE @userParam
        AND ActivityDate >= @startDateParam
        AND ActivityDate <= @endDateParam
        AND COALESCE(OLT.PK_OrganizationalTeam, '') LIKE @orgTeamPK
        AND UAN1.OrganizationalTeamId LIKE @teamId
    GROUP BY DTH.PointPerson
    HAVING SUM(DTH.[Hours]) > 0
    ) AS DerivedTable
        GROUP BY 
        DerivedTable.RowType, 
        DerivedTable.Person,
        DerivedTable.[Status],
        DerivedTable.OrganizationalTeam,
        DerivedTable.Project,
        DerivedTable.Sprint,
        DerivedTable.Story,
        DerivedTable.[Task],
        DerivedTable.MondayHours,
        DerivedTable.TuesdayHours,
        DerivedTable.WednesdayHours,
        DerivedTable.ThursdayHours,
        DerivedTable.FridayHours,
        DerivedTable.SaturdayHours,
        DerivedTable.SundayHours,
            DerivedTable.TotalHours

Upvotes: 0

Views: 90

Answers (1)

James
James

Reputation: 12796

How about you wrap the union queries in an outer query, and then use an aggregate to get the higher total column like this (simplified for brevity):

SELECT a.COL1, a.COL2, MAX(a.TOTAL) AS Total
FROM
(
   SELECT COL1, COL2, TOTAL 
   FROM TABLE1
   ...

   UNION ALL

   SELECT COL1, COL2, TOTAL 
   FROM TABLE2
   ...
) a

GROUP BY a.COL1, a.COL2

You can also use additional aggregate functions MIN/MAX for other columns where you expect duplicates, and you can even mix these up with CASE statements for more complex scenarios.

Upvotes: 1

Related Questions