case statement - 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)

case statement

Author

Topic

ayu
Starting Member

43 Posts

Posted - 2008-04-11 : 11:50:45

i ahve one fucniton:
create function fntotalcountcustclas

( @campaign varchar(50), @startdate datetime, @enddate datetime)
RETURNS TABLE
AS
RETURN
( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount

from
(
select
vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas
from vwcustnmbr vc
join vwitemnbmr vi
on vi.sopnumbe=vc.sopnumbe
Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)

group by vi.itemnmbr,vc.custclas, vc.custclasdescription
) as t
)
when i m executing it:
select * from fntotalcountcustclas('copd','1/1/2008','4/11/2008')
order by totalcount desc

i m getting results like:
itemnmbr,custclas,custclasdescription,totalcount
------------------------------------------------
06-5841 STANDARD Standard(web) 31
06-5840 STANDARD Standard(web) 30
kr-014 STANDARD Standard(web) 72
06-5841 INDPATIENT Patient 12
06-5840 INDPATIENT Patient 9
06-5845 INDPATIENT Patient 6
06-5841 PROGRAM Program 6
06-5841 INST-HOSPITAL Hospital 11
...................

Basically, i ahve to use one condition to get corrrect output related to inputs:

like - i have to input @category varchar(50), @category_value varchar(50)
and if category = 'campaign' then category_value = ''
then output should be itemnmbr sum(totalcount) [whatever should be custclas or custclasdesscription]
itemnmbr sumcount
-----------------
06-5840 52
06-541 101
06-452 26
kr-045 252

and if categroy = 'item' then category_value = any itemnmbrs(06-5840,06-5845,06-5841 etc..)
then output should be
itemnmbr custclas custclasdescription totalcount
-----------------------------------------------------
06-5840 STANDARD Standard(web) 31
06-5840 INDPATIENT Patient 9
06-5840 PROGRAM Program 6
06-5840 INS-HOSPITAL Hospital 17

like that..

can anyone help me to write case statement.
thanks a lot!!

create function fntotalcountcustclas

( @campaign varchar(50), @startdate datetime, @enddate datetime,
@category varchar(50), @category_value varchar(50))
RETURNS TABLE
AS
RETURN
( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount,
case when category

from
(
select
vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas
from vwcustnmbr vc
join vwitemnbmr vi
on vi.sopnumbe=vc.sopnumbe
Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)

group by vi.itemnmbr,vc.custclas, vc.custclasdescription
) as t
)

ayu
Starting Member

43 Posts

Posted - 2008-04-11 : 12:37:06

create function fntotalcountcustclas

( @campaign varchar(50), @startdate datetime, @enddate datetime,
@category varchar(50), @category_value varchar(50))
RETURNS TABLE
AS
RETURN
(case when t.category='campaign' and t.category_value =''
then (select distinct t.itemnmbr, sum(t.totalcustclas) as totalcount group by itemnmbr)
end
else
case when t.categroy = 'item' and t.categroy_value=t.itemnmbr
then (select distinct t.itemnmbr, t.custclas, t.custclasdescription, sum(t.totalcustclas) as totalcount
group by t.itemnmbr, t.custclas, t.custclasdescription )
end

from
(
select
vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas
from vwcustnmbr vc
join vwitemnbmr vi
on vi.sopnumbe=vc.sopnumbe
Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)

group by vi.itemnmbr,vc.custclas, vc.custclasdescription
) as t
)
i m getting error like :
incorrect syntax near case..from..else..as..
can someone help me to figure this out.
thanks a lot!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 12:50:12

You cant return a resultset from case when like this. Use IF ELSE to return result sets.

ayu
Starting Member

43 Posts

Posted - 2008-04-11 : 13:30:49

create function fntotalcountcustclascat

( @campaign varchar(50), @startdate datetime, @enddate datetime,
@category varchar(50), @category_value varchar(50))

RETURNS TABLE

AS

RETURN

( select t.custclas, t.custclasdescription, sum(t.totalcount) as totalcount

from

(

select

vi.itemnmbr,replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, vc.totalcustclas as totalcount,
case @category
when 'campaign' then 'campaign'
when 'item' then 'item'
end as category,

case @category_value
when 'campaign' then ''
when 'item' then vi.itemnmbr
end as category_value

from vwcustnmbrs vc

join vwitemnbmr vi

on vi.sopnumbe=vc.sopnumbe

Where vi.itemnmbr = @category_value and vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)

vi.campaign

) as t

group by t.custclas, t.custclasdescription

)

select * from mytotalcountcustclascat('cdare','1/1/2008','4/11/2008','item','06-5841')
order by totalcount desc

i m getting correct results for input - item and different itemnmbrs..

bu i m getting 0 rows affected for input - categroy-'campaign' and categroy_value-''
instead of getting all rows for all itemnmbrs...

can anybody help me to get this.
thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 14:17:45

Does your itemnumber field has blank values stored in it or are they null values?

ayu
Starting Member

43 Posts

Posted - 2008-04-11 : 14:23:20

no, there is no null or blank in itemnmbr field..there are different itemnmbrs only..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 14:27:05

then how will you get output as you are comparing vi.itemnmbr = @category_value which becomes vi.itemnmbr = '' for value passed. Replace this with
(vi.itemnmbr = @category_value or @category_value='') and try

ayu
Starting Member

43 Posts

Posted - 2008-04-11 : 14:44:18

thanks visakh for replying.

well, i got u what u told..and i tried like:
case @category_value
when '' then replace(@category_value,'',vs.itemnmbr)
when vs.itemnmbr then vs.itemnmbr
end as category_value,

but when i m executing
select * from mytotalcountcustclascat('cdare','1/1/2008','4/11/2008','campaign','')
order by totalcount desc

getting 0 rows affected ...

can u tell me my replace statement is correct or not?
thanks a lot!! once again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 14:47:23

I was not telling about case statement. i was refering to your where clause. make it:-

Where (vi.itemnmbr = @category_value or @category_value='') and vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)
if you're passing '' for @category_value and you want to get all item_number records

ayu
Starting Member

43 Posts

Posted - 2008-04-11 : 14:57:20

yup!! now understand.

thanks a ton!! Visakh.
got the perfect results..

thanks a ton!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-11 : 15:13:25

you're welcome

. Glad that i could help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources