[T-SQL] Table Value Function VS ORs

---

• Table Value Function VS ORs
• Conditional join update
• 6 places after decimal without rounding
• Does a SP get precompiled?
• Database Mail + sysmail_start_sp
• How to report period wise data ?
• Help with execution Plan
• date problem
• how to write a column expression in a query based on other table value exist
• TabLock and TablLockX Confusion
• Advise on working with XML
• need to return the last value
• JOIN problems
• Index filled after created
• Number of days between two integer date columns
• Working on MERGE statement to Handle SCD

Table Value Function VS ORs Posted: 04 Mar 2013 09:22 PM PST Hi,I'm executing the following queries:[code="sql"]UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano=2012 AND Moeda = 'EUR' AND (Conta = '111' OR Conta = '11')UPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 FROM (SELECT SubConta FROM dbo.GetSubContasTVF('111')) t WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta = t.SubContaUPDATE AcumuladosContas SET MES12DB = MES12DB + 5000.00 WHERE TipoLancamento = '000' AND Ano = 2012 AND Moeda = 'EUR' AND Conta IN (SELECT SubConta FROM dbo.GetSubContasTVF('111'))[/code]The TVF is:[code="sql"]ALTER FUNCTION [dbo].[GetSubContasTVF](@Conta NVARCHAR(20))RETURNS @SubContas TABLE (SubConta NVARCHAR(20) NOT NULL PRIMARY KEY)ASBEGIN INSERT INTO @SubContas SELECT SUBSTRING(@Conta, ID, LEN(@Conta)) SubConta FROM PriTally WHERE ID BETWEEN 1 AND LEN(@Conta) - 1 RETURN;END[/code]In SSMS the execution plan for the queries states that the OR takes 30% and the other two 35% each.In SQL Sentry Plan Explorer the OR takes 22%, the 2nd 52% and the 3rd 26%...When using SQLStress the OR is faster with only one thread but when using multiple threads the other ones are faster...What's the best query?Is there any other query to perform the operation?Thanks,Pedro

Conditional join update Posted: 04 Mar 2013 11:57 PM PST Is there a better way of doing this?IF object_id('tempdb..#Temp1') IS NOT NULLBEGIN DROP TABLE #Temp1ENDCREATE TABLE #temp1(Source VARCHAR(5),MPANcore BIGINT,EffectiveFromDate DATETIME,DatafileID bigint,DataSiteConfigATableName VARCHAR(60))Insert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromSettlementDate, DataFileID, 'DataSiteConfigAMPANConfig' From DataSiteConfigAMPANConfigInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigADataAggregator' From DataSiteConfigADataAggregatorInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigADataCollector' From DataSiteConfigADataCollectorInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAEnergisationStatus' From DataSiteConfigAEnergisationStatusInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAGSPGroup' From DataSiteConfigAGSPGroupInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigALineLossfactorClass' From DataSiteConfigALineLossfactorClassInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAMeasurementClass' From DataSiteConfigAMeasurementClassInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromsettlementDate, DataFileID, 'DataSiteConfigAMeteringAddress' From DataSiteConfigAMeteringAddressInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAMeterOperator' From DataSiteConfigAMeterOperatorInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAMeterTimeSwitchCode' From DataSiteConfigAMeterTimeSwitchCodeInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAProfileClass' From DataSiteConfigAProfileClassInsert Into #temp1 Select Distinct Source, MPANCore,EffectiveFromDate, DataFileID, 'DataSiteConfigAStandardSettlementConfiguration' From DataSiteConfigAStandardSettlementConfiguration-- 19 seconds/*Need to use DataSettlementBData At this point as well*/CREATE TABLE [dbo].[DataSiteConfigBMPANConfiguration]( [DataSiteConfigBMPANConfigurationID] [bigint] IDENTITY(1,1) NOT NULL, [Source] [varchar](5) NULL, [MPANCore] [bigint] NULL, [DataSiteMPANID] [bigint] NULL, [CreationTime] [datetime] NULL, [EffectiveFromSettlementDate] [datetime] NULL, [EffectiveToSettlementDate] [datetime] NULL, [EnergisationStatus] [varchar](2) NULL, [MeasurementClassRef] [varchar](2) NULL, [ProfileClass] [varchar](2) NULL, [StandardSettlementConfigurationRef] [int] NULL, ) INSERT INTO dbo.DatasiteConfigBMPANConfiguration(MpanCore, EffectiveFromSettlementDate)SELECT DISTINCT MPANCore, EffectiveFromDateFROM #temp1 WHERE EffectiveFromDate IS NOT NULL AND MPANcore IS NOT nullORDER BY MPANCore, EffectiveFromDate ASC------Create Unique set of dataIF object_id('tempdb..#Temp2') IS NOT NULLBEGIN DROP TABLE #Temp2ENDSelect MPANCore, EffectiveFromDate, DataSiteConfigATableName, Max(DataFileID) as maxfileIDInto #temp2 from #temp1Group by MPANCore, EffectiveFromDate, DataSiteConfigATableName----UPDATE dbo.DatasiteConfigBMPANConfigurationSET EnergisationStatus = b.EnergisationStatusFROM dbo.DatasiteConfigBMPANConfigurationinner JOIN DataSiteConfigAEnergisationStatus b ON b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCore AND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDateInner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileIDWhere c.DataSiteConfigATableName = 'DataSiteConfigAEnergisationStatus'----UPDATE dbo.DatasiteConfigBMPANConfigurationSET MeasurementClassRef= b.measurementClassRefFROM dbo.DatasiteConfigBMPANConfigurationinner JOIN DataSiteConfigAMeasurementClass b ON b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCoreAND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDateInner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileIDWhere c.DataSiteConfigATableName = 'DataSiteConfigAMeasurementClass'UPDATE dbo.DatasiteConfigBMPANConfigurationSET ProfileClass = b.ProfileClassFROM dbo.DatasiteConfigBMPANConfigurationINNER JOIN dbo.DataSiteConfigAProfileClass b ON b.MPANCore = DatasiteConfigBMPANConfiguration.MPANCoreAND b.EffectiveFromDate = DatasiteConfigBMPANConfiguration.EffectiveFromSettlementDateInner join #Temp2 c on b.MPANCore = c.MPANcore and B.DataFileID = c.maxFileIDWhere c.DataSiteConfigATableName = 'DataSiteConfigAProfileClass'any help is really appreciated.Thanks

