I believe that this does the same thing, but without the CTE. It was impossible to do a performance comparison on such small data.

SELECT i.rowid, i.CustomerID, i.InvoiceNumber, i.InvoiceTotal, i.InvoiceAge,...

Also, my approach can easily be used with derived tables, again being consistent. You're approach could quickly become cumbersome with derived tables.

Drew

david.wright-948385 (9/8/2016)

---

That's the point I'm making - it's consistent, but no...

The Dixie Flatline (9/8/2016)

---

There is a potential problem right up at the front. The codes starts with a "Date_Dataset" cte that does a SELECT DISTINCT to produce a...

What are your expected results? It's certainly not clear from your description.

Drew

Are you by any chance looking for this?

SELECT TOP(5) * FROM TopSellerInstrumentsLastWeek i

FULL OUTER JOIN TopSellerScoringLastWeek sc

ON i.pf_id = sc.pf_id

FULL OUTER JOIN TopSellerStyleLastWeek st

ON i.pf_id = st.pf_id

OR sc.pf_id = st.pf_id

ORDER...

Luis Cazares (9/7/2016)

---

colin.dunn (9/7/2016)

---

I usually have my ON clauses on the same line as my JOIN, but...

20,000+ rows does not constitute SAMPLE data. If you need more than 100 rows to illustrate your problem, then it's too complex for a free forum. Why...

You need to set the user's default schema.

Drew

There are several issues here.

1) Your data is not in a format that people can cut and paste it into SSMS to get started. Follow the instructions in...

The Dixie Flatline (9/5/2016)

---

Just a guess here but have you tried adding this to your OVER clause?

SUM([CY_Quantity]) OVER (PARTITION BY [Customer], [Product], [Year]

...

If you're not sure, instead of using an "OR", add another CASE. This will both make it easier to understand your logic and prevent problems with not specifying the...

Lynn Pettis (9/1/2016)

---

ScottPletcher (9/1/2016)

---

Luis Cazares (9/1/2016)

---

Second, cursors can be local or global and...

First, the mantra is "It's a bad idea to declare a cursor. Period. End of sentence." 😀

T-SQL is not C#. It's certainly easy enough to test.

DECLARE @i INT =...

One thing I noticed is that you spread out Aj_total into different balances based on Age, and then the next thing you do is a series of queries to collapse...