Here's another version.

Side note: instead of expanding it to 24hrs per day I just used the hours between noon and 10pm. If you need all 24hrs, you'd need to change...

Your reply doesn't really answer the question on how to actually calculate the working hours.

Your sample data do make room for some interpretation... Also, your desired result is an integer...

Please provide table definition, sample data and expected result in a ready to use format as described in the first link in my signature.

That would make it easier for us...

I would use a calendar table (holding the day values), expand it to cover 24hrs per day (or the possible working hours) and join that to the ShiftData table.

Something like...

Please provide table def and sample data that correspond to your expected result in a ready to use format as described in the first link in my signature.

Glad I could help! 😀

I'm not sure if it helps but you could try to enforce a specific collation within the CASE statement:

SELECT

CASE

...

Please provide some more information.

What would be great would be some sort of ready to use sample data as described in the first link in my signature.

In your case it...

You would need to use dynamic SQL for that:

Here's an example:

DECLARE

@list VARCHAR(30),

@SQLString NVARCHAR(200)

SET @list ='''Bothell'',''Portland'''

SET @SQLString = N'SELECT * FROM Person.Address WHERE city in('+@list+')'

EXEC sp_executesql @SQLString

I just have a headline for SSC jumping into my mind:

Having trouble with your database or your kids?

Have a look at SSC! We'll tune both! 😀 :hehe: 😀

Brave man. Do you want us to consider it some kind of self-flagellation? 😀

If there's any masochist involved you just called to continue :w00t:

Side note:

@jason: no matter how long your...

GregoryF (4/7/2010)

I think you have gone way beyond the scope of someone who asked what the difference between delete and truncate is 🙂 Don't confuse them while they are...

You might want to look into ROW_NUMBER together with CTEs. There are several solutions available on this site. Please search for "duplicate rows" in the scripts section.

Do you know the max number of cross joins ahead of time?

What I'd probably do in this case is to build a static table like the one I posted covering...