Struggling with query

[Gazmysta]

Hi Guys,

Im trying to create a query that will tell me the total qty sold and value for every stock code we sell for a particular customer within a particular time period. The data is held in three tables 1. holds the stock details 2. holds all transaction details and 3. holds customer info. Can anybody help me figure out how to go about designing this query please??

Thanks

 

[dougday]

Well, that really depends on how the tables are structured, but I'll make some assumptions and take a shot

Here's what I would do:

SELECT t2.stockCode, COUNT(*) as qtySold
FROM table2 t2
WHERE (t2.custNumber = 'blabla') AND
      (t2.transDate >= 'startdate') AND 
      (t2.transDate <= 'enddate')
GROUP BY t2.stockCode;

That will give you the basics - the quantity sold between 'startdate' and 'enddate' for customer 'blabla' (of course, replace those values with the ones you need).

If you want more information about the customer or stock, then you'll need to do more nested queries. Something like this:

SELECT t2.stockCode, 
      (SELECT t1.stockName
       FROM table1 t1
       WHERE t1.stockCode = t2.stockCode) as stockName,
      (SELECT t3.custFirstName
       FROM table3 t3
       WHERE t3.custNumber = 'blabla') as custFirstName,
      (SELECT t3.custLastName
       FROM table3 t3
       WHERE t3.custNumber = 'blabla') as custLastName,
      COUNT(*) as qtySold
FROM table2 t2
WHERE (t2.custNumber = 'blabla') AND
      (t2.transDate >= 'startdate') AND 
      (t2.transDate <= 'enddate')
GROUP BY t2.stockCode;

That will get you some more in-depth info about what you're working with, but I'm really not sure if the 2nd query will work properly because of the GROUP BY statement. If it were me, I would split this into 2 or 3 queries and get the information separately.

Anyway, I hope this helps.
-Doug