Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    74

    Unanswered: Help in simplifying

    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

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    what about:

    'locationCount'= (select count(distinct location) from #requestsbyyeartemp where themonth=CAST(dbo.#requestsbyyeartemp.themonth AS SmallInt))

    Hope, this helps.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  3. #3
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    Show all code, please.
    Your piece like a part of query.

  4. #4
    Join Date
    Dec 2004
    Location
    Kharkov, Ukraine
    Posts
    40
    Quote Originally Posted by DoktorBlue
    what about:

    'locationCount'= (select count(distinct location) from #requestsbyyeartemp where themonth=CAST(dbo.#requestsbyyeartemp.themonth AS SmallInt))

    Hope, this helps.
    Is Cast need? I don't can understand which data type of #requestsbyyeartemp.themonth

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    According to the WHEN criterias, it seems like text. But, as you already asked, I'm also curious to see the complete query / script. To get an explanation of the goal of this statement would also help.

    Maybe its already sufficient to have

    'locationCount'= (select count(distinct location) from #requestsbyyeartemp)
    Last edited by DoktorBlue; 01-06-05 at 08:17.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •