Wsrtjtyk

I have a inventory table where i have records which refers to inventory movement of products sold by a company. The movement have 'INCOMING', 'OUTGOING' params based on type of inventory movement. It also has one more column which says the type of 'INCOMING' or 'OUTGOING'... like incoming because of NEW STOCK arrival, outgoing because of PURCHASE by customer... etc...

Now am making a report where i want to list non sold products for a long while. So am making the following query...

SELECT p.id as pid, product_name, DATEDIFF(NOW(), MAX(case when movement_type='OUTGOING' and movement_type_category='PURCHASED' then movement_on end)) AS unsold_days_since_last_sale, DATEDIFF(NOW(), MIN(case when movement_type='INCOMING' and movement_type_category='NEW_STOCK' and quantity>0 then movement_on end)) AS unsold_days_since_first_inventory_in, MAX(case when movement_type='INCOMING' and movement_type_category='NEW_STOCK' and quantity>0 then movement_on end) AS last_inv_in from inventory_movement im left join products p on im.product = p.id GROUP BY product having last_inv_in > 0 ORDER BY unsold_days_since_last_sale desc limit 100 

And i get the following output as shown in the image.

This output is nearly correct but with one issue. If a product was never sold even once in the past the column where i try to get days different between CURRENT DAY and LAST SOLD DAY will return null. In that case i need the DAYS difference between CURRENT DAY and FIRST INVENTORY IN of that product to be on the place so i can order that column descending and get the output. But i can get those data as only 2 different columns not as one column. Can someone help me to write a query to get it as combined column so i can sort that data to get result. Am attaching my inventory movement table snap also to show how the data look like...

I think IfNull function will resolve your issue.

Here's modified query.

 SELECT p.id                                        AS pid, 

   product_name, 

   Datediff(Now(), Ifnull(Max(CASE 

                                WHEN movement_type = 'OUTGOING' 

                                     AND movement_type_category = 

                                         'PURCHASED' THEN 

                                movement_on 

                              end), Min(CASE 

                                          WHEN movement_type = 'INCOMING' 

                                               AND movement_type_category = 

                                                   'NEW_STOCK' 

                                               AND quantity > 0 THEN 

                                          movement_on 

                                        end))) AS 

   unsold_days_since_last_sale, 

   Datediff(Now(), Min(CASE 

                         WHEN movement_type = 'INCOMING' 

                              AND movement_type_category = 'NEW_STOCK' 

                              AND quantity > 0 THEN movement_on 

                       end))                   AS 

   unsold_days_since_first_inventory_in, 

   Max(CASE 

         WHEN movement_type = 'INCOMING' 

              AND movement_type_category = 'NEW_STOCK' 

              AND quantity > 0 THEN movement_on 

       end)                                    AS last_inv_in 

FROM   inventory_movement im 

   LEFT JOIN products p 

          ON im.product = p.id 

GROUP  BY product 

HAVING last_inv_in > 0 

ORDER  BY unsold_days_since_last_sale DESC 

LIMIT  100