CODEWITHSUNDEEP
sqldatediff
Ana Frank
Ana Frank

Reputation: 1

Explanation about coalesce and Datediff (with 0 at the end)

Please, someone can explain this query? I don't get it.

coalesce(datediff('days', start_date::date, comparative_date::date),0)

Datediff returns the difference between two dates, I know this.

Thanks a lot!

Upvotes: 0

Views: 786

Answers (2)

tunnelview
tunnelview

Reputation: 47

A good place to start would be to clearly understand the difference between the three functions as clearly you are just getting familiar with the syntax and logic or sequential execution of the SQL statement.

  1. ISNULL(Column_Name, 'Write the text of your choice to be returned') as Derived_Column_Name
    Explanation: If the value in the Column_Name is present, display that else display the text in quotes as the Derived_Column_Name

  2. COALESCE(First_Column_Value, Second_Column_Value, Third_Column_Value) as Derived_Column_Name
    Explanation: We can use multiple column_names in COALESCE and which ever column has as value that will be treated as the first value and it will be captured in the derived_column_name. If the values in the First_Column_Value is present, display that else check for the value in the Second_Column_Value and if its present display that, if not then check Third_Column_Value and display that.

  3. DATEDIFF(DD,First_Date_Column, Second_Date_Column) as Derived_Column_Name (E.g. Length_of_Stay_Days)
    Explanation: Calculates the difference between the two date columns mentioned in the DATEDIFF() function and returns the difference in number of Days.

Now consider this example query, where ea is a table alias, and ea.AdmissionDate and ea.DischargeDate are date columns in the 'ea' table.

COALESCE(DATEDIFF(DD,ea.AdmissionDate,ea.DischargeDate) similarly, if there is another self join somewhere in the query, and you want to use COALESCE and Datediff together because you have NULL value populating in your result table, then to fix that you could use the below query:

COALESCE(DATEDIFF(DD,ea.AdmissionDate,ea.DischargeDate), DATEDIFF(DD,ep.AdmissionDate,ep.DischargeDate)) AS Length_of_Stay_Days

where 'ea' and 'ep' are the table alias for the same table,and this query will execute and get the difference of first part DATEDIFF(DD,ea.AdmissionDate,ea.DischargeDate) and if it is not available, it will calculate the second part DATEDIFF(DD,ep.AdmissionDate,ep.DischargeDate) and COALESCE will capture either of the difference of number of days and populate that value in the Length_of_Stay_Days column.

Upvotes: 0

Lev Kanyazev
Lev Kanyazev

Reputation: 11

Based on sql documentation, the coalesce function returns the first non null value, so in this case: if the datediff will return null, the second argument passed to the function will be returned, which is "0"

for the datediff function, the syntax is not correct. "days" needs to be "day:, and the passed in "start_date" and "comparative_date" needs to point to some column value

Let me know if it helped you

Upvotes: 1

Related Questions