;WITH Query1
AS
(
	SELECT g.GEOTAXNR, g.VGONUM, g.VGOWOZNUM, g.CBRWYKKDE, g.CBRBRTKDE, g.ADRSTTNAM, g.ADRHUINUM, g.ADRHUILET, g.ADRHUITVG, g.ADRHUIAND, g.VGSOBJSRT, 
	g.VGSOMS, g.VGSGBRKDE, g.VGOIDT, g.VGOADTWOZ, g.VDTINDRCN, g.ADRINDRCN
	FROM fact_BelastingObjecten AS g
	WHERE (g.VDTINDRCN = 'X') AND (g.ADRINDRCN = 'X') AND (g.VGOIDT < '20090102') AND (g.VGOADTWOZ = '00000000' OR
	g.VGOADTWOZ > '20090101')
)
,Query2
AS
(
	SELECT e.GEOTAXNR, e.CBBVGONUM, e.CBBVGOWOZNUM, e.VTGTAXDAT, e.VTGTAXNAM, e.VTGRDN, e.VTGRLT, e.VTGVSTWRD, e.VTGBSK, e.VTGOZBWRD, 
	e.VTGHMSOZB, e.CBBVSLKDE
	FROM fact_BelastingTaxGegBeschikk AS e INNER JOIN
	(SELECT GEOTAXNR, MAX(VTGTAXDAT) AS TAXDATMAX
	FROM fact_BelastingTaxGegBeschikk
	WHERE (VTGWRDPJR = '2008')
	GROUP BY GEOTAXNR) AS f ON e.VTGTAXDAT = f.TAXDATMAX AND e.GEOTAXNR = f.GEOTAXNR
	WHERE (e.VTGWRDPJR = '2008')
)
,Query3
AS
(
	SELECT a.GEOTAXNR, a.CBBVGONUM, a.CBBVGOWOZNUM, a.VTGTAXDAT, a.VTGTAXNAM, a.VTGRDN, a.VTGRLT, a.VTGVSTWRD, a.VTGBSK, a.VTGOZBWRD, 
	a.VTGHMSOZB, a.CBBVSLKDE
	FROM fact_BelastingTaxGegBeschikk AS a INNER JOIN
	(SELECT GEOTAXNR, MAX(VTGTAXDAT) AS TAXDATMAX
	FROM fact_BelastingTaxGegBeschikk
	WHERE (VTGWRDPJR = '2008') AND (VTGBSK = 'B')
	GROUP BY GEOTAXNR) AS b ON a.VTGTAXDAT = b.TAXDATMAX AND a.GEOTAXNR = b.GEOTAXNR
	WHERE (a.VTGWRDPJR = '2008') AND (a.VTGBSK = 'B')
)
,Query4
AS
(
	SELECT c.GEOTAXNR, c.CBBVGONUM, c.CBBVGOWOZNUM, c.VTGTAXDAT, c.VTGTAXNAM, c.VTGRDN, c.VTGRLT, c.VTGVSTWRD, c.VTGBSK, c.VTGOZBWRD, 
	c.VTGHMSOZB, c.CBBVSLKDE
	FROM fact_BelastingTaxGegBeschikk AS c INNER JOIN
	(SELECT GEOTAXNR, MIN(VTGTAXDAT) AS TAXDATMIN
	FROM fact_BelastingTaxGegBeschikk
	WHERE (VTGWRDPJR = '2008') AND (VTGBSK = 'B')
	GROUP BY GEOTAXNR) AS d ON c.VTGTAXDAT = d.TAXDATMIN AND c.GEOTAXNR = d.GEOTAXNR
	WHERE (c.VTGWRDPJR = '2008') AND (c.VTGBSK = 'B')


)
SELECT *
FROM Query1 Q1
	LEFT JOIN Query Q2
		ON Q1.GEOTAXNR = Q2.GEOTAXNR
	LEFT JOIN Query3 Q3
		ON Q1.GEOTAXNR = Q3.GEOTAXNR
	LEFT JOIN Query4 Q4
		ON Q1.GEOTAXNR = Q4.GEOTAXNR
ORDER BY Q1.GEOTAXNR

