By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

Complicated Query by date

daniel aristidou
100+
P: 491
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT        Worker.WName, SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100)) AS SOLD, Worker.WMob, Worker.WID, 
  2.                          Worker.WTel
  3. FROM            Worker INNER JOIN
  4.                          Sale ON Worker.WID = Sale.WID INNER JOIN
  5.                          ProductSale ON Sale.SID = ProductSale.SID
  6. 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
Jun 11 '09 #1
Share this Question
Share on Google+
3 Replies


ck9663
Expert 2.5K+
P: 2,878
On your first question, read this. On your second question, use LEFT JOIN, not INNER.

Happy coding!


--- CK
Jun 11 '09 #2

daniel aristidou
100+
P: 491
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
Expand|Select|Wrap|Line Numbers
  1. SELECT        Worker.WName, Worker.WMob, Worker.WID, Worker.WTel, SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100)) 
  2.                          AS SOLD
  3. FROM            Worker LEFT OUTER JOIN
  4.                          Sale ON Worker.WID = Sale.WID LEFT OUTER JOIN
  5.                          ProductSale ON Sale.SID = ProductSale.SID LEFT OUTER JOIN
  6.                          Reciept ON Worker.WID = Reciept.WID
  7. WHERE        (Sale.SDate BETWEEN @StartDate AND @EndDate)
  8. GROUP BY Worker.WName, Worker.WMob, Worker.WID, Worker.WTel
  9.  
  10.  
  11. And
  12. SELECT        Worker.WID, Worker.WName, Worker.WMob, Worker.WTel, SUM(Reciept.Amount) AS Collected
  13. FROM            Worker LEFT OUTER JOIN
  14.                          Reciept ON Worker.WID = Reciept.WID
  15. WHERE        (Reciept.RDATE BETWEEN @StartDate AND @EndDate)
  16. 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
Expand|Select|Wrap|Line Numbers
  1. SELECT        Worker.WName, Worker.WMob, Worker.WID, Worker.WTel, SUM((ProductSale.Amount * ProductSale.CRPrice) * ((100 - ProductSale.Discount) / 100)) 
  2.                          AS SOLD, SUM(Reciept.Amount) AS Collected
  3. FROM            Worker LEFT OUTER JOIN
  4.                          Sale ON Worker.WID = Sale.WID LEFT OUTER JOIN
  5.                          ProductSale ON Sale.SID = ProductSale.SID LEFT OUTER JOIN
  6.                          Reciept ON Worker.WID = Reciept.WID
  7. WHERE        (Sale.SDate BETWEEN @StartDate AND @EndDate) AND (Reciept.RDATE BETWEEN @StartDate AND @EndDate)
  8. GROUP BY Worker.WName, Worker.WMob, Worker.WID, Worker.WTel, Sale.SDate
Thanks CK for the help :)
Jun 12 '09 #3

ck9663
Expert 2.5K+
P: 2,878
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
Jun 12 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.