Create a new column in SQL query 2

[mentasses]

I have a SELECT query with a column called FUELCARDNUMBER.
I want to create another column called FUELCARD which records "Y" if there is a fuel card number in the FUELCARDNUMBER column and "N" if it is blank.

I have tried:

IIF(IsNull [FUELCARDNUMBER],"N","Y") AS FUELCARD

But this givs me errors.

Any clues?

 

[JayKusch]

SELECT 'FUELCARD'= CASE
WHEN FUELCARDNUMBER IS NULL THEN 'N'
ELSE 'Y'
END
FROM MyTable



Thanks

J. Kusch

 

[mentasses]

Thanks JayKusch

The full query is at present:

SELECT a.STAFFNO,a.REGISTRATION,a.CURRENTCAR,a.DATEOFUSE,
b.MAKE, b.MODEL, b.DATESOLD, b.FUELCARDNUMBER,b.[DESCRIPTION]
FROM ELYOSERVICES.dbo.CARUSAGE a LEFT JOIN VBTGLOBALDATA.dbo.COMPANYCARS b
ON a.REGISTRATION = b.REGISTRATIONNUMBER
WHERE a.CURRENTCAR='T'

Where would I add the additional select statement.

I'm very new to SQL from a background in Access.

 

[amorous]

SELECT a.STAFFNO,a.REGISTRATION,a.CURRENTCAR,a.DATEOFUSE,
b.MAKE, b.MODEL, b.DATESOLD, (CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ELSE 'Y') AS FUELCARD,b.[DESCRIPTION]
FROM ELYOSERVICES.dbo.CARUSAGE a LEFT JOIN VBTGLOBALDATA.dbo.COMPANYCARS b
ON a.REGISTRATION = b.REGISTRATIONNUMBER
WHERE a.CURRENTCAR='T'

VJ

 

[JayKusch]

Amorous was close but forgot the END to the CASE statement. It should read ...

SELECT a.STAFFNO,a.REGISTRATION,a.CURRENTCAR,a.DATEOFUSE,
b.MAKE, b.MODEL, b.DATESOLD, (CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ELSE 'Y' END) AS FUELCARD,b.[DESCRIPTION]
FROM ELYOSERVICES.dbo.CARUSAGE a LEFT JOIN VBTGLOBALDATA.dbo.COMPANYCARS b
ON a.REGISTRATION = b.REGISTRATIONNUMBER
WHERE a.CURRENTCAR='T'

Thanks

J. Kusch

 

[mentasses]

Thanks Guys, works great.

 

[mentasses]

Sorry one last problem.

When I run the query my dates come out in the format
yyyy-mm-dd 00:00:00:000 when I want dd-mm-yyyy.

I need to use the CONVERT function but do not know how to enter it in the query.

Can you help?

 

[amorous]

Does this work,

SELECT a.STAFFNO,a.REGISTRATION,a.CURRENTCAR,Convert(datetime,a.DATEOFUSE) AS Dateofuse,
b.MAKE, b.MODEL, Convert(datetime,b.DATESOLD) AS datesold, (CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ELSE 'Y' END) AS FUELCARD,b.[DESCRIPTION]
FROM ELYOSERVICES.dbo.CARUSAGE a LEFT JOIN VBTGLOBALDATA.dbo.COMPANYCARS b
ON a.REGISTRATION = b.REGISTRATIONNUMBER
WHERE a.CURRENTCAR='T'

-VJ

 

[JayKusch]

SELECT a.STAFFNO,a.REGISTRATION,a.CURRENTCAR,a.DATEOFUSE,
b.MAKE, b.MODEL, CONVERT(Char,b.DATESOLD,105) as DateSold, (CASE WHEN b.FUELCARDNUMBER IS NULL THEN 'N' ELSE 'Y' END) AS FUELCARD,b.[DESCRIPTION]
FROM ELYOSERVICES.dbo.CARUSAGE a LEFT JOIN VBTGLOBALDATA.dbo.COMPANYCARS b
ON a.REGISTRATION = b.REGISTRATIONNUMBER
WHERE a.CURRENTCAR='T'



Thanks

J. Kusch

 

[amorous]

Jaykush,

I am still in the learning stages. Could you please explain me what does 105 stand for in your convert function. I would also be glad if you could point me to a link or BOL or list the different Date conversion functions.

Thanks

-VJ

 

[JayKusch]

The 105 is a "Sytle". It is used in the convert statement to format a given date. Take a look in Books OnLine for the subject of "CAST and CONVERT"

Thanks

J. Kusch

 

[mentasses]

Thanks again to both of you.
You're a couple of STARS.