Performing a bitwise AND operation in Sisense SQL



  • Avatar
    Oxana Noa Umansky

    Hi Michael,

    Thanks for the post! I'll be happy to better understand your use case. Would you share how you use this solution in your analysis? 



    Comment actions Permalink
  • Avatar
    Michael Cowley (Edited )

    Hi Oxana

    Sure! Here's a couple of examples from a custom fact table that I build by joining a number of different tables imported from SQL Server. 

    We are a taxi dispatch company, and we use the Conditions column in the Bookings table to hold 40 different flags to define the type of taxi and driver and service that a customer is requesting. This is both to simplify the database (to avoid 40 different bit columns or a lookup table) and to simplify the processing for the low-end hardware that was originally installed in the taxis (the bitwise comparisons are much easier to see if a vehicle matches multiple requirements).

    So one requirement for the fact table on Bookings is to show what type of vehicle the customer requested in a single text column:

    CASE WHEN b.Conditions/power(2,13) % 2 = 1 THEN 'Wheelchair'
    WHEN b.Conditions/power(2,14) % 2 = 1
         OR b.Conditions/power(2,15) % 2 = 1
         OR b.Conditions/power(2,16) % 2 = 1
         OR b.Conditions/power(2,17) % 2 = 1
         OR b.Conditions/power(2,18) % 2 = 1
         OR b.Conditions/power(2,19) % 2 = 1
         OR b.Conditions/power(2,22) % 2 = 1
         OR b.Conditions/power(2,23) % 2 = 1 THEN 'High Occupancy'
    WHEN b.Conditions/power(2,9)   % 2 = 1 THEN 'Business Class'
    WHEN b.Conditions/power(2,29) % 2 = 1 THEN 'London Cab'
    WHEN b.Conditions/power(2,10) % 2 = 1 THEN 'Station Wagon'
    WHEN b.Conditions/power(2,11) % 2 = 1 THEN 'Sedan'
    ELSE 'Any Vehicle' END AS [Vehicle Type Requested]

    We have separate flags for 6 seat, 7 seat, 8 seat etc vehicles so the High Occupancy type can be indicated by any of those.

    Another requirement is to exclude from the fact table any bookings with a particular flag that indicates a test or training booking:

    WHERE b.Conditions/power(2,20) % 2 <> 1 

    At some point I might build a table that has a descriptively named column for each bit position to replace the numbers in that SQL so it's a bit easier to maintain.

    Comment actions Permalink
  • Avatar
    Michael Cowley

    I should also add I've already posed a feature request here:

    Comment actions Permalink
  • Avatar
    Oxana Noa Umansky

    Thanks Michael! It's an interesting use case that I believe wasn't shared in the community before. I'm sure it will help someone in the future! 

    As for the feature request, our product team continually monitors the Feature Request forum and will be reviewing it soon.   

    Cheers, Oxana

    Comment actions Permalink

Please sign in to leave a comment.