SQL Server Performance

T-SQL - Need Help

Discussion in 'SQL Server 2005 General Developer Questions' started by sqlderby, Jul 10, 2010.

  1. sqlderby Member

    Hi All
    I have following data and require result set but facing problem...as my query showing 28 rows...
    Table 1:

    DT FirstName LastName Extension CADID
    07/07/2010 11:48:57 Ason Asai 201216 31364 452395
    07/07/2010 11:48:59 Ason Asai 201216 31364 452395
    07/07/2010 13:57:54 Ason Asai 201216 31364 452395
    07/07/2010 18:30:03 Ason Asai 201216 31364 452395
    07/07/2010 14:37:22 Ason Asai 201216 31364 452395
    07/07/2010 09:27:11 Ason Asai 201216 31364 452395
    07/07/2010 10:20:46 Ason Asai 201216 31364 452395
    Table 2:

    CadID Extension AgentState TimeStamp_PST
    452395 31364 Ready to Talking 07/07/2010 11:48:32
    452395 31364 Ready to Talking 07/07/2010 11:48:34
    452395 31364 Ready to Talking 07/07/2010 13:57:29
    452395 31364 Ready to Talking 07/07/2010 14:36:56
    Result set should be....

    DT FirstName LastName Extension CADID AgentState TimeStamp_PST
    07/07/2010 11:48:57 Ason Asai 201216 31364 452395 Ready to Talking 07/07/2010 11:48:32
    07/07/2010 11:48:59 Ason Asai 201216 31364 452395 Ready to Talking 07/07/2010 11:48:34
    07/07/2010 13:57:54 Ason Asai 201216 31364 452395 Ready to Talking 07/07/2010 13:57:29
    07/07/2010 18:30:03 Ason Asai 201216 31364 452395


    07/07/2010 14:37:22 Ason Asai 201216 31364 452395 Ready to Talking 07/07/2010 14:36:56
    07/07/2010 09:27:11 Ason Asai 201216 31364 452395


    07/07/2010 10:20:46 Ason Asai 201216 31364 452395


    Pls help ...Thanks
    sqlderby, Jul 10, 2010
    #2

  2. Adriaan New Member

    The magic word is JOIN. Read about building queries in Books Online.
    Adriaan, Jul 11, 2010
    #3

  3. satya Moderator

    satya, Jul 12, 2010
    #4

  4. sqlderby Member

    Thanks Guys.... But it is not that straight. you see I need to combined these tables records for matching CADIDs as well as TimeStamp_PST column records should be to nearest enough to DT column (date) records. These 4 records (TimeStamp_PST) are against to the nearest date in DT column.
    Plus all are same CADIDs and TimeStamp_PST and DT column data would never be same date and time
    Hence I could say that dateadd with -30 and 30 secs of DT date column for TimeStamp_PST column date but I couldn't able to get the same result set as I have described in my question...
    So pls help me...
    Thanks...
    sqlderby, Jul 15, 2010
    #5

  5. sqlderby Member

    Any help?
    sqlderby, Jul 28, 2010
    #6

  6. FrankKalis Moderator

    Can you please post the query you have so far?
    FrankKalis, Jul 28, 2010
    #7

  7. sqlderby Member

    I have use this query....and it's work although I not satisfied...
    select [Datetime], FirstName, LastName, Extension,CADID,CDCount,
    AgentState,max(TIMESTAMP_PST) as TIMESTAMP_PST
    FROM @CD_AS
    GROUP BY [Datetime], FirstName, LastName, Extension,CADID,CDCount, AgentState
    @CD_AS var table in which I have inserting main all of data...
    I still prefer, if I can do this with one query as I have requested in the post earlier...
    sqlderby....
    sqlderby, Aug 2, 2010
    #8

  8. satya Moderator

    See my suggestions on that post, its easy to script out the data for record purpose and reapply with a single-shot of TRUNCATE & re-apply.
    satya, Aug 2, 2010
    #9

  9. ashish287 New Member

    if you getting 28 results it mean yous using full join which is 7*4 = 28 which means matching everything with everything :).
    So review your filters and put the proper join(inner/let/right joins)
    ashish287, Jul 11, 2010
    #10

Share This Page