February 5, 2009 at 3:11 am
I have a table with 10 bit columns from a survey.
A maximum of 3 answers are allowed, so a max of 3 columns set to 1
I can calculate the sum, which I put into a column row_sum, and determine which rows are valid, but here comes the hard part.
For rows with a row_sum > 3 I need to clear the bits on random columns until the row_sum = 3
The process for each row is something like:
while row_sum > 3
find random column with bit set
clear bit
repeat count of row_sum
end
I've tried using a while statement but I've gone beyond my skill level.
Any help would be much appreciated.
Cheers,
Brian
February 5, 2009 at 4:40 am
Have you got the table structure and maybe some test data that we could play with ?
February 5, 2009 at 7:02 am
This may not be particularly random.
-- *** Test Data ***
DECLARE @t TABLE
(
    TId int IDENTITY NOT NULL PRIMARY KEY
    ,bit1 bit NOT NULL
    ,bit2 bit NOT NULL
    ,bit3 bit NOT NULL
    ,bit4 bit NOT NULL
    ,bit5 bit NOT NULL
    ,bit6 bit NOT NULL
    ,bit7 bit NOT NULL
    ,bit8 bit NOT NULL
    ,bit9 bit NOT NULL
    ,bit10 bit NOT NULL
)
INSERT INTO @t (bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8, bit9 ,bit10)
SELECT 1,0,1,0,1,0,0,0,0,0 UNION ALL
SELECT 1,0,1,0,1,0,1,0,0,0 UNION ALL
SELECT 0,0,1,0,0,0,0,0,0,0 UNION ALL
SELECT 1,0,1,1,1,0,0,1,0,0 UNION ALL
SELECT 1,0,1,0,1,0,0,0,1,1 UNION ALL
SELECT 1,1,1,1,1,1,1,1,1,1
-- *** End Test Data ***
SELECT * FROM @t
-- Generate 7 unique random numbers (10 - 3)
DECLARE @r TABLE
(
    lev tinyint NOT NULL
    ,r tinyint NOT NULL
)
DECLARE @i int
SET @i = 1
WHILE 1=1
BEGIN
    INSERT INTO @r
    SELECT @i, r
    FROM
    (
        SELECT CAST(RAND() * 10 AS tinyint) + 1 AS r
    ) D
    WHERE NOT EXISTS
    (
        SELECT NULL
        FROM @r R
        WHERE R.r = D.r
    )
    
    IF @@ROWCOUNT = 1
        SET @i = @i + 1
    IF @i > 7
        BREAK
END
-- SELECT * FROM @r
-- recursively reduce bits and update
;WITH BitUpdate (TId, bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8, bit9 ,bit10, lev)
AS
(
    SELECT T.TId
        ,bit1 = CASE WHEN R.r = 1 THEN 0 ELSE T.bit1 END
        ,bit2 = CASE WHEN R.r = 2 THEN 0 ELSE T.bit2 END
        ,bit3 = CASE WHEN R.r = 3 THEN 0 ELSE T.bit3 END
        ,bit4 = CASE WHEN R.r = 4 THEN 0 ELSE T.bit4 END
        ,bit5 = CASE WHEN R.r = 5 THEN 0 ELSE T.bit5 END
        ,bit6 = CASE WHEN R.r = 6 THEN 0 ELSE T.bit6 END
        ,bit7 = CASE WHEN R.r = 7 THEN 0 ELSE T.bit7 END
        ,bit8 = CASE WHEN R.r = 8 THEN 0 ELSE T.bit8 END
        ,bit9 = CASE WHEN R.r = 9 THEN 0 ELSE T.bit9 END
        ,bit10 = CASE WHEN R.r = 10 THEN 0 ELSE T.bit10 END
        ,1
    FROM @t T
        JOIN @r R
            ON R.lev = 1
    WHERE CAST(Bit1 AS tinyint) + bit2 + bit3 + bit4 + bit5
            + bit6 + bit7 + bit8 + bit9 + bit10 > 3
        
    UNION ALL
    SELECT T.TId
        ,bit1 = CASE WHEN R.r = 1 THEN 0 ELSE T.bit1 END
        ,bit2 = CASE WHEN R.r = 2 THEN 0 ELSE T.bit2 END
        ,bit3 = CASE WHEN R.r = 3 THEN 0 ELSE T.bit3 END
        ,bit4 = CASE WHEN R.r = 4 THEN 0 ELSE T.bit4 END
        ,bit5 = CASE WHEN R.r = 5 THEN 0 ELSE T.bit5 END
        ,bit6 = CASE WHEN R.r = 6 THEN 0 ELSE T.bit6 END
        ,bit7 = CASE WHEN R.r = 7 THEN 0 ELSE T.bit7 END
        ,bit8 = CASE WHEN R.r = 8 THEN 0 ELSE T.bit8 END
        ,bit9 = CASE WHEN R.r = 9 THEN 0 ELSE T.bit9 END
        ,bit10 = CASE WHEN R.r = 10 THEN 0 ELSE T.bit10 END
        ,T.Lev + 1
    FROM BitUpdate T
        JOIN @r R
            ON R.lev = T.Lev + 1
    WHERE CAST(Bit1 AS tinyint) + bit2 + bit3 + bit4 + bit5
            + bit6 + bit7 + bit8 + bit9 + bit10 > 3
            
)
UPDATE T
SET bit1 = B.bit1
    ,bit2 = B.bit2
    ,bit3 = B.bit3
    ,bit4 = B.bit4
    ,bit5 = B.bit5
    ,bit6 = B.bit6
    ,bit7 = B.bit7
    ,bit8 = B.bit8
    ,bit9 = B.bit9
    ,bit10 = B.bit10
