Thursday, August 29, 2013

[T-SQL] Curious thought on how to approach making a query like this "better'

[T-SQL] Curious thought on how to approach making a query like this "better'

Curious thought on how to approach making a query like this "better'

Posted: 28 Aug 2013 03:01 PM PDT

Troubleshooting queries like this are always fun, this definitely isn't one of the worst I've seen, but its a popular one where the report writers liek to copy and paste this code into other procedures as a basis for others. The temp and parameter tables used, often have hundreds of thousands of rows in them...[b]I don't supposed there's a way to throw an error across the entire server if a temp table exceeds a certain number of rows in there?[/b]Anyone else feel the pain or am I just whining?[code="sql"] SELECT b.PrimaryAccountNumber, b.trantime, b.posttime, ISNULL(b.AuthDateTime,b.posttime), RTRIM(b.storename), ISNULL(CONVERT(decimal(30, 4), c.quantity), 0) AS [Qty], 0, ROUND(CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount] ELSE c.originalamount_ END / (CASE WHEN CONVERT(decimal(30, 4), c.quantity) = 0 THEN 1 ELSE CONVERT(decimal(30, 4), c.quantity) END), 3), CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount] ELSE c.originalamount_ END, 0, b.ProgramName, ISNULL(b.PORefNumber, br.PORefNumber), ISNULL(a.OdometerReading, '0'), RTRIM(ISNULL(b.storecity, br.storecity)), RTRIM(ISNULL(b.storestate, br.storestate)), c.unitofcost, c.RepricedTranAmount, (CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount] ELSE c.originalamount_ END - c.RepricedTranAmount) AS Discount, ROUND((CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN c.FedTaxAmount ELSE 0 END) + (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN c.StateTaxAmount ELSE 0 END) + (CASE WHEN c.CountyFuelTaxExemptStatus = 'Y' THEN c.CountyTaxAmount ELSE 0 END) + (CASE WHEN c.CityFuelTaxExemptStatus = 'Y' THEN c.CityTaxAmount ELSE 0 END) + (CASE WHEN c.LocalSalesTaxExemptStatus = 'Y' THEN c.LocalTax ELSE 0 END) + (CASE WHEN c.Misc1ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount1 ELSE 0 END) + (CASE WHEN c.Misc2ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount2 ELSE 0 END) + (CASE WHEN c.StateSalesTaxExemptStatus = 'Y' THEN c.StateSalesTaxAmount ELSE 0 END) + (CASE WHEN c.CountySalesTaxExemptStatus = 'Y' THEN c.CountySalesTaxAmount ELSE 0 END) + (CASE WHEN c.CitySalesTaxExemptStatus = 'Y' THEN c.CitySalesTaxAmount ELSE 0 END) + (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN c.MiscSalesTaxAmount ELSE 0 END) + (CASE WHEN c.FreightExemptStatus = 'Y' THEN c.Freight ELSE 0 END), 2) Exemptions, ISNULL(ROUND(RepricedTranAmount - ((CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN c.FedTaxAmount ELSE 0 END) + (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN c.StateTaxAmount ELSE 0 END) + (CASE WHEN c.CountyFuelTaxExemptStatus = 'Y' THEN c.CountyTaxAmount ELSE 0 END) + (CASE WHEN c.CityFuelTaxExemptStatus = 'Y' THEN c.CityTaxAmount ELSE 0 END) + (CASE WHEN c.LocalSalesTaxExemptStatus = 'Y' THEN c.LocalTax ELSE 0 END) + (CASE WHEN c.Misc1ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount1 ELSE 0 END) + (CASE WHEN c.Misc2ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount2 ELSE 0 END) + (CASE WHEN c.StateSalesTaxExemptStatus = 'Y' THEN c.StateSalesTaxAmount ELSE 0 END) + (CASE WHEN c.CountySalesTaxExemptStatus = 'Y' THEN c.CountySalesTaxAmount ELSE 0 END) + (CASE WHEN c.CitySalesTaxExemptStatus = 'Y' THEN c.CitySalesTaxAmount ELSE 0 END) + (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN c.MiscSalesTaxAmount ELSE 0 END) + (CASE WHEN c.FreightExemptStatus = 'Y' THEN c.Freight ELSE 0 END)), 2), b.transactionamount) AmountDue, 0, (CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN (c.FedTaxAmount * -1) ELSE 0 END) AS fedtax, (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN (c.StateTaxAmount * -1) ELSE 0 END) AS StateTax, (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN (c.MiscSalesTaxAmount * -1) ELSE 0 END) AS MiscSalesTax, (CASE WHEN c.FreightExemptStatus = 'Y' THEN (c.Freight * -1) ELSE 0 END) AS freight, c.[productcode1], k.LutDescription, '', a.ApprovalCode, b.[txncode_internal], b.[tranid], ISNULL(CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount] ELSE c.originalamount_ END, b.transactionamount), b.cmttrantype, a.[taxexemptTotalBalance], ISNULL(a.FleetCardIdDriverNo, ar.FleetCardIdDriverNo), a.[ResponseCode], a.EDTransactionFlag, a.PermitState, a.PermitNumber, b.FleetNumber, a.TrailerNumber, b.DriverLicenseNumber, b.TripNumber, RTRIM(a.DealerName), ISNULL(RTRIM(a.DealerAddressLine1), ''), ISNULL(a.txnflag, 'M'), CASE WHEN a.EDTXnFlag = '1' THEN 'E' WHEN a.tranid IN (SELECT tranid FROM #PaymentTranIDs WHERE TranID IS NOT NULL) THEN 'P' --526171 ELSE ISNULL(a.txnflag, 'M') END, CASE WHEN a.tranid IN (SELECT tranid FROM #PaymentTranIDs WHERE TranID IS NOT NULL) THEN (SELECT DISTINCT yy.[LutDescription] FROM THISDATABASE.dbo.CCardLookUp AS yy WITH (READUNCOMMITTED) INNER JOIN THISDATABASE.dbo.Payments AS pp WITH (READUNCOMMITTED) ON pp.pmtpaymenttype = yy.lutcode AND lutid = 'pmtpaymenttype' WHERE pp.tranid = a.tranid) ELSE b.TransactionDescription END TransactionDescription, CASE WHEN a.txnflag IN ('0', '5', '3') THEN 'Card Transactions' WHEN a.txnflag = '1' THEN 'Tire Programs' WHEN a.txnflag = '2' THEN 'Plus Chek Transactions' WHEN a.txnflag = '4' THEN 'Permit Transaction' WHEN a.EDTxnFlag = '1' THEN 'ED' WHEN a.tranid IN (SELECT tranid FROM #PaymentTranIDs WHERE TranID IS NOT NULL) THEN (SELECT DISTINCT yy.[LutDescription] FROM THISDATABASE.dbo.CCardLookUp AS yy WITH (READUNCOMMITTED) INNER JOIN THISDATABASE.dbo.Payments AS pp WITH (READUNCOMMITTED) ON pp.pmtpaymenttype = yy.lutcode AND lutid = 'pmtpaymenttype' WHERE pp.tranid = a.tranid) ELSE 'Miscellaneous Activity' END TxnFlagDesc, b.RevTgt, b.tranref, --FIL.invoicenumber, --b.InvoiceNumber, CONVERT(BIGINT,LTRIM(RTRIM(b.InvoiceNumber))), p.lutdescription, c.producttype, ISNULL(b.TransactionDescription, d.lutdescription), ISNULL(e.Parent01AID, e.acctid), CASE WHEN cl.LutDescription LIKE 'Monthly%' THEN 'Monthly' WHEN cl.LutDescription IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') THEN 'Weekly' WHEN cl.LutDescription LIKE '%Daily%' THEN 'Daily' WHEN cl.LutDescription LIKE '%Bi-Weekly%' THEN 'Bi-Weekly' WHEN cl.LutDescription LIKE '%Annually%' THEN 'Annually' WHEN cl.LutDescription LIKE '%Quarterly%' THEN 'Quarterly' WHEN cl.LutDescription LIKE '%Semi-Monthly%' THEN 'Semi-Monthly' WHEN cl.LutDescription LIKE '%Semi-Annually%' THEN 'Semi-Annually' WHEN cl.LutDescription LIKE '%45 Day Cycle%' THEN '45 Day Cycle' ELSE 'Other' END BillingCycle, u.CheckNumber, u.CheckAmount, Pt.CreditType, CASE WHEN b.cmttrantype LIKE '%[a-z]%' THEN '' WHEN ISNUMERIC(b.cmttrantype) = 1 AND (((CONVERT(int, b.cmttrantype) % 2) = 1 OR b.cmttrantype IN ('110', '116', '118')) AND b.cmttrantype NOT IN ('111', '115', '117')) THEN -1 WHEN ISNUMERIC(b.cmttrantype) = 1 AND (((CONVERT(int, b.cmttrantype) % 2) = 0 OR b.cmttrantype IN ('111', '115', '117')) AND b.cmttrantype NOT IN ('110', '116', '118')) THEN 1 END, COALESCE(a.DriverName, f1.DriverFirstName, f.DriverFirstName, f1r.DriverFirstName, fr.DriverFirstName) AS FirstName, COALESCE(a.DriverMiddleName, f1.DriverMiddleInitial, f.DriverMiddleInitial, f1r.DriverMiddleInitial, fr.DriverMiddleInitial) AS MiddleInitial, COALESCE(a.DriverSurName, f1.DriverSurname, f.DriverSurname, f1r.DriverSurname, fr.DriverSurname) AS Lastname, ISNULL(a.FleetCardVehicleNo,ISNULL(un.UnitNumber, '')), SUBSTRING(b.PrimaryAccountNumber, 15, 4), @acctid, e.atid, NULL AS invoicenumber, --z.invoicenumber, NULL AS invoicedates, --z.invoicedates, NULL AS invoicedate,--z.invoicedate, NULL AS statementid,--z.statementid, FAI.FleetName, FAI.FleetAddress, FAI.FleetAddress2, FAI.FleetContact, FAI.FleetFax, FII.InstitutionID, FII.InstAddress1, FII.InstAddress2, FII.InstPhone, @imagepath, ISNULL(n.ContactNameDBA, ISNULL(n1.ContactNameDBA, '')), ISNULL(mm.[Gallons_YTD], 0.00), ISNULL(mm.MPG_YTD, 0.00), ISNULL(mm.[Amount_YTD], 0.00), ISNULL(mm.CPM_YTD, 0.00), (ISNULL(mm.[Exemptions_YTD], 0.00) * -1), ISNULL(mm.MPG_YTD, 0.00), (ISNULL(mm.[Disc_YTD], 0.00) * -1), ISNULL(mm.CPM_YTD, 0.00), ISNULL(mm.Gross_YTD, 0.00), 0 [mPlusCheck], 0 [mPermit], 0 [mTire], 0 [mMisc], 0 [mEd], 0 [mDirect], 0 [mPayment], 0 [mSummary], 0 [mAccountTotals], 0 [mPromo], CAST(0.00 AS money), CAST(0.00 AS money), CAST(0.00 AS money), CAST(0.00 AS money), c.BillType, CAST('' AS varchar(15)), '', '', 0, '', COALESCE(ml.prevodometer, 0), COALESCE(ml.miles, 0), COALESCE(mm.[Miles_YTD], 0), 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00 FROM THISDATABASE.dbo.ccard_primary AS b WITH (READUNCOMMITTED) INNER JOIN THISDATABASE.dbo.ccard_secondary AS a WITH (READUNCOMMITTED) ON a.tranid = b.tranid INNER JOIN @RD_Fleet_inv_list AS z ON z.tranid = b.tranid LEFT JOIN THISDATABASE.dbo.CCardLineItems AS c WITH (READUNCOMMITTED) ON c.UniqueId = a.TranId LEFT JOIN THISDATABASE.dbo.CCARDLOOKUP AS k WITH (READUNCOMMITTED) ON k.LutCode = c.ProductCode1 AND k.LutId = 'ProductCode' LEFT JOIN THATDATABASE.dbo.CoreLibLUT AS p WITH (READUNCOMMITTED) ON p.LutCode = CONVERT(char(30), b.ProgramName) AND p.LutId = 'ProgramMgt' LEFT JOIN THISDATABASE.[DBO].TrancodeLookup AS d WITH (READUNCOMMITTED) ON d.Lutcode = b.Txncode_Internal AND d.lutid = 'TranCode' LEFT JOIN THISDATABASE.[DBO].bsegment_primary AS e WITH (READUNCOMMITTED) ON e.accountnumber = b.AccountNumber AND e.FleetNumber = b.FleetNumber LEFT OUTER JOIN THISDATABASE.[DBO].ccardlookup AS cl WITH (READUNCOMMITTED) ON cl.LutCode = e.BillingCycle AND cl.LUTid = 'BillingCycle' LEFT JOIN ANOTHERDATABASE.DBO.usedpluscheks AS u WITH (READUNCOMMITTED) ON CONVERT(varchar(24), U.TranID) = b.uniqueid LEFT JOIN THISDATABASE.[DBO].Payments AS Pt WITH (READUNCOMMITTED) ON CONVERT(varchar(24), Pt.TranId) = b.uniqueid LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS f WITH (READUNCOMMITTED) ON (f.accountlevel = '0' AND a.FleetCardIdDriverNo = f.drivernumber AND @AcctID = f.CompanyAcctID) AND ((f.CardNumber IS NOT NULL AND b.primaryaccountnumber = f.CardNumber) OR (f.CardNumber IS NULL)) AND (b.[posttime] BETWEEN ISNULL(f.activedate, '1900-01-01') AND ISNULL(f.inactivedate, '2900-12-31')) LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS f1 WITH (READUNCOMMITTED) ON (a.FleetCardIdDriverNo = f1.drivernumber AND b.primaryaccountnumber = f1.cardnumber AND f1.accountlevel = '1' AND (b.[posttime] BETWEEN ISNULL(f1.activedate, '1900-01-01') AND ISNULL(f1.inactivedate, '2900-12-31'))) LEFT JOIN ANOTHERDATABASE.dbo.UnitNumbers AS un WITH (READUNCOMMITTED) ON b.primaryaccountnumber = un.CardNumber AND b.posttime >= un.ActiveDate AND b.posttime < ISNULL(un.InactiveDate, GETDATE()) LEFT JOIN @RD_Fleet_Acct_Info AS FAI ON FAI.FleetNumber = b.FleetNumber LEFT JOIN @RD_Fleet_Inst_Info AS FII ON FII.InstitutionID = FAI.InstitutionID LEFT JOIN @RD_Fleet_inv_list AS FIL ON FIL.FleetNumber = b.FleetNumber LEFT JOIN THISDATABASE.[DBO].nameaddraccounts AS n WITH (READUNCOMMITTED) ON CASE WHEN e.parent01aid IS NULL THEN e.AcctID ELSE e.parent01aid END = n.parent02aid AND n.AddressType = '0' LEFT JOIN THISDATABASE.[DBO].nameaddraccounts AS n1 WITH (READUNCOMMITTED) ON n1.Acctid = N.parent01aid AND n1.AddressType = '0' LEFT JOIN @Mileage_YTD AS mm ON mm.pan = b.primaryaccountnumber AND b.trantime = mm.trandate LEFT JOIN @Miles AS ml ON ml.pan = b.primaryaccountnumber AND ml.tranid = CONVERT(varchar(20), b.tranid) AND ml.trantime = b.trantime LEFT JOIN THISDATABASE.[DBO].SpecificStatementMessages AS st WITH (READUNCOMMITTED) ON st.AccountID = FAI.CompanyAcctID LEFT JOIN THISDATABASE.dbo.ccard_primary AS br WITH (READUNCOMMITTED) ON br.tranid = b.RevTgt LEFT JOIN THISDATABASE.dbo.ccard_secondary AS ar WITH (READUNCOMMITTED) ON ar.TranId = br.TranId LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS fr WITH (READUNCOMMITTED) ON (fr.accountlevel = '0' AND a.FleetCardIdDriverNo = fr.drivernumber AND @AcctID = fr.CompanyAcctID) AND ((fr.CardNumber IS NOT NULL AND b.primaryaccountnumber = fr.CardNumber) OR (fr.CardNumber IS NULL)) AND (br.[posttime] BETWEEN ISNULL(fr.activedate, '1900-01-01') AND ISNULL(fr.inactivedate, '2900-12-31')) LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS f1r WITH (READUNCOMMITTED) ON (ar.FleetCardIdDriverNo = f1r.drivernumber AND br.primaryaccountnumber = f1r.cardnumber AND f1r.accountlevel = '1' AND (br.[posttime] BETWEEN ISNULL(f1r.activedate, '1900-01-01') AND ISNULL(f1r.inactivedate, '2900-12-31'))) WHERE b.cmttrantype NOT IN ('Nch', 'QNA', '*SCR', '110', '113', '121', '122', '88', '89', '127','114','117','26','24') AND (NOT(LTRIM(RTRIM(ISNULL(b.Transactionidentifier,'0'))) = '1' and b.cmttrantype in ('49','43'))) AND ISNULL(ROUND(RepricedTranAmount - ((CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN c.FedTaxAmount ELSE 0 END) + (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN c.StateTaxAmount ELSE 0 END) + (CASE WHEN c.CountyFuelTaxExemptStatus = 'Y' THEN c.CountyTaxAmount ELSE 0 END) + (CASE WHEN c.CityFuelTaxExemptStatus = 'Y' THEN c.CityTaxAmount ELSE 0 END) + (CASE WHEN c.LocalSalesTaxExemptStatus = 'Y' THEN c.LocalTax ELSE 0 END) + (CASE WHEN c.Misc1ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount1 ELSE 0 END) + (CASE WHEN c.Misc2ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount2 ELSE 0 END) + (CASE WHEN c.StateSalesTaxExemptStatus = 'Y' THEN c.StateSalesTaxAmount ELSE 0 END) + (CASE WHEN c.CountySalesTaxExemptStatus = 'Y' THEN c.CountySalesTaxAmount ELSE 0 END) + (CASE WHEN c.CitySalesTaxExemptStatus = 'Y' THEN c.CitySalesTaxAmount ELSE 0 END) + (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN c.MiscSalesTaxAmount ELSE 0 END) + (CASE WHEN c.FreightExemptStatus = 'Y' THEN c.Freight ELSE 0 END)), 2), b.transactionamount) <> 0.00 GROUP BY c.tranid, b.PrimaryAccountNumber, b.trantime, b.posttime, ISNULL(b.AuthDateTime,b.posttime), a.taxexemptTotalBalance, c.Transactionamount, a.originalamount_, c.RepricedTranAmount, c.StateTaxAmount, c.FedTaxAmount, c.Freight, c.originalAmount_, b.cmttrantype, a.tranid, b.uniqueid, a.OdometerReading, b.storename, c.quantity, b.ProgramName, b.PORefNumber, b.storecity, b.storestate, c.unitofcost, c.FedExciseTaxExemptStatus, c.StateFuelTaxExemptStatus, c.CountyFuelTaxExemptStatus, c.CityFuelTaxExemptStatus, c.LocalSalesTaxExemptStatus, c.Misc1ExciseTaxExemptStatus, c.Misc2ExciseTaxExemptStatus, c.StateSalesTaxExemptStatus, c.CountySalesTaxExemptStatus, c.CitySalesTaxExemptStatus, c.MiscSalesTaxExemptStatus, c.FreightExemptStatus, c.CountyTaxAmount, c.CityTaxAmount, c.LocalTax, c.MiscTaxAmount1, c.MiscTaxAmount2, c.StateSalesTaxAmount, c.CountySalesTaxAmount, c.CitySalesTaxAmount, c.MiscSalesTaxAmount, c.Freight, c.[productcode1], a.[ApprovalCode], b.[txncode_internal], b.[tranid], b.cmttrantype, a.FleetCardIdDriverNo, a.[ResponseCode], a.EDTransactionFlag, a.PermitState, a.PermitNumber, b.FleetNumber, a.TrailerNumber, b.DriverLicenseNumber, b.TripNumber, a.DealerName, a.DealerAddressLine1, k.LutDescription, a.EDTXnFlag, a.[TxnFlag], b.TransactionDescription, b.RevTgt, b.tranref, --FIL.invoicenumber, --b.InvoiceNumber, CONVERT(BIGINT,LTRIM(RTRIM(b.InvoiceNumber))), p.lutdescription, c.[ProductType], d.lutdescription, e.Parent01AID, e.acctid, cl.LutDescription, u.CheckNumber, u.CheckAmount, Pt.CreditType, f1.DriverFirstName, f.DriverFirstName, f1.DriverMiddleInitial, f.DriverMiddleInitial, f1.DriverSurname, f.DriverSurname, a.FleetCardVehicleNo, un.unitnumber, e.atid, --z.invoicenumber, --z.invoicedates, --z.invoicedate, --z.statementid, FAI.FleetName, FAI.FleetAddress, FAI.FleetAddress2, FAI.FleetContact, FAI.FleetFax, FII.InstitutionID, FII.InstAddress1, FII.InstAddress2, FII.InstPhone, n.ContactNameDBA, n1.ContactNameDBA, mm.mpg_YTD, mm.cpm_YTD, c.BillType, b.transactionamount, ml.prevodometer, ml.miles, mm.[Gallons_YTD], mm.[Amount_YTD], mm.[Exemptions_YTD], mm.[Disc_YTD], mm.[Gross_YTD], mm.[Miles_YTD], br.storecity, br.storestate, a.DriverName, a.DriverMiddleName, a.DriverSurName, f1r.DriverFirstName, fr.DriverFirstName, f1r.DriverMiddleInitial, fr.DriverMiddleInitial, f1r.DriverSurname, fr.DriverSurname, br.PORefNumber, ar.FleetCardIdDriverNo [/code]

Query performance

Posted: 29 Aug 2013 12:52 AM PDT

I have the following SQL query which is executed in a dynamic SQL statement:[code="sql"]SELECT a.id, b.id,CRS.* FROM QBMerge.fo_manchester_keys_ AS aINNER JOIN QBMerge.fo_manchester_keys_ AS b ON a.MatchKeyType13 = b.MatchKeyType13 AND a.RAND_ID > b.RAND_ID AND NOT EXISTS (SELECT 1 FROM ##fo_manchester_LargeClusters_ as d WHERE d.MatchKey = a.MatchKeyType13)CROSS APPLY dbo.CompleteRecordScoring( a.NormalisedForename, a.NormalisedMiddleName, a.NormalisedSurname, a.PhoneticForename, a.PhoneticMiddleName, a.PhoneticSurname, a.NormalisedOrganisationName, a.NormalisedOrganisationName2, a.Premise, a.AddressKey, a.POstOut, a.PostIn, a.HomeTelephoneNumber, a.MobileTelephoneNumber, a.EmailAddress, b.NormalisedForename, b.NormalisedMiddleName, b.NormalisedSurname, b.PhoneticForename, b.PhoneticMiddleName, b.PhoneticSurname, b.NormalisedOrganisationName, b.NormalisedOrganisationName2, b.Premise, b.AddressKey, b.PostOut, b.POstIn, b.HomeTelephoneNumber, b.MobileTelephoneNumber, b.EmailAddress, 1) AS CRS[/code]If I run the query without the CROSS APPLY, it takes a few seconds, however when I include the CLR TVF it takes minutes to run.The weird thing is, it's not my CLR TVF that's taking long, I have used this code before and it can process hundreds of thousands of rows in a few seconds. I have also added a start end time for each row returned by the CLR TVF and the times are very low (hundredths of milliseconds if that).The table :[img]http://i.imgur.com/dmogfM2.jpg[/img]The INDEX on MatchKeyType13:[code="sql"]CREATE NONCLUSTERED INDEX [IX_MatchKeyType13] ON [QBMerge].[fo_manchester_keys_]( [MatchKeyType13] ASC, [Hierarchy] ASC, [RAND_ID] ASC)INCLUDE ( [ID], [NormalisedSurname], [NormalisedForename], [NormalisedMiddleName], [PhoneticSurname], [PhoneticForename], [PhoneticMiddleName], [NormalisedOrganisationName], [NormalisedOrganisationName2], [Premise], [AddressKey], [PostOut], [PostIn], [HomeTelephoneNumber], [MobileTelephoneNumber], [EmailAddress]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO[/code]Another interesting observation when I run the same code on other MatchKeyTypes, I notice the CPU utilisation is maxed out and the query can generate more than a million rows per minute, however, on some match keys the CPU utilisation is more erratic and that's when it takes minutes to do a few hundred thousand records!

Drop Constraints, Truncate and Re-Create Constraints

Posted: 28 Aug 2013 10:58 PM PDT

Hi, I am working on a SP that for a given database;1. Drops the referential integrity constraints.2. Truncates the tables.3. Re-creates the integrity constraints.I started testing the code and the code seems to drop the constraints well but it kind of bugs while Truncating the tables. I am using INFORMATION_SCHEMA to generate the temp table of table's constraint list, the dynamic code generation for Truncation seems to fail.I desperately need to fix this on priority. Here is the code:-----------------------------------------------------------------------------------CREATE PROCEDURE usp_TruncateTableData_ConstraintDropAddASBEGIN SET NOCOUNT ON; DECLARE @FK_TableSchema NVARCHAR(200), @FK_TableName NVARCHAR(200), @FK_Name NVARCHAR(200), @FK_ColumnName NVARCHAR(200), @PK_TableSchema NVARCHAR(200), @PK_TableName NVARCHAR(200), @PK_ColumnName NVARCHAR(200), @DROP VARCHAR(MAX), @ADD VARCHAR(MAX), @TRUNC VARCHAR(MAX) DECLARE @table TABLE( RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200), PrimaryKeyConstraintName NVARCHAR(200), PrimaryKeyConstraintTableSchema NVARCHAR(200), PrimaryKeyConstraintTableName NVARCHAR(200), PrimaryKeyConstraintColumnName NVARCHAR(200) ) INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName) SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintColumnName = COLUMN_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME --SELECT * FROM @table --------------------------------------------------------------------------------------------------------- --DROP CONSTRAINT: DECLARE FK_DROP CURSOR FOR SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintName FROM @table OPEN FK_DROP FETCH NEXT FROM FK_DROP INTO @FK_TableSchema, @FK_TableName, @FK_Name WHILE @@FETCH_STATUS = 0 BEGIN SET @DROP = 'ALTER TABLE [' + @FK_TableSchema + '].[' + @FK_TableName + '] DROP CONSTRAINT ' + @FK_Name EXECUTE(@DROP) --PRINT @DROP FETCH NEXT FROM FK_DROP INTO @FK_TableSchema, @FK_TableName, @FK_Name END CLOSE FK_DROP DEALLOCATE FK_DROP --------------------------------------------------------------------------------------------------------- --TRUNCATE TABLES: DECLARE FK_TRUNC CURSOR FOR SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName FROM @table OPEN FK_TRUNC FETCH NEXT FROM FK_TRUNC INTO @FK_TableSchema, @FK_TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @TRUNC = 'TRUNCATE TABLE [' + @FK_TableName + ']' EXECUTE(@TRUNC) --PRINT @TRUNC FETCH NEXT FROM FK_TRUNC INTO @FK_TableSchema, @FK_TableName END CLOSE FK_TRUNC DEALLOCATE FK_TRUNC --------------------------------------------------------------------------------------------------------- --ADD CONSTRAINT: DECLARE FK_ADD CURSOR FOR SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintName, ForeignKeyConstraintColumnName, PrimaryKeyConstraintTableSchema, PrimaryKeyConstraintTableName, PrimaryKeyConstraintColumnName FROM @table OPEN FK_ADD FETCH NEXT FROM FK_ADD INTO @FK_TableSchema, @FK_TableName, @FK_Name, @FK_ColumnName, @PK_TableSchema, @PK_TableName, @PK_ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SET @ADD = 'ALTER TABLE [' + @FK_TableSchema + '].[' + @FK_TableName + '] ADD CONSTRAINT ' + @FK_Name + ' FOREIGN KEY(' + @FK_ColumnName + ') REFERENCES [' + @PK_TableSchema + '].[' + @PK_TableName + '](' + @PK_ColumnName + ')' EXECUTE(@ADD) --PRINT @ADD FETCH NEXT FROM FK_ADD INTO @FK_TableSchema, @FK_TableName, @FK_Name, @FK_ColumnName, @PK_TableSchema, @PK_TableName, @PK_ColumnName END CLOSE FK_ADD DEALLOCATE FK_ADD --------------------------------------------------------------------------------------------------------- ENDGO-----------------------------------------------------------------------------------

exists() not working in INSTEAD OF INSERT trigger

Posted: 28 Aug 2013 01:28 AM PDT

I do not understand why the exists() tests are not working. I'm using SQL Server 2008 R2.Here's the code:[code="plain"]create trigger dbo.io_trigger_Insert_Alert on dbo.ALERT instead of insertasbegin set nocount on; -- Row exists if exists (select * from dbo.ALERT a, inserted i where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType) begin -- Move record to history insert into dbo.ALERT_STATUS_HISTORY ( OBJECTID, StopID, RouteID, ServiceCenterID, AlertType, Description, TimeRaised, AlertStatus, HandledBy, HandledDateTime, AlertID) select OBJECTID, StopID, RouteID, ServiceCenterID, AlertType, Description, TimeRaised, AlertStatus, HandledBy, HandledDateTime, AlertID from dbo.ALERT; -- Update AlertStatus if exists (select * from dbo.ALERT a, inserted i where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType and a.AlertStatus = N'X') begin update dbo.ALERT set AlertStatus = N'N' from dbo.ALERT a, inserted i where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType; end endend[/code]

How to reordering or removing one identity number

Posted: 28 Aug 2013 05:21 PM PDT

Hi,I have set the identity columns to -1,1 but I want remove the number zero from the identity sequence example below.Currently --Identity column -1[b]0[/b]1234And so on.What I want is ---1[b]1[/b]2345And so on.Basically removing the the first zero. Is it possible?I don't mind if I have to truncate the table.Thanks

HElp with a query!!

Posted: 28 Aug 2013 08:28 AM PDT

Hi all i have this data:[code="sql"]DECLARE @data table (id int,name varchar(max),parent_id int,node_type_id int,node_order INT)INSERT @data SELECT 2,'ClarkDietrich',NULL,1,1 UNION ALLSELECT 293,'CLARK_3.29_Testing_Plan',2,5,0 UNION ALLSELECT 294,'3.29',2,6,2 UNION ALLSELECT 295,'Add the Total # of Coils to the Setup Report',294,7,0 UNION ALLSELECT 296,NULL,295,8,0 UNION ALLSELECT 297,'Pull From Stock ? Gauge Code',294,7,1 UNION ALLSELECT 298,NULL,297,8,0 UNION ALLSELECT 299,'Processing Coil Order Summary Report Additional - ENH',294,7,2 UNION ALLSELECT 300,NULL,299,8,0 UNION ALLSELECT 301,'Add Machine validation in Mill Assignment Edit screen',294,7,3 UNION ALLSELECT 302,NULL,301,8,0 UNION ALLSELECT 308,'TS_SYSTEM ADMINISTRATION',311,2,0 UNION ALLSELECT 309,'TS_SHOP ORDER',311,2,7 UNION ALLSELECT 310,'TS_3.29.0.0',2,2,3 UNION ALLSELECT 311,'TS_MASTER',2,2,0 UNION ALLSELECT 312,'FR60_TC_Machine Maintenance',308,3,100 UNION ALLSELECT 313,NULL,312,4,0 UNION ALLSELECT 314,NULL,313,9,0 UNION ALLSELECT 315,NULL,313,9,0 UNION ALLSELECT 316,NULL,313,9,0 UNION ALLSELECT 317,'FR60_TC_Machine Maintenance',325,3,0 UNION ALLSELECT 318,NULL,317,4,0 UNION ALLSELECT 319,NULL,318,9,0 UNION ALLSELECT 320,NULL,318,9,0 UNION ALLSELECT 321,NULL,318,9,0 UNION ALLSELECT 322,NULL,318,9,0 UNION ALLSELECT 323,NULL,318,9,0 UNION ALLSELECT 325,'TS_IM5972',310,2,1 UNION ALLSELECT 326,'TS_SALES_ORDER',311,2,1 UNION ALLSELECT 327,'FR60_TC_Mill Assignment Edit',309,3,1 UNION ALLSELECT 328,NULL,327,4,0 UNION ALLSELECT 329,'TS_RFDEVICES',311,2,2 UNION ALLSELECT 330,NULL,328,9,0 UNION ALLSELECT 331,NULL,328,9,0 UNION ALLSELECT 332,'TC_RF_Pull_From_Stock',17853,3,0 UNION ALLSELECT 333,NULL,332,4,0 UNION ALLSELECT 334,NULL,328,9,0 UNION ALLSELECT 335,NULL,328,9,0 UNION ALLSELECT 336,NULL,328,9,0 UNION ALLSELECT 337,NULL,333,9,0 UNION ALLSELECT 338,NULL,328,9,0 UNION ALLSELECT 339,'FR60_TC_Mill Assignment Edit',325,3,102 UNION ALLSELECT 340,NULL,339,4,0 UNION ALLSELECT 341,NULL,340,9,0 UNION ALLSELECT 342,NULL,340,9,0 UNION ALLSELECT 343,NULL,340,9,0 UNION ALLSELECT 344,NULL,340,9,0 UNION ALLSELECT 345,NULL,340,9,0 UNION ALLSELECT 346,NULL,340,9,0 UNION ALLSELECT 347,NULL,333,9,0 UNION ALLSELECT 349,'TS_IM5995',310,2,2 UNION ALLSELECT 354,'FR60_TC_Validating Other Modules',325,3,103 UNION ALLSELECT 355,NULL,354,4,0 UNION ALLSELECT 356,NULL,355,9,0 UNION ALLSELECT 357,NULL,355,9,0 UNION ALLSELECT 358,NULL,355,9,0 UNION ALLSELECT 359,NULL,355,9,0 UNION ALLSELECT 360,NULL,355,9,0 UNION ALLSELECT 361,NULL,355,9,0 UNION ALLSELECT 362,NULL,355,9,0 UNION ALLSELECT 363,NULL,355,9,0 UNION ALLSELECT 364,NULL,355,9,0 UNION ALLSELECT 365,NULL,355,9,0 UNION ALLSELECT 366,NULL,355,9,0 UNION ALLSELECT 367,NULL,302,10,0 UNION ALLSELECT 368,NULL,333,9,0 UNION ALLSELECT 369,NULL,333,9,0 UNION ALLSELECT 370,NULL,333,9,0 UNION ALLSELECT 371,NULL,333,9,0 UNION ALLSELECT 383,'TC_RF_Pull_From_Stock',349,3,0 UNION ALLSELECT 384,NULL,383,4,0 UNION ALLSELECT 385,NULL,384,9,0 UNION ALLSELECT 386,NULL,384,9,0 UNION ALLSELECT 387,NULL,384,9,0 UNION ALLSELECT 388,NULL,384,9,0 UNION ALLSELECT 389,NULL,384,9,0 UNION ALLSELECT 390,NULL,384,9,0 UNION ALLSELECT 391,'TS_INVENTORY',311,2,3 UNION ALLSELECT 392,'TS_SETUP',391,2,1 UNION ALLSELECT 393,'Change the Customer information on BOL',294,7,4 UNION ALLSELECT 394,NULL,393,8,0 UNION ALLSELECT 395,'TC_SETUP_MAINTENANCE_REPORT',392,3,100 UNION ALLSELECT 396,NULL,395,4,0 UNION ALLSELECT 397,'TS_TRAFFIC',311,2,4 UNION ALLSELECT 398,NULL,396,9,0 UNION ALLSELECT 399,NULL,396,9,0 UNION ALLSELECT 400,NULL,396,9,0 UNION ALLSELECT 401,'FR60_TC_Reprint Bill of Lading',397,3,1 UNION ALLSELECT 402,NULL,401,4,0 UNION ALLSELECT 403,NULL,396,9,0 UNION ALLSELECT 405,'TS_IM5932',310,2,3 UNION ALLSELECT 406,'TC_SETUP_MAINTENANCE_REPORT',405,3,0 UNION ALLSELECT 407,NULL,406,4,0 UNION ALLSELECT 408,NULL,407,9,0 UNION ALLSELECT 409,NULL,407,9,0 UNION ALLSELECT 410,NULL,407,9,0 UNION ALLSELECT 411,NULL,407,9,0 UNION ALLSELECT 412,NULL,402,9,0 UNION ALLSELECT 414,NULL,402,9,0 UNION ALLSELECT 415,NULL,402,9,0 UNION ALLSELECT 416,NULL,402,9,0 UNION ALLSELECT 417,NULL,402,9,0 UNION ALLSELECT 418,'TS_IM6039',310,2,4 UNION ALLSELECT 419,'FR60_TC_Reprint Bill of Lading',418,3,0 UNION ALLSELECT 420,NULL,419,4,0 UNION ALLSELECT 421,NULL,420,9,0 UNION ALLSELECT 422,NULL,420,9,0 UNION ALLSELECT 423,NULL,420,9,0 UNION ALLSELECT 424,NULL,420,9,0 UNION ALLSELECT 425,NULL,420,9,0 UNION ALLSELECT 293,'CLARK_3.29_Testing_Plan',2,5,0 UNION ALLSELECT 294,'3.29',2,6,2 UNION ALLSELECT 295,'Add the Total # of Coils to the Setup Report',294,7,0 UNION ALLSELECT 296,NULL,295,8,0 UNION ALLSELECT 297,'Pull From Stock ? Gauge Code',294,7,1 UNION ALLSELECT 298,NULL,297,8,0 UNION ALLSELECT 299,'Processing Coil Order Summary Report Additional - ENH',294,7,2 UNION ALLSELECT 300,NULL,299,8,0 UNION ALLSELECT 301,'Add Machine validation in Mill Assignment Edit screen',294,7,3 UNION ALLSELECT 302,NULL,301,8,0 UNION ALLSELECT 308,'TS_SYSTEM ADMINISTRATION',311,2,0 UNION ALLSELECT 309,'TS_SHOP ORDER',311,2,7 UNION ALLSELECT 310,'TS_3.29.0.0',2,2,3 UNION ALLSELECT 311,'TS_MASTER',2,2,0 UNION ALLSELECT 312,'FR60_TC_Machine Maintenance',308,3,100 UNION ALLSELECT 313,NULL,312,4,0 UNION ALLSELECT 314,NULL,313,9,0 UNION ALLSELECT 315,NULL,313,9,0 UNION ALLSELECT 316,NULL,313,9,0 UNION ALLSELECT 317,'FR60_TC_Machine Maintenance',325,3,0 UNION ALLSELECT 318,NULL,317,4,0 UNION ALLSELECT 319,NULL,318,9,0 UNION ALLSELECT 320,NULL,318,9,0 UNION ALLSELECT 321,NULL,318,9,0 UNION ALLSELECT 322,NULL,318,9,0 UNION ALLSELECT 323,NULL,318,9,0 UNION ALLSELECT 325,'TS_IM5972',310,2,1 UNION ALLSELECT 326,'TS_SALES_ORDER',311,2,1 UNION ALLSELECT 327,'FR60_TC_Mill Assignment Edit',309,3,1 UNION ALLSELECT 328,NULL,327,4,0 UNION ALLSELECT 329,'TS_RFDEVICES',311,2,2 UNION ALLSELECT 330,NULL,328,9,0 UNION ALLSELECT 331,NULL,328,9,0 UNION ALLSELECT 332,'TC_RF_Pull_From_Stock',17853,3,0 UNION ALLSELECT 333,NULL,332,4,0 UNION ALLSELECT 334,NULL,328,9,0 UNION ALLSELECT 335,NULL,328,9,0 UNION ALLSELECT 336,NULL,328,9,0 UNION ALLSELECT 337,NULL,333,9,0 UNION ALLSELECT 338,NULL,328,9,0 UNION ALLSELECT 339,'FR60_TC_Mill Assignment Edit',325,3,102 UNION ALLSELECT 340,NULL,339,4,0 UNION ALLSELECT 341,NULL,340,9,0 UNION ALLSELECT 342,NULL,340,9,0 UNION ALLSELECT 343,NULL,340,9,0 UNION ALLSELECT 344,NULL,340,9,0 UNION ALLSELECT 345,NULL,340,9,0 UNION ALLSELECT 346,NULL,340,9,0 UNION ALLSELECT 347,NULL,333,9,0 UNION ALLSELECT 349,'TS_IM5995',310,2,2 UNION ALLSELECT 354,'FR60_TC_Validating Other Modules',325,3,103 UNION ALLSELECT 355,NULL,354,4,0 UNION ALLSELECT 356,NULL,355,9,0 UNION ALLSELECT 357,NULL,355,9,0 UNION ALLSELECT 358,NULL,355,9,0 UNION ALLSELECT 359,NULL,355,9,0 UNION ALLSELECT 360,NULL,355,9,0 UNION ALLSELECT 361,NULL,355,9,0 UNION ALLSELECT 362,NULL,355,9,0 UNION ALLSELECT 363,NULL,355,9,0 UNION ALLSELECT 364,NULL,355,9,0 UNION ALLSELECT 365,NULL,355,9,0 UNION ALLSELECT 366,NULL,355,9,0 UNION ALLSELECT 367,NULL,302,10,0 UNION ALLSELECT 368,NULL,333,9,0 UNION ALLSELECT 369,NULL,333,9,0 UNION ALLSELECT 370,NULL,333,9,0 UNION ALLSELECT 371,NULL,333,9,0 UNION ALLSELECT 383,'TC_RF_Pull_From_Stock',349,3,0 UNION ALLSELECT 384,NULL,383,4,0 UNION ALLSELECT 385,NULL,384,9,0 UNION ALLSELECT 386,NULL,384,9,0 UNION ALLSELECT 387,NULL,384,9,0 UNION ALLSELECT 388,NULL,384,9,0 UNION ALLSELECT 389,NULL,384,9,0 UNION ALLSELECT 390,NULL,384,9,0 UNION ALLSELECT 391,'TS_INVENTORY',311,2,3 UNION ALLSELECT 392,'TS_SETUP',391,2,1 UNION ALLSELECT 393,'Change the Customer information on BOL',294,7,4 UNION ALLSELECT 394,NULL,393,8,0 UNION ALLSELECT 395,'TC_SETUP_MAINTENANCE_REPORT',392,3,100 UNION ALLSELECT 396,NULL,395,4,0 UNION ALLSELECT 397,'TS_TRAFFIC',311,2,4 UNION ALLSELECT 398,NULL,396,9,0 UNION ALLSELECT 399,NULL,396,9,0 UNION ALLSELECT 400,NULL,396,9,0 UNION ALLSELECT 401,'FR60_TC_Reprint Bill of Lading',397,3,1 UNION ALLSELECT 402,NULL,401,4,0 UNION ALLSELECT 403,NULL,396,9,0 UNION ALLSELECT 405,'TS_IM5932',310,2,3 UNION ALLSELECT 406,'TC_SETUP_MAINTENANCE_REPORT',405,3,0 UNION ALLSELECT 407,NULL,406,4,0 UNION ALLSELECT 408,NULL,407,9,0 UNION ALLSELECT 409,NULL,407,9,0 UNION ALLSELECT 410,NULL,407,9,0 UNION ALLSELECT 411,NULL,407,9,0 UNION ALLSELECT 412,NULL,402,9,0 UNION ALLSELECT 414,NULL,402,9,0 UNION ALLSELECT 415,NULL,402,9,0 UNION ALLSELECT 416,NULL,402,9,0 UNION ALLSELECT 417,NULL,402,9,0 UNION ALLSELECT 418,'TS_IM6039',310,2,4 UNION ALLSELECT 419,'FR60_TC_Reprint Bill of Lading',418,3,0 UNION ALLSELECT 420,NULL,419,4,0 UNION ALLSELECT 421,NULL,420,9,0 UNION ALLSELECT 422,NULL,420,9,0 UNION ALLSELECT 423,NULL,420,9,0 UNION ALLSELECT 424,NULL,420,9,0 UNION ALLSELECT 425,NULL,420,9,0SELECT * FROM @data[/code]This table have the relationship to itself by the parent_id column, I would sort through so you can display hierarchically.Anyone can help me with this?..thanks!

help for get max column when store with xml

Posted: 28 Aug 2013 08:39 AM PDT

Hello FriendI can not speak good Englishsorryi want for store data in database of c#1-I have an xml string in c#2-I sent the following procedure3-And without any problems.4-store in table.5-My problem is:I need to get the greatest column value from tableI added it to 1 and I recorded the new recordALTER PROCEDURE [dbo].[SpTest] @Xml xmlASBEGIN DECLARE @ih INTEXEC sp_XML_preparedocument @ih output, @XmlINSERT TableNameSELECT *FROM OPENXML(@ih, 'TBL_FACTOR/RD_FACTOR')WITH TableNameEXEC sp_XML_removedocument @ih

How to change this select statement?

Posted: 28 Aug 2013 02:44 AM PDT

[code="sql"]SELECT SUM(ISNULL(ord_totalweight, 0)) AS Expr1 FROM dbo.orderheader WITH (NOLOCK) WHERE (mov_number = TripSegment. 'Move Number')) AS [Total Weight],(SELECT SUM(ISNULL(ord_totalpieces, 0)) AS Expr1 FROM dbo.orderheader WITH (NOLOCK) WHERE (mov_number = TripSegment. 'Move Number')) AS [Total Pieces], [/code]This statement here references the orderheader table to retrieve information about the mov_number's weight information. The weight information here is incomplete and the DB is not normalized - so a better source for the data has been found in a dbo.stops table.I have wrote this statement below, which references the stops table that seems to be the ultimate authority for ord_totalweight as far as I can see. It pulls...the right information at least.[code="sql"]select SUM(ord_totalweight) AS Weight, SUM(ord_totalpieces) AS Pieces from orderheader where ord_hdrnumber in (select distinct ord_hdrnumber from stops where mov_number = '213135')[/code]How can I rewrite the original statement to reference the stops table? I used mov_number 213135 as an example in my query above. because mov_number 213135 does not have its weight information stored in dbo.orderheader. 213135 does have it stored in dbo.stops though.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog