Question 236867 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql query to avoid negative symbol in the output table

Here I have query in which I am trying to avoid the negative symbol in the final output. But why do I get that negative symbol ?
I have try to use the replace() but it doesn’t work.
can you please help me with this ?
here is the query ..,

WITH Partitioned AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, start_date ORDER BY customer_id, start_date) AS RowNumber
    FROM data_bank.customer_nodes
) 
SELECT 
    *,
    CASE 
        WHEN RowNumber > 1 THEN 0
        ELSE COALESCE(DATEDIFF( (SELECT MAX(start_date) FROM data_bank.customer_nodes WHERE start_date < a.start_date AND customer_id = a.customer_id), a.start_date), 0)
    END AS Days_between
FROM Partitioned a

Here is the output I got from above query

enter image description here

Tags:

3

Answers


  1. 0 
    WITH Partitioned AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, start_date ORDER BY customer_id, start_date) AS RowNumber
    FROM data_bank.customer_nodes
) 
SELECT 
    *,
    CASE 
        WHEN RowNumber > 1 THEN 0
        ELSE GREATEST(0, DATEDIFF(a.start_date, COALESCE((SELECT MAX(start_date) FROM data_bank.customer_nodes WHERE start_date < a.start_date AND customer_id = a.customer_id), a.start_date)))
    END AS Days_between
FROM Partitioned a;

    Here, I’ve modified the CASE statement to calculate the difference as the maximum of 0 and the result of DATEDIFF. This way, even if the subquery’s result is greater than the current start_date, the negative value will be replaced by 0.

    Login or Signup to reply.
  2. 0

    I think you are subtracting the larger date from the smaller date, so it’s showing negative number. Change the positions of a.start_date and (SELECT MAX(start_date)...) in the DATEDIFF function to ensure that you’re subtracting the smaller date from the larger date.

    WITH Partitioned AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id, start_date ORDER BY customer_id, start_date) AS RowNumber
    FROM data_bank.customer_nodes
) 
SELECT 
    *,
    CASE 
        WHEN RowNumber > 1 THEN 0
        ELSE COALESCE(DATEDIFF(a.start_date, (SELECT MAX(start_date) FROM data_bank.customer_nodes WHERE start_date < a.start_date AND customer_id = a.customer_id)), 0)
    END AS Days_between
FROM Partitioned a;
    Login or Signup to reply.
  3. 0

    When first argument of DATEDIFF is smaller than the second argument, then you get negative results. You can use ABS() function of mysql to convert negative values to unsigned values, as folllows:

    ABS(
  COALESCE(
    DATEDIFF(
      (
        SELECT MAX(start_date) FROM data_bank.customer_nodes 
          WHERE start_date < a.start_date AND customer_id = a.customer_id
      ), 
      a.start_date
    ), 
    0
  )
)

    NOTE: I have only shared relevant portion of the updated query.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search