#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    5
    Rep Power
    0

    HELP with COMPLEX COUNT/CASE


    Hey guys, I am having trouble with the following:
    COUNT( CASE WHEN xferleads.timestamp >= wo_date
    AND xferleads.timestamp <= wo_end_date
    THEN xferleads.customer
    ELSE NULL END ) AS countslw

    This works well except under one condition:
    There are times when wo_end_date is NULL and under such a condition the count is obviously 0

    I assume the solution is a nested CASE but I am having trouble with the syntax.

    In other words - count how many xferleads.timestamp there are between wo_date and wo_end_date. If wo_end_date is NULL, count SINCE wo_date only.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Code:
    COUNT( CASE WHEN xferleads.timestamp >= wo_date
                 AND xferleads.timestamp <= COALESCE(wo_end_date,xferleads.timestamp)
                THEN xferleads.customer
                ELSE NULL END ) AS countslw
    when wo_end_date is null, substitute xferleads.timestamp, which is always going to be less than or equal to itself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    5
    Rep Power
    0

    you're amazing


    thank you.

IMN logo majestic logo threadwatch logo seochat tools logo