asked
Outer query is giving wrong count
SELECT COUNT(X.ACTIVITY_ID) FROM ( SELECT ACTIVITY_ID FROM mi_s_data.dbo.[MI_FACT_ACTIVITY] WHERE Date_Key BETWEEN ( CONVERT(INT, CONVERT(VARCHAR(10), DATEPART(yyyy, GETDATE()), 112)) - 2 ) * 10000 + 101 AND CONVERT(INT, CONVERT(VARCHAR(10), GETDATE(), 112)) UNION SELECT ACTIVITY_ID FROM [MI_S_DATA].[DBO].[MOS_FACT_ACTIVITY_V1] MFA WHERE Date_Key BETWEEN ( CONVERT(INT, CONVERT(VARCHAR(10), DATEPART(yyyy, GETDATE()), 112)) - 2 ) * 10000 + 101 AND CONVERT(INT, CONVERT(VARCHAR(10), GETDATE(), 112)) AND MFA.ACTIVITY_UID IS NULL ) X --Count is 1763046 while running individual count like below: SELECT COUNT(*) FROM ( SELECT ACTIVITY_ID FROM mi_s_data.dbo.[MI_FACT_ACTIVITY] WHERE Date_Key BETWEEN ( CONVERT(INT, CONVERT(VARCHAR(10), DATEPART(yyyy, GETDATE()), 112)) - 2 ) * 10000 + 101 AND CONVERT(INT, CONVERT(VARCHAR(10), GETDATE(), 112)) ) a --5774079 SELECT COUNT(*) FROM ( SELECT ACTIVITY_ID FROM [MI_S_DATA].[DBO].[MOS_FACT_ACTIVITY_V1] MFA WHERE Date_Key BETWEEN ( CONVERT(INT, CONVERT(VARCHAR(10), DATEPART(yyyy, GETDATE()), 112)) - 2 ) * 10000 + 101 AND CONVERT(INT, CONVERT(VARCHAR(10), GETDATE(), 112)) AND MFA.ACTIVITY_UID IS NULL ) a --0
Set Theory 101. "UNION" statement removes duplicates. If you want to have the duplicates in for your counting, then use "UNION ALL". See [T-SQL UNION][1] documentation for more. [1]:
https://msdn.microsoft.com/en-us/library/ms180026.aspx
18 People are following this question.
