December 13, 2009 at 9:07 am
Hi folks
I'm looking for an elegant, efficient manner to evaluate the domain of a binary value.
With respect to the simple scenario below (not my actual data - but reflects my issue), the domain for the three codes are
CDEE_FLG
A0
A1
B0
B1
C1
As part of my processing, I only take action when E_FLG is true (i.e. = 1) for records associated with a code. In this case only C meets this criteria and is therefore processed.
The logic would be something like...
For all records according to CDE
where DT is less than or equal to (some date)
and the domain of E_FLG is true
Then start processing
To provide some context. The codes and dates reflect trades and the dates they were entered. E_FLG is an exit flag that denotes whether a trade is still active. The process I'm referring to above inserts a new trade into a trade table. A & B still have active trades so there's no need to insert new trades - only C meets this criteria.
Thx in advance,
Wayne
-- Source data
DROP TABLE dbo.DOM_EVAL
CREATE TABLE dbo.DOM_EVAL
(
CDE CHAR(1) NOT NULL,
DT DATE NOT NULL,
E_FLG BIT
)
INSERT INTO dbo.DOM_EVAL(CDE, DT, E_FLG)
SELECT 'A', '2009-01-03', 1 UNION ALL
SELECT 'A', '2009-01-07', 1 UNION ALL
SELECT 'A', '2009-01-14', 1 UNION ALL
SELECT 'A', '2009-02-03', 1 UNION ALL
SELECT 'A', '2009-02-20', 1 UNION ALL
SELECT 'A', '2009-03-03', 1 UNION ALL
SELECT 'A', '2009-03-17', 0 UNION ALL
SELECT 'B', '2009-01-07', 1 UNION ALL
SELECT 'B', '2009-01-16', 1 UNION ALL
SELECT 'B', '2009-02-25', 1 UNION ALL
SELECT 'B', '2009-03-30', 1 UNION ALL
SELECT 'B', '2009-04-16', 1 UNION ALL
SELECT 'B', '2009-05-21', 1 UNION ALL
SELECT 'B', '2009-06-23', 1 UNION ALL
SELECT 'B', '2009-07-10', 1 UNION ALL
SELECT 'B', '2009-08-01', 0 UNION ALL
SELECT 'C', '2009-02-24', 1 UNION ALL
SELECT 'C', '2009-03-17', 1 UNION ALL
SELECT 'C', '2009-04-13', 1 UNION ALL
SELECT 'C', '2009-04-19', 1 UNION ALL
SELECT 'C', '2009-05-16', 1 UNION ALL
SELECT 'C', '2009-07-20', 1 UNION ALL
SELECT 'C', '2009-08-10', 1 UNION ALL
SELECT 'C', '2009-09-27', 1 UNION ALL
SELECT 'C', '2009-10-10', 1 UNION ALL
SELECT 'C', '2009-11-11', 1
December 13, 2009 at 9:51 am
SELECT CDE
FROM dbo.DOM_EVAL
GROUP BY CDE
HAVING COUNT(*) = SUM(CAST(E_Flg AS TINYINT))
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2009 at 10:34 am
Thanks Jeff!
December 13, 2009 at 5:42 pm
You bet, Wayne.
As a side bar... now you know why I don't care to store flags as bits. A single bit flag in a row still takes a whole byte with 7 unused bits. If you have to bit flags, there will be 6 unused bits but they still fit in a byte. Since it uses a whole byte anyway, I store single bit flags as a TinyInt so I don't have to do any conversions to add the "True" bits up.
In fact, even if I have more than one bit, I'll usually let each have their own TinyInt column just for the sake of such possible future convenience.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2009 at 8:59 pm
Jeff Moden (12/13/2009)
As a side bar... now you know why I don't care to store flags as bits. A single bit flag in a row still takes a whole byte with 7 unused bits. If you have to bit flags, there will be 6 unused bits but they still fit in a byte. Since it uses a whole byte anyway, I store single bit flags as a TinyInt so I don't have to do any conversions to add the "True" bits up.In fact, even if I have more than one bit, I'll usually let each have their own TinyInt column just for the sake of such possible future convenience.
The other side of the coin is this:
If the entity to be stored should only ever have values 0 or 1, why tempt fate by using a data type which allows 253 other values? Sure, you could add a CONSTRAINT, but it seems a bit of a stretch just to avoid the odd CAST or CONVERT. Adding up bit flags is not a natural operation - so I quite like having to do the extra work, just as a reminder. If it really becomes inconvenient, it is often possible to hide the conversions with a view. Finally, I'd just like to make the point that future columns of type BIT will effectively be for free since up to 8 can be stored in a single byte.
Some other possible solutions...
-- Solution 1
SELECT CDE
FROM dbo.DOM_EVAL
GROUP BY
CDE
HAVING COUNT(*) = COUNT(NULLIF(E_Flg, 0));
-- Solution 2
;WITH CTE
AS (
SELECT CDE, rnk = RANK() OVER (PARTITION BY CDE ORDER BY E_Flg)
FROM dbo.DOM_EVAL DE
)
SELECT CDE
FROM CTE
GROUP BY CDE
HAVING MAX(rnk) = 1;
-- Solution 3
SELECT CDE
FROM dbo.DOM_EVAL
EXCEPT
SELECT CDE
FROM dbo.DOM_EVAL
WHERE E_FLG = 0;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply