WhatsApp_Image_2024-09-24_at_6.48.00_PM.jpeg

WhatsApp_Image_2024-09-24_at_6.48.00_PM_(2).jpeg

WhatsApp_Image_2024-09-24_at_6.48.00_PM_(1).jpeg

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 .

RESULT.jpg

BUT MY code not work properly . so please sir help me to find out proper result.

date and summation not work properly.

Which date do you want? The one from order_date or target_date?

I can help you if you explain what you're trying to achieve, and what each of the 3 tables represents.

What date do you want in the final column to represent? Where is the staff name? Is that orders.client_contact?

What is the item name? What is the target? target_value.target_product?

What does achievement mean?

commented: ➕1️⃣ +17
commented: please help me +0

I want to show sale target and achievement . as you want
Which date do you want? I want to show sum as per --- order_date
I can help you if you explain what you're trying to achieve, and what each of the 3 tables represents. – Achieve means – sum of quantity – group of catragories_id and Customer_name
What date do you want in the final column to represent? Where is the staff name? Is that orders.client_contact?--- orders.client_name = customer_name
What is the item name?- target_value.target_product = order_item.categories_id
What is the target? target_value.target_product? -- target_value.target_pay is my target value.
What does achievement mean? – sum of quantity – group of catragories_id and Customer_name

So I want
date- order_date
staff Name-- orders.client_name = customer_name
item Name-- target_value.target_product = order_item.categories_id
Target-- target_value.target_pay
Achievement-- sum of quantity

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.