Hello - I have two BAQs, one that returns the total value of orders added by customer for the current month and one that returns the total value of orders shipped by customer for the current month. Both BAQs work separately but I am unsure of how to combine them so that in a single BAQ I get the total orders added and total orders shipped by customer for the current month.
I attempted to combine them but get a bad SQL statement error. All of my subqueries are “InnerSubQuery”. I am guessing I need to incorporate “Union” or “CTE”.
Orders added by customer for the current month (this BAQ does return customers with NULL totals:
select
[Customer].[Name] as [Customer_Name],
(SUM(case when Datepart(month,OrderHed.OrderDate)=Datepart(month,getdate()) then (orderhed.DocOrderAmt) else NULL end)) as [Calculated_OrderTotals]
from Erp.OrderHed as OrderHed
inner join Erp.Customer as Customer on
OrderHed.Company = Customer.Company
and OrderHed.CustNum = Customer.CustNum
group by [Customer].[Name]
Orders shipped by customer for the current month (This BAQ does NOT return customers with NULL values):
select
[Subquery4].[Customer_Name] as [Customer_Name],
[Subquery4].[Calculated_Totals] as [Calculated_Totals]
from (select
[Second].[Customer_Name] as [Customer_Name],
(SUM(case when Second.Calculated_TotalShipped>0 then Second.Calculated_TotalShipped else NULL end)) as [Calculated_Totals]
from (select distinct
[SubQuery11].[Customer_Name] as [Customer_Name],
((case when Subquery2.Calculated_OrdersShippedThisMonth=1 then Subquery2.Calculated_ValueShipped else NULL end)) as [Calculated_TotalShipped]
from (select distinct
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
((case when datepart(month, Shiphead.Shipdate)=datepart(month,getdate()) AND ShipHead.ShipDate IS NOT NULL then 1 else NULL end)) as [Calculated_OrdersShippedThisMonth],
[ShipDtl].[OrderNum] as [ShipDtl_OrderNum],
[Customer].[Name] as [Customer_Name]
from Erp.Customer as Customer
inner join Erp.ShipHead as ShipHead on
Customer.Company = ShipHead.Company
and Customer.CustNum = ShipHead.CustNum
inner join Erp.ShipDtl as ShipDtl on
ShipHead.Company = ShipDtl.Company
and ShipHead.PackNum = ShipDtl.PackNum) as SubQuery11
inner join (select distinct
[SubQuery1].[ShipHead_ShipDate] as [ShipHead_ShipDate],
[SubQuery1].[Calculated_OrdersShippedThisMonth] as [Calculated_OrdersShippedThisMonth],
[SubQuery1].[ShipDtl_OrderNum] as [ShipDtl_OrderNum],
((case when SubQuery1.Calculated_OrdersShippedThisMonth=1 then Orderhed.DocOrderAmt else NULL end)) as [Calculated_ValueShipped],
[Customer1].[Name] as [Customer1_Name]
from (select distinct
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
((case when datepart(month, Shiphead.Shipdate)=datepart(month,getdate()) AND ShipHead.ShipDate IS NOT NULL then 1 else NULL end)) as [Calculated_OrdersShippedThisMonth],
[ShipDtl].[OrderNum] as [ShipDtl_OrderNum],
[Customer].[Name] as [Customer_Name]
from Erp.Customer as Customer
inner join Erp.ShipHead as ShipHead on
Customer.Company = ShipHead.Company
and Customer.CustNum = ShipHead.CustNum
inner join Erp.ShipDtl as ShipDtl on
ShipHead.Company = ShipDtl.Company
and ShipHead.PackNum = ShipDtl.PackNum) as SubQuery1
inner join Erp.OrderHed as OrderHed on
OrderHed.OrderNum = SubQuery1.ShipDtl_OrderNum
inner join Erp.Customer as Customer1 on
OrderHed.Company = Customer1.Company
and OrderHed.CustNum = Customer1.CustNum
where (SubQuery1.Calculated_OrdersShippedThisMonth = 1)) as Subquery2 on
SubQuery11.ShipDtl_OrderNum = Subquery2.ShipDtl_OrderNum) as Second
group by [Second].[Customer_Name]) as Subquery4
Yes, CTE would be the way to go (IMO). Change both queries to CTE and then make a third query as a Top Level. Add both queries to the 3rd query as tables and join on like fields and you should be good.
@jkane I am having one more issue - the combined BAQ is omitting a few customers who did not have anything ship but did have orders added. When I create the “OrdersAdded” BAQ separately I get a full list of our customers with every customer either having a value for the amount of orders added or a 0 but this is cut down to only those that had shipments when I combine everything. How can I retain the full list of customers and keep the query from omitting customers who may not have any shipments in the current month?
/*
Disclaimer!!!
This is not a real query being executed, but a simplified version for general vision.
Executing it with any other tool may produce a different result.
*/
with [SubQuery5] as
(select distinct
[Customer2].[Name] as [Customer2_Name],
(SUM(case when Datepart(month,OrderHed1.OrderDate)=Datepart(month,getdate()) then (orderhed1.DocOrderAmt) else 0 end)) as [Calculated_OrderTotals]
from Erp.OrderHed as OrderHed1
inner join Erp.Customer as Customer2 on
OrderHed1.Company = Customer2.Company
and OrderHed1.CustNum = Customer2.CustNum
group by [Customer2].[Name])
,[SubQuery1] as
(select distinct
[ShipHead].[ShipDate] as [ShipHead_ShipDate],
((case when datepart(month, Shiphead.Shipdate)=datepart(month,getdate()) AND ShipHead.ShipDate IS NOT NULL then 1 else 0 end)) as [Calculated_OrdersShippedThisMonth],
[ShipDtl].[OrderNum] as [ShipDtl_OrderNum],
[Customer].[Name] as [Customer_Name]
from Erp.Customer as Customer
inner join Erp.ShipHead as ShipHead on
Customer.Company = ShipHead.Company
and Customer.CustNum = ShipHead.CustNum
inner join Erp.ShipDtl as ShipDtl on
ShipHead.Company = ShipDtl.Company
and ShipHead.PackNum = ShipDtl.PackNum)
,[Subquery2] as
(select distinct
[SubQuery1].[ShipHead_ShipDate] as [ShipHead_ShipDate],
[SubQuery1].[Calculated_OrdersShippedThisMonth] as [Calculated_OrdersShippedThisMonth],
[SubQuery1].[ShipDtl_OrderNum] as [ShipDtl_OrderNum],
((case when SubQuery1.Calculated_OrdersShippedThisMonth=1 then Orderhed.DocOrderAmt else 0 end)) as [Calculated_ValueShipped],
[Customer1].[Name] as [Customer1_Name]
from SubQuery1 as SubQuery1
inner join Erp.OrderHed as OrderHed on
OrderHed.OrderNum = SubQuery1.ShipDtl_OrderNum
inner join Erp.Customer as Customer1 on
OrderHed.Company = Customer1.Company
and OrderHed.CustNum = Customer1.CustNum
where (SubQuery1.Calculated_OrdersShippedThisMonth = 1))
,[Subquery3] as
(select distinct
[SubQuery11].[Customer_Name] as [Customer_Name],
((case when Subquery2.Calculated_OrdersShippedThisMonth=1 then Subquery2.Calculated_ValueShipped else 0 end)) as [Calculated_TotalShipped]
from SubQuery1 as SubQuery11
inner join Subquery2 as Subquery2 on
SubQuery11.ShipDtl_OrderNum = Subquery2.ShipDtl_OrderNum)
,[Subquery4] as
(select
[Second].[Customer_Name] as [Customer_Name],
(SUM(case when Second.Calculated_TotalShipped>0 then Second.Calculated_TotalShipped else 0 end)) as [Calculated_Totals]
from Subquery3 as Second
group by [Second].[Customer_Name])
select
[SubQuery5].[Customer2_Name] as [Customer2_Name],
[SubQuery5].[Calculated_OrderTotals] as [Calculated_OrderTotals],
[Subquery4].[Calculated_Totals] as [Calculated_Totals]
from SubQuery5 as SubQuery5
inner join Subquery4 as Subquery4 on
Subquery4.Customer_Name = SubQuery5.Customer2_Name