6 places after decimal without rounding Posted: 04 Mar 2013 09:58 AM PST I have a sample tableCREATE TABLE dbo.Analysis(ID INT IDENTITY,Margin [numeric](21, 6) NULL,Gallons INT,Freight [numeric](21, 6) NULL,AccMargin [numeric](21, 6) NULL)INSERT INTO dbo.Analysis ( Margin,Gallons,Freight)SELECT 0.050220,5022,-30.180000INSERT INTO dbo.Analysis ( Margin,Gallons,Freight)SELECT 0.050220,-5022,318.260000UPDATE dbo.AnalysisSET AccMargin = (MArgin/Gallons + Freight/Gallons) I want the AccMargin to be -0.005999 and -0.063383 without rounding . Currently it is rounding up to -0.006000and -0.063383 for -0.005999 . I want 6 places after the decimal without rounding.select *,(MArgin/Gallons + Freight/Gallons) As CorrectAccMargin from dbo.Analysis

Does a SP get precompiled? Posted: 04 Mar 2013 07:09 PM PST Hi,I have a large Stored Procedure. One tiny part of it is dynamic: SET @qry = 'SELECT something'; EXEC(@qry) . Is the result of this that the SP will be compiled every time it is called? And if so, would it be better to move the dynamic part to a new, small SP, and call that one from the large SP? Or wouldn't that give any performance improvement?Thanks,Raymond

Database Mail + sysmail_start_sp Posted: 04 Mar 2013 09:53 PM PST Hi,My Database Mail works fine, but every time i open SSMS, in order to send mail. I have to execute sysmail_start_sp to shoot the mail.What should i do so that it will never stops, as i am suppose to put this Email in a JOB.

How to report period wise data ? Posted: 04 Mar 2013 09:16 PM PST Hi ,I have a table which holds the employee details : /*******************************************************************//****** Object: Table [dbo].[empleaves] Script Date: 05-03-2013 17:42:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[empleaves]( [empid] [int] NULL, [leavedate] [date] NULL) ON [PRIMARY]GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (1, CAST(0xCE360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (1, CAST(0xD0360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xD0360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xBA360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xBB360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (2, CAST(0xBC360B00 AS Date))GOINSERT [dbo].[empleaves] ([empid], [leavedate]) VALUES (3, CAST(0xC9360B00 AS Date))GO/*******************************************************************/I need a query which will report the leaves of employees as :empid StartingDate EndingDate NoOfDays1 28-02-2013 28-02-2013 11 02-03-2013 02-03-2013 12 02-03-2013 02-03-2013 12 08-02-2013 10-02-2013 33 23-02-2013 23-02-2013 1How can I achieve this .?

