SQL Group BY SELECT CASE

Asked

Viewed 54 times

2

I have the query below to select some records and a Boolean "Isbranch" that indicates whether to select the branch or not. The problem is that when it is false the results are not getting grouped.

Ex.

Isbranch = True

29/05/2016 | Route1 | 5 | Branch1

29/05/2016 | Route1 | 5 | Branch2

Isbranch = False

29/05/2016 | Route1 | 5 | B

29/05/2016 | Route1 | 5 | B

I would like to have the following result:

Isbranch = False

29/05/2016 | Route1 | 10 | B

Note. in case I remove the Branch column the grouping works.

SELECT 
    {Order}.[AssignedDate]      AssignedDate,
    {Order}.[RouteCode]         RouteCode,
    COUNT({Order}.[Sequence])   Seq,
    CASE
        WHEN @IsBranch = 1 THEN {Order}.[Branch] 
        ELSE 'B'
    END AS Branch

FROM {Order}
 LEFT JOIN {Item}             ON {Order}.[WasteCode]      = {Item}.[ItemCode]
                             AND {Order}.[DBId]           = {Item}.[DBId]
 LEFT JOIN {ContainerType}    ON {Order}.[ContainerCode]  = {ContainerType}.[ItemCode]
                             AND {Order}.[DBId]           = {ContainerType}.[DBId]                             
 LEFT JOIN {SiteAddress}      ON {Order}.[SiteAddress]    = {SiteAddress}.[Address]
INNER JOIN {BusinessPartner}  ON {SiteAddress}.[CardCode] = {BusinessPartner}.[Id] 
                             AND {Order}.[CardCode]       = {BusinessPartner}.[CardCode]
 LEFT JOIN {SitePlanningArea} ON {SiteAddress}.[Id]       = {SitePlanningArea}.[SiteAddressId]
WHERE {Order}.[AssignedDate]      >= @StartDate
  AND {Order}.[AssignedDate]      <= @EndDate
  AND {Order}.[RouteCode]         <> @Empty
  AND {Order}.[Scheduled]          = 1
  AND {Order}.[OrderStatusId]      = @New
  AND ({Order}.[OrderStatusId]     = @Parked OR @IsParked = 0)
  AND ({Item}.[ItemGroup]          IN (SELECT {WasteGroup}.[ItemGroup]
                                         FROM {WasteGroup}
                                        WHERE {WasteGroup}.[WasteType] = @WasteTypeId) OR @WasteTypeId = 0)
  AND ({ContainerType}.[ItemGroup] IN (SELECT {ContainerGroup}.[ItemGroup]
                                         FROM {ContainerGroup}
                                        WHERE {ContainerGroup}.[ContainerType] = @ContainerTypeId) OR @ContainerTypeId = 0)
  AND ({Order}.[RouteCode] LIKE '%' || @Search || '%' OR @Search = @Empty)
  AND ({SitePlanningArea}.[PlanningAreaId] = @PlanningAreaId OR @PlanningAreaId = 0)
  AND ((@IsBranch = 1  AND {Order}.[Branch] IN (@BranchInClause) OR {Order}.[TipName] IN (@TipNameInClause)) OR @IsBranch = 0)     
GROUP BY {Order}.[AssignedDate], {Order}.[RouteCode], Branch
UNION
SELECT 
    {Order}.[ParkedDate]        AssignedDate,
    {Order}.[ParkedCode]        RouteCode,
    COUNT({Order}.[Sequence])   Seq,
    CASE
        WHEN @IsBranch = 1 THEN {Order}.[Branch] 
        ELSE 'B'
    END AS Branch

FROM {Order}
 LEFT JOIN {Item}             ON {Order}.[WasteCode]      = {Item}.[ItemCode]
                             AND {Order}.[DBId]           = {Item}.[DBId]
 LEFT JOIN {ContainerType}    ON {Order}.[ContainerCode]  = {ContainerType}.[ItemCode]
                             AND {Order}.[DBId]           = {ContainerType}.[DBId]                             
 LEFT JOIN {SiteAddress}      ON {Order}.[SiteAddress]    = {SiteAddress}.[Address]
INNER JOIN {BusinessPartner}  ON {SiteAddress}.[CardCode] = {BusinessPartner}.[Id] 
                             AND {Order}.[CardCode]       = {BusinessPartner}.[CardCode]
 LEFT JOIN {SitePlanningArea} ON {SiteAddress}.[Id]       = {SitePlanningArea}.[SiteAddressId]
WHERE {Order}.[ParkedDate]        >= @StartDate
  AND {Order}.[ParkedDate]        <= @EndDate
  AND {Order}.[ParkedCode]        <> @Empty
  AND {Order}.[OrderStatusId]      = @Parked
  AND ({Item}.[ItemGroup]          IN (SELECT {WasteGroup}.[ItemGroup]
                                         FROM {WasteGroup}
                                        WHERE {WasteGroup}.[WasteType] = @WasteTypeId) OR @WasteTypeId = 0)
  AND ({ContainerType}.[ItemGroup] IN (SELECT {ContainerGroup}.[ItemGroup]
                                         FROM {ContainerGroup}
                                        WHERE {ContainerGroup}.[ContainerType] = @ContainerTypeId) OR @ContainerTypeId = 0)
  AND ({Order}.[RouteCode] like '%' || @Search || '%' OR @Search = @Empty)
  AND ({SitePlanningArea}.[PlanningAreaId] = @PlanningAreaId OR @PlanningAreaId = 0)
  AND ((@IsBranch = 1  AND {Order}.[Branch] IN (@BranchInClause) OR {Order}.[TipName] IN (@TipNameInClause)) OR @IsBranch = 0)  
GROUP BY {Order}.[ParkedDate], {Order}.[ParkedCode], Branch

ORDER BY AssignedDate, Branch
  • Even with the UNION are coming repeated lines?

  • The problem is in Branch, if I remove out change just to select null it works. But when I make the case to add Branch I’m forced to put the branch in group by and then it’s separate.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.