OK I've made some changes to make sure that everything is linking off of the date datatype, and now it's doing something similar by trying to pass the a list of the premiseIDs:
SELECT TOP (1000001) [semijoin1].[c81],
(COUNT_BIG(DISTINCT [basetable0].[c177]) + MAX(
CASE
WHEN [basetable0].[c177] IS NULL THEN 1
ELSE 0
END)) AS [a0]
FROM
(
(
SELECT [t5].[EventListKey] AS [c98],[t8]
.[EventLabel] AS [c177]
FROM
(
(
SELECT [$Table].[EventListKey] as [EventListKey], [$Table]
.[SourceSystem] as [SourceSystem], [$Table]
.[SourceSystemID] as [SourceSystemID], [$Table]
--hiding for the forum
.[Qualification] as [Qualification], [$Table]
.[ResponsibleGroup] as [ResponsibleGroup], [$Table]
.[BeginDateOnly] as [BeginDateOnly], [$Table]
.[BeginTimeOnly] as [BeginTimeOnly]
FROM [UDM].[TableQualifiedEventList] as [$Table]
) AS [t8]
LEFT OUTER JOIN
(
SELECT [$Table].[EventListKey] as [EventListKey], [$Table]
.[SourceSystem] as [SourceSystem], [$Table]
.[SourceSystemID] as [SourceSystemID], [$Table]
.[SourceSystemVersion] as [SourceSystemVersion], [$Table]
--hiding for the forum
.[DispatchedOffice] as [DispatchedOffice], [$Table]
.[BeginDateOnly] as [BeginDateOnly], [$Table]
.[BeginTimeOnly] as [BeginTimeOnly]
FROM [UDM].[TableEventList] as [$Table]
) AS [t5]
ON ( [t8].[EventListKey] = [t5].[EventListKey] )
)
WHERE ( ( ( [t8].[MetricType] = 'Sustained Event' )
AND ( [t8].[Normalization] = 'Normalized' ) )
AND ( [t8].[Qualification] = 'Qualified Outage' ) )
GROUP BY [t5].[EventListKey],[t8]
.[EventLabel]
) AS [basetable0]
INNER JOIN
(
SELECT [t4].[PremiseID] AS [c81],[t5]
.[EventListKey] AS [c98]
FROM
(
(
(
(
SELECT [$Table].[EventPremiseKey] as [EventPremiseKey], [$Table]
.[SourceSystem] as [SourceSystem], [$Table]
.[SourceSystemID] as [SourceSystemID], [$Table]
--hiding for the forum
.[EventListKey] as [EventListKey]
FROM [UDM].[TableEventPremise] as [$Table]
) AS [t6]
LEFT OUTER JOIN
(
SELECT [_].[CustomerPremiseKey], [_]
.[SourceSystem], [_]
.[SourceSystemID], [_]
--hiding for the forum
.[GPSLatitude], [_]
.[GPSLongitude], [_]
.[MeterID], [_]
.[MeterFieldName], [_]
.[ServiceBirth], [_]
.[ServiceDeath], [_]
.[PrimaryDeviceID], [_]
.[PrimaryDeviceFieldLabel]
FROM [UDM].[TableCustomerPremises] as [_]
WHERE [_].[SourceSystemID] = 2
AND [_].[SourceSystemID] is not null
) AS [t4]
ON ( [t6].[CustomerPremiseKey] = [t4].[CustomerPremiseKey] )
)
LEFT OUTER JOIN
(
SELECT [$Table].[EventListKey] as [EventListKey], [$Table]
.[SourceSystem] as [SourceSystem], [$Table]
.[SourceSystemID] as [SourceSystemID], [$Table]
.[SourceSystemVersion] as [SourceSystemVersion], [$Table]
--hiding for the forum
.[DispatchedOffice] as [DispatchedOffice], [$Table]
.[BeginDateOnly] as [BeginDateOnly], [$Table]
.[BeginTimeOnly] as [BeginTimeOnly]
FROM [UDM].[TableEventList] as [$Table]
) AS [t5]
ON ( [t6].[EventListKey] = [t5].[EventListKey] )
)
LEFT OUTER JOIN
(
SELECT [$Table].[EventListKey] as [EventListKey], [$Table]
.[SourceSystem] as [SourceSystem], [$Table]
.[SourceSystemID] as [SourceSystemID], [$Table]
--hiding for the forum
.[BeginDateOnly] as [BeginDateOnly], [$Table]
.[BeginTimeOnly] as [BeginTimeOnly]
FROM [UDM].[TableQualifiedEventList] as [$Table]
) AS [t8]
ON ( [t5].[EventListKey] = [t8].[EventListKey] )
)
WHERE ( ( ( [t8].[MetricType] = 'Sustained Event' )
AND ( [t8].[Normalization] = 'Normalized per IEEE 1366' ) )
AND ( ( [t8].[Qualification] = 'Qualified Outage' )
AND ( ([t4].[PremiseID] IN (1380036215,6710121583,3020456851,4070193589,3030641333,6950759772,
4360614969,9120736306,8970027451,4640296258,6720221414,3730218667,2230563108,6300668790,
9370555164,9950824118,4860938001,2050851077,5660076649,1950563439,5090377082,9150849585,
8770496798,7300857654,9510649132,9700166137,8610187412,4650946407,2730899016,6730178349,
490669915,9010594331,1680679614,6160132619,8490451331,9100200244,2540760976,2050727636,
40375263,4360651321,2900344154,6920503881,5160068541,250666575,1090901008,8180416967,
3620916542,3880290214,6600053663,5850434770,1860272155,2120411743,4540619244,3490664492,
8070893815,3890738177,5410104832,2060641227,8280941255,7180106175,2750674205,910691443,
5030440726,5770935062,5650776145,8410810901,9130167156,7440554718,8400005438,4210965680,
8270452750,3780133095,360770338,7910613420,3350080990,430462603,4290333969,7680711386, I don't understand why it's doing this at all.
