New answers tagged sql-server-2008-express
Are you just looking for distinct Agents that have at least one null invoice and at least one non-null invoice? SELECT [AC].[Agent] FROM [AgentCommTMP] AS [AC] GROUP BY [AC].[Agent] HAVING Count(CASE WHEN [AC].[InvoiceID] IS NULL THEN 1 ELSE Null END) > 0 AND Count(CASE WHEN [AC].[InvoiceID] IS NULL THEN Null ELSE 1 END) > 0
Why does the DISTINCT clause level all the queries results down to 3,074? Based on your question, your table would have data similar to this for any Agent (A1). Agent | InvoiceID A1, 1 A1, NULL A1, NULL A1, 2 A1, 245 A1, 3 Based on this data, a query to get agents which have invoice id which are not null would be SELECT Agent FROM [AgentCommTMP] ...
The following is your common denominator, which, not seeing the data and assuming I am reading your question correctly, would have a count of 3074 rows. SELECT DISTINCT [AC2].[Agent] FROM [AgentCommTMP] AS [AC2] WHERE [AC2].[InvoiceID] IS NOT NULL which is different from the following which has the potential to have more rows. SELECT ...
Top 50 recent answers are included