Hi. I am new to this forum/community and need help to join multiple result sets in SQL. The last time I used SQL was in 2000. The goal is to provide one single data set result from multiple queries. I have included the code from two queries that I have developed. I want to have a single query that provides last_name, first_name, add_date, PO_type, and new customers added by all personel in the same department. I have tried UNION operator which does not work for me and not sure about how to use CASE. Listed below are examples of the code.
SELECT a.Last_name, a.First_name, b.Add_date, COUNT(c.PO_Type) AS Blanket, COUNT(c.PO_Type) AS [Spot_buy] FROM Plexus_control_v_Plexus_user a JOIN Common_v_Position d ON a.Position_Key = d.Position_key JOIN Sales_v_PO b ON a.Plexus_user_no = b.add_by JOIN Sales_v_PO_type c ON b.PO_type_key = c.PO_type_key AND c.PO_type = 'blanket' OR c.PO_type = 'spot buy' AND d.Position IN ('Customer Service') WHERE b.add_date BETWEEN '07/01/2010' AND '07/16/2010' GROUP BY a.Last_name, a.First_name, d.Position, b.add_date ORDER BY a.Last_name, a.First_name
SELECT a.First_name, a.Last_name, b.Add_date, COUNT(c.Customer_Code) AS [New Customer Added] FROM Plexus_control_v_Plexus_user a JOIN Common_v_Position d ON a.Position_Key = d.Position_key JOIN Common_v_Customer b ON a.Plexus_user_no = b.add_by JOIN Common_v_Customer c ON b.customer_no = c.customer_no WHERE b.add_date BETWEEN '07/01/2010' AND '07/16/2010' AND d.Position IN ('Customer Service') GROUP BY a.First_name, a.Last_name, b.Add_date ORDER BY a.First_name, a.Last_name
Any help would be greatly appreciated and please tell me what I am doing wrong.
Thanks so much.