How to CASE a SmallInt to a Varchar value

Why does this syntax work for bit but not for smallint ?
-- This works OK - Setting = BIT
CASE (dbo.tblAssessment.Setting)
WHEN 1 THEN 'Internal'
WHEN 0 THEN 'External'
END AS Setting,
-- This fails - error converting value 'N/A' to column of datatype smallint
-- Credit = SMALLINT
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN 'N/A'
ELSE dbo.tblAssessment.Credit
END AS Credit,
Thanks.Hi
It is expecting to return a smallint as one of the ELSE's in a smallint.
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN CONVERT(CHAR(10), 'N/A' )
ELSE CONVERT(CHAR(10), dbo.tblAssessment.Credit)
END AS Credit
Regards
Mike
"hals_left" wrote:

> Why does this syntax work for bit but not for smallint ?
>
> -- This works OK - Setting = BIT
> CASE (dbo.tblAssessment.Setting)
> WHEN 1 THEN 'Internal'
> WHEN 0 THEN 'External'
> END AS Setting,
> -- This fails - error converting value 'N/A' to column of datatype smallin
t
> -- Credit = SMALLINT
> CASE (dbo.tblAssessment.Credit)
> WHEN 101 THEN 'N/A'
> ELSE dbo.tblAssessment.Credit
> END AS Credit,
> Thanks.
>

How to calculate the total days between open and close date

Hi All,

I have a table call case and case_status have two fields, date and status as below:

date status

04/01/2006 open

04/05/2006 closed

04/10/2006 open

04/15/2006 closed

Whenever i open and closed the case, one record is insert into the case_status table.

Now I would need to calculate the total days of the case in storeprocedure.

Anyone can help me please.

Aung

This articledoes something similar. check if it helps.|||

One try:

CREATE

PROCEDURE [dbo].[caseDays]

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;RETURN(SELECTSUM(datediff( dd, c.openDate, d.closedDate))as myCaseDateFROM(SELECT a.cDateas openDate, row_NUmber()over(ORDERBY a.cDate)as ROWNUMBERFROM case_statusAS aWHERE(a.status='open'))as c

inner

join(SELECT b.cDateas closedDate, row_NUmber()over(ORDERBY b.cDate)as ROWNUMBERFROM case_statusAS bWHERE(b.status='closed'))as dON c.ROWNUMBER=d.ROWNUMBER)

END

I hope this one will be close to your solution.

Limno

|||

Thanks for your responsed.

But my problem is total days in two date between open and closed. I still facing this problem.

Thanks

Aung

|||

Hello:

datediff( dd,openDate,closedDate)

This function will give you how many days between open and closed days.

If this is not what you want, give a little more details about your problem.

Limno

|||Wouldn't the table also need a CaseID field so you know what case was being opened and closed?

How to build this expression?

Greetings friends,

I have the following T-SQL CASE statement. I've spent the last 10 minutes trying to convert it to an expression in my derived column component but to no avail.

case
when f.etypeid < 10 then '000' + cast(f.etypeid as varchar)
when f.etypeid > 10 and f.etypeid < 100 then '00' + cast(f.etypeid as varchar)
else
'0' + cast(f.etypeid as varchar)
end

Many thanks for your help in advance.

Hi again guys,

Finally I managed to work it out. Silly me!

The solution to the above is as follows :

etypeid < 10 ? "000" + (dt_str,1,1252)etypeid : etypeid > 10 && etypeid < 100 ? "00" + (dt_str,2,1252)etypeid : "0" + (dt_str,3,1252)etypeid

Sorry for the bother SSIS friends

|||Mark your post as an answer, please.