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
  2. Adriaan New Member

    The magic word is JOIN. Read about building queries in Books Online.
  3. satya Moderator

  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...
  5. sqlderby Member

    Any help?
  6. FrankKalis Moderator

    Can you please post the query you have so far?
  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....
  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.
  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)

Share This Page