I have this code:
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),
Week1Price varchar(25)
)
INSERT INTO #temp1 (MemberNo, MemberItemCode, WVItemCode, WVDescription, Week1Usage, Week1Price)
select MemberNo, ItemCode, WVItemCode, Description, QtyShipped, Price
FROM InvoiceDetail
WHERE Unit=@Unit
AND InvoiceDate BETWEEN @BegDate AND @Week1End;
...which successfully populates the MemberItemCode column.
However, if I later attempt to update the WVItemCode column like so:
- this should either update WVItemCode or set it to 'X'
Update #temp1 set
WVItemCode = isnull((Select top 1 ItemCode From MasterMemberUnitMapping where Unit=@Unit and
MemberNo=#temp1.MemberNo and MemberItemCode= #temp1.MemberItemCode and ItemCode in (Select ItemCode from UnitProducts where
Unit=@Unit)), 'X')
- this should either update WVItemCode or set it back to #temp1.MemberItemCode
Update #temp1 set
WVItemCode = isnull((Select top 1 ItemCode From MasterMemberMapping where MemberNo=#temp1.MemberNo and MemberItemCode +
PackType = #temp1.MemberItemCode), #temp1.MemberItemCode)
Where WVItemCode='X'
...all the wvItemCode vals are NULL. Shouldn't they always be either their original values or 'X'?
The way I understand the Update code, the first update should either update WVItemCode or set it to 'X' and then the second update should update it if it is 'X' or else set it back to the original #temp1.MemberItemCode value.
I also tried appending the following:
OR WVItemCode=#tempCombined.MemberItemCode
...so that the final Where clause was:
Where WVItemCode='X' OR WVItemCode=#temp1.MemberItemCode
...but that still leaves me with nothing but Null MemberItemCode vals. How are these updates nullifying that column? When I omit them, I get the MemberItemCode vals.
UPDATE
I can't find it now, but somebody recommended a UNION, and I used it. However:
I was hoping the UNION would work:
DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate);
DECLARE @Week2Begin datetime = DATEADD(Day, 7, @BegDate);
create table #temp1
(
MemberNo varchar(25),
MemberItemCode varchar(25),
ShortName varchar(25),
ItemCode varchar(50),
PAItem varchar(25),
PAItemCode varchar(25),
PADescription varchar(250),
Week1Usage varchar(25),
Week1Price varchar(25),
Week2Usage varchar(25),
Week2Price varchar(25)
)
INSERT INTO #temp1 (MemberNo, MemberItemCode, PADescription, Week1Usage, Week1Price, Week2Usage, Week2Price)
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE, NULL, NULL
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @BegDate AND @Week1End
UNION ALL
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, NULL, NULL, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate;
...and it first it seemed like it did, but then I noticed that each row in the result set always has two NULL values - first the two Week2 values, then the two Week1 Values, like this:
Week 1 Usage Week 1 Price Week 2 Usage Week 2 Price
------------ ------------ ------------ ------------
1.000 163.530 NULL NULL
NULL NULL 1.000 163.530
What I need is:
Week 1 Usage Week 1 Price Week 2 Usage Week 2 Price
------------ ------------ ------------ ------------
1.000 163.530 1.000 163.530
Is there a way to do that with UNION, or do I need to go back to my 3-table idea (a temp table for week1, another for week2, and a third to combine them)?