Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
osirisa
Constraint Violating Yak Guru
289 Posts |
 Posted - 2009-02-24 : 12:02:14
|
Hi Group:Enclosed is the SQL statement that I am using, the syntax may not look right but is because the sqlconnection is reading from an Ingrid Table. set @cSQLVar = 'insert into #Analyst SELECT TOP (100) PERCENT COUNT(dw_chg.assignee) AS NO_AnalystFROM dbo.dw_ca_contact RIGHT OUTER JOIN dbo.dw_chg ON dbo.dw_ca_contact.contact_uuid = dbo.dw_chg.group_idWHERE (dbo.dw_chg.active_flag = 1) AND (DATEADD(s, dbo.dw_chg.open_date, CONVERT(DATETIME, ''1970-01-01 00:00:00'', 102)) > GETDATE() - 14) AND (dbo.dw_ca_contact.last_name NOT LIKE ''GSP%'') AND (dbo.dw_ca_contact.last_name NOT LIKE ''NA-HRMS%'') AND (dbo.dw_ca_contact.last_name NOT LIKE ''UG.Support%'') AND (dbo.dw_ca_contact.last_name NOT LIKE ''Time and Att%'') AND (dbo.dw_ca_contact.last_name NOT LIKE ''Applications-NA%'') AND (dbo.dw_ca_contact.last_name NOT LIKE ''Ricoh%'') AND (dbo.dw_ca_contact.last_name NOT LIKE ''Database Admin%'') AND (dbo.dw_ca_contact.last_name NOT LIKE ''GLV4%'') AND (dbo.dw_ca_contact.last_name NOT LIKE ''CPC%'')'] What I am trying to find out is how many records has NO Analyst assigned to. However looks like what I am getting is only the records with an Analyst Assigned and is also giving me the following error.Warning: Null value is eliminated by an aggregate or other SET operation.Any help is greatly appreciate it. Thanks, |
|
|
nathans
Aged Yak Warrior
938 Posts |
Posted - 2009-02-24 : 12:17:17
|
If you are trying to get all the records from dbo.dw_chg that have zero records in dbo.dw_ca_contact then this query is better:select count(*)from dbo.dw_chg dcleftjoin dbo.dw_ca_contract dcc on dc.group_id = dcc.contact_uuidwhere dcc.contact_uuid is null and ...[your other filters] Without sample data and desired resultset its difficult to interpret what you need. I am assuming you are trying to eliminate every analyst by including all last_name filters in the where.Also, the "...null value is eliminated" is a warning and is telling you that a null value was encountered in your aggregate COUNT and was eliminated altogether. In some cases thats ok, in others you would want the null to evaluate to a 0, for example in a SUM of sales items, etc.Please post back with sample data / desired results if this does not help. Good luck!Nathan Skerl |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-24 : 12:29:49
|
| whats the need of dynamic sql here? |
|
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2009-02-24 : 13:15:31
|
| Thank You NathanActually the result will be something like thisNo_Analyst40What I really want is the SUM of the Null values, but the system eliminates them from the COUNT function. Any help:) |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-24 : 13:20:37
|
| Can u also give some sample data ..that will explain your cause better. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-24 : 13:23:31
|
quote:
Originally posted by osirisa
Thank You NathanActually the result will be something like thisNo_Analyst40What I really want is the SUM of the Null values, but the system eliminates them from the COUNT function. Any help:)
then change the count(*) tocount(*) - COUNT(dw_chg.assignee)this will give you count of null values in dw_chg.assignee field |
|
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2009-02-24 : 13:59:40
|
| KOOL, Thank YOU>....:) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-02-25 : 09:09:54
|
| welcome |
|
|
|
|
|
|
|
|
Need help with an Error Message