Złączenia i podzapytania w SQL

Ćwiczę podzapytania i złączenia ale czegoś mi brakuje.

Przykład:
Podaj imiona i nazwiska pracowników, którzy przyjęli zamówienia o największych wartościach w kwartałach lat 1996 i 1997.

Napisałem to tak:

SELECT YEAR([Data Zamówienia]) [Rok], DATEPART(QUARTER, [Data Zamówienia]) [Kwartał], MAX(Wartość) [Wartość]
FROM 
	(
	SELECT z.IDzamówienia [ID], SUM(pz.CenaJednostkowa*pz.Ilość) [Wartość], p.Imię + p.Nazwisko [Pracownik], z.DataZamówienia [Data Zamówienia]
	FROM Zamówienia z 
		INNER JOIN PozycjeZamówienia pz
			ON pz.IDzamówienia = z.IDzamówienia
		INNER JOIN Pracownicy p 
			ON p.IDpracownika = z.IDpracownika
	GROUP BY z.IDzamówienia, p.Imię + p.Nazwisko, z.DataZamówienia) AS war
WHERE YEAR([Data Zamówienia]) BETWEEN '1996' AND '1997'
GROUP BY YEAR([Data Zamówienia]), DATEPART(QUARTER, [Data Zamówienia])

Zapytanie zwraca mi maksymalną wartość dla każdego kwartału dla roku 1996 i 1997, ale nie mam jak wyświetlić jeszcze pracownika. Mam kilka takich przykładów, i zawsze napotykam na identyczny problem. Nie mam "uchwytu" żeby dobrać się do tego czego trzeba.
Byłbym bardzo wdzięczny za wyjaśnienie, ew. rozwiązanie i wyjaśnienie. W każdym razie chciałbym to zrozumieć, bo obstawiam że nie jest to skomplikowane.

Załączam schemat bazy:
https://i.paste.pics/1eebb3e9254fcab9ae92cd381a72a18e.png

Można tak:

1. Robisz sobie agregat:
   id_pracownika, rok||kwartal, wartosc_zamowienia
   pytanie bonusowe: Jak rabat ma się do wartości zamówienia w kontekście ceny jednostkowej/ilości produktu?

2. Z agregatu wybierasz te id_pracownika, które mają max. w określonym kwartale.

3. Robisz joina między pracownicy i step#2 (+ jakiegoś distincta, jeśli pracownik został top-pracownikiem Q1,Q2,Q3,Q4, ...)

Można też funkcjami analitycznymi kombinować, ale ćwiczysz coś innego.

Udało mi się wpaść na rozwiązanie jeszcze przed sprawdzaniem odpowiedzi. Ale widzę że tok rozumowania był podobny. Po prostu mogę użyć tego samego podzapytania i złączyć je na wartościach. Tyle że wydaje mi się, że rozmiar tego zapytania jest większy niż mógłby być.

SELECT Q2.Pracownik, Q1.Wartość, Q1.Kwartał, Q2.[Data Zamówienia]
FROM (/*zamówienia o maksymalnych wartościach w kwartałach roku 1996 i 1997*/
	SELECT YEAR([Data Zamówienia]) [Rok], DATEPART(QUARTER, [Data Zamówienia]) [Kwartał], MAX(Wartość) [Wartość]
	FROM 
		(/*wartości każdego zamówienia*/
		SELECT z.IDzamówienia [ID], SUM(pz.CenaJednostkowa*pz.Ilość) [Wartość], p.Imię + p.Nazwisko [Pracownik], z.DataZamówienia [Data Zamówienia]
		FROM Zamówienia z 
			INNER JOIN PozycjeZamówienia pz
				ON pz.IDzamówienia = z.IDzamówienia
			INNER JOIN Pracownicy p 
				ON p.IDpracownika = z.IDpracownika
		GROUP BY z.IDzamówienia, p.Imię + p.Nazwisko, z.DataZamówienia) AS war
	WHERE YEAR([Data Zamówienia]) BETWEEN '1996' AND '1997'
	GROUP BY YEAR([Data Zamówienia]), DATEPART(QUARTER, [Data Zamówienia])) AS Q1
		INNER JOIN (/*podzapytanie war*/
					SELECT z.IDzamówienia [ID], SUM(pz.CenaJednostkowa*pz.Ilość) [Wartość], p.Imię + p.Nazwisko [Pracownik], z.DataZamówienia [Data Zamówienia]
					FROM Zamówienia z 
						INNER JOIN PozycjeZamówienia pz
							ON pz.IDzamówienia = z.IDzamówienia
						INNER JOIN Pracownicy p 
							ON p.IDpracownika = z.IDpracownika
					GROUP BY z.IDzamówienia, p.Imię + p.Nazwisko, z.DataZamówienia) AS Q2 ON Q2.Wartość = Q1.Wartość;

