To keep it a bit more simple, let's just stick with Graham

name      shift_type        sunday_shift_date    
Graham     Late ...

robert.gerald.taylor (11/5/2013)

---

Couldn't you do this in a calculated member and use the DateDiff() function to get the number of years. Then use IIF() to put in groups?

Just thinking out...

Thanks guys, I tried this previously but it wasn't working for me because the actual solution uses the IN statement for NAME as there's three values to be pulled through....

Er, I'll take that as a compliment.

I really have been out of touch for the last year 😀

lowell 2012-09-12

@67 posts, i didn't think this was homework for the OP, and i got all excited about making a recursive CTE work before someone else beat me to...

Ah, I'm sorry Lowell, my bad, I've been zoned out all...

Thanks for responding Lowell,

The only issue with this technique is that there are literally 10s of thousands of parts so I'm not sure if this technique would work; I wont...

Lynn Pettis 23/04/2012

Take a look at this and see if you couldn't incorporate this into your calendar table. Using a computed column should dynamically update the identifier column each day....

Hi ColdCoffee, does this help:

Date Fiscal_Year Identifier

30-Mar-11 11-12 ...

I don't know enough about the cube product they're using. If it was SSAS then I'm sure I could create running totals quite easily for both ProductID and then...

Hi Jeff,

I'll double check with them. I think the logic they had was that they wanted to slice/dice data, by productID, then by SalesmanID etc, not together.

However, so...