question

ankurjai avatar image
ankurjai 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
sql-server-2008sql-server-2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.