adding a date seems to remove any null values

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

adding a date seems to remove any null values

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2014-09-01 : 02:46:48

Hi guys,

I have 2 tables and doing a left join. I am doing a very simply query but when I add this simple line it removes any counts that would have been 0
date >= DATEADD(day, -2, convert(date, GETDATE()))

I need to keep the names of these people that whould show as a 0 count, how can I make this so?

query:
Select distinct
agent_login_id, count(agent_login_id)
from Agents
left join gncs on agent_login_id=Engineer
where date >= DATEADD(day, -2, convert(date, GETDATE()))
group by agent_login_id

Admittedly if the person does not have a record in the GNCs table for the date period they will not show, but they are 100% in the agents table.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-01 : 10:50:32

Assuming date column is in the gnus table, move the where clause to the join clause


Select
agent_login_id, count(agent_login_id) 
from Agents
left join gncs on agent_login_id=Engineer
And date >= DATEADD(day, -2, convert(date, GETDATE()))
group by agent_login_id

You don't need a distinct clause when you have a group by clause

2revup
Posting Yak Master

112 Posts

Posted - 2014-09-01 : 17:56:45

Thanks James - When running that it gives me all names back but the count for all returns 1.

Cheers,
Brad

2revup
Posting Yak Master

112 Posts

Posted - 2014-09-01 : 18:07:52

All good, this is what I ended up doing.

Select
agent_login_id, e.count
from Agents
left join (select engineer, count(Engineer)as count from gncs where date >= DATEADD(day, -3, convert(date, GETDATE())) group by Engineer)e on agent_login_id=e.Engineer

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-09-02 : 11:53:58

This condition:

where date >= DATEADD(day, -2, convert(date, GETDATE()))

isn't meaningful: a given datetime will always be ge a date that is two days before that same datetime.

Are the date columns actually on different tables?

To avoid this type of confusion, an excellent tip when writing SQL is to always qualify every column with a table alias when using two or more columns in a SQL query.



SELECT
a.agent_login_id, count(gncs.Engineer)
FROM Agents a
LEFT OUTER JOIN gncs ON a.agent_login_id = gncs.Engineer AND
    gncs.date >= DATEADD(day, -2, convert(gncs.date, GETDATE())) --??
GROUP BY a.agent_login_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources