Prev: Inner Dynamic SQL Single Quotes Issue
Next: how to strip specific email address from nvarchar field with multiple addresses
From: JP on 30 Jun 2008 21:46
I have a query that produces a list of companies and their effective controls
status for the given year:

select companyname, iseffective, year from internalcontrols
where year > 2004

This produces a list similar to this:

ABC Corp. yes 2005
ABC Corp. yes 2006
ABC Corp. yes 2007
ABC Corp. yes 2008
XYZ Inc. yes 2005
XYZ Inc. yes 2006
XYZ Inc. no 2007
XYZ Inc. yes 2008
EFG Corp. no 2008

What I would like to do is show the company one time and display an "X" if
there was a "no" in any of the years and a blank "" if all years have yes.
So the results above would be displayed as:

ABC Corp.
XYZ Inc. X
EFG Corp. X

I tried using the CASE Statement but that didn't seem to work. How should
I go about creating the query?
From: Tom Cooper on 30 Jun 2008 22:06
Select companyname, Max(Case When iseffective = 'yes' Then 'X' Else ' ' End)
From internalcontrols
Where year > 2004
Group By companyname;

Tom

"JP" <JP(a)discussions.microsoft.com> wrote in message
news:E256AE4C-BEC2-49D4-9758-FA30852406B5(a)microsoft.com...
>I have a query that produces a list of companies and their effective
>controls
> status for the given year:
>
> select companyname, iseffective, year from internalcontrols
> where year > 2004
>
> This produces a list similar to this:
>
> ABC Corp. yes 2005
> ABC Corp. yes 2006
> ABC Corp. yes 2007
> ABC Corp. yes 2008
> XYZ Inc. yes 2005
> XYZ Inc. yes 2006
> XYZ Inc. no 2007
> XYZ Inc. yes 2008
> EFG Corp. no 2008
>
> What I would like to do is show the company one time and display an "X" if
> there was a "no" in any of the years and a blank "" if all years have yes.
> So the results above would be displayed as:
>
> ABC Corp.
> XYZ Inc. X
> EFG Corp. X
>
> I tried using the CASE Statement but that didn't seem to work. How
> should
> I go about creating the query?


From: Eric Isaacs on 30 Jun 2008 22:21
Your data in your InternalControls table is not normalized. You
should probably look inot normalizing it first, but with that caveat,
here's a quick solution that will work.


IF OBJECT_ID('tempdb..#InternalControls') IS NOT NULL
DROP TABLE #InternalControls

CREATE TABLE #InternalControls
(
CompanyName VARCHAR(50),
IsEffective VARCHAR(3),
[Year] INT
)

INSERT #InternalControls (
CompanyName,
IsEffective,
[Year]
)
SELECT 'ABC Corp.', 'yes', '2005'
UNION SELECT 'ABC Corp.', 'yes', '2006'
UNION SELECT 'ABC Corp.', 'yes', '2007'
UNION SELECT 'ABC Corp.', 'yes', '2008'
UNION SELECT 'XYZ Inc.', 'yes', '2005'
UNION SELECT 'XYZ Inc.', 'yes', '2006'
UNION SELECT 'XYZ Inc.', 'no', ' 2007'
UNION SELECT 'XYZ Inc.', 'yes', '2008'
UNION SELECT 'EFG Corp.', 'no', '2008'

SELECT * FROM #InternalControls

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results

CREATE TABLE #Results
(
CompanyName VARCHAR(50),
IsNotEffective VARCHAR(1)
)

INSERT #Results (
CompanyName,
IsNotEffective
)
SELECT DISTINCT CompanyName, 'X' FROM #InternalControls
WHERE IsEffective = 'no'

INSERT #Results (
CompanyName,
IsNotEffective
)
SELECT DISTINCT
CompanyName,
''
FROM
#InternalControls
WHERE
CompanyName NOT IN (SELECT CompanyName FROM #Results)

SELECT * FROM #Results

This solution assumes that there are no "missing" records. If there
are any no's it flags them, but it doesn't verify that all the years
are present and yes. It assumes they're yes if they're missing.
From: Eric Isaacs on 30 Jun 2008 22:29
Tom's solution is much cleaner, but he had it turned around.

SELECT
CompanyName,
CASE WHEN MIN(IsEffective) = 'Yes' THEN ''
ELSE 'X'
END
FROM
InternalControls
WHERE
YEAR > 2004
GROUP BY
CompanyName
From: JP on 30 Jun 2008 23:25
Thanks!

"Tom Cooper" wrote:

> Select companyname, Max(Case When iseffective = 'yes' Then 'X' Else ' ' End)
> From internalcontrols
> Where year > 2004
> Group By companyname;
>
> Tom
>
> "JP" <JP(a)discussions.microsoft.com> wrote in message
> news:E256AE4C-BEC2-49D4-9758-FA30852406B5(a)microsoft.com...
> >I have a query that produces a list of companies and their effective
> >controls
> > status for the given year:
> >
> > select companyname, iseffective, year from internalcontrols
> > where year > 2004
> >
> > This produces a list similar to this:
> >
> > ABC Corp. yes 2005
> > ABC Corp. yes 2006
> > ABC Corp. yes 2007
> > ABC Corp. yes 2008
> > XYZ Inc. yes 2005
> > XYZ Inc. yes 2006
> > XYZ Inc. no 2007
> > XYZ Inc. yes 2008
> > EFG Corp. no 2008
> >
> > What I would like to do is show the company one time and display an "X" if
> > there was a "no" in any of the years and a blank "" if all years have yes.
> > So the results above would be displayed as:
> >
> > ABC Corp.
> > XYZ Inc. X
> > EFG Corp. X
> >
> > I tried using the CASE Statement but that didn't seem to work. How
> > should
> > I go about creating the query?
>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Inner Dynamic SQL Single Quotes Issue
Next: how to strip specific email address from nvarchar field with multiple addresses