Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert "TinyInt" into Binary format and store the multiple bit values in columns Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2014 6:27 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 9:55 PM
Points: 30, Visits: 98
Hi team,

I have an requirement which I need expertise help on how to write the logic.

Our system records the appointment details in the back end ( Pattern Column) in "tinyint" format.

For eg - if the number in that column is 55, I need to write a view with the below columns and they need to populated with the converted binary values as below. As this indicate, the client have appointment on the day where the value is "1".

Sat Fri Thu Wed Tue Mon Sun
0 1 1 0 1 1 1

Thanks for the help in advance.

Regards,
Krishna.
Post #1557758
Posted Wednesday, April 2, 2014 6:48 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 3,342, Visits: 7,225
Do you need something like this?
WITH SampleData AS(
SELECT 55 Value UNION ALL
SELECT 63 UNION ALL
SELECT 26
)
SELECT Value,
(Value & 64) / 64 AS Sat,
(Value & 32) / 32 AS Fri,
(Value & 16) / 16 AS Thu,
(Value & 8) / 8 AS Wed,
(Value & 4) / 4 AS Tue,
(Value & 2) / 2 AS Mon,
(Value & 1) / 1 AS Sun
FROM SampleData




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1557765
Posted Wednesday, April 2, 2014 10:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 6:43 PM
Points: 36,752, Visits: 31,208
Just a small simplification of Luis' fine code...
WITH SampleData AS(
SELECT 55 Value UNION ALL
SELECT 63 UNION ALL
SELECT 26
)
SELECT Value,
SIGN(Value & 64) AS Sat,
SIGN(Value & 32) AS Fri,
SIGN(Value & 16) AS Thu,
SIGN(Value & 8) AS Wed,
SIGN(Value & 4) AS Tue,
SIGN(Value & 2) AS Mon,
SIGN(Value & 1) AS Sun
FROM SampleData



--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557805
Posted Thursday, April 3, 2014 9:47 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 3,342, Visits: 7,225
And if you need them as bit values:
WITH SampleData AS(
SELECT 55 Value UNION ALL
SELECT 63 UNION ALL
SELECT 26
)
SELECT Value,
CAST(Value & 64 AS bit) AS Sat,
CAST(Value & 32 AS bit) AS Fri,
CAST(Value & 16 AS bit) AS Thu,
CAST(Value & 8 AS bit) AS Wed,
CAST(Value & 4 AS bit) AS Tue,
CAST(Value & 2 AS bit) AS Mon,
CAST(Value & 1 AS bit) AS Sun
FROM SampleData




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1558064
Posted Thursday, April 3, 2014 4:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 9:55 PM
Points: 30, Visits: 98
Hi Team,

Thanks all for your replies. On the same topic, I did the following approach and it is throwing an error, if I include the statement which I have highlightes in italic and bold.

If I dont include that part of the statement, it is working fine And I include that the following are the error message

" 'iif' is not built in recognised fucntion"
" Incorrect syntax near "recurrence" , expecting conversation"
and all the select columns are coming as invalid columns.

I am using sql server 2008 r2.

Any help is most appreciated.

select
ID,Interval,Type,Start,
Case when Type=0 Then 'Daily ' + iif(Interval>1, 'every '+ Interval+' days','') End Recurrence,
CASE WHEN (Pattern % 2) >= 1 THEN 1 ELSE 0 END Sunday,
CASE WHEN (Pattern % 4) >= 2 THEN 1 ELSE 0 END Monday,
CASE WHEN (Pattern % 8) >= 4 THEN 1 ELSE 0 END Thuesday,
CASE WHEN (Pattern % 16) >= 8 THEN 1 ELSE 0 END Wednesday,
CASE WHEN (Pattern % 32) >= 16 THEN 1 ELSE 0 END Thursday,
CASE WHEN (Pattern % 64) >= 32 THEN 1 ELSE 0 END Friday,
CASE WHEN (Pattern % 128) >= 64 THEN 1 ELSE 0 END Saturday from sample table
Post #1558286
Posted Thursday, April 3, 2014 4:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 3,342, Visits: 7,225
You can't use IIF unless you use 2012. For 2008 and previous versions, you need to use a (nested) CASE.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1558293
Posted Thursday, April 3, 2014 4:42 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 9:55 PM
Points: 30, Visits: 98
Ahh great..thanks for the tip..

here is the original requirement in the VB script...

Dim Recurrence As string

Select Case RecurrenceType

Case Is = 0
If Interval > 1 Then
Recurrence = "Daily Every " & Interval & " Days"
Else
Recurrence = "Daily"
End if

Case is = 1
If Interval = 0 Then
Recurrence = "Weekly on "
Else
Recurrence = "Every " & Interval & " week on "
End if

And the following is the nested case statement, I have created based on your advise

as am new to case statements, could you please advise where am I making the mistake, as the sql server is throwing
"Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'Daily Every ' to data type tinyint."

Case when Type=0 Then 'Daily '
When Interval > 1 Then 'Daily Every ' + Interval + ' Days' END Recurrence,

Case when RecurrenceType=1 Then 'Weekly on '
When Interval = 0 Then 'Every ' + Interval + ' Weeks on ' END Recurrence,


Thanks for your time in advance.
Post #1558304
Posted Thursday, April 3, 2014 5:16 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:47 PM
Points: 3,342, Visits: 7,225
You need to cast Interval to a varchar value.


Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1558317
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse