coalesce join only picking up first field in the coalesce RRS feed

  • Question

  • Hi,

    I am so stuck please can you help! I have not pasted the whole query as it is so long.I am trying to do a join as below however it is only picking up A,[Start_Dt] but not A.[Start_Dt+1] or [Start_Dt+2] even though there are some entries for these fields that should be showing any ideas - have also tried an OR clause but it gave weird results? Warddiary has no nulls in it but the table A. does                                                                                                 


    left join on Warddiary.[Date_in_Ward_Diary]=coalesce(A.[Start_Dt],A.[Start_Dt]+1,A.[Start_Dt]+2)

    Many thanks, A


    Friday, February 26, 2016 9:45 AM


All replies

  • That's how COALESCE (Transact-SQL) works; it returns the first none-null value and only that one; not all

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, February 26, 2016 9:55 AM
  • coalesce(A.[Start_Dt],A.[Start_Dt]+1,A.[Start_Dt]+2)

    Coalesce will work like If A.start_Dt  is null then it will take ,A.[Start_Dt]+1 and if A.[Start_Dt]+1 is null then it will take next parameter value.

    Like it will always return the first not NULL parameter value

    Please mark as answer if my post is helped to solve your problem
    and vote as helpful if it helped so that forum users can benefit 


    Friday, February 26, 2016 10:08 AM
  • What exactly is the output you want? This will work:

    coalesce(convert(varchar(10),A.[Start_Dt], 112), '')
    + coalesce(convert(varchar(10),A.[Start_Dt] + 1, 112), '')
    + coalesce(convert(varchar(10),A.[Start_Dt] + 2, 112), '')

    Or I SQL Server 2012 and later

    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, February 26, 2016 10:14 AM
  • QUERY:left join on Warddiary.[Date_in_Ward_Diary]=coalesce(A.[Start_Dt],A.[Start_Dt]+1,A.[Start_Dt]+2)

    That makes little sense. coalece(a, b, c) is syntactic sugar for

           WHEN b IS NOT NULL THEN b
           WHEN c IS NOT NULL THEN c

    If I am to guess, you need

    Warddiary.[Date_in_Ward_Diary] BETWEEN A.[Start_Dt] AND A.[Start_Dt]+2

    • Proposed as answer by Russ Loski Friday, February 26, 2016 10:51 AM
    Friday, February 26, 2016 10:31 AM
  • Well, I suggest to not use + operator with dates. You need to use dateadd(day, 2, A.Start_Dt) for the second condition.

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    My TechNet articles

    Friday, February 26, 2016 1:24 PM
  • Are those actual column names or are you intending to perform date operations on StartDate when you say StartDate+1 and StartDate+2


    Friday, February 26, 2016 2:23 PM
  • Your code will pick up the non null A.[Start_Dt] values. If it is null, the other two columns in coaleasce function will return null as well and this condition will always be false in normal settings. The result looks like only picking up a.[Start_Dt]. Please post your table DDL and sample data if you have more questions. Thanks.
    Friday, February 26, 2016 2:47 PM
  • Well, I suggest to not use + operator with dates. You need to use dateadd(day, 2, A.Start_Dt) for the second condition.

    Yes, that is a very good point!

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, February 26, 2016 11:04 PM