That would have given it an outright depth of 10.

My example should technically have only a depth of 2.

Like this:

SELECT 
CASE WHEN 11 = 12 THEN
    ...

I'm aware of the error message but this isn't REALLY a case of nested case statements but one of a lot of conditions. I wonder what would happen if you...

You wouldn't get 12 columns.

Only 4 are selected:

   Item_ID
   Year_ID
   Month_ID
   Quantity

I'm not sure what the new Item_ID is supposed to represent...

At a casual glance, it seems like he is telling it to create 12 rows for every one row in the original table. The CASE statement would be telling it...

ANY thoughts? I didn't post this earlier because it isn't a fully formed idea but it could be there is some kind of implicit conversion being attempted behind the scenes...

I'll take a shot in the dark here and guess [UserGuid].

(Which field identifies the user? Can it be the UniqueIdentifier?

If you look at all the other fields you can see...

Before that I was thinking along these lines:

declare @startdate datetime, @enddate datetime
declare @dw_startdate int
select @startdate = getdate()
select @dw_startdate = ((datepart(dw, @startdate) + @@DATEFIRST -1)%7)+1
select @enddate = @startdate +
  ...

After you posted your solution (pretty slick, btw) I tried to figure out on my own how you did it and arrived at a slightly messier version:

Sunday= 1, DaysToWednesday= 3,...

If people get away from sulking over the whole NetFlix prize money thing, I'd be interested in hearing an answer to the original question. What kind of good offsite hosting...

Thanks.

I added a comma (and dollar sign. I know... I'm being provincial. ) remover to that function.

Getting rid of the unintuitiveness (is that a word?) was the whole point of...

YES!!!

This has got to be the most inefficient way of doing this EVER!!

(Because I could... for "sanity" I ignored the out of range...

Though that still doesn't anticipate the stupidity that comes up in real life. There could still be situations where the string contains multiple periods, out of place commas, wandering negative...

Look into the sysobjects (type='U'), syscolumns and systypes tables in BOL or run a few queries and you'll get the hang of it.

There's also: select * from Information_Schema.Columns

You can make a report manager by creating a set of tables with rows defining available reports, parameters (type, default value, allows null or not), permissions, etc... and another set...

Yes.

If you have that relationship (and enforce it) then that is what would happen. He was talking about the reverse, though, ensuring that you can't accidentally create a childless row...