Optimizing Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Optimizing Query

Author

Topic

johnnyhermes
Starting Member

1 Post

Posted - 2011-05-11 : 07:10:35

Hello,

Have a query wich calculates the hours an employee works in a month.
I use a "UNION" to get all months of the year 2011 (later 2012, 2013, etc...). I think I coul make my query way more efficient.
Right now it looks like this:

SELECT 
			RES.ID														AS RESOURCE_ID
,			RES.NAME + ', ' + RES.INITIALS								AS NAAM
,			RES.EMPLOYEE_NUMBER											AS MEDEWERKER_NUMMER
,			convert(varchar(10),RES.DATE_OF_BIRTH,105)					AS GEBOORTE_DATUM
,			TGL.AVAILABILITY * 24										AS UREN_PER_WEEK
,			TGL.VALID_FROM												AS GELDIG_VAN
,			TGL.VALID_UNTIL												AS GELDIG_TOT
,			'201101'													AS PERIODE
,			CAST (rep_pvtest.dbo.HARMONY_PLB_AANTAL_MND_JAAR(TGL.VALID_FROM,TGL.VALID_UNTIL,'2011-02-01','2011-01-01') 
			*
			rep_pvtest.dbo.HARMONY_PLB_LEEFTIJD (RES.DATE_OF_BIRTH,TGL.AVAILABILITY,@FULLTIME_FTE)
			+
			(ISNULL(ROUND(rep_pvtest.dbo.HARMONY_PLB_AANTAL_WERKDG_MAAND(TGL.VALID_FROM,TGL.VALID_UNTIL,'2011-02-01','2011-01-01')
			/
			rep_pvtest.dbo.HARMONY_PLB_AANTAL_WERKDG_MAAND_OVERIG(TGL.VALID_FROM,TGL.VALID_UNTIL,'2011-02-01','2011-01-01')
			*
			rep_pvtest.dbo.HARMONY_PLB_LEEFTIJD (RES.DATE_OF_BIRTH,TGL.AVAILABILITY,@FULLTIME_FTE)
			,2),0)) AS FLOAT) AS 'Overige uren'

FROM		ROS_RESOURCES AS RES

INNER JOIN	ROS_TERMS_GROUP_LINES AS TGL
ON			RES.ID = TGL.RSE_ID

WHERE		1=1
AND			RES.ID = @ResourceID
AND			TGL.VALID_UNTIL >= '2011-02-01'
AND			TGL.VALID_FROM	<= '2011-01-01'
AND			
			-- *** Bepalen ontziebepaling ***
			(SELECT COUNT(*)
			FROM ROS_PROPERTY_VALUES AS RPV
			INNER JOIN ROS_USER_PROPERTIES AS RUP
			ON RPV.upy_id = RUP.id

			WHERE (RPV.upy_id = @KENMERK AND RPV.value = @KENMERK_WAARDE)
			AND (RPV.ID = RES.ID)) = 0 -- Op deze manier kan de ontziebepaling bepaald worden. Deze personen voldoen niet aan de ontziebepaling en krijgen geen extra PLB uren!!!!!

UNION

SELECT 
			RES.ID														AS RESOURCE_ID
,			RES.NAME + ', ' + RES.INITIALS								AS NAAM
,			RES.EMPLOYEE_NUMBER											AS MEDEWERKER_NUMMER
,			convert(varchar(10),RES.DATE_OF_BIRTH,105)					AS GEBOORTE_DATUM
,			TGL.AVAILABILITY * 24										AS UREN_PER_WEEK
,			TGL.VALID_FROM												AS GELDIG_VAN
,			TGL.VALID_UNTIL												AS GELDIG_TOT
,			'201102'													AS PERIODE
,			CAST (rep_pvtest.dbo.HARMONY_PLB_AANTAL_MND_JAAR(TGL.VALID_FROM,TGL.VALID_UNTIL,'2011-03-01','2011-02-01') 
			*
			rep_pvtest.dbo.HARMONY_PLB_LEEFTIJD (RES.DATE_OF_BIRTH,TGL.AVAILABILITY,@FULLTIME_FTE)
			+
			(ISNULL(ROUND(rep_pvtest.dbo.HARMONY_PLB_AANTAL_WERKDG_MAAND(TGL.VALID_FROM,TGL.VALID_UNTIL,'2011-03-01','2011-02-01')
			/
			rep_pvtest.dbo.HARMONY_PLB_AANTAL_WERKDG_MAAND_OVERIG(TGL.VALID_FROM,TGL.VALID_UNTIL,'2011-03-01','2011-02-01')
			*
			rep_pvtest.dbo.HARMONY_PLB_LEEFTIJD (RES.DATE_OF_BIRTH,TGL.AVAILABILITY,@FULLTIME_FTE)
			,2),0)) AS FLOAT) AS 'Overige uren'

FROM		ROS_RESOURCES AS RES

INNER JOIN	ROS_TERMS_GROUP_LINES AS TGL
ON			RES.ID = TGL.RSE_ID

WHERE		1=1
AND			RES.ID = @ResourceID
AND			TGL.VALID_UNTIL >= '2011-03-01'
AND			TGL.VALID_FROM	<= '2011-02-01'
AND			
			-- *** Bepalen ontziebepaling ***
			(SELECT COUNT(*)
			FROM ROS_PROPERTY_VALUES AS RPV
			INNER JOIN ROS_USER_PROPERTIES AS RUP
			ON RPV.upy_id = RUP.id

			WHERE (RPV.upy_id = @KENMERK AND RPV.value = @KENMERK_WAARDE)
			AND (RPV.ID = RES.ID)) = 0 -- Op deze manier kan de ontziebepaling bepaald worden. Deze personen voldoen niet aan de ontziebepaling en krijgen geen extra PLB uren!!!!!

UNION

201103

UNION

201104

ETC...

I hope that you can help me with this.

Thanks very much in advance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources