>CREATE TABLE JobMaster (JobNumber int, JobDescription varchar(50)) > >CREATE TABLE JobHours (JobNumber int, EmployeeID int, DateWorked DateTime, > HoursWorked decimal(10,2)) > >CREATE TABLE JobMaterials (JobNumber int, Notes varchar(50), > purchaseamount decimal(14,2), > PurchaseDate DateTime) >>
>insert into JobMaster values (1, 'Job A') >insert into JobMaster values (2, 'Job B') >insert into JobMaster values (3, 'Job C') > >INSERT INTO JobHours values (1, 1, '01-01-08',8) >INSERT INTO JobHours values (1, 1, '01-02-08',8) >INSERT INTO JobHours values (1, 2, '01-01-08',7) >INSERT INTO JobHours values (1, 2, '01-02-08',7) > > >INSERT INTO JobHours values (2, 3, '01-01-08',7) >INSERT INTO JobHours values (2, 3, '01-02-08',7) >INSERT INTO JobHours values (2, 4, '01-01-08',6) >INSERT INTO JobHours values (2, 4, '01-02-08',6) > > >insert into JobMaterials values (1, 'bought tractor',1000,'1-1-08') >insert into JobMaterials values (1, 'bought cement',2000,'1-1-08') > >insert into JobMaterials values (3, 'bought tractor',10000,'1-1-08') >insert into JobMaterials values (3, 'bought cement',20000,'1-1-08') >>
>-- Tot Hrs Worked total of Material purchases >--Job A 30 3,000 >--Job B 26 null >--Job C null 30,000 >>
CREATE TABLE JobMaster (JobNumber int, JobDescription varchar(50)) CREATE TABLE JobHours (JobNumber int, EmployeeID int, DateWorked DateTime, HoursWorked decimal(10,2)) CREATE TABLE JobMaterials (JobNumber int, Notes varchar(50), purchaseamount decimal(14,2), PurchaseDate DateTime) insert into JobMaster values (1, 'Job A') insert into JobMaster values (2, 'Job B') insert into JobMaster values (3, 'Job C') INSERT INTO JobHours values (1, 1, '01-01-08',8) INSERT INTO JobHours values (1, 1, '01-02-08',8) INSERT INTO JobHours values (1, 2, '01-01-08',7) INSERT INTO JobHours values (1, 2, '01-02-08',7) INSERT INTO JobHours values (2, 3, '01-01-08',7) INSERT INTO JobHours values (2, 3, '01-02-08',7) INSERT INTO JobHours values (2, 4, '01-01-08',6) INSERT INTO JobHours values (2, 4, '01-02-08',6) insert into JobMaterials values (1, 'bought tractor',1000,'1-1-08') insert into JobMaterials values (1, 'bought cement',2000,'1-1-08') insert into JobMaterials values (3, 'bought tractor',10000,'1-1-08') insert into JobMaterials values (3, 'bought cement',20000,'1-1-08') select JobMaster.*, cast(ISNULL(JM.Bought,0) as int) as MaterialsBought, CAST(isnull(JH.HoursWorked, 0) as int) as HoursWorked from JobMaster LEFT JOIN (select JobNumber, SUM(purchaseamount) as Bought from JobMaterials group by JobNumber ) JM on JobMaster.JobNumber = JM.JobNumber LEFT JOIN (select JobNumber, SUM(HoursWorked) as HoursWorked from JobHours group by JobNumber) JH on JobMaster.JobNumber = JH.JobNumber