Help with execution Plan Posted: 04 Mar 2013 02:06 PM PST Hi all,I am trying to learn Execution Plan reading. I executed the below query[code="sql"]USE [AdventureWork2008] GOSELECT *FROM Sales.SalesOrderHeader H INNER JOINSales.SalesOrderDetail D ON H.SalesOrderID=D.SalesOrderID[/code]The above script is a normal Select script where as its execution plan shows that the query is performing Compute Scalar operation , i am not able to understand what is the need that a simple select query needs to Compute scalar operation.

date problem Posted: 04 Mar 2013 01:45 AM PST Hi not sure if this is the correct forum but I need some help with dates in SQL.I have been given a database that has the dates stored in 3 different parts (week of year, year and day number) and I need to change it back to a proper datefor example if today is Monday 04/03/2013 then the data will be stored as week no = 10 , year = 2013 , daynumber = 2but I need to change it back to Monday 04/03/2013please helpthanksJames

how to write a column expression in a query based on other table value exist Posted: 04 Mar 2013 11:23 AM PST Hello Please help me here.,i have below tables calledstudent (sno, sname, address)StudentDrillEnrolled (sno, DrillClass)StudentDrillNotEnrolled (Sno, Reason, Viever)how to write a query, so that i will have sno,sname,address, Enrolled (Y/N/NA)enrolled will be calculatedif sno exist in StudentDrillEnrolled table then Yif sno exist in StudentDrillNotEnrolled table then it is Nif not exist in both tables enrolled & not enrolled then N/Aplease help me hereThanks in advanceasitti

TabLock and TablLockX Confusion Posted: 04 Mar 2013 12:54 PM PST I am trying to figure out how this works. Locking difference between TabLock and TablLockX. They seem to do the same thing. I did my queries on the tally table for simplicity (1 mill +)[code="sql"]BEGIN BEGIN TRANSACTION -- UPDATE Common.Tally WITH (TABLOCK) --SET N = 4 --WHERE N = 4 SELECT N FROM Common.Tally WITH (TABLOCK) WHERE N = 4 WAITFOR DELAY '00:15' COMMIT TRANSACTIONEND[/code]You can use the update or the Select it has the same effect. In another SSMS window I now run a Select[code="sql"]Select * FROM Common.TallyWHERE N=100[/code]When I execute SP_Lock I get Tally X (exclusive Lock)Tally IS (Intent to share)I thought LockX locked it down to where it could not be multi-user. I would like to Update/Insert/Delete and as long as it is not the same records. People should be able to ping the table with selects. Can someone help with what I am missing?

