Nice article Greg.

I've had to use CASE expressions quite often recently in a data migration from an old ERP system.

You not only just taught me a few new tricks...

ahh....worked well.

Thanks Luis.

Keep on Keeping on and enjoy your weekend!

J Livingston's query runs faster. Both solutions work well.

Sorry for the late reply but it really helped me in crunch.

OK, Your right I didn't have

left join SumSalesQtyCommit as sot

On sot.[No.] = ile.[Item No.] and sot.[Location Code] = ile.[Location Code]

I had

left join SumSalesQtyCommit as sot

On sot.[No.] = ile.[Item No.]...

Thanks for the suggestion Mickey but even with the test data I only get two results back.

Ok,

Here is some more sample data.

If you run the Inner query for #ItemLedgerEntry you will see 8 results appear.

For the Locations that have no records for...

Robert,

This is very close but when I run it in my production system I only get back 2 records. The results I get back are the results where there...

Thanks Eirikur,

When I run the query in the actual database I get back over 7,000 records for 1 item. I should only get 8 results back for this item....

this worked well. Thanks for the assistance!

here is the final script. I'll be adding more onto it but this was a good start.

WITH SumInvoiceQty AS

(

SELECT [Item No_],...

Sean,

That's money!! I'll run it on my actual table in the morning. This way isn't that expensive either when compared to the windowing function. Thanks for your input.

Keith...

Thanks all. Your SQL skills humble me all the time. Amazing detail as always. I am so thankful to be part of such a community.

Sure, so I'll explain what I found in my data analysis.

The original CTE you wrote would have set the flag for the Blocked and DoNotUse columns. I originally did...

Sean,

I think this will work. I just changed the sort order of Historical in the ranking function.

with NumberedSet as

(

SELECT ItemNo, Blocked, DoNotUse, Historical, rn = ROW_NUMBER() OVER (PARTITION...

Sean,

thanks for your post. I agree with the usage of the CTE. I'll definitely use that method. After doing some data analysis on your script and some changes on...