FROM @t T
    JOIN BitUpdate B
        ON T.TId = B.TId
WHERE CAST(B.Bit1 AS tinyint) + B.bit2 + B.bit3 + B.bit4 + B.bit5
        + B.bit6 + B.bit7 + B.bit8 + B.bit9 + B.bit10 = 3
SELECT * FROM @t
February 5, 2009 at 8:39 am
This may be more random. It may be better to do this in the middle tier or with a cursor.
-- *** Test Data ***
DECLARE @t TABLE
(
    TId int IDENTITY NOT NULL PRIMARY KEY
    ,bit1 bit NOT NULL
    ,bit2 bit NOT NULL
    ,bit3 bit NOT NULL
    ,bit4 bit NOT NULL
    ,bit5 bit NOT NULL
    ,bit6 bit NOT NULL
    ,bit7 bit NOT NULL
    ,bit8 bit NOT NULL
    ,bit9 bit NOT NULL
    ,bit10 bit NOT NULL
)
INSERT INTO @t (bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8, bit9 ,bit10)
SELECT 1,0,1,0,1,0,0,0,0,0 UNION ALL
SELECT 1,0,1,0,1,0,1,0,0,0 UNION ALL
SELECT 0,0,1,0,0,0,0,0,0,0 UNION ALL
SELECT 1,0,1,1,1,0,0,1,0,0 UNION ALL
SELECT 1,0,1,0,1,0,0,0,1,1 UNION ALL
SELECT 1,1,1,1,1,1,1,1,1,1
-- *** End Test Data ***
SELECT * FROM @t
-- recursively reduce bits and update
;WITH BitUpdate (TId, bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8, bit9 ,bit10, lev)
AS
(
    SELECT
        TId
        ,bit1 = CASE WHEN Random = 1 THEN 0 ELSE bit1 END
        ,bit2 = CASE WHEN Random = 2 THEN 0 ELSE bit2 END
        ,bit3 = CASE WHEN Random = 3 THEN 0 ELSE bit3 END
        ,bit4 = CASE WHEN Random = 4 THEN 0 ELSE bit4 END
        ,bit5 = CASE WHEN Random = 5 THEN 0 ELSE bit5 END
        ,bit6 = CASE WHEN Random = 6 THEN 0 ELSE bit6 END
        ,bit7 = CASE WHEN Random = 7 THEN 0 ELSE bit7 END
        ,bit8 = CASE WHEN Random = 8 THEN 0 ELSE bit8 END
        ,bit9 = CASE WHEN Random = 9 THEN 0 ELSE bit9 END
        ,bit10 = CASE WHEN Random = 10 THEN 0 ELSE bit10 END
        ,1
    FROM
    (
        SELECT TId, bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8, bit9 ,bit10
            ,cast(substring(RID, patindex('%[0-9]%', RID), 1) as tinyint) + 1 AS Random
        FROM
        (
            SELECT TId, bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8, bit9 ,bit10
                ,CAST(NEWID() AS char(36)) AS RID
            FROM @t
            WHERE CAST(Bit1 AS tinyint) + bit2 + bit3 + bit4 + bit5
                    + bit6 + bit7 + bit8 + bit9 + bit10 > 3
        ) D1
    ) D
    UNION ALL
    SELECT
        TId
        ,bit1 = CASE WHEN Random = 1 THEN 0 ELSE bit1 END
        ,bit2 = CASE WHEN Random = 2 THEN 0 ELSE bit2 END
        ,bit3 = CASE WHEN Random = 3 THEN 0 ELSE bit3 END
        ,bit4 = CASE WHEN Random = 4 THEN 0 ELSE bit4 END
        ,bit5 = CASE WHEN Random = 5 THEN 0 ELSE bit5 END
        ,bit6 = CASE WHEN Random = 6 THEN 0 ELSE bit6 END
        ,bit7 = CASE WHEN Random = 7 THEN 0 ELSE bit7 END
        ,bit8 = CASE WHEN Random = 8 THEN 0 ELSE bit8 END
        ,bit9 = CASE WHEN Random = 9 THEN 0 ELSE bit9 END
        ,bit10 = CASE WHEN Random = 10 THEN 0 ELSE bit10 END
        ,lev + 1
    FROM
    (
        SELECT TId, bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8, bit9 ,bit10, lev
            ,cast(substring(RID, patindex('%[0-9]%', RID), 1) as tinyint) + 1 AS Random
        FROM
        (
            SELECT TId, bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8, bit9 ,bit10, lev
                ,CAST(NEWID() AS char(36)) AS RID
            FROM BitUpdate
            WHERE CAST(Bit1 AS tinyint) + bit2 + bit3 + bit4 + bit5
                    + bit6 + bit7 + bit8 + bit9 + bit10 > 3
        ) D1
    ) D
)
UPDATE T
SET bit1 = B.bit1
    ,bit2 = B.bit2
    ,bit3 = B.bit3
    ,bit4 = B.bit4
    ,bit5 = B.bit5
    ,bit6 = B.bit6
    ,bit7 = B.bit7
    ,bit8 = B.bit8
    ,bit9 = B.bit9
    ,bit10 = B.bit10
