Thanks to TenmanS14 for his idea, the following works (although the data is not right yet, at least it is combining the week1 and week2 vals).
create table #temp1
(
MemberNo varchar(25),
MemberItemCode varchar(25),
ShortName varchar(25),
ItemCode varchar(50),
WVItem varchar(25),
WVItemCode varchar(25),
WVDescription varchar(250),
Week1Usage varchar(25),
Week2Usage varchar(25),
UsageVariance varchar(25),
Week1Price varchar(25),
Week2Price varchar(25),
PriceVariance varchar(25),
PercentageOfPriceVariance varchar(25)
)
INSERT INTO #temp1 (MemberNo, MemberItemCode, WVDescription, Week1Usage, Week1Price)
select MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @BegDate AND @Week1End;
create table #temp2
(
MemberNo varchar(25),
Week2Usage varchar(25),
Week2Price varchar(25)
)
INSERT INTO #temp2 (MemberNo, Week2Usage, Week2Price)
select MEMBERNO, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate;
create table #tempCombined
(
MemberNo varchar(25),
MemberItemCode varchar(25),
ShortName varchar(25),
ItemCode varchar(50),
WVItem varchar(25),
WVItemCode varchar(25),
WVDescription varchar(250),
Week1Usage varchar(25),
Week2Usage varchar(25),
UsageVariance varchar(25),
Week1Price varchar(25),
Week2Price varchar(25),
PriceVariance varchar(25),
PercentageOfPriceVariance varchar(25)
)
INSERT INTO #tempCombined (MemberNo, MemberItemCode, WVDescription, Week1Usage, Week1Price, week2Usage, week2Price)
select t1.MEMBERNO, t1.ITEMCODE, t1.WVDESCRIPTION, t1.Week1Usage, t1.Week1Price, t2.Week2Usage, t2.Week2Price
FROM #temp1 as t1
join #temp2 as t2 on t1.MemberNo = t2.MemberNo
select * from #tempCombined ORDER BY WVDescription, Week1Usage DESC;