More Sql Assistance Rqd

[DaveEllen]

Ok,

here in sunny Romford where they are still on SQL Server 6.5 (written by bill gates granpa)

I want to create a view which converts a calculated field from a number, say 15 into a text description say 11-30 days.

I've created a table with this data in for my query to look at to do the required conversion. I've got to do a sub query but am struggling.

the select query i'll create the view from is below

select DISTINCT FOLDER3_REF,FOLDER1_REF,TEAM,MAIL_STATUS,DATE_RECEIVED,DOC_TYPE,

DATEDIFF(DAY,date_received,getdate()) AS daystodo, description

from v_intray (NOLOCK),intray_lookup2 (NOLOCK)

where TEAM = 'BENEFITS'

AND DOC_TYPE IN ('PCE','PTISREV','PTNEWIS','PTSTDNEW','PTSTDREV','CTISNEW','CTISREV','CTSTDNEW','CTSTDREV')

and mail_status IN ('N','NT')

and mail_status !='VF'

and date_received > '01 August 2003 0:00'

and date_comp = (null)

and date_pend = (null)

(select description from intray_lookup2 where lookup_id >= 1)

it returns data but the description field (0-10 days,11-30 days etc) bears no relation to the daystodo field which is what i want it to

answers on a postcard please

[DaveEllen]

Done it...........

select DISTINCT FOLDER3_REF,FOLDER1_REF,TEAM,MAIL_STATUS,DATE_RECEIVED,DOC_TYPE,

DATEDIFF(DAY,date_received,getdate()) AS daystodo, description

from v_intray (NOLOCK),intray_lookup2 (NOLOCK)

where DATEDIFF(DAY,date_received,getdate()) between daysfrom and daysto

and TEAM = 'BENEFITS'

AND DOC_TYPE IN ('PCE','PTISREV','PTNEWIS','PTSTDNEW','PTSTDREV','CTISNEW','CTISREV','CTSTDNEW','CTSTDREV')

and mail_status IN ('N','NT')

and mail_status !='VF'

and date_received > '01 August 2003 0:00'

and date_comp = (null)

and date_pend = (null)

[TonyGoose]

So glad we could help :duh:

[javadude]

Its the teddy bear effect. Explain your problem out loud, even to a teddy bear, and the chances are that you'll work out the answer yourself as you do.

[DaveEllen]

Is that what its called !

Had a look at a query doing the same thing in MSAccess & bobs your aunt