Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Problem with totalize per quarter
Message
 
 
Information générale
Forum:
Microsoft SQL Server
Catégorie:
Syntaxe SQL
Versions des environnements
SQL Server:
SQL Server 2012
OS:
Windows 10
Network:
Windows Server 2012
Divers
Thread ID:
01639700
Message ID:
01639714
Vues:
73
This message has been marked as a message which has helped to the initial question of the thread.
I see a few problems in your query.

1. Since you use LEFT JOIN with HistItems, you want to move condition on the Items table into the JOIN. See this blog post for explanations

http://beyondrelational.com/modules/2/blogs/78/posts/11135/why-left-join-doesnt-bring-all-records-from-the-left-table.aspx

2. Don't use DATEPART function for the year condition. Instead construct 2 dates for beginning of the year and beginning of the next year and use

myDateColumn >=@StartDate and myDateColumn < @EndDate

condition.

See this blog post for details

Bad habits to kick : mis-handling date / range queries


>This is now my final version with Pivot. At first I was reluctant to dive into Pivot but in hindsight it's easier than I expected. The result is exactly what I need:
>
>SELECT Ministerie, Departement, KSC, Kostensoort,
>		Year,
>		ISNULL(QPivot.[1],0.00) + 
>		ISNULL(QPivot.[2],0.00) + 
>		ISNULL(QPivot.[3],0.00) + 
>		ISNULL(QPivot.[4],0.00) AS Total, 
>
>		ISNULL(QPivot.[1],0.00) AS Q1,
>		ISNULL(QPivot.[2],0.00) AS Q2,
>		ISNULL(QPivot.[3],0.00) AS Q3,
>		ISNULL(QPivot.[4],0.00) AS Q4
>		
>FROM (SELECT DepGroups.dgName AS Ministerie,
>		Departments.deName AS Departement,
>		GLSub.gsAccount AS KSC,
>		GLSub.gsDescr AS Kostensoort,
>		DATEPART(YEAR,Runs.ruStartDat) AS Year,
>		DATEPART(Quarter, Runs.ruStartDat) AS Quarter,
>		SUM(itAmount) AS itAmount
>
>	FROM PayEmps
>	
>	JOIN Departments
>	ON Departments.DeKey = PayEmps.paDeKey
>	JOIN DepGroups
>	ON Departments.deDgKey = DepGroups.dgKey
>	JOIN Runs
>	ON Runs.ruKey = PayEmps.paRuKey
>
>	LEFT JOIN HistItems Items
>	ON Items.itRuKey = Runs.ruKey
>	AND Items.itEmKey = PayEmps.paEmKey
>
>	LEFT JOIN GLSub
>	ON Items.itGsKey = GLSub.gsKey
>
>	WHERE PayEmps.delFlag = 0
>			AND Runs.DelFlag = 0
>			AND Runs.ruConcept = 0
>			AND Runs.ruStatus = 5
>			AND DATEPART(YEAR,Runs.ruStartDat) = ?pnYear
>			AND Items.DelFlag = 0
>		        AND GsAccount = '4101'
>                        AND Runs.ruYear = 2015
>
>	GROUP BY DepGroups.dgName, Departments.deName, 
>		GLSub.gsAccount, GLSub.gsDescr,
>		DATEPART(YEAR,Runs.ruStartDat),
>		DATEPART(QUARTER,Runs.ruStartDat)) AS QuarterlyData
>	
>	PIVOT(SUM([itAmount])   
>        FOR QUARTER IN ([1],[2],[3],[4])) AS QPivot
>
>	ORDER BY Ministerie, Departement, KSC
>
If it's not broken, fix it until it is.


My Blog
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform