raphael.kong (10/10/2011)

---

humm ...

In case of bulk insert, no trigger are fired, OK ; But, when the software insert data into the table (using the DSN) the trigger IS fired, as...

Assuming ID is the PK of stg_PAreporting:

WITH JobOrder

AS

(

SELECT ID

jobname

,jobname + '-' + CAST(ROW_NUMBER() OVER (PARTITION BY jobname ORDER BY [date]) AS varchar(3)) AS NewJobName

FROM stg_PAreporting

)

UPDATE JobOrder

SET jobname = NewJobName

Indexes on FinalOutput, like the following, may also help.

CREATE NONCLUSTERED INDEX FinalOutputProdNumTransDate

ON dbo.FinalOutput ([Product Number (SKU)], [Trans Date])

INCLUDE ([Seller - Company ID]);

CREATE NONCLUSTERED INDEX FinalOutputProdLineTransDate

ON dbo.FinalOutput ([Product Line ID], [Trans...

With some filtered covering indexes on dbo.PromoMaster

CREATE NONCLUSTERED INDEX FIPromoMasterSKUNotAll

ON dbo.PromoMaster (SKU, [Start Date], [End Date])

INCLUDE ([T1 ID], [Programe ID], [Promo Type], [Rebate Value])

WHERE SKU <> 'ALL';

CREATE NONCLUSTERED INDEX FIPromoMasterSKUAll

ON...

This will probably work but will not use any indexes:

UPDATE P

SET UPC = sourc.upc,

PROD_NAME = sourc.name,

...

You should be able to work out what you need to do in the first CTE.

DECLARE @CurrDate datetime;

SET @CurrDate = '20110831';

WITH CurrentWeek

AS

(

SELECT DATEADD(d, DATEDIFF(d, 0, @CurrDate) - DATEPART(weekday, @CurrDate) + 1, 0) AS WeekStart

,DATEADD(d, DATEDIFF(d, 0, @CurrDate) - DATEPART(weekday, @CurrDate) + 8, 0) As WeekEnd

)

,Years100

AS

(

SELECT...

In relational theory a relation (table, view etc) is an unordered set.

You should really alter your code so that the ORDER BY is on the outer most query.

If you are...

Maybe the cartisian product:

WITH Numbers(N)

AS

(

SELECT 0

UNION ALL SELECT 1

UNION ALL SELECT 2

UNION ALL SELECT 3

UNION ALL SELECT 4

UNION ALL SELECT 5

)

SELECT

N1.N AS exid1

,N2.N AS exid2

,N3.N AS exid3

FROM Numbers N1

CROSS JOIN...

You asked a similar question before on this thread:

http://www.sqlservercentral.com/Forums/Topic1139620-145-1.aspx#bm1139686

I suggest you study the original thread so you understand what is happening.

With the follwoing test data:

CREATE TABLE #Accounts

(

AccountName varchar(25) NOT NULL

)

INSERT INTO #Accounts VALUES ('AAA'),('BBB')

CREATE TABLE #Products

(

ProductName varchar(25) NOT NULL

,[Range] varchar(25) NOT NULL

)

INSERT INTO #Products VALUES ('ProdAA','Core'),('ProdBB','2nd'),('ProdCC','Core')

CREATE TABLE #AccountProduct

(

AccountName varchar(25) NOT...

You will need to put the groups in order.

Something like the following should work:

WITH Grps

AS

(

SELECT Historyid, SequenceNumber

,HistoryId - ROW_NUMBER() OVER (PARTITION BY Letter ORDER BY historyid) AS grp

FROM #Test

)

, MinHists

AS

(

SELECT...

1. Pivoting will reduce joins

2. Try to get rid of the distinct if possible.

3. Try to make some of the outer joins inner joins.

You could start with the following, which...

Sorry for not getting back earlier but I was diverted.

Lamprey13 - Thanks for working out what was wrong.

Jeff - Thanks for your code which is exactly what I was looking...

Hi Skcadavre,

Thanks for your efforts. I tried to do something quickly and thought I was losing the plot. (I can get it to work if I materialize the result but...