Add record/s from two tables to a third table: MS SQL Server 2005

Question

Hi, I want to add a record/s from two tables to a third table. It is something like the following:

table1: pln

taskName   plnHec      pinDate(mm/dd/yyyy)
xx          10            3/1/2008 
yy          20            4/1/2008
zz          10            3/1/2008
zz          10            4/1/2008
xx          10            4/1/2008

table2 : actual

taskName          actHec           acDate
xx                  9              4/1/2008
yy                 20              4/1/2008
ww                 10              4/1/2008

table3 : performance

taskName       pdate        plnHec     actHec     cumulativepln  cumulativeact
xx             4/1/2008       10         9           20              9
yy             4/1/2008       20        20           20             20
ww             4/1/2008       0         10           0              10

I am using MS SQL Server 2005. Can anyone help me in solving this problem?

Answer 1

If I understand what your trying to do (Which isn't to clear)

insert into performance (taskname,pdate,plnhec,acthec,cumaltivepin,cumaltiveact)

select actual.taskname,max(pindate),acthec,plnhec, sum(plnhec),sm(acthec)
from actual
left join pl on actual.taskname=pln.taskname
group by taskname,acthec

This assumes acthec is the same for all tasknames, otherwise you'll need to pick a rule such as min or max. Otherwise youll get two rows in performance.

Answer 2

INSERT
INTO   performance
SELECT taskname, acDate,
       (
       SELECT  TOP 1 plnHeс
       FROM    pln pi
       WHERE   pi.taskName = ao.taskName
               AND pi.pinDate = ao.acDate
       ),
       actHec. SUM(plnHec), actHec
FROM   actual ao
JOIN   pln po
ON     po.taskName = ao.taskName
GROUP BY
       ao.taskName

Answer 3

Maybe I'm wrong here but I guess you want to use the Performance table here as a kind of summary of the other tables?

E.g. cumulativepnl, cumulativeact are SUM values of the plnHec and actHec columns in the other tables.

Do you really need this to be a new table as the summary data can all be extracted from the 2 original tables via SQL (stored procedure or perhaps a View).

If you really need to replicate the data, I would use something like Josh's query.

Answer 4

Only select part, assuming that pdate=max(pinDate) and pln/actHec fields need to be latest values and some act rows can be missing:

select 
    p.taskName, 
    max(p.pinDate) as pdate, 
    (select top 1 plnHec from pln where taskName=p.taskName order by pinDate desc) as plnHec,
    isnull((select top 1 actHec from actual where taskName=p.taskName order by acDate desc),0) as plnHec,
    sum(isnull(a.actHec,0)) as cumulativeact,
    sum(p.plnHec) as cumulativepln
from
    pln p left join actual a on a.taskName=p.taskName
group by
    p.taskName