Advise on working with XML Posted: 04 Mar 2013 07:43 AM PST *advice I have a big nasty query I have been assigned to look at that basically is pulling varchar data into a temp xml table as such the query performance is terrible at best. [code="sql"] --DECLARE @XmlFilter xml DECLARE @WhereClause VARCHAR(MAX) DECLARE @CartPrice VARCHAR(2000) DECLARE @ExpandedCartPrice VARCHAR(2000) DECLARE @Operator VARCHAR(10) DECLARE @VariableID VARCHAR(10) DECLARE @Variance VARCHAR(10) DECLARE @Unit VARCHAR(10) DECLARE @SqlVar VARCHAR(50) = 'ProdDesc.Map' DECLARE @ElementCount INT DECLARE @StateParm VARCHAR(2000) DECLARE @UseCaEvent bit = 0 CREATE TABLE #MyParameters (TableAlias VARCHAR(200) , Parm VARCHAR(200), ParmValue VARCHAR(MAX) , ParmFilter VARCHAR(MAX), ParmWhere VARCHAR(MAX)) DECLARE @PriceElements AS TABLE (ID INT IDENTITY(1,1) ,VALUE VARCHAR(50)) --******* Build Dynamic WHERE Clause ******* BEGIN --******* Shread The XML ******* BEGIN --XML Root Attributes SELECT ParamValues.Parm.value('@Type[1]', 'VARCHAR(200)') QueryType , ParamValues.Parm.value('@UserID[1]', 'VARCHAR(200)') UserID INTO #FilterAttributes FROM @XmlFilter.nodes('/Filter') AS ParamValues(Parm) --Collect Query Parameters /* NOTES: Rating is not Used right now */ INSERT #MyParameters (TableAlias,Parm, ParmValue, ParmFilter ) --ProductSeller Parameters SELECT 'ProdEx','ISBN', ParamValues.Parm.value('ISBN[1]', 'VARCHAR(Max)'), 'ProdEx.ISBN = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','MfgSKU', ParamValues.Parm.value('MfgSKU[1]', 'VARCHAR(Max)'), 'ProdEx.MfgSKU = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','UPC', ParamValues.Parm.value('UPC[1]', 'VARCHAR(Max)'), 'ProdEx.UPC = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','VendorID', ParamValues.Parm.value('VendorID[1]', 'VARCHAR(Max)'), 'ProdEx.VendorID = [VALUE]' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','ProductID', ParamValues.Parm.value('ProductID[1]', 'VARCHAR(Max)'), 'ProdEx.ProductID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','CVSKU', ParamValues.Parm.value('CVSKU[1]', 'VARCHAR(Max)'), 'ProdEx.CVSKU like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdDesc','ProductName',ParamValues.Parm.value('ProductName[1]', 'VARCHAR(Max)'), 'ProdDesc.ProductName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorName',ParamValues.Parm.value('CompetitorName[1]', 'VARCHAR(Max)'), 'comp.CompetitorName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorID', ParamValues.Parm.value('CompetitorID[1]', 'VARCHAR(Max)'), 'comp.CompetitorID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','SellerState', ParamValues.Parm.value('SellerState[1]', 'VARCHAR(Max)'), 'compMar.SellerState in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','Rating', ParamValues.Parm.value('Rating[1]', 'VARCHAR(Max)'), 'compMar.Rating >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','MarketID', ParamValues.Parm.value('MarketID[1]', 'VARCHAR(Max)'), 'compMar.MarketID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION --dg: commenting this out, it does not seem to be used and if it comes through it will break SELECT 'compListing','Reviews', ParamValues.Parm.value('Reviews[1]', 'VARCHAR(Max)'), 'compListing.ReviewCnt >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compListing','SuspectStatus', ParamValues.Parm.value('SuspectStatus[1]', 'VARCHAR(Max)'), 'compListing.SuspectStatusID = [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMP','IgnoreInCart', ParamValues.Parm.value('IgnoreInCart[1]', 'VARCHAR(Max)'), 'compMP.InCart = 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','CartPrice', ParamValues.Parm.value('CartPrice[1]', 'VARCHAR(Max)'), '[VALUE] and compMP.Price > 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','Condition', ParamValues.Parm.value('Condition[1]', 'VARCHAR(Max)'), 'CompMp.ConditionId IN ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankUpper',ParamValues.Parm.value('PriceRankUpper[1]', 'VARCHAR(Max)'), 'compMP.PricePosition <= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankLower',ParamValues.Parm.value('PriceRankLower[1]', 'VARCHAR(Max)'), 'compMP.PricePosition >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','SellerDT', ParamValues.Parm.value('SellerDT[1]', 'VARCHAR(Max)'), 'compMP.DT between ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceLocationID',ParamValues.Parm.value('PriceLocationID[1]', 'VARCHAR(Max)'),'compMP.PriceLocationID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceAttributeID',ParamValues.Parm.value('PriceAttributeID[1]', 'VARCHAR(Max)'),'compMP.PriceAttributeID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp', 'Active', ParamValues.Parm.value('Active[1]', 'VARCHAR(Max)'), 'compMP.LatestData = 1' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) [/code]Here is my query plan for the above statement. any suggestions would be much appreciated![code="xml"]<?xml version="1.0" encoding="utf-16"?><ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.1.1" Build="10.0.5500.0"> <BatchSequence> <Batch> <Statements> <StmtSimple StatementText="INSERT #MyParameters (TableAlias,Parm, ParmValue, ParmFilter ) --ProductSeller Parameters SELECT 'ProdEx','ISBN', ParamValues.Parm.value('ISBN[1]', 'VARCHAR(Max)'), 'ProdEx.ISBN = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','MfgSKU', ParamValues.Parm.value('MfgSKU[1]', 'VARCHAR(Max)'), 'ProdEx.MfgSKU = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','UPC', ParamValues.Parm.value('UPC[1]', 'VARCHAR(Max)'), 'ProdEx.UPC = ''[VALUE]''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','VendorID', ParamValues.Parm.value('VendorID[1]', 'VARCHAR(Max)'), 'ProdEx.VendorID = [VALUE]' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','ProductID', ParamValues.Parm.value('ProductID[1]', 'VARCHAR(Max)'), 'ProdEx.ProductID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdEx','CVSKU', ParamValues.Parm.value('CVSKU[1]', 'VARCHAR(Max)'), 'ProdEx.CVSKU like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'ProdDesc','ProductName',ParamValues.Parm.value('ProductName[1]', 'VARCHAR(Max)'), 'ProdDesc.ProductName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorName',ParamValues.Parm.value('CompetitorName[1]', 'VARCHAR(Max)'), 'comp.CompetitorName like ''%[VALUE]%''' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'comp','CompetitorID', ParamValues.Parm.value('CompetitorID[1]', 'VARCHAR(Max)'), 'comp.CompetitorID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','SellerState', ParamValues.Parm.value('SellerState[1]', 'VARCHAR(Max)'), 'compMar.SellerState in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','Rating', ParamValues.Parm.value('Rating[1]', 'VARCHAR(Max)'), 'compMar.Rating >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMar','MarketID', ParamValues.Parm.value('MarketID[1]', 'VARCHAR(Max)'), 'compMar.MarketID in ([VALUE])' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION --dg: commenting this out, it does not seem to be used and if it comes through it will break SELECT 'compListing','Reviews', ParamValues.Parm.value('Reviews[1]', 'VARCHAR(Max)'), 'compListing.ReviewCnt >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compListing','SuspectStatus', ParamValues.Parm.value('SuspectStatus[1]', 'VARCHAR(Max)'), 'compListing.SuspectStatusID = [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'compMP','IgnoreInCart', ParamValues.Parm.value('IgnoreInCart[1]', 'VARCHAR(Max)'), 'compMP.InCart = 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','CartPrice', ParamValues.Parm.value('CartPrice[1]', 'VARCHAR(Max)'), '[VALUE] and compMP.Price > 0' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','Condition', ParamValues.Parm.value('Condition[1]', 'VARCHAR(Max)'), 'CompMp.ConditionId IN ([VALUE])' FROM @XmlFilter.nodes('/Filter/ProductSeller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankUpper',ParamValues.Parm.value('PriceRankUpper[1]', 'VARCHAR(Max)'), 'compMP.PricePosition <= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'CompMp','PriceRankLower',ParamValues.Parm.value('PriceRankLower[1]', 'VARCHAR(Max)'), 'compMP.PricePosition >= [VALUE]' FROM @XmlFilter.nodes('/Filter/Seller') AS ParamValues(Parm) UNION SELECT 'C" StatementId="4" StatementCompId="18" StatementType="INSERT" StatementSubTreeCost="1278.45" StatementEstRows="4013.67" StatementOptmLevel="FULL" QueryHash="0x39653846CAF10C9C" QueryPlanHash="0x85B7E36B41C9C5DF"> <StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="false" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" /> <QueryPlan DegreeOfParallelism="1" MemoryGrant="23608" CachedPlanSize="1072" CompileTime="512" CompileCPU="512" CompileMemory="22344"> <RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="4013.67" EstimateIO="27.3643" EstimateCPU="0.00401367" AvgRowSize="9" EstimatedTotalSubtreeCost="1278.45" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList /> <RunTimeInformation> <RunTimeCountersPerThread Thread="0" ActualRows="23" ActualEndOfScans="1" ActualExecutions="1" /> </RunTimeInformation> <Update DMLRequestSort="0"> <Object Database="[tempdb]" Schema="[dbo]" Table="[#MyParameters]" /> <SetPredicate> <ScalarOperator ScalarString="[#MyParameters].[TableAlias] = [Expr1514],[#MyParameters].[Parm] = [Expr1515],[#MyParameters].[ParmValue] = [Union1512],[#MyParameters].[ParmFilter] = [Expr1516],[#MyParameters].[ParmWhere] = NULL"> <ScalarExpressionList> <ScalarOperator> <MultipleAssign> <Assign> <ColumnReference Table="[#MyParameters]" Column="TableAlias" /> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1514" /> </Identifier> </ScalarOperator> </Assign> <Assign> <ColumnReference Table="[#MyParameters]" Column="Parm" /> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1515" /> </Identifier> </ScalarOperator> </Assign> <Assign> <ColumnReference Table="[#MyParameters]" Column="ParmValue" /> <ScalarOperator> <Identifier> <ColumnReference Column="Union1512" /> </Identifier> </ScalarOperator> </Assign> <Assign> <ColumnReference Table="[#MyParameters]" Column="ParmFilter" /> <ScalarOperator> <Identifier> <ColumnReference Column="Expr1516" /> </Identifier> </ScalarOperator> </Assign> <Assign> <ColumnReference Table="[#MyParameters]" Column="ParmWhere" /> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Assign> </MultipleAssign> </ScalarOperator> </ScalarExpressionList> </ScalarOperator> </SetPredicate> <RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4013.67" EstimateIO="0" EstimateCPU="0.000401367" AvgRowSize="8265" EstimatedTotalSubtreeCost="1251.08" Parallel="0" EstimateRebinds="0" EstimateRewinds="0"> <OutputList> <ColumnReference Column="Union1512" /> <ColumnReference Column="Expr1514" /> <ColumnReference Column="Expr1515" /> <ColumnReference Column="Expr1516" /> </OutputList> <ComputeScalar> <DefinedValues> <DefinedValue> <Co

