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?– Ronaldo Araújo Alves
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.
– Denis