SQL query with a condtion

RIAS
RIAS used Ask the Experts™
on
Hello,

Need a little tweak here
DECLARE @LoggedDate1 AS DATE = '2018-06-11'
DECLARE @LoggedDate2 as DATE = '2018-06-12'




SELECT  Ref as [Ref No], Garage, EstFinishDate,Resolution ,IsNotActive as [Job Completed]
FROM  TABLE1 WHERE IsDeleted is null AND  (  LoggedDate >= @LoggedDate1    AND LoggedDate  <= @LoggedDate2)

Open in new window


In the results how can I display
[Job Completed]= 'Completed' when IsNotActive = True

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope this is what you are expecting..
SELECT  Ref as [Ref No], Garage, EstFinishDate,Resolution ,IsNotActive as [Job Completed]
, CASE WHEN IsNotActive = True THEN 'Completed' ELSE NULL END AS [Job Completed]
FROM  TABLE1 WHERE IsDeleted is null AND  (  LoggedDate >= @LoggedDate1    AND LoggedDate  <= @LoggedDate2)

Open in new window

Senior Developer
Commented:
The problem is: There is no TRUE/FALSE data type. Thus what does IsNotActive=True mean?

Is it a BIT, the default data type for such values, then you need IsNotActiive=1. Or is it a text column? btw, we should never use negated properties. IsActive makes more sense. Avoids double negation.

e.g. this should work:

DECLARE @StartDate AS DATE = '2018-06-11';
DECLARE @EndDate AS DATE = '2018-06-12';

SELECT Ref AS [Ref No],
       Garage,
       EstFinishDate,
       Resolution,
       CASE
           WHEN IsNotActive = 1 THEN
               'Completed'
           ELSE
               ''
       END AS [Job Completed]
FROM TABLE1
WHERE IsDeleted IS NULL
      AND LoggedDate >= @StartDate
      AND LoggedDate <= @EndDate;

Open in new window

Author

Commented:
Thanks...trying
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Thanks Ste5an for correcting..
Missed single quotes..
SELECT  Ref as [Ref No], Garage, EstFinishDate,Resolution ,IsNotActive as [Job Completed]
, CASE WHEN IsNotActive = 'True' THEN 'Completed' ELSE NULL END AS [Job Completed]
FROM  TABLE1 WHERE IsDeleted is null AND  (  LoggedDate >= @LoggedDate1    AND LoggedDate  <= @LoggedDate2)

Open in new window

Author

Commented:
Thanks Experts!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial