I'm putting this example of an alternative in for reference by future readers. It uses a WeekNo instead of your week ending date, but the logic is...

I can't either. Glad you finally got it to work.

While we are still old school then, I think Cross Applying a VALUES clause is a great trick. Very clean and powerful alternative to UNPIVOT.

😛

I don't get that error message with the sample data you supplied. If your test data has more than one date within the range, you have to...

https://technet.microsoft.com/en-us/library/ms177443(v=sql.105).aspx

Are you trying to sum up the amount of all rows between a range? If so, you need to use the SUM() function in your subquery.

Any chance of changing the input to make that ["fishing"]?

The error message is that the input is not properly formatted. Making it a one-element array fixes the problem.

Here's how the line in question should read.

WHEN CAST(GETDATE() AS DATE) BETWEEN CAST([StartDate] AS DATE) AND CAST([EndDate] AS DATE)

The key is that [StartDate] comes before [EndDate]. ...

Sorry for the drama guys. Totally my fault.

I vaguely remember a similar debate about this many years ago. The purpose of these forums are to...

I don't know it for...

I just realized you posted 3 questions in 30 minutes. These aren't interview questions are they?

Answer withdrawn because I prefer not to answer interview questions.

Could you please at least give an example of what the result should be?

Don't explain it in words, draw us a picture.

Create a result table and populate it with...

Answer withdrawn because I prefer not to help people with interview questions.