Re: select according to category, count, and order

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 06/28/04 

Date: Tue, 29 Jun 2004 00:08:13 +0200

On Mon, 28 Jun 2004 09:05:01 -0700, kingm12345 wrote:

>Okay, I had to learn to use Query Analyzer, etc. Hopefully this will help everyone understand my issue.
(snip)

Hi kingm12345,

Thanks for posting DDL + INSERTS for sample data. This gave me the
opportunity to test the suggestion I made earlier in this thread.

I made an error. A small one, but it goofed up the results. My error
consisted of two quotes in the CASE-expression of the ORDER BY. Without
those quotes, the query reads:

SELECT Category, EmployeeCount
FROM (SELECT 'Category' =
    CASE
        WHEN DATEDIFF(year, ADJ_SCD, getdate()) > 30 THEN '>30'
                WHEN DATEDIFF(year, ADJ_SCD, getdate())<=30 AND
DATEDIFF(year, ADJ_SCD, getdate())>=26 THEN '26-30'
        WHEN DATEDIFF(year, ADJ_SCD, getdate())<=25 AND DATEDIFF(year,
ADJ_SCD, getdate())>=21 THEN '21-25'
        WHEN DATEDIFF(year, ADJ_SCD, getdate())<=20 AND DATEDIFF(year,
ADJ_SCD, getdate())>=16 THEN '16-20'
                WHEN DATEDIFF(year, ADJ_SCD, getdate())<=15 AND
DATEDIFF(year, ADJ_SCD, getdate())>=11 THEN '11-15'
                WHEN DATEDIFF(year, ADJ_SCD, getdate())<=10 AND
DATEDIFF(year, ADJ_SCD, getdate())>=5 THEN '5-10'
        ELSE '<5'
        END,
        Count (*) as EmployeeCount
FROM Personnel

GROUP BY CASE
        WHEN DATEDIFF(year, ADJ_SCD, getdate()) > 30 THEN '>30'
                WHEN DATEDIFF(year, ADJ_SCD, getdate())<=30 AND
DATEDIFF(year, ADJ_SCD, getdate())>=26 THEN '26-30'
        WHEN DATEDIFF(year, ADJ_SCD, getdate())<=25 AND DATEDIFF(year,
ADJ_SCD, getdate())>=21 THEN '21-25'
        WHEN DATEDIFF(year, ADJ_SCD, getdate())<=20 AND DATEDIFF(year,
ADJ_SCD, getdate())>=16 THEN '16-20'
                WHEN DATEDIFF(year, ADJ_SCD, getdate())<=15 AND
DATEDIFF(year, ADJ_SCD, getdate())>=11 THEN '11-15'
                WHEN DATEDIFF(year, ADJ_SCD, getdate())<=10 AND
DATEDIFF(year, ADJ_SCD, getdate())>=5 THEN '5-10'
        ELSE '<5'
        END) X
ORDER BY CASE Category
         WHEN '<5' THEN 1
         WHEN '5-10' THEN 2
         WHEN '11-15' THEN 3
         WHEN '16-20' THEN 4
         WHEN '21-25' THEN 5
         WHEN '26-30' THEN 6
         ELSE 7
         END

And the results are:

Category EmployeeCount
-------- -------------
<5 2
11-15 4
16-20 4
21-25 3
26-30 4
>30 4

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Speed Awareness Workshops
    ... are from the insurers who pay the biggest handouts to confused.com. ... Their quotes are about as impartial as going to Big Eric whose son runs ... If you have a better suggestion, ...
    (uk.transport)
  • Re: how to include special characters in a formula?
    ... > I have tried the suggestion below, but it still doesnt work. ... >> Use double quotes instead of apostrophes. ... >> Pearson Software Consulting, LLC ...
    (microsoft.public.excel.programming)
  • Re: Google search algorithms
    ... Lady Nina wrote: ... then searched for 'problems with' (without the quotes) and the very ... first drop down suggestion was what I was about to type. ... '87 semi-rat LS650, OMF#24 ...
    (uk.rec.motorcycles)
  • Re: Dat files help "Att James"
    ... Double quotes give you two things above and beyond what single ... That is worthless information for us. ... > cgi folder like ... You should have posted some sample data. ...
    (comp.lang.perl.misc)
  • Re: If value is in a list
    ... Is there a point in your repeating your suggestion ... ... And please trim your quotes. ... Anyone who slaps a 'this page is best viewed with Browser X' label on ...
    (comp.lang.javascript)