6

I have a table my_table:

case_id    first_created  last_paid       submitted_time
   3456    2021-01-27     2021-01-29      2021-01-26 21:34:36.566023+00:00
   7891    2021-08-02     2021-09-16      2022-10-26 19:49:14.135585+00:00
   1245    2021-09-13     None            2022-10-31 02:03:59.620348+00:00
   9073    None           None            2021-09-12 10:25:30.845687+00:00
   6891    2021-08-03     2021-09-17      None

I created 2 new variables:

select *,
       first_created-coalesce(submitted_time::date) as create_duration,
       last_paid-coalesce(submitted_time::date) as paid_duration 
from my_table;

The output:

  case_id    first_created  last_paid       submitted_time               create_duration paid_duration
   3456    2021-01-27     2021-01-29      2021-01-26 21:34:36.566023+00:00     1                3
   7891    2021-08-02     2021-09-16      2022-10-26 19:49:14.135585+00:00     -450            -405
   1245    2021-09-13     null            2022-10-31 02:03:59.620348+00:00     -412            null
   9073    None           None            2021-09-12 10:25:30.845687+00:00     null           null
   6891    2021-08-03     2021-09-17      null                                 null            null

My question is how can I replace new variables' value with 0, if it is smaller than 0?

The ideal output should look like:

  case_id    first_created  last_paid       submitted_time               create_duration paid_duration
   3456    2021-01-27     2021-01-29      2021-01-26 21:34:36.566023+00:00     1                3
   7891    2021-08-02     2021-09-16      2022-10-26 19:49:14.135585+00:00     0                0
   1245    2021-09-13     null            2022-10-31 02:03:59.620348+00:00     0               null
   9073    None           None            2021-09-12 10:25:30.845687+00:00     null             null
   6891    2021-08-03     2021-09-17      null                                 null             null

My code:

select  *,
        first_created-coalesce(submitted_time::date) as create_duration,
        last_paid-coalesce(submitted_time::date) as paid_duration, 
        case
            when create_duration < 0 THEN 0
            else create_duration
        end as QuantityText
from my_table
Improve this question
4
  • Just use CASE over the entire expression. Commented Feb 6, 2023 at 20:36
  • @PM77-1 Hi ,thank you very much for your reply,I have just updated my code that with Case ,but it still not work ,can you give me more detail ,thank you very much! Commented Feb 6, 2023 at 20:45
  • 2
    @William you can't reference aliased values by their alias at this level - they'd have to be lower (in the source table, cte, subquery). Try greatest(): greatest(first_po_created-coalesce(submitted_timestamp::date),0) as create_duration, greatest(last_po_paid-coalesce(submitted_timestamp::date),0) as paid_duration Commented Feb 6, 2023 at 20:48
  • 1
    @Zegarek thank you very much ,it works ,can you please post it as answer ,so that I can check it ,thanks! Commented Feb 6, 2023 at 20:51

2 Answers 2

Reset to default
18

greatest(yourvalue,0)

Given yourvalue lower than 0, 0 will be returned as the greater value:

select *
  ,greatest(0,first_created-coalesce(submitted_time::date)) as create_duration
  ,greatest(0,last_paid    -coalesce(submitted_time::date)) as paid_duration
from my_table;

This will also change null values to 0.

case statement

If you wish to keep the null results, you can resort to a regular case statement. In order to alias your calculation you'll have to put it in a subquery or a cte:

select *
  ,case when create_duration<0 then 0 else create_duration end as create_duration_0
  ,case when paid_duration  <0 then 0 else paid_duration   end as paid_duration_0
from (
    select *
      ,first_created-coalesce(submitted_time::date) as create_duration
      ,last_paid    -coalesce(submitted_time::date) as paid_duration
    from my_table ) as subquery;

(n+abs(n))/2

If you sum a number with its absolute value, then divide by two (average them out), you'll get that same number if it was positive, or you'll get zero if it was negative because a negative number will always balance itself out with its absolute value:

  • (-1+abs(-1))/2 = (-1+1)/2 = 0/2 = 0
  • ( 1+abs( 1))/2 = ( 1+1)/2 = 2/2 = 1
select *
  ,(create_duration + abs(create_duration)) / 2 as create_duration_0
  ,(paid_duration   + abs(paid_duration)  ) / 2 as paid_duration_0
from (
    select *
      ,first_created-coalesce(submitted_time::date) as create_duration,
      ,last_paid    -coalesce(submitted_time::date) as paid_duration
    from my_table ) as subquery;

Which according to this demo, is slightly faster than case and about as fast as greatest(), without affecting null values.

Note that select * pulls everything from below, so you'll end up seeing create_duration as well as create_duration_0 - you can get rid of it by listing your desired output columns explicitly in the outer query. You can also rewrite it without subquery/cte, repeating the calculation, which will look ugly but in most cases planner will notice the repetition and evaluate it only once

select *
  ,case when first_created-coalesce(submitted_time::date) < 0
        then 0
        else first_created-coalesce(submitted_time::date)
   end as create_duration
  ,(abs(last_paid-coalesce(submitted_time::date))
    +   last_paid-coalesce(submitted_time::date) )/2 
       as paid_duration
from my_table;

or using a scalar subquery

select *
  ,(select case when a<0 then 0 else a end 
    from (select first_created-coalesce(submitted_time::date)) as alias(a) ) 
    as create_duration
  ,(select case when a<0 then 0 else a end 
    from (select last_paid-coalesce(submitted_time::date)) as alias(a) ) 
    as paid_duration
from my_table;

Neither of which help with anything in this case but are good to know.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thank you for your answer ,is there anyway I can keep the null value of create_duration and paid_duration ,I don't want to covert it to 0 when it is null ,I only want to convert it to 0 when it is less than0.
@William Sure, with a regular case. See my update.
@William I stumbled upon an (abs(val)+val)/2 purely arithmetic method that seems to be faster than case, about as fast as greatest(), while still keeping null values. It's added along with a link to a demo.
,thank you so much,I learned a lot from your answer!
1

If you are planning on attaching your SQL Database to an ASP.NET app, you could create a c# script to query your database, and use the following:

Parameters.AddWithValue(‘Data You want to change’ ‘0’);

However, if your not using your SQL database with a ASP.NET app, this will not work.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.