here is three Sql table name of them - 1. orders 2.order_item 3. target_value
I am trying Two way here is may two code :
1
SELECT I.target_id as Iid,
I.target_date as Idate,
I.customer_name as Icust,
I.target_product as Icat,
I.target_pay as Ipay,
I.target_rec as Irec,
I.active as Iact,
I.status as Ista,
COALESCE(S.sale, 0) AS sale,
COALESCE(P.cat_n, 0) AS cat_n,
COALESCE(R.cus_n, 0) AS cus_n,
COALESCE(O.order_return, 0) AS order_return,
I.target_pay - (S.sale - O.order_return) AS T_achi
FROM target_value I
LEFT JOIN (
SELECT categories_id, categories_name AS cat_n
FROM categories
) P ON P.categories_id = I.target_product
LEFT JOIN (
SELECT customer_id, customer_name AS cus_n
FROM customer
) R ON R.customer_id = I.customer_name
LEFT JOIN (
SELECT order_id, order_date, categories_id, SUM(quantity) AS sale
FROM order_item
GROUP BY categories_id
) S ON S.categories_id = I.target_product
LEFT JOIN (
SELECT order_id, order_date, categories_id, SUM(Rquantity) AS order_return
FROM order_item
GROUP BY categories_id
) O ON O.categories_id = I.target_product WHERE S.order_date BETWEEN '2024-09-01' AND '2024-09-24';
code 2
SELECT
target_value.customer_name, target_value.target_product, target_value.target_pay,
target_value.target_rec, SUM(target_value.target_pay) AS TPP,
order_item.categories_id, product.product_name, orders.order_date, orders.client_name, customer.customer_name, categories.categories_name, SUM(order_item.quantity) AS T1, SUM(order_item.Rquantity) AS T2,
(SUM(order_item.quantity) - SUM(order_item.Rquantity) ) AS Turn,
COALESCE(SUM(target_value.target_pay), 0) AS TPP,
COALESCE(SUM(order_item.quantity), 0) AS T1,
COALESCE(SUM(order_item.Rquantity), 0) AS T2,
COALESCE((SUM(order_item.quantity) - SUM(order_item.Rquantity) ), 0) AS Turn
FROM target_value
INNER JOIN
order_item ON target_value.target_product = order_item.categories_id
INNER JOIN product ON product.product_id = order_item.product_id
INNER JOIN orders ON orders.order_id = order_item.order_id
INNER JOIN customer ON orders.client_name = customer.customer_id
INNER JOIN categories ON categories.categories_id = order_item.categories_id
WHERE orders.order_date BETWEEN '2022-08-10' AND '2024-09-25' GROUP BY order_item.categories_id, orders.client_name;
but i want result like below .
BUT MY code not work properly . so please sir help me to find out proper result.
date and summation not work properly.