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...

Looking at your views, I suspect blocking as well.

(Bad stats may not help with this.)

Try just using the tables and doing a FULL JOIN.

Something like:

SELECT

CASE WHEN BIN.[Site] IS NULL THEN...

Another thing to consider is collation.

SELECT INTO will always get the collation of character columns correct.

If you do use the CREATE TABLE method then the collation should always be specified,...

Your code looks as though it will work although I would also be inclined to try an outer join to see if it is more efficient.

Soemthing like:

SELECT

COALESCE(T1.Item, T2.Item) AS Item

,COALESCE(T1.Warehouse,...

It depends on your data. Maybe:

INSERT INTO TableA (Name, Location, Category, Code)

SELECT 'Alex', Location, Category, Code

FROM TableA

WHERE Name = 'James'

If this does not work, post some sample data, in consumable...

SELECT *

FROM at_pdesc_off

JOIN tb_Action

ON at_pdesc_off.Action_id = tb_Action.Action_id

AND tb_Action.delete_flg = 'N'

-- * is against the table where all the rows should be kept.

LEFT JOIN tb_code_value

ON at_pdesc_off.pers_kind_cd = tb_code_value.code_value

AND tb_code_value.code_subcat = 'PERS_KIND/PDOF'

WHERE...

1. In future, please provide data in a consumable format.

eg

CREATE TABLE #t

(

Col1 varchar(30) NOT NULL

,Col2 bit NOT NULL

)

INSERT INTO #t

SELECT 'Ramu', 1

UNION ALL SELECT 'Raju', 0

UNION ALL SELECT 'Ravi', 1

UNION...

You will have to define what you want and fix it accordingly.

eg. The following will produce 2011-01-28 to 2011-01-31 on 2011-02-28

and 2011-02-28 on 2011-03-28, 2011-03-29, 2011-03-30 and 2011-03-31.

;WITH DateRange

AS

(

SELECT

DATEADD(day, DATEDIFF(day,...

SELECT *

FROM YourTable

WHERE date1 >= DATEADD(day, DATEDIFF(day, 0, DATEADD(month, -1, CURRENT_TIMESTAMP)), 0)

AND date1 < DATEADD(day, DATEDIFF(day, 0, DATEADD(month, -1, CURRENT_TIMESTAMP)), 1)

Join to a numbers/tally table:

http://www.sqlservercentral.com/articles/T-SQL/62867/

To make the query SARGABLE you need to compare to an integer.

Something like:

WHERE table-name-removed >= YEAR(CURRENT_TIMESTAMP - 7) * 10000 + MONTH(CURRENT_TIMESTAMP - 7) * 100 + DAY(CURRENT_TIMESTAMP - 7)

1. sp_getapplock applies a user defined application lock. It saves an applicaiton having to cope with locking tables.

2. As sp_getapplock is waiting indefinately, SET LOCK_TIMEOUT must be set to -1...

It is far from clear what you want.

I suspect you may need to use UNION ALL instead of JOIN.

Maybe something like:

;WITH Combined

AS

(

SELECT nRunId, nTotal, nWaste, -1 AS nProductRunId

FROM LiveRun

UNION ALL

SELECT...