SELECT * FROM

OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=C:\temp\book1.xls', [sheet1$])

Try this

BEGIN

 DECLARE @SplitID int

 SET @SplitID = 1035

 SET NOCOUNT ON

 SET DATEFORMAT dmy

 DECLARE @DealTree table (

  DealID int

  , ParentID int

  , lvl varchar(50))

 DECLARE @Temp table...

look here

http://support.microsoft.com/default.aspx?scid=kb;en-us;304261

If there are only two workshops then

SELECT q2.Location, q2.Product, q2.Workshop,

(CASE WHEN q2.WorkShop = 'Work1'

  THEN (CASE WHEN q1.Unshipped_Qty > q2.TotalQTYAvailable

    THEN 0 ELSE q2.TotalQTYAvailable - q1.Unshipped_Qty END)...

cannot use LEFT with text data type

For SQL2K try

declare @rc int,@dir nvarchar(4000)

exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dir output, 'no_output'

select @dir

declare @rc int,@dir nvarchar(4000)

exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @dir output, 'no_output'

select @dir...

SELECT i.WgtGrp, i.ItemNo, i.Description, i.RegDate, i.Weight

FROM ITEM2 i

INNER JOIN (

SELECT WgtGrp, ItemNo, MIN('2004/09/18 11:15:00' - RegDate) AS [Diff]

FROM ITEM2

GROUP  BY WgtGrp, ItemNo) x

ON x.WgtGrp...

Phil's query will give errors, I think this what he meant.

SELECT

QNumber, Bulletin, Issue, Locale, Installed, Applicable,

Installed + Applicable as Total

from ( select QNumbers0 as QNumber

, ID0 as Bulletin

,...

Seems like a presentation problem more than a sql one but you are right the union output has to have the same dataype, so your best bet it to use...

This sort of question pops up now and again. Whilst the solutions presented will work and possibly satisfy most needs it got me thinking. ...

SET NOCOUNT ON

will stop the rows affected message

The following will anser you last question

WHILE CHARINDEX(',',@Section) > 0

BEGIN

insert into [TBLCONTACT_TO_TBLSECTION] puTBLCONTACT, puTBLTOP)

 values (@ContactID, LEFT(@Section,CHARINDEX(',',@Section)-1)

SET @Section = SUBSTRING(@Section,CHARINDEX(',',@Section)+1,LEN(@Section))

END

insert into [TBLCONTACT_TO_TBLSECTION] puTBLCONTACT, puTBLTOP)

 values...

You could use this in the where clause

CHARINDEX(',' + CAST(@LearnID as varchar) + ',',',' + puTOP_SECTIONID) + ',') > 0

but you will sacrifice performance

Try this for a laugh

SELECT COALESCE(

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9]%',[column]),15),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9]%',[column]),14),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9]%',[column]),13),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9].[0-9][0-9][0-9]%',[column]),14),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9].[0-9][0-9]%',[column]),13),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9][0-9].[0-9]%',[column]),12),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9].[0-9][0-9][0-9]%',[column]),13),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9].[0-9][0-9]%',[column]),12),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9][0-9].[0-9].[0-9]%',[column]),11),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9].[0-9][0-9][0-9].[0-9][0-9][0-9]%',[column]),14),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9].[0-9][0-9][0-9].[0-9][0-9]%',[column]),13),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9].[0-9][0-9][0-9].[0-9]%',[column]),12),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9].[0-9][0-9].[0-9][0-9][0-9]%',[column]),13),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9].[0-9][0-9].[0-9][0-9]%',[column]),12),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9].[0-9][0-9].[0-9]%',[column]),11),

  SUBSTRING([column],PATINDEX('%[0-9][0-9][0-9].[0-9][0-9].[0-9].[0-9][0-9][0-9]%',[column]),12),

 ...

If you want to have both sets of data in the same row then you have to join the table to itself.

Performance will depend of several factors.

Correct Indexing

Dates having no...