Selecting records and true or false value

Question

Post reply

Selecting records and true or false value

SIJCOOKE

SSC Veteran

Points: 270
More actions
September 21, 2019 at 4:36 pm
Go to Answer
#3682277

Hi All,
Thanks in advanced for help given. I have the following table:

I want to run a query to show two columns - process ID and a Boolean column. The Boolean column will be true if all the completeddatetime values have a datestamp for that process id. It will be false if any of the completeddatetime values have a null.
So the output should say:
5931 true
5932 true
5933 false
5934 false
I have tried this with iif statement and case statement but cant get it working, but am sure its quite simple!
Thank you. sijcooke

Jeff Moden

SSC Guru

Points: 1004329
More actions
September 21, 2019 at 6:56 pm
Answer
#3682292
Here's one simple method provided those are actual NULLs and not just a word of '[NULL]':
```
 SELECT  ProcessID
        ,IsCompleted = CASE WHEN COUNT(ProcessID)=COUNT(CompletedDateTime) THEN 'True' ELSE 'False' END
   FROM yourtable
  GROUP BY ProcessID
;
```
As a bit of a sidebar and judging only from what you've posted, I have to tell you that there's no way that I'd allow the CompletedDateTime to continue as a character-based column. It should be a DATETIME datatype or (depending on what you need to do with the column) a DATETIME2(0) datatype.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

as_1234 SSCrazy Points: 2859 More actions · Answer 1

Hello,

Can you post some table data as detailed here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help ? It enables people to give more accurate answers.

I'm wondering if your question could be solved with a self join on the table but I can test with some data. The left side will be used to list all the process ID's and the right hand side to evaluate the timestamps. Others may have better ideas.

SIJCOOKE SSC Veteran Points: 270 More actions · Answer 2

Hi, ive not done this before so struggling a little.. heres what I have so far, hope it helps:

CREATE TABLE TABLE_PROCESSTIMES
(
  PROCESSID integer NOT NULL,
  COMPLETEDDATETIME timestamp,
);

SELECT '5933','[null]' UNION ALL
SELECT '5933','19.09.2019, 11:30:00.000' UNION ALL
SELECT '5934','[null]' UNION ALL
SELECT '5933','[null]' UNION ALL
SELECT '5931','19.09.2019, 11:30:00.000' UNION ALL
SELECT '5931','19.09.2019, 11:30:00.000' UNION ALL
SELECT '5931','19.09.2019, 11:30:00.000' UNION ALL
SELECT '5932','19.09.2019, 11:30:00.000'

SIJCOOKE SSC Veteran Points: 270 More actions · Answer 3

Yes they are actual nulls - Perfect, thanks so much!!

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 4

And now I see that you've posted while I was constructing a response. You can't actually insert into a TIMESTAMP datatype in SQL Server. With that in mind, which RDBMS are you using?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)