Tzn. podzapytanie war jest w Q1, po czym łączę je z Q1 jako Q2. Nie można nadać temu podzapytaniu jakiegoś aliasu, żeby skrócić zapis? Bo poza Q1 war jest niewidoczny.

Można użyć tzw. faktoringu, tzn. skorzystać z klauzuli WITH i budować zapytanie w stylu:

WITH agregat AS ( /* Twoje  zapytanie */ )
select a.*,b.* from agregat a inner join tabela b on ...

Sprawdzałem wiele możliwości i zawsze daje mi "Incorrect syntax near )" kiedy próbuję użyć WITH. Mam identycznie jak tutaj podałeś.

Niepotrzebnie masz tyle tych podzapytań:

 with grpQ as (
SELECT 
	 YEAR([Data Zamówienia]) rok
	 ,DATEPART(QUARTER, [Data Zamówienia]) kw
	 p.Imię + p.Nazwisko [Pracownik]
	 SUM(pz.CenaJednostkowa*pz.Ilość) [Wartość]
FROM 
	Zamówienia z 
    INNER JOIN PozycjeZamówienia pz ON pz.IDzamówienia = z.IDzamówienia
    INNER JOIN Pracownicy p ON p.IDpracownika = z.IDpracownika
WHERE 
	YEAR([Data Zamówienia]) BETWEEN '1996' AND '1997'
GROUP BY 
	 YEAR([Data Zamówienia])
	 ,DATEPART(QUARTER, [Data Zamówienia])
	 p.Imię + p.Nazwisko),
rpt as (
select 
	*
	,rank() over (partition by  rok,kw order by [wartość] desc) r 
from 
	qrpQ)

select * from rpt where r=1

No właśnie jak wyżej napisałem u mnie nie działa WITH. Podejrzewałbym zbyt stare Management Studio ale jest to wersja najnowsza.

Jaka wersja SQL Servera?

Zrób sobie jakiś prosty test na tego WITHa. Może problem jest gdzie indziej :-)

WITH foo(moj_atrybut) as (select 1)
select f.* from foo f;

Na PC działa, na laptopie nie. Na obu ta sama wersja. Ale nawet jak użyję WITH to nie pozwala mi go użyć w podzapytaniu.

pokaż zapytanie które puszczasz. with musi być pierwsze

Nie wiem co jest ze mną nie tak ale ruszyło.

WITH Q1 AS (
SELECT k.NazwaFirmy [Klient]
FROM Klienci k
)

SELECT *
FROM (
SELECT *
FROM Q1) AS Q12

Tyle że jeżeli chciałbym zadeklarować więcej kwerend (być może nie ma to tutaj sensu ale chodzi o przykład), np:

WITH Q1 AS (
SELECT k.NazwaFirmy [Klient]
FROM Klienci k
)
WITH Q2 AS (
SELECT d.NazwaFimy [Dostawca]
FROM Dostawcy d
)
SELECT *
FROM (
SELECT *
FROM Q1,Q2) AS Q12

Pojawia się już seria błędów:

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'WITH'.
Msg 319, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Sprawdź sobie składnię: https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15

W skrócie:

WITH 
Q1 AS ( /* Zapytanie */ ),
Q2 AS ( /* Inne zapytanie*/ )
SELECT a.*, b.* from Q1 a, Q2 b;

Dokładnie ten sam błąd. Nawet z przecinkiem (którego wcześniej nie było).

WITH Q1 AS (SELECT k.NazwaFirmy [Klient] FROM Klienci k),
WITH Q2 AS (SELECT d.NazwaFirmy [Dostawca] FROM Dostawcy d)

SELECT Q12.*
FROM (
SELECT a.*, b.*
FROM Q1 a,Q2 b) AS Q12

Wystarczy że użyję WITH drugi raz i wszystko się sypie.

Edit

Dobra, widzę problem. Musi być jeden WITH na całą serię kwerend. Nawet zagnieżdżanie działa bez problemów. To by chyba było na tyle. Dzięki za pomoc.