How to create a query to include a "X" in the expression field when a field in the query Is Not Null.

Hi,
I have a SQL table with 13,000 records.  I am trying to create a query using 5 key fields
from the table to identify if the maintenance performed on a car under a specific MaintenanceWorkOrder is also performed under another MaintenanceWorkOrder. For example, if I go to WalMart to get a Oil Change, WalMart has 3 different Oil Change Maintenance packages ( A, B, & C) that include the oil change service.  I will use the data from the queries in excel to create a Pivot table to analyze the maintenance performed.

I created the first query to select only the 5 fields I need to work with since I do not need all the fiedls from the table.

Question: How can I create the second query to include a extra column called MaintIncluded that will list a "X" in the colmun field on each row that has a MaintenanceDescription.

This is what I have so far, but,not sure ow to produce the results below:

SELECT MaintenanceID, MaintenanceWorkOrder, AutoMake, MaintenanceSystem, MaintenanceDescription
CASE WHEN MaintenanceDescription IS NOT NULL THEN ''X'' ELSE '''' END AS x
FROM         dbo.qryMaintDescAnalysis
This is what I have so far, but,not sure how to produce the results below:
 
SELECT MaintenanceID, MaintenanceWorkOrder, AutoMake, MaintenanceSystem, MaintenanceDescription
CASE WHEN MaintenanceDescription IS NOT NULL THEN ''X'' ELSE '''' END AS x
FROM         dbo.qryMaintDescAnalysis
 
I abbrv. the names to fit in the window.
 
MaintID MaintWorkOrder AutoMake       MaintSystem        MaintDescription           MaintInclude
1                 22233              Kia                   Brake                Change Rotors                 X
2                 22223             Dodge               Brake                Null             
3                 11123             Mazda               Brake               Change Brake Pads          X
4                 1580               Kia        30,000 Mile Checkup    Change Brake Pads          X
5                 23570             Volvo                  AC                  Repair AC Controls           X

Open in new window

cesemjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pratima PharandeCommented:
SELECT MaintenanceID, MaintenanceWorkOrder, AutoMake, MaintenanceSystem, MaintenanceDescription
CASE WHEN MaintenanceDescription IS NOT NULL THEN '' ELSE 'X' END AS MaintInclude
FROM         dbo.qryMaintDescAnalysis
0
Pratima PharandeCommented:
SELECT MaintenanceID, MaintenanceWorkOrder, AutoMake, MaintenanceSystem, MaintenanceDescription
CASE WHEN MaintenanceDescription IS NOT NULL THEN 'X' ELSE '' END AS MaintInclude
FROM         dbo.qryMaintDescAnalysis
0
Cedric_DCommented:
You forgot a comma before the CASE:

SELECT MaintenanceID, MaintenanceWorkOrder, AutoMake, MaintenanceSystem, MaintenanceDescription,
 CASE WHEN MaintenanceDescription IS NOT NULL THEN 'X' ELSE '' END AS MaintInclude
FROM         dbo.qryMaintDescAnalysis
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Mark WillsTopic AdvisorCommented:
Yeah, your original query was fine except for that comma... Would go a step further...

SELECT MaintenanceID, MaintenanceWorkOrder, AutoMake, MaintenanceSystem, MaintenanceDescription,
 CASE WHEN len(rtrim(isnull(MaintenanceDescription,''))) > 0 THEN 'X' ELSE '' END AS MaintInclude
FROM         dbo.qryMaintDescAnalysis
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cesemjAuthor Commented:
Thank you all, the suggestion works,

Not sure if I should create a new post for thisbut I have a question.

Can I use the Min on the MaintenanceDescription field and Group By on the MaintenanceWorkOrder or AutoMake to determine if the maintenance performed on a car under a specific MaintenanceWorkOrder is also performed under another MaintenanceWorkOrder?

This way I am only listing the MaintenanceDescription one tiem and listing all the MaintenanceWorkOrders or AutoMakes that the same MaintenanceDescription is linked to.

I would create another Query that uses second query above, dbo.qryMaintDescAnalysis, as the source to display results.

Thanks again

For example, if I go to WalMart to get a Oil Change, WalMart has 3 different Oil Change Maintenance packages ( A, B, & C) that include the oil change service.  
0
cesemjAuthor Commented:
Or correct me if I am worng, Iuse dbo.qryMaintDescAnalysis as a data source or just export the data to another program like Excel or use a third party Pivot tool to create a Pivot table to group and sort the results?

0
Mark WillsTopic AdvisorCommented:
So long as it doesn't get out of hand, then for the sake of a simple comma, more than happy to continue - if it does start a whole new life, then maybe we need to consider another question then - you are premium, so points shouldn't be a problem...

You can, but the group by becomes interesting... and suggets it might not be the group by you are thinking of... and where is "car" in the above - it is automake ?

Try this and let us know how you want it to be different...

SELECT MaintenanceSystem,  MaintenanceWorkOrder, AutoMake
FROM    dbo.qryMaintDescAnalysis
order by MaintenanceSystem, MaintenanceWorkOrder, AutoMake

0
Mark WillsTopic AdvisorCommented:
We can PIVOT in SQL as well...

lets have a look at the output examples and we can take it from there...
0
cesemjAuthor Commented:
Thank you, Yes the Car is the AutoMake field sorry,
The output I am trying to receive is listed below:
The first and Second query givees me the results below: 
=======================================================
MaintID MaintWorkOrder AutoMake MaintSystem MaintDescription MintInclude
1       22233          Kia      Brake       ChangeRotors        X
2       22223          Dodge    Brake       Null             
3       11123          Mazda    Brake       Change Brake Pads   X
4       1580           Kia      30000 Check Change Brake Pads   X
5       23570          Volvo    AC          Repair AC Controls  X 
 
The Final query or Pivot View gives me the results below allowing me to
identify if the maintenance performed listed under MaintDescription on a AutoMake under a specific MaintenanceWorkOrder is also performed under another MaintenanceWorkOrder. 
=========================================================
MaintSystem MaintDescription      22223  11123  1580  23570
 
Brake       ChangeRotors            X
Brake       Change Brake Pads              X     X
AC          Repair AC Controls                          X
 
orderd by AutoMake.
 
OR if the result above is not possible then
 
===============================================
MaintDescription      22223  11123  1580  23570
 
ChangeRotors            X
Change Brake Pads              X     X
Repair AC Controls                          X
 
orderd by MaintenanceSystem, MaintenanceWorkOrder, AutoMake.
 
OR
 
Thanks again,

Open in new window

0
cesemjAuthor Commented:
You are correct, my thinking was wrong the Order by is what I should have been thinking.  

SELECT MaintenanceID, MaintenanceWorkOrder, AutoMake, MaintenanceSystem, MaintenanceDescription,
 CASE WHEN len(rtrim(isnull(MaintenanceDescription,''))) > 0 THEN 'X' ELSE '' END AS MaintInclude
FROM         dbo.qryMaintDescAnalysis
order by MaintenanceSystem, MaintenanceWorkOrder, AutoMake

0
Mark WillsTopic AdvisorCommented:
built a pivot table for your other question, went to post and it is deleted !!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.