Pivot or not - 2 SQL Queries

Transact-SQL
1

Hi

I have two SQL queries that query the same table. One query returns the total the customer has spent this year

SELECT t2.ORD_Customer 
	, SUM(CASE WHEN t3.Order_Or_Credit = 'C' then -t1.Net_Price else t1.Net_Price end) as 'TO This Year'
FROM dbo.Sales_Order_Lines as t1
	LEFT JOIN dbo.Sales_Order_Header as t2 ON t1.Order_Number = t2.ORD_Order_No
	LEFT JOIN dbo.Sales_Order_Header2 as t3 ON t1.Order_Number = t3.Order_Number
WHERE t1.Qty_Invoiced = t1.Qty_Ordered
	AND t1.Qty_Allocated = 0
AND t2.Ord_Credit_Hold = 'N'
GROUP BY t2.ORD_Customer

and the second returns the value of outstanding orders the customer has.

SELECT t2.ORD_customer
	, SUM(t1.net_price) as 'OS Orders'
FROM dbo.Sales_Order_Lines as t1
	LEFT JOIN dbo.Sales_Order_Header as t2 ON t1.Order_Number = t2.Ord_Order_No
	LEFT JOIN dbo.Sales_Order_Header2 as t3 ON t1.Order_Number = t3.Order_Number
WHERE t1.Line_Status <> 6
	AND t3.Order_or_Credit = ''
	AND t3.Sample_Order = 'N'
GROUP BY t2.ORD_Customer

I am wanting to display 1 row per customer with the total spent this year and the total outstanding orders. I am thinking I need to PIVOT the data but I am not sure if that is possible or if that is the best method.

Any help/advice would be greatly accepted.

2

If you show your current result of data and what your desired result should be like.

Then i can help

3

I am not totally convinced about these queries as:

  1. You are grouping by a column, t2.ORD_Customer, which could potentially be NULL.
  2. In the first query, the LEFT JOIN to dbo.Sales_Order_Header is in effect an INNER JOIN due to t2.Ord_Credit_Hold in the WHERE clause.
  3. In the second query, the LEFT JOIN to dbo.Sales_Order_Header2 is in effect an INNER JOIN due to t3.Order_or_Credit and t3.Sample_Order in the WHERE clause.

Without consumable test data it is difficult to tell what you want.
This easiest, although potentially inefficient, approach would just be to join the queries:

WITH Query1
AS
(
	SELECT t2.ORD_Customer 
		, SUM(CASE WHEN t3.Order_Or_Credit = 'C' then -t1.Net_Price else t1.Net_Price end) as TOThisYear
	FROM dbo.Sales_Order_Lines as t1
		JOIN dbo.Sales_Order_Header as t2 ON t1.Order_Number = t2.ORD_Order_No
		LEFT JOIN dbo.Sales_Order_Header2 as t3 ON t1.Order_Number = t3.Order_Number
	WHERE t1.Qty_Invoiced = t1.Qty_Ordered
		AND t1.Qty_Allocated = 0
	AND t2.Ord_Credit_Hold = 'N'
	GROUP BY t2.ORD_Customer
)
,Query2
AS
(
	SELECT t2.ORD_customer
		, SUM(t1.net_price) as OSOrders
	FROM dbo.Sales_Order_Lines as t1
		LEFT JOIN dbo.Sales_Order_Header as t2 ON t1.Order_Number = t2.Ord_Order_No
		JOIN dbo.Sales_Order_Header2 as t3 ON t1.Order_Number = t3.Order_Number
	WHERE t1.Line_Status <> 6
		AND t3.Order_or_Credit = ''
		AND t3.Sample_Order = 'N'
	GROUP BY t2.ORD_Customer
)
SELECT COALESCE(Q1.ORD_customer, Q2.ORD_customer) AS ORD_customer
	,Q1.TOThisYear, Q2.OSOrders
FROM Query1 Q1
	FULL JOIN Query2 Q2
		ON Q1.ORD_customer = Q2.ORD_customer;
1 Like
4

What @Ifor points out are very valid :thumbsup:

I think this might work for you thou:

select t2.ord_customer
      ,sum(case
              when t1.qty_invoices=t1.qty_ordered
               and t1.qty_allocated=0
               and t3.ord_credit_hold='N'
              then t1.net_price*case when t3.order_or_credit='C' then -1 else  1 end
              else 0
           end
          ) as to_this_year
      ,sum(case
              when t1.line_status<>6
               and  t3.order_or_credit=''
               and  t3.sample_order='N'
              then t1.net_price
              else 0
           end
          ) as os_orders
  from dbo.sales_order_lines as t1
       inner join dbo.sales_order_header as t2
               on t2.ord_order_no=t1.order_number
       inner join dbo_sales_order_header2 as t3
               on t3.order_number=t1.order_number
 where (t1.qty_invoices=t1.qty_ordered
   and  t1.qty_allocated=0
   and  t2.ord_credit_hold='N'
       )
    or (t1.line_status<>6
   and  t3.order_or_credit=''
   and  t3.sample_order='N'
       )
 group by t2.ord_customer
;
5

Thanks for the help.