or

DELETE FROM titleauthor

WHERE EXISTS (

 SELECT *

 FROM titles T

 WHERE T.title LIKE '%Straight%'

  AND T.title_id = titleauthor.title_id )

Interesting. It could act as a non-vendor specific JOIN hint.

If you look at the query plans for the original and COALESCE version, is there a difference between the type of...

-- Test Data

DECLARE @t TABLE

(

 StopCol varchar(20) NOT NULL

 ,other_col char(1) NOT NULL

)

INSERT INTO @t

SELECT '176.7', 'y' UNION ALL

SELECT '4476.7', 'X' UNION ALL

SELECT '176', 'y' UNION ALL

SELECT '4476', 'X'

-- Show test data

SELECT...

Assuming the PK is (Site, Item), try something like the following:

-- Insert

INSERT INTO preferred_supplier

SELECT SITE, ITEM, PREF_SUPPLIER, [DESCRIPTION]

FROM pref_temp T

WHERE NOT EXISTS (

 SELECT *

 FROM preferred_supplier S

 WHERE S.Site = T.Site AND...

You need to use CONVERT instead of CAST.

The style will be 101 for US dates or 103 for UK dates.

Ideally you should alter the table so that dates are stored...

-- Simple; assuming no time component in date and consecutive days

DECLARE @t TABLE

(

 tDate datetime NOT NULL

 ,Client int NOT NULL

 ,A int NOT NULL

)

INSERT INTO @t

SELECT '20061101', 1, 100 UNION ALL

SELECT '20061102',...

I am glad my efforts sort of worked!

You are correct, those horrible multi-valued columns need to be dealt with. The following link may give you some...

Asim,

>>can you tell me how should i take care about this logic below in my stored procedure

As you have failed to give a cogent description of what you are trying...

Asim

Microsoft SQL Server is a relational database so rows in a table should have NO order. (Basic theory.) There often is some order but this cannot be guaranteed!

The...

If the accuracy of a FLOAT is alright, then just force a FLOAT by adding a decimal point to one of the numbers.

eg. set @Size...

Carl,

I see no need for dynamic SQL here as the only thing you are adding to the string is @Crit_Label.

To speed this procedure up I suggest you:

1. Convert to static...

If possible, normalize the schema although a reporting tool should be able to cope with the current table structure.

To get the output requested, something like the following should work:

SELECT

 T.Item_ID

 ,T.Year_ID

 ,N.Nbr AS...

Farrell,

I think the difference is that your quick age calculation just uses the difference in years. As all your BirthDates are in December you should really minus one from the...

You need to group by the formula for TableValue:

INSERT INTO ClaimCounts (Month_of_file, CalcAction, TableValue, CalculatedValue)

SELECT

 @Month_of_file_filter AS Month_of_file

 ,'Record Count by Loss Month' AS CalcAction

 ,CONVERT(varchar(4), LossDate, 120) AS TableValue -- change...

You cannot assign a value to a function. Try something like:

UPDATE CUSTOMERS

SET [DATE] = DATEADD(year, 100, [DATE])

WHERE YEAR([DATE]) BETWEEN 1900 AND 1999