condition in select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

condition in select query

Author

Topic

palak
Yak Posting Veteran

55 Posts

Posted - 2008-06-12 : 08:34:13

create function stzipcustinfo

( @campaign varchar(50), @startdate datetime, @enddate datetime,@audience_type char(10) )

RETURNS TABLE

AS

RETURN

(Select distinct t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,

t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone,sum(t.totalquantity) as Totalquantity,

t.Audience_type

from

(

select distinct

v.sopnumbe, v.campaign, v.custnmbr, v.custname, v.address1, v.address2,v.city,v.state,v.country,v.zipcode,

v.Phone, v.totalquantity as totalquantity,

case @audience_type

when v.custclas then v.custclas

end as audience_type
from vwstzipcustaudienceinfo v

Where (v.itemnmbr = '' or v.itemnmbr=v.itemnmbr) and v.Campaign = @Campaign and (v.docdate between @startdate and @enddate)

) as t

where t.audience_type is not null

group by t.campaign,t.Sopnumbe,t.Custnmbr,t.Custname,

t.Address1,t.Address2,t.City,t.State,t.Country,t.Zipcode, t.Phone, t.Audience_type

)

--select * from mystatezipcustaudienceinfo('cpd','1/1/2008','5/15/2008','INDPATIENT') --getting 500rows

--select * from mystatezipcustaudienceinfo('Cpd'','1/1/2008','5/15/2008','INST-HOSPITAL') -- note getting single row

problem have a “-“ in them..i m not getting result..so what condition should be in red color one..

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 08:58:44

Whats the value contained in v.custclas field for it?

palak
Yak Posting Veteran

55 Posts

Posted - 2008-06-12 : 09:02:21

INST-HOSPITAL

INST-UNIVPROFME

INST-CLINPRAC

INST-COMMOUTREA

These results for these Audience types do not have a problem.

INDPATIENT

STANDARD

PROF

One thing I notice is that ones with a problem have a “-“ in them, and none of the correct ones have a “-“.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 09:15:23

what are values you have in your table for custclas? is it like INST-HOSPITAL or INSTHOSPITAL?

palak
Yak Posting Veteran

55 Posts

Posted - 2008-06-12 : 09:17:09

STANDARD
INDPATIENT
INST-HOSPITAL
PROGRAM
INST-COMMOUTREA
INST-CLINPRAC
INST-UNIVPROFME
INST-MKT/MEDIA
INST-MCOHCO
PROF
INST-FEDGOVT
INST-LOCALGOVT
PATIENT
INDPROF-OTHER

INDPROF-HEALTH
INDPROF-HLTHPRO

palak
Yak Posting Veteran

55 Posts

Posted - 2008-06-12 : 09:18:24

i have values like:
STANDARD
INDPATIENT
INST-HOSPITAL
PROGRAM
INST-COMMOUTREA
INST-CLINPRAC
INST-UNIVPROFME
INST-MKT/MEDIA
INST-MCOHCO
PROF
INST-FEDGOVT
INST-LOCALGOVT
PATIENT
INDPROF-OTHER

INDPROF-HEALTH
INDPROF-HLTHPRO
but i m getting results for only thos values which r not with '-'
like:
standard, patient, indpateint, prof..

so i want condition for stating '-'..

if u can help me..thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 09:21:40

ah i got the problem
just noticed you've parameter as
@audience_type char(10)
and passing value INST-HOSPITAL which has 13 characters so this will get truncated and you wont get a match. so please change the length of parameter to accomodate even the longest value in table. You could simply put length equal to length of v.custclas column.

palak
Yak Posting Veteran

55 Posts

Posted - 2008-06-12 : 09:32:15

Thanks Visakh16..

got the perfect solution and got the mistake also..

thanks a lot!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources