With just a little thought I came up with the following two solutions:
set nocount on;
CREATE TABLE dbo.CodeIDTable1 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
);
CREATE TABLE dbo.CodeIDTable2 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
);
CREATE TABLE dbo.CodeIDTable3 (
CodeID INT NOT NULL
,Code CHAR(1) NOT NULL
);
INSERT dbo.CodeIDTable1 VALUES (1, 'A');
INSERT dbo.CodeIDTable1 VALUES (2, 'B');
INSERT dbo.CodeIDTable1 VALUES (3, 'C');
INSERT dbo.CodeIDTable2 VALUES (4, 'D');
INSERT dbo.CodeIDTable2 VALUES (5, 'E');
INSERT dbo.CodeIDTable2 VALUES (6, 'F');
INSERT dbo.CodeIDTable3 VALUES (7, 'G');
INSERT dbo.CodeIDTable3 VALUES (8, 'H');
INSERT dbo.CodeIDTable3 VALUES (9, 'I');
CREATE TABLE dbo.Details (
HeaderID INT NOT NULL
,DetailID INT NOT NULL
,CodeID INT NOT NULL
,EmployeeID INT NOT NULL
,Date Datetime NOT NULL
,Amount DECIMAL (18,2) NOT NULL
);
CREATE TABLE dbo.Header (
HeaderID INT NOT NULL
,EmployeeID INT NOT NULL
,HeaderDate DATETIME NOT NULL
);
INSERT dbo.Header VALUES (1, 1, '2013-01-01');
INSERT dbo.Header VALUES (2, 1, '2013-01-01');
INSERT dbo.Header VALUES (3, 1, '2013-01-01');
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 1, 1, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 2, 1, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (1, 3, 1, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 4, 5, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 5, 5, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 6, 5, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (2, 7, 6, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 8, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 9, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 10, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 11, 7, 1, '2013-01-01', 1.00);
INSERT dbo.Details (HeaderID, DetailID, CodeID, EmployeeID, Date, Amount) VALUES (3, 12, 7, 1, '2013-01-01', 1.00);
--drop TABLE EmployeeSummary
CREATE TABLE dbo.EmployeeSummary (
EmployeeID INT NOT NULL
,Code1PeriodToDate DECIMAL(18,2) NULL
,Code1YearToDate DECIMAL(18,2) NULL
,Code2PeriodToDate DECIMAL(18,2) NULL
,Code2YearToDate DECIMAL(18,2) NULL
,Code3PeriodToDate DECIMAL(18,2) NULL
,Code3YearToDate DECIMAL(18,2) NULL
);
DECLARE @FirstDayOfYear DATETIME = '2013-01-01', @LastDayOfYear DATETIME = '2013-12-31';
DECLARE @ReportStartingDate DATETIME = '2013-01-01', @ReportEndingDate DATETIME = '2013-12-31';
with Codes as (
select
1 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable1
union all
select
2 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable2
union all
select
3 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable3
)
insert into dbo.EmployeeSummary
select
h.EmployeeID,
sum(case when c.CodeTableID = 1 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountPeriod,
sum(case when c.CodeTableID = 1 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountYTD,
sum(case when c.CodeTableID = 2 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountPeriod,
sum(case when c.CodeTableID = 2 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountYTD,
sum(case when c.CodeTableID = 3 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountPeriod,
sum(case when c.CodeTableID = 3 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountYTD
from
dbo.Header h
inner join dbo.Details d
on (h.HeaderID = d.HeaderID)
inner join Codes c
on (c.CodeID = d.CodeID)
group by
h.EmployeeID;
select * from dbo.EmployeeSummary;
TRUNCATE TABLE dbo.EmployeeSummary;
INSERT EmployeeSummary (EmployeeID) VALUES (1);
with Codes as (
select
1 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable1
union all
select
2 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable2
union all
select
3 as CodeTableID,
CodeID,
Code
from
dbo.CodeIDTable3
),SummaryData as (
select
h.EmployeeID,
sum(case when c.CodeTableID = 1 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountPeriod,
sum(case when c.CodeTableID = 1 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code1AmountYTD,
sum(case when c.CodeTableID = 2 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountPeriod,
sum(case when c.CodeTableID = 2 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code2AmountYTD,
sum(case when c.CodeTableID = 3 and h.HeaderDate BETWEEN @ReportStartingDate AND @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountPeriod,
sum(case when c.CodeTableID = 3 and h.HeaderDate <= @ReportEndingDate THEN d.Amount else 0 end) AS Code3AmountYTD
from
dbo.Header h
inner join dbo.Details d
on (h.HeaderID = d.HeaderID)
inner join Codes c
on (c.CodeID = d.CodeID)
group by
h.EmployeeID)
update es set
Code1PeriodToDate = sd.Code1AmountPeriod,
Code1YearToDate = sd.Code1AmountYTD,
Code2PeriodToDate = sd.Code2AmountPeriod,
Code2YearToDate = sd.Code2AmountYTD,
Code3PeriodToDate = sd.Code3AmountPeriod,
Code3YearToDate = sd.Code3AmountYTD
from
dbo.EmployeeSummary es
inner join SummaryData sd
on es.EmployeeID = sd.EmployeeID;
select * from dbo.EmployeeSummary;
go
drop table dbo.Header;
drop table dbo.Details;
drop table dbo.CodeIDTable1;
drop table dbo.CodeIDTable2;
drop table dbo.CodeIDTable3;
drop table dbo.EmployeeSummary;
go