Diffucult T-SQL question: showing sales data that was not sold
-
Hi I want to create a report that lists the customers and all the items that they have not bought from us for a certain period. I have 3 relevant tables, CustomerMaster, InventoryMaster, SalesDetail. SalesDetail holds all the sale info, for example CustomerCode : 1AFR204 StockCode : FRA202 QtyInvoiced : 2 NetSalesValue: 1295 Year : 2006 Month : 7 The master tables holds all customers and inventory. I want the report to basicaly look like this: Ultra liquors Newton Park Not listed: Boland Cabernet Spier Sauvignon Blanc To be able to do this I need a recordset that consists of the stockcode and customercode of items not sold. Any ideas how to do this? I have been playing with not in subqueries. Its easy to display items not sold or customers that did not buy, seperately, but to combine them is a different animal. Thanks Izak
-
Hi I want to create a report that lists the customers and all the items that they have not bought from us for a certain period. I have 3 relevant tables, CustomerMaster, InventoryMaster, SalesDetail. SalesDetail holds all the sale info, for example CustomerCode : 1AFR204 StockCode : FRA202 QtyInvoiced : 2 NetSalesValue: 1295 Year : 2006 Month : 7 The master tables holds all customers and inventory. I want the report to basicaly look like this: Ultra liquors Newton Park Not listed: Boland Cabernet Spier Sauvignon Blanc To be able to do this I need a recordset that consists of the stockcode and customercode of items not sold. Any ideas how to do this? I have been playing with not in subqueries. Its easy to display items not sold or customers that did not buy, seperately, but to combine them is a different animal. Thanks Izak
SELECT CM.CustomerCode, IM.StockCode
FROM CustomerMaster CM, InventoryMaster IM
WHERE CM.CustomerCode NOT IN(SELECT SD.CustomerCode
FROM SalesDetail SD
WHERE SD.StockCode = IM.StockCode
AND SD.Year = 2006
AND (SD.Month >= 6 and SD.Month <= 7))
ORDER BY CustomerCode--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
-
SELECT CM.CustomerCode, IM.StockCode
FROM CustomerMaster CM, InventoryMaster IM
WHERE CM.CustomerCode NOT IN(SELECT SD.CustomerCode
FROM SalesDetail SD
WHERE SD.StockCode = IM.StockCode
AND SD.Year = 2006
AND (SD.Month >= 6 and SD.Month <= 7))
ORDER BY CustomerCode--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters
Thanks! If I understand it correctly, the customermaster and the inventorymaster creates all possible combinations except for the combinations in the NOT IN clause? So... If customermaster has 200 records and inventorymaster has 100 records and the salesdetal has 750 distinct records. 200 * 100 = 20000 - 750 = 19250, the result will have 19250 possible sales combinations that did not happen. Am my logic correct? Thanks again Eric, this has been very helpfull! :) Izak
-
Thanks! If I understand it correctly, the customermaster and the inventorymaster creates all possible combinations except for the combinations in the NOT IN clause? So... If customermaster has 200 records and inventorymaster has 100 records and the salesdetal has 750 distinct records. 200 * 100 = 20000 - 750 = 19250, the result will have 19250 possible sales combinations that did not happen. Am my logic correct? Thanks again Eric, this has been very helpfull! :) Izak
Izak - wrote:
Am my logic correct?
Seems so to me.
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters