Luis Cazares (2/25/2016)

---

Dodgier according to who?

If your problem is that you have too many columns in the GROUP BY clause, you could use a different approach.

I used a CTE because...

tindog (2/25/2016)

---

What do you mean by 'looks dodgier'? Does it not return the correct data?

What's the issue with the first query in your recent post? Also, the second query can't...

tindog (2/25/2016)

---

After adding your sample data into some temporary tables, this returned the same results as above:

SELECTp.PayID,

e.Name,

c.Name,

SUM(p.Amt1) SumAmt1,

SUM(p.Amt2) SumAmt2

FROM#UserPayments p

INNER JOIN#Employees e ON p.EmpID = e.EmpID

INNER JOIN#Companies c ON...

I'll try to come up with a solution based on this approach but I find that the simple GROUP BY query would do, what I'll work on the frontend is...

Thanks for your replies... I'm thinking, its also sort of like the same, isnt it? I mean, you'll need to repeat each year as a new line... But next year...

Awesome... Solution corrected and thanks for the explanations!

I got one more doubt though :hehe: What would you use on 2005, just out of curiosity, would the final query change that...

Agghh!! Close but not close enough...

I got it now, final SELECT goes like:

SELECT

Monday,

YEAR(Monday) AS tty,

ROW_NUMBER() OVER(PARTITION BY YEAR(Monday) ORDER BY YEAR(Monday))

FROM CTE_Dates

Ah! This one worked quite prefectly... What would be the best way of repeating this part 9 times...

Luis Cazares (9/11/2015)

---

xynergy (9/11/2015)

---

Dude, I just went from 4 minutes to a few seconds on that task!

Its pure awesomeness... Thanks both for your help... I'll carry on working on my...

Dude, I just went from 4 minutes to a few seconds on that task!

Its pure awesomeness... Thanks both for your help... I'll carry on working on my frontend...

Cheers! :hehe: