My code is attached

SELECT SALES.Reference AS 'SalesOrder Referance'

,SERV2.Reference AS 'ServiceOrder Reference'

,SALESTAT.NAME AS 'Sales Order Status'

,SALECAT.Category AS 'Sales Order Category'

,SOJ1.[No Of SOJ] AS 'NoOfSOJ'

,SOJ2.[JobNumber] AS 'SOJ Number'

,SUB1.[ActivityDate] AS 'Appointment Date'

,CUST1.ShortName AS...

Thanks. I tried your code but get the following error -

(2 row(s) affected)

Server: Msg 536, Level 16, State 3, Line 1

Invalid length parameter passed to the substring function.

Hi guys,

HAVING tsql is just what I was after.

Thanks for all your help.

Argh! Stupid me.

Thanks so much. Was driving me mad.

Fantastic - Never come across the SUBSTRING expression.

Therefore if I wanted to say if the - was the third or forth character, how would you express that?

Thanks ever so much

Sorry Guys -

Got it working.

SELECTTOP 100 PERCENT [PlaceRef]

,[TenantName]

,[Address1]

,[Address2]

,[Address3]

,[Address4]

,[Address5]

,[PostCode]

,[LocationType]

,[TenancyStatus]

,[ReviewDate]

,[ActualDate]

,[Days Under/Over Target]

,[Outstanding]

,[Missed Target] = CASE

WHEN [Days Under/Over Target] > 0 OR [Outstanding] = 'OVERDUE' THEN 'YES'

ELSE 'NO' END

,[ReviewMONTH]

,[ReviewYEAR]

FROM

(

SELECT DISTINCT...

Here is my finished code. I did tweak what you gave me.

SELECT dbo.DW_SERVICEORDERSJOBS_F.JobNumber AS [Soj.JobNumber]

, dbo.DW_SERVICEORDERSJOBS_F.ServicePriority AS [Soj.ServicePriority]

,CONVERT(datetime, CONVERT(varchar, dbo.DW_SERVICEORDERSJOBS_F.CreatedDate, 104), 104) AS [Soj.CreatedDate]

,DATEPART(YYYY,dbo.DW_SERVICEORDERSJOBS_F.CreatedDate) AS...

Thank you very much.

I've run it and the result is what I expect.

I've also studied the code and seen what you've done.

That makes sense to me now.

Many Thanks

Ryan

Thanks for the reply -

But I get the following error -

Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains...

Good Morning,

I have another example here and again am struggling to get my head around just pulling back the minimum line that has the minimum CreatedDateTime.

ExampleSELECT UP.TableRecordUID

,UP.CreatedDate + UP.CreatedTime AS...

Thanks Guys.

It's for SQL2000.

I've Simplified the query below -

SELECTTOP 100 PERCENT [ID]

,[Gender]

FROM

(

SELECT TEN.[tncy-sys-ref] + PER.[GENDER] AS 'ID'

,CASE WHEN PER.[GENDER] = 'M' THEN '1'

WHEN PER.[GENDER] = 'F' THEN '2'

ELSE '0'END AS...

Thank you very much for you all your suggestions

Sorry Gail - what would I need to change the Scale to then - I have never used Scales.

Thanks

Thanks for the reply - importing it as numeric changes .03 to 0 and 1.45 to 1.