FROM @t T
    JOIN BitUpdate B
        ON T.TId = B.TId
WHERE CAST(B.Bit1 AS tinyint) + B.bit2 + B.bit3 + B.bit4 + B.bit5
        + B.bit6 + B.bit7 + B.bit8 + B.bit9 + B.bit10 = 3
SELECT * FROM @t
February 5, 2009 at 1:39 pm
Wow, that definitely went past my skill level.
I'll try it out today and get back to you.
Thanks again.
February 5, 2009 at 2:39 pm
I'm getting the following error:
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'WITH'.
I've tried to fix the syntax with no luck. Any ideas?
Cheers, Brian
February 6, 2009 at 2:53 am
Make sure you have the semi-colon in front of the WITH.
Also, make sure you are running on at least a SQL2005 machine with a DB in 2005 compatibility mode.
February 6, 2009 at 3:52 am
Completely random
DECLARE @Sample TABLE
(
ID INT IDENTITY (1, 1) PRIMARY KEY CLUSTERED,
bit01 BIT NOT NULL,
bit02 BIT NOT NULL,
bit03 BIT NOT NULL,
bit04 BIT NOT NULL,
bit05 BIT NOT NULL,
bit06 BIT NOT NULL,
bit07 BIT NOT NULL,
bit08 BIT NOT NULL,
bit09 BIT NOT NULL,
bit10 BIT NOT NULL
)
INSERT@Sample
SELECT1, 0, 1, 0, 1, 0, 0, 0, 0, 0 UNION ALL
SELECT1, 0, 1, 0, 1, 0, 1, 0, 0, 0 UNION ALL
SELECT0, 0, 1, 0, 0, 0, 0, 0, 0, 0 UNION ALL
SELECT1, 0, 1, 1, 1, 0, 0, 1, 0, 0 UNION ALL
SELECT1, 0, 1, 0, 1, 0, 0, 0, 1, 1 UNION ALL
SELECT1, 1, 1, 1, 1, 1, 1, 1, 1, 1
SELECT*
FROM@Sample
UPDATEs
SETs.bit01 = y.bit01,
s.bit02 = y.bit02,
s.bit03 = y.bit03,
s.bit04 = y.bit04,
s.bit05 = y.bit05,
s.bit06 = y.bit06,
s.bit07 = y.bit07,
s.bit08 = y.bit08,
s.bit09 = y.bit09,
s.bit10 = y.bit10
FROM@Sample AS s
INNER JOIN(
SELECTp.ID,
p.bit01,
p.bit02,
p.bit03,
p.bit04,
p.bit05,
p.bit06,
p.bit07,
p.bit08,
p.bit09,
p.bit10
FROM(
SELECTID,
CASE
WHEN recID > 3 THEN 0
ELSE theValue
END AS theValue,
theCol
FROM(
SELECTu.ID,
SIGN(u.theValue) AS theValue,
ROW_NUMBER() OVER (PARTITION BY u.ID ORDER BY u.theValue DESC, NEWID()) AS recID,
theCol
FROM@Sample AS s
UNPIVOT(
theValue
FOR theCol IN (s.bit01, s.bit02, s.bit03, s.bit04, s.bit05,
s.bit06, s.bit07, s.bit08, s.bit09, s.bit10)
) AS u
) AS d
) AS w
PIVOT(
MAX(w.theValue)
FOR w.theCol IN ([bit01], [bit02], [bit03], [bit04], [bit05],
[bit06], [bit07], [bit08], [bit09], [bit10])
) AS p
) AS y ON y.ID = s.ID
SELECT*
FROM@Sample
N 56°04'39.16"
E 12°55'05.25"
February 6, 2009 at 4:32 am
Very, very nice!
I think the SELECT p.ID, p.theValue, p.theCol should be either:
SELECT p.*
or
SELECT p.ID, p.bit01, p.bit02 ...
February 6, 2009 at 7:24 am
Thanks!
Corrected. I orignally posted p.* but changed it becuase I don't like * in production code.
Obvously I wasn't focused enough.
N 56°04'39.16"
E 12°55'05.25"
February 6, 2009 at 3:19 pm
You guys are good!
I'll try that today, and I'll check that I'm in 2005 compatibility mode.
Thanks again.
February 7, 2009 at 11:17 am
Ok... now... my question would be, why do you want to destroy data that you have by overwriting it? The answers to those questions, whatever they are, may prove useful for something else in the future. And why is it important that you only need 3 answers when there are many more? What are the business reasons behind this?
--Jeff Moden
Change is inevitable... Change for the better is not.
February 7, 2009 at 5:41 pm
The table is filled with survey questions. This particular group of questions are worded in the form:
Please select the 3 most relevant answers:
The client only wants 3 answers, and their business rule is to randomly select 3 if more are selected.
As to the wisdom of throwing away data, these calculations are performed on a temp table used for calculations, the original data is stored in another table.
Cheers,
Brian
February 7, 2009 at 6:08 pm
Then, it would seem to me that the data is being collected incorrectly. The GUI should prevent more than 3 answers from being entered. Otherwise, you're not following the client's business rules precisely. When it comes to this type of data collection, allowing more than the required number of answer and randomly reporting on only 3 of the given answers will skew polls possibly introducing a huge margin of error.
I recommend that you don't do it the way you proposed. I recommend you fix the GUI to enforce the limit of 3.
And, WHEW!... Glad to see you're doing this in a temp table so as to preserve the original data. Randomly picking 3 from selected items screws up a whole bunch of things (client will be pissed). But, if your company let's honesty prevail, the client may still be able to use the data if it already exists. If it doesn't, fix the GUI.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 8, 2009 at 12:24 am
The online survey GUI enforces the correct business rules, but they also use paper survey forms and it is these forms that have the extra answers.
There are other corrections that need to be made as it seems impossible to stop people adding bad data. The client knows this and is doing there best to get the most out of the data.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply