SELECT
	'XX' AS LOB
	, HandleTimeHalf
	, table5.DateTime
	, '0' AS CallTypeID
	, CallsOfferedHalf
	, CallsHandledHalf
	, 'YY' AS Enterprise
	, loggedOnTimeToHalf
	, NotReadyTimeToHalf
	, RouterQueueCallsToHalf
	, TalkTimeHalf
	, RouterQueueWaitTimeToHalf
	, AnsInterval6
	, HoldTimeToHalf
	, TotalCallsAbandToHalf
	, TotalAbandonMinusServiceAban
	, CallsHandleMinusSericeCallsHandle
	, AbandInterval6
	, AbandInterval7
	, AbandInterval8
	, AbandInterval9
	, AbandInterval10
	, AnsInterval7
	, AnsInterval8
	, AnsInterval9
	, AnsInterval10
	, WorkNotReadyTimeToHalf
	, sHoldTimeToHalf
	, TalkInTimeToHalf
	, TalkTimeToHalf
	, CallsAnsweredToHalf
	, AvailTimeToHalf
	, 1234 AS AgentTeamID
	, TeamACW
	, '0' AS ShortCallsHalf, 
	'0' AS AnswerWaitTimeHalf
FROM 
	(
	SELECT
		DATETIME
		, SUM(TeamACW) AS TeamACW
	FROM 
		(
		SELECT
			table1.DateTime
			, CASE WHEN TeamACW IS NULL THEN '0' ELSE TeamACW END AS TeamACW
			, HandledTime
		FROM
			(
			SELECT
				DATETIME
				, SUM(WorkNotReadyTimeToHalf) AS TeamACWOld
				, 1234 AS CallTypeID
			FROM
				ABCD WITH (NOLOCK)
			WHERE
				SkillID IN (1,2,3,4,5)
				AND DATETIME >='09/01/09'
			GROUP BY
				DATETIME

			UNION ALL SELECT
				DATETIME
				, SUM(WorkNotReadyTimeToHalf) AS TeamACWOld
				, 5678 AS CallTypeID
			FROM
				ABCD WITH (NOLOCK)
			WHERE
				SkillID IN (6,7,8,9,10)
				AND DATETIME >='09/01/09'
			GROUP BY
				DATETIME

			UNION ALL SELECT
				DATETIME
				, SUM(WorkNotReadyTimeToHalf) AS TeamACWOld
				, 1212 AS CallTypeID
			FROM
				ABCD WITH (NOLOCK)
			WHERE
				SkillID IN (11,12)
				AND DATETIME>='09/01/09'
			GROUP BY
				DATETIME

			UNION ALL SELECT DATETIME
				, SUM(WorkNotReadyTimeToHalf) AS TeamACWOld
				, 3434 AS CallTypeID
			FROM
				ABCD WITH (NOLOCK)
			WHERE
				SkillID IN (13,14)
				AND DATETIME>='09/01/09'
			GROUP BY
				DATETIME
			)
			table1 

			LEFT OUTER JOIN (
				SELECT
					DATETIME
					, SUM(HandleTimeHalf) AS HandledTime
					, SUM(HandleTimeHalf-TalkTimeHalf-HoldTimeToHalf) AS TeamACW
					, CallTypeID
				FROM
					EFGH WITH (NOLOCK)
				WHERE
					CallID IN (1234,5678,1212,3434)
					AND DATETIME>='09/01/09'
				GROUP BY
					DATETIME
					, CallTypeID
			)
			table2 ON
				table1.DateTime = table2.DateTime
				AND table1.CallTypeID = table2.CallTypeID
		)
		ctemp3
	GROUP BY
		DATETIME
	)
	table5

	LEFT OUTER JOIN (
		SELECT
			'XX' AS LOB
			, '0' AS HandleTimeHalf
			, DATETIME
			, '0' AS CallTypeID
			, '0' AS CallsOfferedHalf
			, '0' AS CallsHandledHalf
			, 'YY' AS Enterprise
			, SUM(LoggedOnTimeToHalf) AS loggedOnTimeToHalf
			, SUM(NotReadyTimeToHalf) AS NotReadyTimeToHalf
			, '0' AS RouterQueueCallsToHalf
			, '0' AS TalkTimeHalf
			, '0' AS RouterQueueWaitTimeToHalf
			, '0' AS AnsInterval6
			, '0' AS HoldTimeToHalf
			, '0' AS TotalCallsAbandToHalf
			, '0' AS TotalAbandonMinusServiceAban
			, '0' AS CallsHandleMinusSericeCallsHandle
			, '0' AS AbandInterval6
			, '0' AS AbandInterval7
			, '0' AS AbandInterval8
			, '0' AS AbandInterval9
			, '0' AS AbandInterval10
			, '0' AS AnsInterval7
			, '0' AS AnsInterval8
			, '0' AS AnsInterval9
			, '0' AS AnsInterval10
			, '0' AS WorkNotReadyTimeToHalf
			, '0' AS sHoldTimeToHalf
			, '0' AS TalkInTimeToHalf
			, '0' AS TalkTimeToHalf
			, '0' AS CallsAnsweredToHalf
			, SUM(AvailTimeToHalf) AS AvailTimeToHalf
		FROM
			ABAB WITH (NOLOCK)
			, CDCD WITH (NOLOCK)
		WHERE
			ABAB.ID = CDCD.ID
			AND CDCD.ID3 IN (1000,10001,1002,1003)
			AND DATEPART(hh, DATETIME) BETWEEN 6 AND 21
			AND DATENAME(dw, DATETIME) NOT IN ('Sunday', 'Saturday')
			AND DATETIME >= '09/01/09'
		GROUP BY
			DATETIME
		)
		table6 ON table5.DateTime = table6.DateTime

SELECT
	[datetime]
	, SUM([workNotReadyTimeTohalf]) AS [TeamACWOld]
	, [callTypeId]
FROM
	(
	SELECT
		[datetime]
		, [workNotReadyTimeTohalf]
		, CASE
			WHEN [SkillID] IN (1, 2, 3, 4, 5) THEN 1234
			WHEN [SkillID] IN (6, 7, 8, 9, 10) THEN 5678
			WHEN [SkillID] IN (11, 12) THEN 1212
			WHEN [SkillID] IN (13, 14) THEN 3434
		END AS [callTypeID]
	FROM
		ABCD
	WHERE
		[datetime] >= '20090901' --1st Sept 2009 ?? imposibile to tell from your string 09/01/09 (UK / US?)
		AND [skillID] BETWEEN 1 and 14
	)
	skillMap
GROUP BY
	[datetime]
	, [callTypeId]