Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Posts
    51

    Unanswered: 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 PM

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149
    Try 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

  3. #3
    Join Date
    Jan 2004
    Posts
    51
    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 )

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest:
    Code:
    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

  5. #5
    Join Date
    Jan 2004
    Posts
    51
    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?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  8. #8
    Join Date
    Jan 2004
    Posts
    51
    pat check your pm

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    From the PM, I got:
    Quote Originally Posted by Densma
    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 Manager
    There was also some informaiton that pointed back into this thread too. Moving on, I'd suggest:
    Code:
    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

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jan 2004
    Posts
    51
    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 ...

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    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

  13. #13
    Join Date
    Jan 2004
    Posts
    51
    thanks pat
    that works without error

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •