Adding text to a value?

Hello!

Hoping this is simple. I have a age SP that does the age from getdate and DOB. I also have it that does months if the year is zero. What I would like to do if the value is 10, and it's a month value, how would I add the "mo" to it. So it reads 10mo. Is this possible?

Thanks!

Rudy

use the "+" operator

@.Months_string = @.months + 'mo'

if @.months is a number field then cast it first:

@.Months_string = cast(@.months as varchar)+ 'mo'|||

You need to cast the number to a character and then just concatenate

selectconvert(varchar(3), @.age)+'mo.'

|||

Code Snippet

select case when yr > 0
then cast(yr as varchar) + ' Yr'
else cast(mo as varchar) + ' Mo'
end as theValue
from ( select 3 as yr, 3 as mo union all
select 1, 5 union all
select 0, 10
) a

/*
theValue
3 Yr
1 Yr
10 Mo
*/

|||

WOW!!

Thank you all! Great suggestions!

Rudy

|||

Ok!

I spoke way too soon! The first mistake I made was to trust I actually wrote the store procedure correct. LOL. It works, but it calculates the age wrong. I guess I was so excited it fiinally worked with out any errors, I didn't check if it was correct. My DOB field is date time, my Age field in nvarchar. He is my feeble attempt. I'm sure there is a better way to write this.

UPDATE Active_Orders

SET Age =CASEWHENdateadd(year,datediff(year, DOB,GetDate()), DOB)

<GetDate()THEN(datediff(year, DOB,GetDate()))- 1 ELSEdatediff(month, DOB,getdate())

% 12 ENDFROM Active_Orders

Any help on this would be greatly appreciated!

Thanks!!

Jim

|||

Jim:

Would something like this work for you:

Code Snippet

UPDATE Active_Orders
SET Age = CASE WHEN year(getdate() - cast(@.dob as datetime)) > 1900
THEN (datediff (year, DOB, GetDate())) - 1
ELSE datediff(month, DOB, getdate()) % 12
END
FROM Active_Orders

|||

Code Snippet

createtable #t1 (namevarchar(25), dob datetime)

insertinto #t1

select'Gomez','02/29/1908'union

select'Morticia','05/01/1936'union

select'Wednesday','10/31/1959'union

select'Friday','12/25/2006'

select*,

age =casewhendatediff(mm, dob,getdate())< 12

--months

thencasewhendatediff(mm, dob,getdate())< 0

then 0

elsedatediff(mm, dob,getdate())

end

else

-- years

datediff(mm, dob,getdate())/12

end

from #t1

So, the update statement would be:

Code Snippet

UPDATE Active_Orders

SET Age =casewhendatediff(mm, dob,getdate())< 12

--months

thencasewhendatediff(mm, dob,getdate())< 0

then 0

elsedatediff(mm, dob,getdate())

end

else

-- years

datediff(mm, dob,getdate())/12

end

FROM Active_Orders

adding identity column

I'm adding an Identity column to a table like this:
SELECT DISTINCT
T.NAME_LAST,
T.NAME_FIRST,
T.DOB,
RowID = IDENTITY(INT, 1, 1)
INTO #TempResultSet
FROM #TempPopulation T
LEFT JOIN #TempDateOfService B
ON B.KEY_ID = T.KEY_ID
ORDER BY NAME_LAST, NAME_FIRST
The problem I'm having is that the RowID isn't added to the table after it's
Ordered, which is what I want/need. Is there a way to do this, without
having to put the data into another temp table, where it's already ordered?
Thanks, AndreSELECT INTO does not guarantee order.
See this KB article:
http://support.microsoft.com/kb/273586
And yesterday's thread in this group:
"Row numbering unpredictable"
If you know the order by is NAME_LAST, NAME_FIRST then what difference does
it make what the identity value is? Never mind that if someone changes
their last name, suddenly your identity values are all messed up.
"Andre" <no@.spam.com> wrote in message
news:OGoht0oXGHA.3740@.TK2MSFTNGP03.phx.gbl...
> I'm adding an Identity column to a table like this:
> SELECT DISTINCT
> T.NAME_LAST,
> T.NAME_FIRST,
> T.DOB,
> RowID = IDENTITY(INT, 1, 1)
> INTO #TempResultSet
> FROM #TempPopulation T
> LEFT JOIN #TempDateOfService B
> ON B.KEY_ID = T.KEY_ID
> ORDER BY NAME_LAST, NAME_FIRST
> The problem I'm having is that the RowID isn't added to the table after
> it's
> Ordered, which is what I want/need. Is there a way to do this, without
> having to put the data into another temp table, where it's already
> ordered?
> Thanks, Andre
>|||Thanks Aaron. The reason I need the identity column in the order of
name_last, name_first is because I'm using the identity column in a sproc
that handles paging. I get my resultset, in this case it's 2000 records,
and I only want to return 25 records to the front-end. I need my data
ordered so I know which 25 to send in. I've got it working by putting my
ordered data into a temp table, then into another temp where I add the ident
column. It just seemed like an extra step, and an extra temp table.
Perhaps not the most optimal solution but it works.
Andre|||> Thanks Aaron. The reason I need the identity column in the order of
> name_last, name_first is because I'm using the identity column in a sproc
> that handles paging.
THERE ARE BETTER SOLUTIONS THAN THIS!
http://www.aspfaq.com/2120|||I'm basically doing exactly what the article showed, but just having to make
use of the temp table to order my data. My method is precisely what I've
seen in SQL Magazine previously.
Thanks Aaron.
Andre