need to return the last value Posted: 04 Mar 2013 09:12 AM PST Hello comunityI have write this TSQL, but my problema that is return more than one LastPrice for the same REF:select distinct sl.ref as 'Ref', epcpond as 'epcpond',max(ousrdata + ousrhora) as 'Maxdate'from sl inner join planOref x on Rtrim(sl.ref) = RTRIM(x.ref)where epcpond <> 0and sl.datalc <= '20130104'group by sl.ref,epcpondorder by ref ascI know the problem is on the Group by, but i can´t use SUM, MAX or MIN on field LastPrice, the result is for exemple:REF LastPrice MaxDate2009461 3.642000 2012-09-04 16:14:11.0002009461 3.640000 2012-09-04 16:19:24.000 -- This is the correct value and lastEntryThe unique and correct value is the last because the MaxDate is greather than the first.Any idea to solve my problem!Many thanksLuis Santos

JOIN problems Posted: 04 Mar 2013 06:49 AM PST Hey guys,I have these result codes from one table...There are like 12 distinct ones...When I join to the another table to get how many accounts were associated with each result code, Im getting back the correct numbers that I am looking for...But the requestor has asked to show all the result codes even if there are 0 accounts associated with them...But for some reason I am just getting back the result code that has a number attached to it...If it is zero it just doesnt show up...I only am getting back like 6 rows where I should be getting back 12...Let me also say, I have to join to this other table to get a specific type I tried putting all the distinct codes in a temp table, then selecting from the temp table and doing a left outer join on the other table to get the number accounts and inner join the other table to get the correct types but it still only returns the 6 codes that have data associated to them...Any suggestions?

Index filled after created Posted: 04 Mar 2013 04:03 AM PST If you have a table that is already populated (say 900,000 rows) and then you create an index, is the index automatically filled at that point?Thanks,Tom

Number of days between two integer date columns Posted: 04 Mar 2013 04:07 AM PST Hi Friends,I have a requirement where i have to write a Sql for :basically i have 2 columns 'startdate_SK' and 'enddate_SK' columns in table 'A''startdate_SK' is a integer ex:99971231'enddate_SK' is a integer ex: 17530101requirement: need to find the number of days between the above two columnsex: 'startdate_SK' - 20130101 and 'enddate_SK' - 20130201 then my result should be 31IMP:: both columns are integers in the table.i can use datediff but they are not date columns instead they are integers.Please suggest, any help is appreciated,--Thanks,Sam.

Working on MERGE statement to Handle SCD Posted: 04 Mar 2013 12:56 AM PST Hi All,Could you please provide me an example or step, to handle both slowly changing dimension type 1 & type 2 in a single MERGE tsql query in SQL Server 2008.Please help.. :)ThanksSam

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610