Weight (in the detail table) could be anything. Maybe an example will help.

Let's say you want to allocate a corporate Overhead charge to different departments based on their overall...

Hey Justin!

I think the following works:

;WITH cteClip AS (

SELECT MachineID

,CASE WHEN Tag = 'Active' THEN TimeStart

WHEN TimeStart < ISNULL((SELECT MAX(t2.TimeStop) FROM @TempSource t2

WHERE t1.MachineID = t2.MachineID and t2.Tag =...

Rats! I was looking at PATINDEX and STUFF to solve this but didn't make the leap from RegEx to using the LIKE patterning to identify the GUIDs.

Then I realized...

Post deleted as wrong info provided.

Maybe this article will help?

http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/

The Reg Ex you'll need to use is I think:

/^\{?[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}\}?$/i

I don't like to use the term "penny rounding" for...

Mr. ColdCoffee - I love the * 1.00 to eliminate the awkward CAST I used in my version!

Gotta remember little tricks like this.

As a newbie to the forum you should follow the advice in many of the other postings regarding posting of DDL and sample data. You'll find others are not...

Posting some DDL and sample data would be helpful, but if I understand right you want to create a set of prior year columns (not sure which is sales) based...

You've certainly provided extensive DDL here but without running all the code you've provided, I don't see an expected results set.

I can understand about the 12 months you want to...

Jeff,

That's a great article even if it is a bit "techno."

I've used the quirky update for other purposes although not against a prod table (just temp or table variables) and...

If I had a penny for every time a business user came up with an "unreasonable requirement" I wouldn't need to fudge round to be a millionare by now!

You do not have to be. Deliberately changing a number to another, incorrect value is fraud. Perhaps not legally in the sense that accountants can get away with it, but...

Thanks to all for your comments! To implement penny rounding, you simply need to change the zero (second argument of ROUND) of the "a" CTE to 2:

a AS (

...

Jeff,

That's a very interesting article and it has described what I could not articulate in my dislike for correlated subqueries. I intrinsically knew that there would be a performance...