Help In Simplifying

Jan 6, 2005

Hi,

Any suggestions on how to simplify this piece of code:

'locationCount'= CASE dbo.#requestsbyyeartemp.themonth when '1' then (select count(distinct location) from #requestsbyyeartemp where themonth=1)
when '2' then (select count(distinct location) from #requestsbyyeartemp where themonth=2)
when '3' then (select count(distinct location) from #requestsbyyeartemp where themonth=3)
when '4' then (select count(distinct location) from #requestsbyyeartemp where themonth=4)
when '5' then (select count(distinct location) from #requestsbyyeartemp where themonth=5)
when '6' then (select count(distinct location) from #requestsbyyeartemp where themonth=6)
when '7' then (select count(distinct location) from #requestsbyyeartemp where themonth=7)
when '8' then (select count(distinct location) from #requestsbyyeartemp where themonth=8)
when '9' then (select count(distinct location) from #requestsbyyeartemp where themonth=9)
when '10' then (select count(distinct location) from #requestsbyyeartemp where themonth=10)
when '11' then (select count(distinct location) from #requestsbyyeartemp where themonth=11)
when '12' then (select count(distinct location) from #requestsbyyeartemp where themonth=12) end

locationCount is a variable in a temp table. I am looking for ways to make things less repetitive and more efficient.

Thanks

View 4 Replies

Simplifying Characters For Comparison

Nov 1, 2005

I'm currently working on a series of comparison queries, between data from different countries.

The bad thing about this is, that many countries use special symbols such as ^ ~ � � ` which basically means I have a tough time making the query effecient.

How would I work about converting Ji�i to Jini, so I can compare properly. Is there any way at all?

Thanks in advance,

Trin

View 3 Replies

Dec 11, 2007

I'm dealing with queries that manages a lot of data consolidation and I'm having to repeat large blocks of code to get the desired result.
I'm wondering if that is any way to make it simple.

My queries looks like this:

Code BlockSELECT
(A + B) AS C,
(D + E) AS F,
(G + H) AS I,
(A + B) * (D + E) AS total1,
(D + E) * (G + H) AS total2,
(A + B) * (G + H) AS total3,
(A + B) * (D + E) * (G + H) AS total
FROM table

Can I somehow declare each part of the expression and make it something like this?

Code Block
SELECT
(A + B) AS C,
(D + E) AS F,
(G + H) AS I,
C * F AS total1,
F * I AS total2,
C * I AS total3,
C * F * I AS total
FROM table

Maybe it seems silly, but each sub-expression, on the real life, includes a lot of CASEs and aggragate functions and my codes are getting complex and confuse.

Since I have lots of code pieces that I repeat more than once, I would like to make it simpler.

tks for any help

View 5 Replies

View Related

Help In Simplifying

Simplifying Characters For Comparison

Simplifying Queries With Complex Sub-expressions?