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?
 
Mark WillsConnect With a Mentor Topic 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
 
Pratima PharandeConnect With a Mentor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Cedric_DConnect With a Mentor Commented:
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
 
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
All Courses

From novice to tech pro — start learning today.