David Emerson
newsgrps at dalyn.co.nz
Tue Nov 21 19:16:54 CST 2006
SQL 2000 I have the following query running in Query Analyser: SELECT dbo.tblCustStatement.StatementID, dbo.tblCustomers.TradingName as CustName, dbo.tblCustStatement.StatementDate, dbo.tblCustomers.AccountNo, dbo.tblCustStatement.StatementNumber FROM dbo.tblCustomers INNER JOIN dbo.tblCustICP ON dbo.tblCustomers.CustomerID = dbo.tblCustICP.CustIDNo INNER JOIN dbo.tblCustStatement ON dbo.tblCustomers.CustomerID = dbo.tblCustStatement.CustIDNo LEFT OUTER JOIN dbo.vwAgedDebtorReceipt ON dbo.tblCustStatement.CustIDNo = dbo.vwAgedDebtorReceipt.CustIDNo AND dbo.tblCustStatement.StatementNumber = dbo.vwAgedDebtorReceipt.StatNum LEFT OUTER JOIN dbo.vwAgedDebtorReceiptInv ON dbo.tblCustStatement.CustIDNo = dbo.vwAgedDebtorReceiptInv.CustIDNo AND dbo.tblCustStatement.StatementNumber = dbo.vwAgedDebtorReceiptInv.InvNum WHERE -- Remming out this statement works (and removing the AND at the beginning of the next line) (dbo.tblCustStatement.StatementDate >= '2006-11-01' And dbo.tblCustStatement.StatementDate <= '2006-11-30') -- Remming out this statement works AND (dbo.tblCustomers.AccStatus= 'Active') AND (dbo.tblCustStatement.[CurrentMth]+ dbo.tblCustStatement.[OneMonth]+ dbo.tblCustStatement.[TwoMonths]+ dbo.tblCustStatement.[ThreeMonths] + ISNULL(dbo.vwAgedDebtorReceipt.NetReceipts,0) + ISNULL(dbo.vwAgedDebtorReceipt.GJCredit,0)+ ISNULL(dbo.vwAgedDebtorReceiptInv.NetReceiptsInv,0) + ISNULL(dbo.vwAgedDebtorReceiptInv.GJCreditInv,0) > 20) AND (dbo.tblCustomers.PayArrange = 0) AND (dbo.tblCustomers.NoOverdueNote = 0) -- Remming out this statement works AND ( CASE WHEN ISNUMERIC(LEFT(dbo.tblCustomers.CreditStatus,2)) = 0 THEN '00' ELSE LEFT(dbo.tblCustomers.CreditStatus,2) END < CAST(DAY(GETDATE()) AS varchar(2))) AND (dbo.tblCustomers.CreditStatus NOT LIKE 'Last%' OR dbo.tblCustomers.CreditStatus IS NULL) -- Remming out this statement works AND (dbo.tblCustStatement.StatementNumber <> 1) ORDER BY CustName, dbo.tblCustomers.AccountNo, dbo.tblCustStatement.StatementDate When I remark out any 1 of the AND statements noted above in the WHERE clause the results come back in a couple of seconds. However, when I include all AND statement the query runs doesn't return any results (at least not within the first ten minutes!) There doesn't seem to be any pattern to the lines that can be remmed or combinations of remaining lines. The only clue I have is that data from the database from two months ago works fine but there are 750 more records in each table being filtered. Any suggestions as to how I can solve this - it seems to be a data issue but the various combinations of AND statements above doesn't make sense.