Do Not Sell My Personal Information Jump to content


More Sql Assistance Rqd


DaveEllen
 Share

Recommended Posts

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

Link to comment
Share on other sites


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)

Link to comment
Share on other sites


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.

Link to comment
Share on other sites


Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...




Forums


News


Membership