jOIN Query help(urgent)

i want to create a join query (for view) that will show one data per day for each agent.
just a select query joing these two tables..Seems like the date field is given me problem
i want result like below

TOTALCALL , TOTALESCA , AGENTID , DATE
50 , 5 , IDME1 , 10/28/2004 12:28:00 PM

TOTALESCA shows NuMbers of escalated calls out of totalcalls

table 1

TOTALCALL
AGENTID
DATE

TABLE 2

TOTALESCA
AGENTID
DATE

SAMPLE DATA ON TABLE 1

TOTALCALL, AGENTID, DATE
50 , IDME1 , 10/28/2004 12:28:00 PM

SAMPLE DATA ON TABLE 2

TOTALESCA, AGENTID , DATE
5 , IDME1 , 10/28/2004 12:28:00 PMTry datepart function to ignore the time part in your query and try it. If you still have issue, publish the query you have and I could help|||gives me inaccurate results and date column on select shows wrong data's.

possible unique data is agent id and date (wihout time messed), if i join with agent id then it will filter out table 2
Note.. There's some days agent wont escalate anycall and so therefore no record on table2, but has record on table1.
i want a query that will still show 0 on TOTALESCA column even if agent didnt escalate any call on that day.
Begining to think this is not possible with query
any idea?
eg below

TOTALCALL , TOTALESCA , AGENTID , DATE
30, 0 , IDME1 , 10/28/2004

SELECT table1.TOTALCALL, table2.TOTALESCA,
table2.[agent Id],
datepart(day,table1.Date)
FROM dbo.totalcall table1,
dbo.totalEsc table2
WHERE datepart(day,table1.Date)=datepart(day,table2.Date )|||I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||pat your query work like charm

but i get this message too
Null value is eliminated by an aggregate or other SET operation.

what does that means?|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.

-PatP|||It means that you've got rows in one table that aren't matched in the other... Either somebody had no calls escalated (which I'd expected) or they escalated calls that they never got (which would worry me). As there is a perfectly reasonable explanation, I wouldn't get worked up about the message.

-PatP|||pat check your pm|||From the PM, I got:hey pat,
can you help me join this query with another table? the query i got from you yesterday.
there's one more table that has agent name, agent id, supervisor and manager. agent id is unique. i want the same result on this query but now to show agent name, agent id, supervisor and manager. let's say this is table3 and has this columns

agent_ID NVARCHAR(20),
SUP_LAST NVARCHAR(25),
SUP_FRST NVARCHAR(25),
MGR_LAST NVARCHAR(255),
MGR_FRST NVARCHAR(255)

i will love to concenate like
EMP_FRST+' '+EMP_LAST) AS Agent,
(SUP_FRST+' '+SUP_LAST) AS Supervisor,
(MGR_FRST+' '+MGR_LAST) AS ManagerThere was also some informaiton that pointed back into this thread too. Moving on, I'd suggest:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(TOTALCALL) AS DAY_CALLS
, Sum(TOTALESCA) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)-PatP|||To eliminate the error do either:

set ansi_warnings off

or

, Sum(isnull(TOTALCALL, 0)) AS DAY_CALLS
, Sum(isnull(TOTALESCA, 0)) AS DAY_ESCA|||thanks
i had this error
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.EMP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.SUP_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_FRST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.MGR_LAST' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

but i fixed it when i added all column to groupby
rdjabarov
set ansi_warnings off works but i want to use the query to create view.
where should i put it
im getting error when i try

ALTER VIEW myview
as
set ansi_warnings off
SELECT...

or

ALTER VIEW myview
set ansi_warnings off
as

SELECT ...|||I would recommend that you avoid changing settings to suppress messages. That has always been a receipe for disaster for me. You could also suppress them using:SELECT Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, Sum(Coalesce(TOTALCALL, 0)) AS DAY_CALLS
, Sum(Coalesce(TOTALESCA, 0)) AS DAY_ESCA
, EMP_FRST + ' ' + EMP_LAST AS Agent
, SUP_FRST + ' ' + SUP_LAST AS Supervisor
, MGR_FRST + ' ' + MGR_LAST AS Manager
FROM table1 AS a
FULL JOIN table2 AS b
ON (a.AGENTID = b.AGENTID
AND Convert(CHAR(10), a.[DATE], 121) = Convert(CHAR(10), b.[DATE], 121))
LEFT JOIN table3 AS c
ON (c.AGENTID = Coalesce(a.AGENTID, b.AGENTID))
GROUP BY Coalesce(a.AGENTID, b.AGENTID)
, Convert(CHAR(10), Coalesce(a.[DATE], b.[DATE]), 121)
, EMP_FRST, EMP_LAST, SUP_FRST, SUP_LAST, MGR_FRST, MGR_LAST-PatP|||thanks pat
that works without error|||The setting needs to be set on the connection that is used to create an object, so it needs to preceed the CREATE statement.

Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!|||Reciepe for disaster? For that matter any T-SQL statement can be viewed as a potential receipe! In addition, relying on default settings is a receipe for disaster in itself!True, but what I meant was that changing settings (of any kind, any where) to make warning messages go away has always proved to be a disaster for me. I didn't mean that you ought to rely on default settings, I meant that changing settings to suppress messages was a receipe for disaster.

I always try find the underlying source of the problem, and correct it or code to ignore the meassage instead of finding ways to suppress the message.

-PatP|||That's why I gave 2 options, SET and ISNULL (which you changed to Coalesce).