Click here to Skip to main content
15,936,119 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have these Insert statements in a Stored Procedure:

SQL
INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price)
select MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @BegDate AND @Week1End;

INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week2Usage, Week2Price)
select MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate;


They get the right data, but each row has (of course) blank values for either Week2Usage and Week2Price (the rows inserted with the first block) or blank values for Week1Usage and Week1Price (the rows inserted with the second block).

How can I combine these two statements so that one Insert will populate all four of those fields, with no empty "WeekN" values?

I thought maybe something like this work:

SQL
INSERT INTO #temp1 (MemberNo, MemberItemCode, PADescription, Week1Usage, Week2Usage, Week1Price, Week2Price) 
    select MEMBERNO, ITEMCODE, DESCRIPTION, 
    (select QTYSHIPPED FROM INVOICEDETAIL WHERE UNIT=@Unit AND INVOICEDATE BETWEEN @BegDate AND @Week1End), 
    (select QTYSHIPPED FROM INVOICEDETAIL WHERE UNIT=@Unit AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate), 
    (select PRICE FROM INVOICEDETAIL WHERE UNIT=@Unit AND INVOICEDATE BETWEEN @BegDate AND @Week1End), 
    (select PRICE FROM INVOICEDETAIL WHERE UNIT=@Unit AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate), 
    FROM INVOICEDETAIL 
    WHERE UNIT=@Unit


...but it gives me:

Msg 512, Level 16, State 1, Procedure priceVarianceTest, Line 39
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


Is there a workaround for this, or do I need to create another temp table and then populate a new table combining those values, or...?
Posted
Updated 15-Jan-16 5:16am
v2

SQL
INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price)
select MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND ((INVOICEDATE BETWEEN @BegDate AND @Week1End) OR (INVOICEDATE BETWEEN @Week2Begin AND @EndDate))
 
Share this answer
 
v2
Comments
B. Clay Shannon 15-Jan-16 11:19am    
Thanks, but that won't work, because the Week2 values are never populated. It's not a simple "OR" situation, otherwise I could make the conditional part "(INVOICEDATE BETWEEN @BegDate AND @EndDate)"
TenmanS14 15-Jan-16 11:48am    
is there a value which will be common between them and unique for each final record, rather than them just being 2 different weeks of data? i.e. could you only have one memberno per week or one of each unit per week?

INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price, week2Usage, week2Price)
select InvD1.MEMBERNO, InvD1.ITEMCODE, InvD1.DESCRIPTION, InvD1.QTYSHIPPED, InvD1.PRICE, InvD2.qtyshipped, InvD2.price
FROM INVOICEDETAIL as InvD1
join INVOICEDETAIL as InvD2 on InvD1.unit = InvD2.unit
WHERE InvD1.UNIT=@Unit
AND (INVOICEDATE BETWEEN @BegDate AND @Week1End)
B. Clay Shannon 15-Jan-16 12:01pm    
That helps, thanks.
TenmanS14 18-Jan-16 6:41am    
LOL, apologies for that, had been in the pub for lunch before I wrote it... on the right lines though I think, I suspect you could achieve what you want with a self join..
As an alternative to solution 1, you can also use simply UNION operation. In other words
SQL
INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, Week1Usage, Week1Price)
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM  INVOICEDETAIL
WHERE UNIT=@Unit
AND   INVOICEDATE BETWEEN @BegDate AND @Week1End
UNION ALL
SELECT MEMBERNO, ITEMCODE, DESCRIPTION, QTYSHIPPED, PRICE
FROM  INVOICEDETAIL
WHERE UNIT=@Unit
AND   INVOICEDATE BETWEEN @Week2Begin AND @EndDate;


Correction concerning the columns
SQL
INSERT INTO #temp1 (MemberNo, MemberItemCode, NCDescription, 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;
 
Share this answer
 
v2
Comments
B. Clay Shannon 15-Jan-16 12:46pm    
Looks very promising; I'll try that, too.
I looked at it a little closer, and see that it doesn't populate Week2Usage and Week2Price, so that won't work.
Wendelius 15-Jan-16 14:58pm    
Sorry didn't notice that you used different columns in the inserts. Now fixed.
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).

SQL
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)
)

-- populate week1 vals
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)
)

-- populate week2 vals
INSERT INTO #temp2 (MemberNo, Week2Usage, Week2Price)
select MEMBERNO, QTYSHIPPED, PRICE
FROM INVOICEDETAIL
WHERE UNIT=@Unit
AND INVOICEDATE BETWEEN @Week2Begin AND @EndDate;

-- now I have some records with week1 vals, and some with week2 vals; combine them into a third table

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;
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900