Here's an issue I am having, I am currently getting data from a customer table, sales table, and a definitions table, it has 2 inner joins, It creates a temporary output table. Now, this table only includes data from the customers that actually had sales. I need a way to select into that temp table all the customers that are not currently in that table. Is there a way to do this? Any help would be greatly appreciated.
Jim
Hi,
therefore better use a LEFT JOIN which will be also applied to those customers which don′t have any entries in the joined tables. But this is just a common approach which *could* fit to your needs. Before giving you more information or advices please post the query you are using here.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
I have tried a left join with no avalial, Actually I have tried every join I could think of. Basically the stored procedure I am using to create the table uses 4 join statements to evaluate a number of sales totals, it creates a temp table, but no matter how may times I evaluate the statement or at what point, I cannot seem to get the left join to act any differently than an inner or right.
Included in the stored procedure I am using, perhaps someone out there will see something I do not.
ALTER PROCEDURE [dbo].[sp_get_minimumData] (@.StartDate as DateTime, @.StopDate as DateTime, @.prdGroup as char(1))
AS
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_minimum_data]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmp_minimum_data]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Current_Minimums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmp_Current_Minimums]
SELECT dbo.CustomerBase.qb_seq AS qb_seq, dbo.CustomerBase.CustQbName AS Customer,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_1_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_1) ELSE '0.00' END AS SalesItem1,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_2_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_2) ELSE '0.00' END AS SalesItem2,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_3_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_3) ELSE '0.00' END AS SalesItem3,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_4_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_4) ELSE '0.00' END AS SalesItem4,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_5_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_5) ELSE '0.00' END AS SalesItem5,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_6_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_6) ELSE '0.00' END AS SalesItem6,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_7_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_7) ELSE '0.00' END AS SalesItem7,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_8_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_8) ELSE '0.00' END AS SalesItem8,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_9_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_9) ELSE '0.00' END AS SalesItem9,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_10_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_10) ELSE '0.00' END AS SalesItem10,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_11_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_11) ELSE '0.00' END AS SalesItem11,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_12_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_12) ELSE '0.00' END AS SalesItem12,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_13_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_13) ELSE '0.00' END AS SalesItem13,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_14_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_14) ELSE '0.00' END AS SalesItem14,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_15_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_15) ELSE '0.00' END AS SalesItem15,
CASE WHEN dbo.tbl_ItemizerMap.sales_item_16_min = 'True' THEN SUM(dbo.tbl_BatchArchive.sales_item_16) ELSE '0.00' END AS SalesItem16,
CASE WHEN dbo.tbl_ItemizerMap.tax_item_1_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_1) ELSE '0.00' END AS TaxItem1,
CASE WHEN dbo.tbl_ItemizerMap.tax_item_2_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_2) ELSE '0.00' END AS TaxItem2,
CASE WHEN dbo.tbl_ItemizerMap.tax_item_3_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_3) ELSE '0.00' END AS TaxItem3,
CASE WHEN dbo.tbl_ItemizerMap.tax_item_4_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_4) ELSE '0.00' END AS TaxItem4,
CASE WHEN dbo.tbl_ItemizerMap.tax_item_5_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_5) ELSE '0.00' END AS TaxItem5,
CASE WHEN dbo.tbl_ItemizerMap.tax_item_6_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_6) ELSE '0.00' END AS TaxItem6,
CASE WHEN dbo.tbl_ItemizerMap.tax_item_7_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_7) ELSE '0.00' END AS TaxItem7,
CASE WHEN dbo.tbl_ItemizerMap.tax_item_8_min = 'True' THEN SUM(dbo.tbl_BatchArchive.tax_item_8) ELSE '0.00' END AS TaxItem8,
CASE WHEN dbo.tbl_ItemizerMap.service_charge_min = 'True' THEN SUM(dbo.tbl_BatchArchive.service_charge) ELSE '0.00' END AS ServiceChargeItem,
CASE WHEN dbo.tbl_ItemizerMap.auto_service_charge_min = 'True' THEN SUM(dbo.tbl_BatchArchive.auto_service_charge) ELSE '0.00' END AS AutoServiceChargeItem,
dbo.CustomerBase.minPeriodGroup
INTO tmp_minimum_data
FROM dbo.tbl_BatchArchive INNER JOIN
dbo.tbl_ItemizerMap ON dbo.tbl_BatchArchive.rvc_num = dbo.tbl_ItemizerMap.rvc_num AND
dbo.tbl_BatchArchive.qb_FullName <> dbo.tbl_ItemizerMap.cash_account_name INNER JOIN
dbo.CustomerBase ON dbo.tbl_BatchArchive.qb_seq = dbo.CustomerBase.qb_seq
WHERE (dbo.tbl_BatchArchive.bus_date BETWEEN @.StartDate AND @.StopDate) AND (dbo.CustomerBase.minPeriodGroup = @.prdGroup)
GROUP BY dbo.tbl_BatchArchive.qb_FullName, dbo.tbl_BatchArchive.bus_date, dbo.tbl_ItemizerMap.sales_item_1_min, dbo.tbl_ItemizerMap.sales_item_2_min,
dbo.tbl_ItemizerMap.sales_item_3_min, dbo.tbl_ItemizerMap.sales_item_4_min, dbo.tbl_ItemizerMap.sales_item_5_min,
dbo.tbl_ItemizerMap.sales_item_6_min, dbo.tbl_ItemizerMap.sales_item_7_min, dbo.tbl_ItemizerMap.sales_item_8_min,
dbo.tbl_ItemizerMap.sales_item_9_min, dbo.tbl_ItemizerMap.sales_item_10_min, dbo.tbl_ItemizerMap.sales_item_11_min,
dbo.tbl_ItemizerMap.sales_item_12_min, dbo.tbl_ItemizerMap.sales_item_13_min, dbo.tbl_ItemizerMap.sales_item_14_min,
dbo.tbl_ItemizerMap.sales_item_15_min, dbo.tbl_ItemizerMap.sales_item_16_min, dbo.tbl_ItemizerMap.tax_item_1_min,
dbo.tbl_ItemizerMap.tax_item_2_min, dbo.tbl_ItemizerMap.tax_item_3_min, dbo.tbl_ItemizerMap.tax_item_4_min,
dbo.tbl_ItemizerMap.tax_item_5_min, dbo.tbl_ItemizerMap.tax_item_6_min, dbo.tbl_ItemizerMap.tax_item_7_min,
dbo.tbl_ItemizerMap.tax_item_8_min, dbo.tbl_ItemizerMap.service_charge_min, dbo.tbl_ItemizerMap.auto_service_charge_min,
dbo.CustomerBase.minPeriodGroup, dbo.CustomerBase.CustQbName, dbo.CustomerBase.qb_seq
SELECT dbo.CustomerBase.qb_seq, dbo.CustomerBase.CustomerAccount, dbo.tmp_minimum_data.Customer,
SUM(dbo.tmp_minimum_data.SalesItem1) + SUM(dbo.tmp_minimum_data.SalesItem2)
+ SUM(dbo.tmp_minimum_data.SalesItem3) + SUM(dbo.tmp_minimum_data.SalesItem4) + SUM(dbo.tmp_minimum_data.SalesItem5)
+ SUM(dbo.tmp_minimum_data.SalesItem6) + SUM(dbo.tmp_minimum_data.SalesItem7) + SUM(dbo.tmp_minimum_data.SalesItem8)
+ SUM(dbo.tmp_minimum_data.SalesItem9) + SUM(dbo.tmp_minimum_data.SalesItem10) + SUM(dbo.tmp_minimum_data.SalesItem11)
+ SUM(dbo.tmp_minimum_data.SalesItem12) + SUM(dbo.tmp_minimum_data.SalesItem13) + SUM(dbo.tmp_minimum_data.SalesItem14)
+ SUM(dbo.tmp_minimum_data.SalesItem15) + SUM(dbo.tmp_minimum_data.SalesItem16) + SUM(dbo.tmp_minimum_data.TaxItem1)
+ SUM(dbo.tmp_minimum_data.TaxItem2) + SUM(dbo.tmp_minimum_data.TaxItem3) + SUM(dbo.tmp_minimum_data.TaxItem4)
+ SUM(dbo.tmp_minimum_data.TaxItem5) + SUM(dbo.tmp_minimum_data.TaxItem6) + SUM(dbo.tmp_minimum_data.TaxItem7)
+ SUM(dbo.tmp_minimum_data.TaxItem8) + SUM(dbo.tmp_minimum_data.ServiceChargeItem) + SUM(dbo.tmp_minimum_data.AutoServiceChargeItem) AS ValueTwardMinimum, dbo.CustomerBase.minValue,
CASE WHEN dbo.CustomerBase.minValue - (SUM(dbo.tmp_minimum_data.SalesItem1) + SUM(dbo.tmp_minimum_data.SalesItem2)
+ SUM(dbo.tmp_minimum_data.SalesItem3) + SUM(dbo.tmp_minimum_data.SalesItem4) + SUM(dbo.tmp_minimum_data.SalesItem5)
+ SUM(dbo.tmp_minimum_data.SalesItem6) + SUM(dbo.tmp_minimum_data.SalesItem7) + SUM(dbo.tmp_minimum_data.SalesItem8)
+ SUM(dbo.tmp_minimum_data.SalesItem9) + SUM(dbo.tmp_minimum_data.SalesItem10) + SUM(dbo.tmp_minimum_data.SalesItem11)
+ SUM(dbo.tmp_minimum_data.SalesItem12) + SUM(dbo.tmp_minimum_data.SalesItem13) + SUM(dbo.tmp_minimum_data.SalesItem14)
+ SUM(dbo.tmp_minimum_data.SalesItem15) + SUM(dbo.tmp_minimum_data.SalesItem16) + SUM(dbo.tmp_minimum_data.TaxItem1)
+ SUM(dbo.tmp_minimum_data.TaxItem2) + SUM(dbo.tmp_minimum_data.TaxItem3) + SUM(dbo.tmp_minimum_data.TaxItem4)
+ SUM(dbo.tmp_minimum_data.TaxItem5) + SUM(dbo.tmp_minimum_data.TaxItem6) + SUM(dbo.tmp_minimum_data.TaxItem7)
+ SUM(dbo.tmp_minimum_data.TaxItem8) + SUM(dbo.tmp_minimum_data.ServiceChargeItem) + SUM(dbo.tmp_minimum_data.AutoServiceChargeItem))> 0 THEN
dbo.CustomerBase.minValue - (SUM(dbo.tmp_minimum_data.SalesItem1) + SUM(dbo.tmp_minimum_data.SalesItem2)
+ SUM(dbo.tmp_minimum_data.SalesItem3) + SUM(dbo.tmp_minimum_data.SalesItem4) + SUM(dbo.tmp_minimum_data.SalesItem5)
+ SUM(dbo.tmp_minimum_data.SalesItem6) + SUM(dbo.tmp_minimum_data.SalesItem7) + SUM(dbo.tmp_minimum_data.SalesItem8)
+ SUM(dbo.tmp_minimum_data.SalesItem9) + SUM(dbo.tmp_minimum_data.SalesItem10) + SUM(dbo.tmp_minimum_data.SalesItem11)
+ SUM(dbo.tmp_minimum_data.SalesItem12) + SUM(dbo.tmp_minimum_data.SalesItem13) + SUM(dbo.tmp_minimum_data.SalesItem14)
+ SUM(dbo.tmp_minimum_data.SalesItem15) + SUM(dbo.tmp_minimum_data.SalesItem16) + SUM(dbo.tmp_minimum_data.TaxItem1)
+ SUM(dbo.tmp_minimum_data.TaxItem2) + SUM(dbo.tmp_minimum_data.TaxItem3) + SUM(dbo.tmp_minimum_data.TaxItem4)
+ SUM(dbo.tmp_minimum_data.TaxItem5) + SUM(dbo.tmp_minimum_data.TaxItem6) + SUM(dbo.tmp_minimum_data.TaxItem7)
+ SUM(dbo.tmp_minimum_data.TaxItem8) + SUM(dbo.tmp_minimum_data.ServiceChargeItem) + SUM(dbo.tmp_minimum_data.AutoServiceChargeItem)) ELSE 0 END AS InvoiceAmmount
INTO tmp_Current_Minimums
FROM dbo.tmp_minimum_data INNER JOIN
dbo.CustomerBase ON dbo.CustomerBase.qb_seq = dbo.tmp_minimum_data.qb_seq
WHERE (dbo.tmp_minimum_data.SalesItem1 + dbo.tmp_minimum_data.SalesItem2 + dbo.tmp_minimum_data.SalesItem3 + dbo.tmp_minimum_data.SalesItem4
+ dbo.tmp_minimum_data.SalesItem5 + dbo.tmp_minimum_data.SalesItem6 + dbo.tmp_minimum_data.SalesItem7 + dbo.tmp_minimum_data.SalesItem8
+ dbo.tmp_minimum_data.SalesItem9 + dbo.tmp_minimum_data.SalesItem10 + dbo.tmp_minimum_data.SalesItem11 + dbo.tmp_minimum_data.SalesItem12
+ dbo.tmp_minimum_data.SalesItem13 + dbo.tmp_minimum_data.SalesItem14 + dbo.tmp_minimum_data.SalesItem15 + dbo.tmp_minimum_data.SalesItem16
+ dbo.tmp_minimum_data.TaxItem1 + dbo.tmp_minimum_data.TaxItem2 + dbo.tmp_minimum_data.TaxItem3 + dbo.tmp_minimum_data.TaxItem4 + dbo.tmp_minimum_data.TaxItem5
+ dbo.tmp_minimum_data.TaxItem6 + dbo.tmp_minimum_data.TaxItem7 + dbo.tmp_minimum_data.TaxItem8 + dbo.tmp_minimum_data.ServiceChargeItem + dbo.tmp_minimum_data.AutoServiceChargeItem <> 0) AND
(dbo.CustomerBase.minValue - (dbo.tmp_minimum_data.SalesItem1 + dbo.tmp_minimum_data.SalesItem2 + dbo.tmp_minimum_data.SalesItem3 + dbo.tmp_minimum_data.SalesItem4
+ dbo.tmp_minimum_data.SalesItem5 + dbo.tmp_minimum_data.SalesItem6 + dbo.tmp_minimum_data.SalesItem7 + dbo.tmp_minimum_data.SalesItem8
+ dbo.tmp_minimum_data.SalesItem9 + dbo.tmp_minimum_data.SalesItem10 + dbo.tmp_minimum_data.SalesItem11 + dbo.tmp_minimum_data.SalesItem12
+ dbo.tmp_minimum_data.SalesItem13 + dbo.tmp_minimum_data.SalesItem14 + dbo.tmp_minimum_data.SalesItem15 + dbo.tmp_minimum_data.SalesItem16
+ dbo.tmp_minimum_data.TaxItem1 + dbo.tmp_minimum_data.TaxItem2 + dbo.tmp_minimum_data.TaxItem3 + dbo.tmp_minimum_data.TaxItem4 + dbo.tmp_minimum_data.TaxItem5
+ dbo.tmp_minimum_data.TaxItem6 + dbo.tmp_minimum_data.TaxItem7 + dbo.tmp_minimum_data.TaxItem8 + dbo.tmp_minimum_data.ServiceChargeItem + dbo.tmp_minimum_data.AutoServiceChargeItem)
<> 0) AND (dbo.CustomerBase.minValue <> 0)
GROUP BY dbo.tmp_minimum_data.Customer, dbo.CustomerBase.minValue, dbo.CustomerBase.CustomerAccount, dbo.CustomerBase.qb_seq
DROP TABLE tmp_minimum_data
ALTER TABLE dbo.tmp_Current_Minimums ADD
changed Boolean NULL,
reason nvarchar(50) NULL
SELECT * FROM tmp_Current_Minimums
|||Hi
I think LEFT and INNER joins behave the same in 2 cases:
1. No data is missing from the left joined table
2. You are using the left joined table in the WHERE clause. In this case , try to add your criteria to the LEFT JOIN ON criteria.
NB
|||Join to the customer using a NOT EXISTS (in the temp table). That will pull back all the rows from customer not already in the temp table.|||I would like to thank everyone, I ended up using a Cursor and some creative data manipulation to do what I needed, but thank you all for your assistance.
No comments:
Post a Comment