Trying to compare days with bit fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trying to compare days with bit fields

Author

Topic

kevinbednar
Starting Member

1 Post

Posted - 2013-09-19 : 20:56:30

Hey all, I have a problem I'm trying to figure out here. I am trying to figure out the availability of a contact by comparing their available flag to the current day of the week. IE, the contact has 7 BIT fields in the table,1 for each day of the week, being T or F depending on if they are available. I'm trying to figure out how to read the correct field based on the day of the week to see if their available (T) that day for a notification. Each field name in the table is as such: mon, tue, wed, etc. I can get the current DOW from sql and trim it to the same length and case of the field names to try and figure out which one I need to check whether that fields contents are true or not, but I'm stumped on where to go from here. I can basically get around sql server MOST of the time, but I am by no means a pro, but this one has me stumped. I'm sure I have to be missing something obvious here. Any help is greatly appreciated!

Kevin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-20 : 00:53:48

[code]SELECT CASE
WHEN BitColumn AND POWER(2, 7) > 0 THEN 'Monday'
WHEN BitColumn AND POWER(2, 6) > 0 THEN 'Tuesday'
WHEN BitColumn AND POWER(2, 5) > 0 THEN 'Wednesday'
WHEN BitColumn AND POWER(2, 4) > 0 THEN 'Thursday'
WHEN BitColumn AND POWER(2, 3) > 0 THEN 'Friday'
WHEN BitColumn AND POWER(2, 2) > 0 THEN 'Saturday'
WHEN BitColumn AND POWER(2, 1) > 0 THEN 'Sunday'
END[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-09-20 : 02:55:24

If your field stores in bit range 0 - 7:


select case when yourbitfield&power(2,datepart(dw,getdate())-1)>0
          then 'Available'
          else 'Unavailable'
       end as contact_availability
  from yourtable

If your field stores in bit range 1 - 8:


select case when yourbitfield&power(2,datepart(dw,getdate()))>0
          then 'Available'
          else 'Unavailable'
       end as contact_availability
  from yourtable

Also you must set language according to which bit represent which day.
Ex. if first bit represents monday, you can use: set language british
if first bit represents sunday, you can use: set language english

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-20 : 11:49:08

According to the OP "the contact has 7 BIT fields in the table,1 for each day of the week." So, doing bit-wise operations on a single column, doesn't sound like the right answer.

DECLARE @Foo TABLE (ID INT, Mon BIT, Tue BIT, Wed BIT, Thu BIT, Fri BIT, Sat BIT, Sun Bit)

INSERT @Foo
VALUES 
(1, 1, 0, 0, 0, 0, 0, 0),
(2, 0, 1, 0, 0, 0, 0, 0),
(3, 0, 0, 1, 0, 0, 0, 0),
(4, 0, 0, 0, 1, 0, 0, 0),
(5, 0, 0, 0, 0, 1, 0, 0),
(6, 0, 0, 0, 0, 0, 1, 0),
(7, 0, 0, 0, 0, 0, 0, 1)


SELECT	
	ID,
	CASE 
		WHEN Mon = 1 AND DATENAME(WEEKDAY, SYSDATETIME()) = 'Monday' THEN 1
		WHEN Tue = 1 AND DATENAME(WEEKDAY, SYSDATETIME()) = 'Tuesday' THEN 1
		WHEN Wed = 1 AND DATENAME(WEEKDAY, SYSDATETIME()) = 'Wednesday' THEN 1
		WHEN Thu = 1 AND DATENAME(WEEKDAY, SYSDATETIME()) = 'Thursday' THEN 1
		WHEN Fri = 1 AND DATENAME(WEEKDAY, SYSDATETIME()) = 'Friday' THEN 1
		WHEN Sat = 1 AND DATENAME(WEEKDAY, SYSDATETIME()) = 'Saturday' THEN 1
		WHEN Sun = 1 AND DATENAME(WEEKDAY, SYSDATETIME()) = 'Sunday' THEN 1
		ELSE 0
	END AS IsCurrentDay
FROM
	@Foo

If that doesn't help, please post your DDL, DML and expected output. Here are some links that can help you provide that sow e cna help you better:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 02:29:28

[code]
DECLARE @Foo TABLE (ID INT, Mon BIT, Tue BIT, Wed BIT, Thu BIT, Fri BIT, Sat BIT, Sun Bit)

INSERT @Foo
VALUES
(1, 1, 0, 0, 0, 0, 0, 0),
(2, 0, 1, 0, 0, 0, 0, 0),
(3, 0, 0, 1, 0, 0, 0, 0),
(4, 0, 0, 0, 1, 0, 0, 0),
(5, 0, 0, 0, 0, 1, 0, 0),
(6, 0, 0, 0, 0, 0, 1, 0),
(7, 0, 0, 0, 0, 0, 0, 1)

IF EXISTS (SELECT 1
FROM @foo
UNPIVOT (BitVal FOR dayval IN (Mon , Tue , Wed , Thu , Fri , Sat , Sun))u
WHERE BitVal = 1
AND dayval = LEFT(DATENAME(dw,GETDATE()),3)
)
PRINT 'Available for Notification'
ELSE
PRINT 'Not Available'
[/code]

You may replace the print statements with whatever you need to put for notification

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources