Mon- Fri Bit fields Query

I am having trouble figuring out how to complete this any help is appreiciated.

I have Mon , Tues , Weds , Thurs , Fri as bit fields I need to write a query for a report to see if the value is true, and display M, T, W, Th, F in a column Days if they are scheduled that day

example:

Mon and Wed I got on the bus the checkboxes are set to True, I want one column in the report Days: to display M, W

Ray:

Is something like this sufficient?

set nocount on

declare @.monStr varchar (11) set @.monStr = 'M,'
declare @.tueStr varchar (11) set @.tueStr = 'T,'
declare @.wedStr varchar (11) set @.wedStr = 'W,'
declare @.thuStr varchar (11) set @.thuStr = 'Th,'
declare @.friStr varchar (11) set @.friStr = 'F,'

declare @.recurringEvent table
( eventName varchar (20) not null,
Mon bit,
Tues bit,
Wed bit,
Thur bit,
Fri bit
)

insert into @.recurringEvent values ('Ride Bus', 1,0,1,0,0)
insert into @.recurringEvent values ('Go to Classes', 1,0,1,1,0)
insert into @.recurringEvent values ('Sleep @. night', 1,1,1,1,1)
insert into @.recurringEvent values ('Feed Dog', 1,0,1,0,1)
insert into @.recurringEvent values ('Feed Gator', 0,0,0,0,0)

select eventName,
case when convert (tinyint, mon) + convert (tinyint, tues)
+ convert (tinyint, wed) + convert (tinyint, thur)
+ convert (tinyint, fri) = 0
then ''
else left (left (@.monStr, 2 * mon)
+ left (@.tueStr, 2 * tues)
+ left (@.wedStr, 2 * wed)
+ left (@.thuStr, 3 * thur)
+ left (@.friStr, 2 * fri),
datalength (left (@.monStr, 2 * mon)
+ left (@.tueStr, 2 * tues)
+ left (@.wedStr, 2 * wed)
+ left (@.thuStr, 3 * thur)
+ left (@.friStr, 2 * fri)) - 1
)
end as Days
from @.recurringEvent

-- -
-- S A M P L E O U T P U T
-- -

-- eventName Days
-- -- -
-- Ride Bus M,W
-- Go to Classes M,W,Th
-- Sleep @. night M,T,W,Th,F
-- Feed Dog M,W,F
-- Feed Gator

|||

I prefer the case construct

case when mon=0 and tues=0 and wed=0 and thur=0 and fri=0

over

case when convert (tinyint, mon) + convert (tinyint, tues)
+ convert (tinyint, wed) + convert (tinyint, thur)
+ convert (tinyint, fri) = 0

Sorry. I just don't really like the look of this particular query.

Dave

|||Yea I'm not able to get this to work I'm going to look into it more this has to be a common scenerio.|||

Mugambo's code will return NULL if one of the data fields is NULL for any day of the week. This code should always work because NULL's and 0's will be caught by the ELSE for each test and an empty string will be returned.

set nocount on

declare @.recurringEvent table
( eventName varchar (20) not null,
Mon bit,
Tues bit,
Wed bit,
Thur bit,
Fri bit
)

insert into @.recurringEvent values ('Ride Bus', 1,NULL,1,0,0)
insert into @.recurringEvent values ('Go to Classes', 1,0,1,1,0)
insert into @.recurringEvent values ('Sleep @. night', 1,1,1,1,1)
insert into @.recurringEvent values ('Feed Dog', 1,0,1,0,1)
insert into @.recurringEvent values ('Feed Gator', 0,0,0,0,0)

Select EventName,
Replace(RTrim(
Case When Mon = 1 Then 'M ' Else '' End
+ Case When Tues = 1 Then 'T ' Else '' End
+ Case When Wed = 1 Then 'W ' Else '' End
+ Case When Thur = 1 Then 'TH ' Else '' End
+ Case When Fri = 1 Then 'F ' Else '' End)
, ' ', ',')
From @.RecurringEvent

|||Ok I just kind of dont understand how to use the code?|||

Thanks for picking me up Matros; I knew I didn't like what I had but I was too busy to continue with it.

Dave

|||

Ray,

The code posted here uses a table variable (which you won't need) so that the code can be tested. Table variable exist in memory only for the duration of the query, so no table structure changes occur. This means you can copy/paste the code, as is, in to a query window so you can run it and, more importantly, learn from it.

In your query, you will need to add this part...

Replace(RTrim(
Case When Mon = 1 Then 'M ' Else '' End
+ Case When Tues = 1 Then 'T ' Else '' End
+ Case When Wed = 1 Then 'W ' Else '' End
+ Case When Thur = 1 Then 'TH ' Else '' End
+ Case When Fri = 1 Then 'F ' Else '' End)
, ' ', ',') As WeekdayUsage

as though it were another field being returned from the query. Of course, this assumes that the bit field you use to store Monday usage is named Mon, and Tuesday's field is named Tue, etc..

If you continue to have problems implementing this solution, perhaps it would be best to post the existing query so that we may assist further.

|||

I was playing around with the code, and I don't believe this is what I am trying to do let me explain a lil more.

I have a table that does a transportation schedule. It has Mon, Tues, Wed, Thurs, and Fri as bit data types so that I may have checkboxes on the front-end. I need to build a report in reporting services that will list the days attend like so:

Data Types:

Rec# int Not Null PK Indentity Seed

Name Varchar(25) Null

Pickup Varchar(25) Null

Time Smalldatetime Null

Mon Bit Null

Tues Bit Null

Wed Bit Null

Thurs Bit Null

Fri Bit Null

Report Ex:

Name: Pickup: Time: (Days Attend: "Virtual Column doesn't exist in database")

Bill Center City 9:00am (M,W "True Value in database is True or 1")

Robert West Ave. 9:30am (W,Th,F "True Value in database is True or 1")

Can I do a case statement for each, and cancatenate them to display together, also I need them all to display in the same column?

|||That is exactly what mastros' code does:

Replace(RTrim(
Case When Mon = 1 Then 'M ' Else '' End
+ Case When Tues = 1 Then 'T ' Else '' End
+ Case When Wed = 1 Then 'W ' Else '' End
+ Case When Thur = 1 Then 'TH ' Else '' End
+ Case When Fri = 1 Then 'F ' Else '' End)
, ' ', ',') As DaysAttend

The output of this will be a column called DaysAttend which has a value of "M,W".

|||

If I build a view for the report how would I use the code?

Select PickupTime, Vehicle#, Destination, Mon, Tues, Wed, Thurs, Fri

Replace(RTrim(

Case When Mon = 1 Then 'M ' Else '' End

+ Case When Tues = 1 Then 'T ' Else '' End

+ Case When Wed = 1 Then 'W ' Else '' End

+ Case When Thurs = 1 Then 'TH ' Else '' End

+ Case When Fri = 1 Then 'F ' Else '' End)

, ' ', ',') As DaysAttend

From Schedule

|||Sweet I got it works great thanks..... Alot everyone. I