Using logical functions on grouping in T-SQL

Question

Is there a way to evaluate OR or AND logic functions using a group in t-SQL?

DECLARE @TimeClock TABLE 
                   (
                       EName  NVARCHAR(14),
                       Time   NVARCHAR(5),
                       OnTime TINYINT
                   )
    
INSERT INTO @TimeClock VALUES ('Adam', '07:55', 1)
INSERT INTO @TimeClock VALUES ('Adam', '08:03', 0)
INSERT INTO @TimeClock VALUES ('Bob',  '07:57', 1)
INSERT INTO @TimeClock VALUES ('Bob',  '07:59', 1)
INSERT INTO @TimeClock VALUES ('Carl', '08:01', 0)
INSERT INTO @TimeClock VALUES ('Carl', '08:02', 0)

SELECT    
    EName,
    OR(OnTime) AS SometimesLate,
    AND(OnTime) AS AlwaysLate,
    NOT(OR(OnTime)) AS NeverLate
FROM 
    @TimeClock
GROUP BY 
    EName

I could do it like this for a very simple case:

SELECT    
    tc.EName,
    IIF(n.EName IS NOT NULL, 1, 0) AS SometimesLate,
    IIF(o.EName IS NULL, 1, 0) AS AlwaysLate,
    IIF(n.EName IS NULL, 1, 0) AS NeverLate
FROM 
    (SELECT DISTINCT EName FROM @TimeClock) tc
LEFT JOIN 
    (SELECT DISTINCT EName FROM @TimeClock WHERE OnTime = 1) o ON o.EName = tc.EName
LEFT JOIN 
    (SELECT DISTINCT EName FROM @TimeClock WHERE OnTime = 0) n ON n.EName = tc.EName

But if I had a lot of different columns (e.g., OnTime, 5 minutes late, 10 minutes late, etc.) that becomes pretty intensive.

Aside: There are various tricks using [`Sign`](https://learn.microsoft.com/en-us/sql/t-sql/functions/sign-transact-sql?view=sql-server-ver15) for implementing logic that can be combined with aggregation. Masked bits can be reduced to `1`/`0` as in [this](https://stackoverflow.com/a/47949762/92546) answer. To `or` bits use `Sign( B1 + B2 + ... )`. (`And` is simply `B1 * B2 * ...`.) A more fiendish example of arithmetic logic is used in a CTE in [this](https://stackoverflow.com/a/62944142/92546) answer. — HABO, Sep 21 '20 at 22:16

GMB · Accepted Answer · 2020-09-21T21:41:43.497

2

Some databases have boolean aggregation functions (such as Postgres), but SQL Server is not one of them.

However, no need for that complicated logic here. We can emulate boolean aggregation with MIN(), MAX(), and basic arithmetics, like so:

select    
    ename,
    1 - min(ontime) as sometimeslate,
    1 - max(ontime) as alwayslate,
    min(ontime) as neverlate
from @timeclock
group by ename

edited Sep 21 '20 at 21:41

answered Sep 21 '20 at 21:36

GMB

216,147
25
84
135

Okay, that's just brilliant. Thanks. – DaveX Sep 21 '20 at 22:32
Also, looking at all the possibilities, I now see MAX(ontime) as sometimesontime – DaveX Sep 21 '20 at 22:33

Using logical functions on grouping in T-SQL

1 Answers1