messy TSQL v ASP [RSLVD]

[invitro]

Ok here are the tables:

tblMainPurchase
-Parent for tblPurchase. Holds information like total purchase, total tax charged, shipping, etc.

tblPurchase
-child of tblMainPurchase. Holds actuall items of the Purchase. ProductID, Price Charged for Each product, etc.

tblProduct
-Products to purchase

tblPrice
-Price categories for products. Example: tblProduct might have a TV item. tblPrice holds all the price items for that TV: Black 100$, Blue 200$, Green 300$.

Now, all that works great... however, when I get to the reporting part it all breaks down. What I am trying to do is:

Extract information for each product sold. Meaning, I want to make al ist like this:

TV -
Blue - Total Sold Units - 200
Black - Total Sold Units - 15

VCR
Green - Total Units - 20
Green w\accessories - Total Units - 400

...etc.

Here is the query I am using for TSQL

CREATE PROCEDURE sp_getSalesByDay

	@intDay varchar(2), @intMonth varchar(2), @intYear varchar(4)
AS
	DECLARE @sql varchar(1000)


	SET @sql = "SELECT " +

	"tblMainPurchase.guidMainPurchaseID, tblMainPurchase.guidUserID, tblMainPurchase.dblTotalPurchase,
	tblMainPurchase.dblTaxPrice, tblMainPurchase.dblShipping, tblMainPurchase.dblCertificateValue,
	tblMainPurchase.datPurchased," + 

	"tblPurchase.guidPurchaseID, tblPurchase.guidProductID, tblPurchase.guidPriceID, tblPurchase.intQuantity,
	tblPurchase.dblPrice, tblPurchase.datPurchased," + 

	"tblProduct.charName," + 

	"tblPrice.charPriceName " +
	
	" FROM tblMainPurchase " +

	"LEFT JOIN tblPurchase ON tblMainPurchase.guidMainPurchaseID = tblPurchase.guidMainPurchaseID
	
	LEFT JOIN tblProduct ON tblProduct.guidProductID = tblPurchase.guidProductID

	LEFT JOIN tblPrice ON tblProduct.guidProductID = tblPrice.guidProductID  " +
	
	
	"WHERE DATEPART(YEAR, tblPurchase.datPurchased) ='" + @intYear + "' AND " +

	"DATEPART(MONTH, tblPurchase.datPurchased) ='" + @intMonth + "' AND " +

	"DATEPART(DAY, tblPurchase.datPurchased) ='" + @intDay + "' " +

	"ORDER BY tblProduct.charName, tblPurchase.guidPriceID"


	--Order by char name not ID, this way, they display alphabeticaly

	EXEC(@sql)
GO

What I get.. are some wierd results. I get the resuls, but more then I hoped for... I get like 15 results where my test enivornment has only a couple of values in tblMainPurchase and tblPurchase.

I know I'm not joining them right... any ideas?

[invitro]

Ok so the problem lies with
tblPrice

I took out that whole entire part and it works fine.
For some reason it was joining EVERY price category with my tables...

I still need to retrieve tblPrice items for correct price naming..

If we have a VCR, I want to report how many BLUE and ORANGE VCR's it sold.. not just the total.

hmm... any ideas?

[invitro]

ok i just fixed it...

tblPrice was comparing tblPrice.guidProductID not tblPrice.guidPriceID

It works great now...

arent you happy you didnt have to dig into that messy SQL string?

[mendhak]

Originally posted by invitro

arent you happy you didnt have to dig into that messy SQL string?

You bet. I was actually going to turn my monitor on for a change.