;WITH CTE1
AS
(
	SELECT GEOTAXNR
		,CBBVGONUM
		,CBBVGOWOZNUM
		,VTGTAXDAT
		,VTGTAXNAM
		,VTGRDN
		,VTGRLT
		,VTGVSTWRD
		,VTGBSK
		,VTGOZBWRD
		,VTGHMSOZB
		,CBBVSLKDE
		,ROW_NUMBER() OVER (PARTITION BY GEOTAXNR ORDER BY VTGTAXDAT DESC) AS RowNum2
		,ROW_NUMBER() OVER (PARTITION BY GEOTAXNR, VTGBSK ORDER BY VTGTAXDAT DESC) AS RowNum3
		,ROW_NUMBER() OVER (PARTITION BY GEOTAXNR, VTGBSK ORDER BY VTGTAXDAT) AS RowNum4
	FROM fact_BelastingTaxGegBeschikk
	WHERE VTGWRDPJR = '2008'
)
,CTE2
AS
(
	SELECT GEOTAXNR
		,MAX(CASE RowNum2 WHEN 1 THEN CBBVGONUM END) AS CBBVGONUM2
		,MAX(CASE RowNum2 WHEN 1 THEN CBBVGOWOZNUM END) AS CBBVGOWOZNUM2
		,MAX(CASE RowNum2 WHEN 1 THEN VTGTAXDAT END) AS VTGTAXDAT2
		,MAX(CASE RowNum2 WHEN 1 THEN VTGTAXNAM END) AS VTGTAXNAM2
		,MAX(CASE RowNum2 WHEN 1 THEN VTGRDN END) AS VTGRDN2
		,MAX(CASE RowNum2 WHEN 1 THEN VTGRLT END) AS VTGRLT2
		,MAX(CASE RowNum2 WHEN 1 THEN VTGVSTWRD END) AS VTGVSTWRD2
		,MAX(CASE RowNum2 WHEN 1 THEN VTGBSK END) AS VTGBSK2
		,MAX(CASE RowNum2 WHEN 1 THEN VTGOZBWRD END) AS VTGOZBWRD2
		,MAX(CASE RowNum2 WHEN 1 THEN VTGHMSOZB END) AS VTGHMSOZB2
		,MAX(CASE RowNum2 WHEN 1 THEN CBBVSLKDE END) AS CBBVSLKDE2

		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN CBBVGONUM END) AS CBBVGONUM3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN CBBVGOWOZNUM END) AS CBBVGOWOZNUM3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN VTGTAXDAT END) AS VTGTAXDAT3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN VTGTAXNAM END) AS VTGTAXNAM3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN VTGRDN END) AS VTGRDN3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN VTGRLT END) AS VTGRLT3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN VTGVSTWRD END) AS VTGVSTWRD3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN VTGBSK END) AS VTGBSK3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN VTGOZBWRD END) AS VTGOZBWRD3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN VTGHMSOZB END) AS VTGHMSOZB3
		,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN CBBVSLKDE END) AS CBBVSLKDE3
		
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN CBBVGONUM END) AS CBBVGONUM4
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN CBBVGOWOZNUM END) AS CBBVGOWOZNUM4
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN VTGTAXDAT END) AS VTGTAXDAT4
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN VTGTAXNAM END) AS VTGTAXNAM4
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN VTGRDN END) AS VTGRDN4
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN VTGRLT END) AS VTGRLT4
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN VTGVSTWRD END) AS VTGVSTWRD4
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN VTGBSK END) AS VTGBSK4
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN VTGOZBWRD END) AS VTGOZBWRD4
		,MAX(CASE RowNum4 WHEN 1 AND VTGBSK = 'B' THEN VTGHMSOZB END) AS VTGHMSOZB4
		,MAX(CASE RowNum4 WHEN 1  AND VTGBSK = 'B'THEN CBBVSLKDE END) AS CBBVSLKDE4
	FROM CTE1
	GROUP BY GEOTAXNR
)
SELECT g.GEOTAXNR
	,g.VGONUM
	,g.VGOWOZNUM
	,g.CBRWYKKDE
	,g.CBRBRTKDE
	,g.ADRSTTNAM
	,g.ADRHUINUM
	,g.ADRHUILET
	,g.ADRHUITVG
	,g.ADRHUIAND
	,g.VGSOBJSRT
	,g.VGSOMS
	,g.VGSGBRKDE
	,g.VGOIDT
	,g.VGOADTWOZ
	,g.VDTINDRCN
	,g.ADRINDRCN
	,C.*
FROM fact_BelastingObjecten AS g
	LEFT JOIN CTE2 C
		ON g.GEOTAXNR = C.GEOTAXNR
WHERE g.VDTINDRCN = 'X'
	AND g.ADRINDRCN = 'X'
	AND g.VGOIDT < '20090102'
	AND
	(
		g.VGOADTWOZ = '00000000'
		OR g.VGOADTWOZ > '20090101'
	)
ORDER BY g.GEOTAXNR

quote:

---

Originally posted by Marteijn

When using your efficient query I get a syntax error on the following line:

,MAX(CASE RowNum3 WHEN 1 AND VTGBSK = 'B' THEN CBBVGONUM END) AS CBBVGONUM3

---

,MAX(CASE WHEN RowNum3 = 1 AND VTGBSK = 'B' THEN CBBVGONUM END) AS CBBVGONUM3