Hi.
Wouldn't the logic of the APPLY syntax be logically similar to the following:
SELECT
T.[ModelRegion]
,T.[TimeIndex]
,Sum(T.[Quantity]) AS [Quantity]
,Sum(T.[Amount]) AS [Amount]
,T.[CalendarYear]
,T.[Month]
,T.[ReportingDate]
FROM
(
SELECT
CASE [Model]
WHEN 'Mountain-100' THEN 'M200'
WHEN 'Road-150' THEN 'R250'
WHEN 'Road-650' THEN 'R750'
WHEN 'Touring-1000' THEN 'T1000'
ELSE Left([Model], 1) + Right([Model], 3)
END + ' ' + [Region] AS [ModelRegion]
, (Convert(Integer, [CalendarYear]) * 100) + Convert(Integer, [Month]) AS [TimeIndex]
, [dbo].[udfBuildISO8601Date] ([CalendarYear], [Month], 25) AS ReportingDate
FROM
[dbo].[vDMPrep]
WHERE
[Model] IN ('Mountain-100', 'Mountain-200', 'Road-150', 'Road-250',
'Road-650', 'Road-750', 'Touring-1000')
) T
GROUP BY
T.[ModelRegion]
,T.[TimeIndex]
,T.[CalendarYear]
,T.[Month]
,T.[ReportingDate]
I don't have an AdventureWorks db handy to compare the execution plans but I'd be most interested to know what are the pro's and con's between the APPLY and the above query, especially in terms of performance. Would there be more overhead in my query as compared to using APPLY?
PS: Very sorry that the indentation doesn't seem to work here!
Arvin