Why would this query sometimes return more than one record for a Description?

See more:

SQL

T-SQL

The Stored Procedure I've created should only return one record for each two week period represented within it, because it first queries data for the first week, and thereafter it uses basically the same query for the second week period, and finally combines the records from the two temp tables they are stored in into a single record in a third temp table (which also adds a few more values).

Yet, even though the above is theoretically what *should* happen, I sometimes get multiple records for the same item.

Also, the value differs for the value in the "WEEK2PRICE" column. How could this be? The Stored Procedure, AFAICT, would not allow such duplication. Here it is:

CREATE Procedure [dbo].[priceAndUsageVariance]
	@Unit varchar(25),
	@BegDate datetime,
	@EndDate datetime
AS 

DECLARE @Week1End datetime = DATEADD(Day, 6, @BegDate);
DECLARE @Week2Begin datetime = DATEADD(Day, 7, @BegDate);

// temp1 holds some values for the first week
CREATE TABLE #TEMP1
(
	MemberNo VARCHAR(6),
	MemberName VARCHAR(50),
	MEMBERITEMCODE VARCHAR(25),
	DESCRIPTION VARCHAR(50),
	WEEK1USAGE DECIMAL(18,2),
	WEEK1PRICE DECIMAL(18,2)
);

INSERT INTO #TEMP1 (MemberNo, MemberName, MEMBERITEMCODE, DESCRIPTION, WEEK1USAGE, 

WEEK1PRICE)
SELECT INVD.MEMBERNO, MemberName, ITEMCODE, DESCRIPTION, SUM(QTYSHIPPED), PRICE 
FROM INVOICEDETAIL INVD
JOIN MEMBERS M ON INVD.MEMBERNO = M.MEMBERNO
WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @BEGDATE AND @Week1End
GROUP BY ITEMCODE, DESCRIPTION, PRICE, INVD.MEMBERNO, MemberName

// temp2 holds some values for the second week
CREATE TABLE #TEMP2
(
	MemberNo VARCHAR(6),
	MemberName VARCHAR(50),
	MEMBERITEMCODE VARCHAR(25),
	DESCRIPTION VARCHAR(50),
	WEEK2USAGE DECIMAL(18,2),
	WEEK2PRICE DECIMAL(18,2)
);

INSERT INTO #TEMP2 (MemberNo, MemberName, MEMBERITEMCODE, DESCRIPTION, WEEK2USAGE, 

WEEK2PRICE)
SELECT INVD.MEMBERNO, MemberName, ITEMCODE, DESCRIPTION, SUM(QTYSHIPPED), PRICE 
FROM INVOICEDETAIL INVD
JOIN MEMBERS M ON INVD.MEMBERNO = M.MEMBERNO
WHERE UNIT=@UNIT AND INVOICEDATE BETWEEN @Week2Begin AND @ENDDATE
GROUP BY ITEMCODE, DESCRIPTION, PRICE, INVD.MEMBERNO, MemberName

// Now tempCombined gets the shared values from temp1 as well as the unique vals from temp1 and the unique vals from temp2
CREATE TABLE #TEMPCOMBINED(
  MemberNo VARCHAR(6),
  MemberName VARCHAR(50),
  ItemCode VARCHAR(15),
  MemberItemCode VARCHAR(20),
  PlatypusDESCRIPTION VARCHAR(50),
  MEMBERDESCRIPTION VARCHAR(200),
  WEEK1USAGE DECIMAL(18,2),
  WEEK2USAGE DECIMAL(18,2),
  USAGEVARIANCE AS WEEK2USAGE - WEEK1USAGE,
  WEEK1PRICE DECIMAL(18,2),
  WEEK2PRICE DECIMAL(18,2),
  PRICEVARIANCE AS WEEK2PRICE - WEEK1PRICE,
  PRICEVARIANCEPERCENTAGE AS CAST((WEEK2PRICE - WEEK1PRICE) / NULLIF(WEEK1PRICE,0) AS DECIMAL

(18,5))
);

INSERT INTO #TEMPCOMBINED (MemberNo, MemberName, ItemCode, MemberItemCode, 

PlatypusDESCRIPTION, MEMBERDESCRIPTION,
WEEK1USAGE, WEEK2USAGE, WEEK1PRICE, WEEK2PRICE)
SELECT T1.MemberNo, T1.MemberName, 'X', T1.MEMBERITEMCODE, NULL, T1.DESCRIPTION, 

T1.WEEK1USAGE, T2.WEEK2USAGE, 
T1.WEEK1PRICE, T2.WEEK2PRICE
FROM #TEMP1 T1
LEFT JOIN #TEMP2 T2 ON T1.MEMBERITEMCODE = T2.MEMBERITEMCODE

	// Now some mumbo-jumbo is performed to display the "general" description rather than the "localized" description
	UPDATE #TEMPCOMBINED SET ItemCode = ISNULL(
	(SELECT TOP 1 ItemCode 
	 FROM MasterMemberUnitMapping 
	 WHERE Unit=@Unit
	 AND MemberNo=#TEMPCOMBINED.MemberNo 
	 AND MemberItemCode = #TEMPCOMBINED.MemberItemCode 
	 AND ItemCode IN (SELECT ItemCode FROM UnitProducts WHERE Unit=@Unit)),'X'
	)
	WHERE ItemCode='X'

	UPDATE #TEMPCOMBINED SET ItemCode = ISNULL(
	(SELECT TOP 1 ItemCode FROM MasterMemberMapping WHERE 
	 MemberNo=#TEMPCOMBINED.MemberNo AND MemberItemCode + PackType = 

#TEMPCOMBINED.MemberItemCode ),'X'
	) 
	WHERE ItemCode='X'

	UPDATE #TEMPCOMBINED SET PlatypusDESCRIPTION = ISNULL(MP.Description,'')
	FROM #TEMPCOMBINED TC 
	INNER JOIN MasterProducts MP ON MP.Itemcode=TC.ItemCode

// finally, what is hoped to be the desired amalgamation is returned
SELECT TC.PlatypusDESCRIPTION, TC.MemberName, TC.WEEK1USAGE, TC.WEEK2USAGE, 

TC.USAGEVARIANCE,
TC.WEEK1PRICE, TC.WEEK2PRICE, TC.PRICEVARIANCE, TC.PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED TC
ORDER BY TC.PlatypusDESCRIPTION, TC.MemberName;

So how can it be that I'm getting back duplicate records for a given week, and that the values are generally, but not universally duplicated, between those quasi-redundant records?