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
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.Code: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
I know I'm not joining them right... any ideas?
