Complicated Query by date

[daniel aristidou]

Hi guys,
i hope this question belongs here as i intend to work a solution in sql server which is my back end system rather than my front end system which is VB.

Im trying to create a query that performs a number of calculations on data and returns a table of workers and the amount they sold (in cash) between a certain period of time.

Well here are my relevant tables:

Worker 'Worker table holds direct information ie name & tel
Sale 'Holds information about which worker made the sale, and Which customer
SaleProduct 'This holds the information on which product and how many - it also contains the value of each individual product and how much discount

The operation therefore that needs to take place is:
For each worker:
Find the sales that they made between the dates specified (this is the problem)
Find the relevant SaleProduct for that sale
Multiply the amount of Products by the Price '(this is saved in the Saleproduct Table since prices change) And remove the discount
Add all of the totals up to get a total
Return a row with the workers details and the total sold.

Here is my query:

SELECT        Worker.WName, SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100)) AS SOLD, Worker.WMob, Worker.WID, 
                         Worker.WTel
FROM            Worker INNER JOIN
                         Sale ON Worker.WID = Sale.WID INNER JOIN
                         ProductSale ON Sale.SID = ProductSale.SID
GROUP BY Worker.WName, Worker.WMob, Worker.WID, Worker.WTel

The field that needs to be filtered is Sale.SDate
Also the current query does not return workers that have not made any sales, is there any solution to this?

Any help is greatly appreciated

[ck9663]

On your first question, read this. On your second question, use LEFT JOIN, not INNER.

Happy coding!


--- CK

[daniel aristidou]

Thanks CK Your help is really appreciated, The tips you gave me above helped me greatly.

But following up this question i have another problem i need to merge this table with a similar one. Here are the two codes

SELECT        Worker.WName, Worker.WMob, Worker.WID, Worker.WTel, SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100)) 
                         AS SOLD
FROM            Worker LEFT OUTER JOIN
                         Sale ON Worker.WID = Sale.WID LEFT OUTER JOIN
                         ProductSale ON Sale.SID = ProductSale.SID LEFT OUTER JOIN
                         Reciept ON Worker.WID = Reciept.WID
WHERE        (Sale.SDate BETWEEN @StartDate AND @EndDate)
GROUP BY Worker.WName, Worker.WMob, Worker.WID, Worker.WTel
 
 
And
SELECT        Worker.WID, Worker.WName, Worker.WMob, Worker.WTel, SUM(Reciept.Amount) AS Collected
FROM            Worker LEFT OUTER JOIN
                         Reciept ON Worker.WID = Reciept.WID
WHERE        (Reciept.RDATE BETWEEN @StartDate AND @EndDate)
GROUP BY Worker.WID, Worker.WName, Worker.WMob, Worker.WTel

As you can see the only difference is the aggregate function and the Where statements (i know the where statements are not to blame because removing them produces the same results), however when i merge these two tables i get wrong results in the sums despite getting the correct results when they are run separately. I tried adding Distinct statements before the Reciept.Amount in the sum function but since multiplereciepts have the same amount paid it again resulted in wrong results.
This is my merged code

SELECT        Worker.WName, Worker.WMob, Worker.WID, Worker.WTel, SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100)) 
                         AS SOLD, SUM(Reciept.Amount) AS Collected
FROM            Worker LEFT OUTER JOIN
                         Sale ON Worker.WID = Sale.WID LEFT OUTER JOIN
                         ProductSale ON Sale.SID = ProductSale.SID LEFT OUTER JOIN
                         Reciept ON Worker.WID = Reciept.WID
WHERE        (Sale.SDate BETWEEN @StartDate AND @EndDate) AND (Reciept.RDATE BETWEEN @StartDate AND @EndDate)
GROUP BY Worker.WName, Worker.WMob, Worker.WID, Worker.WTel, Sale.SDate

Thanks CK for the help :)

[ck9663]

I assume when you said "merge" you mean UNION or "concatenate" this two results.

1. Try to align your columns. On your first query, Name is the first column, on your second, it's ID. Align properly.

2. Use UNION ALL. UNION (without - ALL) filter out duplicates.

Happy coding!


--- CK