count

Hi,
There is a select query which returns a count as below:
select
@.count = count(typecode)
from
tbl_Types
where
typecode = @.typecode
depending on the count, this is what needs to be done...
If there is a count of 1 to 26 then I would like to use A-Z and then
anything from 26 onwards needs to use 1-infinity
For example, let's say the count returns 4, so now I would like the sql to
return D since D is the fourth letter in the alphabet.
Let's say the count returns 8, so now I would like the sql to return H since
H is the 8th alphabet.
And if for example the count is something like 27 then the sql should return
1 since a-z takes you to 1-26 and now should use number 1.
And a last example, if the coount is 30, then sql should return the number 4
Hope this is clear.
ThanksYou could use the CASE expression,
such as :
SELECT @.count = CASE
WHEN count(typecode) = 1 THEN 'A'
WHEN count(typecode) = 2 THEN 'B'
WHEN count(typecode) = 3 THEN 'C' -- and
so forth up to 26
WHEN count(typecode) > 26 THEN
(count(typecode) - 26) -- for anything over 26
ELSE '--'
END
from
tbl_Types
where
typecode = @.typecode
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
___________________________________
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:7768EE85-DE6E-4220-9513-E2B557A4CE42@.microsoft.com...
> Hi,
> There is a select query which returns a count as below:
> select
> @.count = count(typecode)
> from
> tbl_Types
> where
> typecode = @.typecode
> depending on the count, this is what needs to be done...
> If there is a count of 1 to 26 then I would like to use A-Z and then
> anything from 26 onwards needs to use 1-infinity
> For example, let's say the count returns 4, so now I would like the sql to
> return D since D is the fourth letter in the alphabet.
> Let's say the count returns 8, so now I would like the sql to return H
since
> H is the 8th alphabet.
> And if for example the count is something like 27 then the sql should
return
> 1 since a-z takes you to 1-26 and now should use number 1.
> And a last example, if the coount is 30, then sql should return the number
4
> Hope this is clear.
> Thanks|||Could you simplify the select to:
SELECT CASE WHEN Count(*) BETWEEN 1 AND 26 THEN CHAR(64+COUNT(*)) ELSE
CAST(COUNT(*) - 26 as char(4)) END
or have I missed something (I usually do!)
Cheers
Will|||Will.. That works like a charm.
another solution.. juz for fun :)
SELECT coalesce(CHAR(64 + count(typecode) +
(count(typecode)/27)*200),cast(count(typecode)-26 as varchar)) from
tbl_Types
where
typecode = @.typecode|||This is great.
It saves me having alot of lines
Thanks
"Will" wrote:

> Could you simplify the select to:
>
> SELECT CASE WHEN Count(*) BETWEEN 1 AND 26 THEN CHAR(64+COUNT(*)) ELSE
> CAST(COUNT(*) - 26 as char(4)) END
> or have I missed something (I usually do!)
> Cheers
> Will
>|||What is the purpose of:
(count(typecode)/27)*200
?
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:242B8E78-B391-439E-BAC9-E08C23FFE465@.microsoft.com...
> Will.. That works like a charm.
> another solution.. juz for fun :)
> SELECT coalesce(CHAR(64 + count(typecode) +
> (count(typecode)/27)*200),cast(count(typecode)-26 as varchar)) from
> tbl_Types
> where
> typecode = @.typecode
>|||the char function returns a null if the input value is greater than 255.
So if the count is >26 I make sure the value is >255 so that coalesce can
handle it :)
--
"Jim Underwood" wrote:

> What is the purpose of:
> (count(typecode)/27)*200
> ?
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:242B8E78-B391-439E-BAC9-E08C23FFE465@.microsoft.com...
>
>|||it looks to me like a "trigger" to suddenly push the value out of the
range of valid char(x) so that it becomes null once the value goes over
26.|||Ahhhh...
And it is casted to an integer by default, so it returns zero otherwise.
That's an interesting approach.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:84F92B38-7D8A-430C-A919-1AEDEC2F4526@.microsoft.com...
> the char function returns a null if the input value is greater than 255.
> So if the count is >26 I make sure the value is >255 so that coalesce can
> handle it :)
> --
>
>
> "Jim Underwood" wrote:
>|||I think "short circuit" would be a more appropriate term.
"Will" <william_pegg@.yahoo.co.uk> wrote in message
news:1147269321.865790.201980@.i39g2000cwa.googlegroups.com...
> it looks to me like a "trigger" to suddenly push the value out of the
> range of valid char(x) so that it becomes null once the value goes over
> 26.
>sql