I’m trying to get the total of amount sold of the items in the store & the total of cash receipts. I used certain date … it works find but i face several wrong data.
i tried this query:
SELECT DISTINCT
orders.id, orders_items.orderid, orders_items.id AS ITEM_ID, orders.userid, users.userid, users.fname, SUM((orders_items.price - orders_items.discount) * (orders_items.quantity + orders_items.quantity_store)) AS ITEM_PRICE, orders_items.`date` AS ITEM_DATE, orders_receipts.`date` AS RECEIPT_DATE, SUM(orders_receipts.amount) AS RECEIPT_AMOUNT, orders_receipts.orderid, orders_receipts.id AS REC_ID, orders_receipts.check_date
FROM
orders
INNER JOIN orders_items ON orders.id = orders_items.orderid
INNER JOIN orders_receipts ON orders.id = orders_receipts.orderid
INNER JOIN users ON orders.userid = users.userid
WHERE
orders_items.`date` BETWEEN '2010-03-01' AND '2010-03-31' OR orders_receipts.`date` BETWEEN '1267390800' AND '1268168400'
GROUP BY
orders.id
ORDER BY
orders.id DESC
WRONG DATA:
1- if there is no receipt paid the items sold will not show in the list!
2- if there are many items sold, the receipts gets multiplied by the number of items!
3- if there are many receipts, the items price gets multiplied by the number of receipts!
I was able to solve Problem No (1 & 2) by adding “Distinct” after the sum.
QUERY Adjusted
SELECT DISTINCT
orders.id, orders_items.orderid, orders_items.id AS ITEM_ID, orders.userid, users.userid, users.fname, SUM(Distinct((orders_items.price - orders_items.discount) * (orders_items.quantity + orders_items.quantity_store))) AS ITEM_PRICE, orders_items.`date` AS ITEM_DATE, orders_receipts.`date` AS RECEIPT_DATE, SUM(Distinct orders_receipts.amount) AS RECEIPT_AMOUNT, orders_receipts.orderid, orders_receipts.id AS REC_ID, orders_receipts.check_date
FROM
orders
INNER JOIN orders_items ON orders.id = orders_items.orderid
INNER JOIN orders_receipts ON orders.id = orders_receipts.orderid
INNER JOIN users ON orders.userid = users.userid
WHERE
orders_items.`date` BETWEEN '2010-03-01' AND '2010-03-31' OR orders_receipts.`date` BETWEEN '1267390800' AND '1268168400'
GROUP BY
orders.id
ORDER BY
orders.id DESC
Now i want to show the items sold in the period selected even if the order not paid (no receipts add to the order)
SELECT orders.id
, users.userid
, users.fname
, oi.ITEM_PRICE
, r.RECEIPT_AMOUNT
FROM orders
INNER
JOIN users
ON users.userid = orders.userid
INNER
JOIN ( SELECT orderid
, SUM((price - discount) *
(quantity + quantity_store))
AS ITEM_PRICE
FROM orders_items
WHERE `date` BETWEEN '2010-03-01'
AND '2010-03-31'
GROUP
BY orderid ) AS oi
ON oi.orderid = orders.id
LEFT OUTER
JOIN ( SELECT orderid
, SUM(amount) AS RECEIPT_AMOUNT
FROM orders_receipts
WHERE `date` BETWEEN '1267390800'
AND '1268168400'
GROUP
BY orderid ) AS r
ON r.orderid = orders.id
Yes correct. but what i need is getting the order_items in the given date (done). OR orders_receipts in the given date.
In that case i get only order_items AND orders_receipts in the given date.
EXAMPLE:
Case (1): I have a new items sold today & i got the payment (orders_receipts) today as well. When i search for the orders & the receipts for today date using the query given i get the sold items from table (order_items) & receipts payment from table (orders_receipts). WORKS GREAT
Case (2): I have an old items sold from 01-01-2009 in the table (order_items). but i got the payment (orders_receipts) today only. When i search in the orders & the receipts i got for today date using the query given i get NOTHING While i have to see the new receipt payment form table (orders_receipts). NOT Working for me yet
I should get the result in the two cases. while what i got it working now is only case 1.
I hope you got my point.
SELECT orders.id
, users.userid
, users.fname
, oi.ITEM_PRICE
, [COLOR="Blue"]oi.latest_order_time_date[/COLOR]
, r.RECEIPT_AMOUNT
, [COLOR="blue"]r.latest_order_receipt_date[/COLOR]
FROM orders
INNER
JOIN users
ON users.userid = orders.userid
INNER
JOIN ( SELECT orderid
, [COLOR="blue"]MAX(`date`) AS latest_order_time_date[/COLOR]
, SUM((price - discount) *
(quantity + quantity_store))
AS ITEM_PRICE
FROM orders_items
GROUP
BY orderid ) AS oi
ON oi.orderid = orders.id
LEFT OUTER
JOIN ( SELECT orderid
, [COLOR="blue"]MAX(`date`) AS latest_order_receipt_date[/COLOR]
, SUM(amount) AS RECEIPT_AMOUNT
FROM orders_receipts
GROUP
BY orderid ) AS r
ON r.orderid = orders.id
[COLOR="blue"]WHERE oi.latest_order_time_date
BETWEEN '2010-03-01'
AND '2010-03-31'
OR r.latest_order_receipt_date
BETWEEN '1267390800'
AND '1268168400' [/COLOR]
Great r937!
That’s what i need. but there are two things i noticed after i applied the new query.
1- the query loading slow little bit.
2- i get the wrong figure of “amount” in the table “orders_receipts” & the figure of “SUM((price - discount) * (quantity + quantity_store))” in the table “orders_items”.
How?!
A- I get the total amount of orders_receipts of the order while i need only the total amount of orders_receipts in the specified date.
Case: I have an order with payment from long time & payment for today. when i search for today payment i get the both payments (Old & today).
B- I get the total amount of orders_items in the order while i need only the total amount of orders_items in the specified date.
Case: I have an order with items sold from long time & the order updated today with new items. when i search for today items sold i get the both items sold (Old & today).
