Combining SQL Server table data

Question

I have tried many options to combine SQL Server data, some of it I can get to work, but the data isn't right.

I have user, stats and results tables. I have taken the data from the first two table and combined them and the information is fine;

    create table #statsTable(
s_fullname char(45),               <--Here
s_dialdate smalldatetime,
s_campaign char(3),
s_calls int,
s_holdtime int,
s_talktime int,
s_wrapuptime int ,
s_dialtime int ,
s_pitches int ,
s_agent char(3) ,                 <-- to here, insert fine
s_samount decimal(20,2),          <--Here
s_damount decimal(10,2) ,
s_upamount numeric (10,0),
s_mdamount decimal (12,2))        <--to here, uses a separate query, not so much

I have tried using joins etc but nothing works, what seems to happen is the last four values seem to be combined im not sure but they are now right. The following inserts the first part of the above table;

   INSERT  INTO #statsTable (s_fullname, s_agent, s_calls, 
            s_holdtime, s_talktime, s_wrapuptime, s_pitches, s_dialtime, s_campaign,
            s_dialdate)
      SELECT
         agent.name, agent.code, calls, holdtime, 
         talktime, wrapuptime, pitches, dialtime, campaign,
         dialdate  
      FROM stats, agent 
      WHERE 
         agent.code LIKE stats.agent 
         AND dialdate = '02-27-2013'

The next part of it is where the trouble starts, no matter if I try to join or use an insert or update query, the last four fields get jumbled.

The 3 tables that I'm pulling data from look like so;

    agent
       name (full name)
       code (3 char ID)

    stats
       dialdate
       agent      (3 char ID)
       campaign   (3 char ID)
       calls      (number of calls)
       holdtime
       talktime 
       wrapuptime
       dialtime
       pitches

    results
       lcdate  (date last call was made)
       campaginid  (3 char ID)
       sale        (overall sale amount)
       donation    (donation amount)
       up_sale     (up-sale amount)
       md_amount   (not sure its purpose, but a decimal none the less)

There is obviously more data in each of these tables but this is the only relevant data as it pertains to the final output.

Thanks in advance

Answer 1

Can you join through the three tables or do you get duplication of records if you do something like this?...

INSERT  INTO #statsTable 
            (
            s_fullname, 
            s_agent, 
            s_calls, 
            s_holdtime, 
            s_talktime, 
            s_wrapuptime, 
            s_pitches, 
            s_dialtime, 
            s_campaign,
            s_dialdate,
            s_upamount  --<<new
            )
SELECT      agent.name, 
            agent.code, 
            calls, 
            holdtime, 
            talktime, 
            wrapuptime, 
            pitches, 
            dialtime, 
            campaign,
            dialdate,
            r.up_sale     --<<like this?  
FROM    "stats" s
            INNER JOIN agent a 
                ON s.agent = a.code
            INNER JOIN results r 
                ON s.campagin  = r.campaginid  
WHERE   dialdate = '02-27-2013';


SELECT * 
FROM #statsTable;