Count Dates based on Dates in Two Other Date Columns with Conditions

Database
Enthusiast

Count Dates based on Dates in Two Other Date Columns with Conditions

Hello All...appreciate any assistance you can provide on the following conundrum!

I want to count CALLS if:

  1. the CALL DATE is within 6 Mos (less than 180 days) of CREATE DATE OR PHASE DATE as Calls Within Scope
  2. the CALL DATE is NOT within 6 Mos (less than 180 days) of CREATE DATE OR PHASE DATE as Calls Within Scope

Here is my query which is not returning expected results of 3 > Within 6 Mo Range and 5 > CallsALL. 

This returns 0 > Within 6 Mo Range and 5 > CallsAll

 

count(case when d.oprty_crte_dt - CAST(a.call_dt AS date format 'MM/DD/YYYY') < 180 OR d.phase_dt - CAST(a.call_dt AS date format 'MM/DD/YYYY') < 180 then 1 end) as CallsIn6MoRange,
count(case when d.oprty_crte_dt - CAST(a.call_dt AS date format 'MM/DD/YYYY') < 180 OR d.phase_dt - CAST(a.call_dt AS date format 'MM/DD/YYYY') < 180 then 1 else 0 end) as CallsAll

Here's an example of my dataset and my expected result:

CALL DATECREATE DATE# Days from Call DateCondition Met?PHASE DATE# Days from Call DateCondition Met?# Calls Within 6 Mo Range# Total Calls
4/5/20184/2/2018-3YES5/1/201829YES11
4/2/20184/2/20180YES5/1/201829YES11
1/28/20184/2/201864YES5/1/201829YES11
12/31/20174/2/201892YES5/1/201829YES11
6/3/20164/2/2018668NO5/1/201829NO01
      TOTAL45

 

You can probably tell, i'm a NOVICE SQLer!

4 REPLIES
Teradata Employee

Re: Count Dates based on Dates in Two Other Date Columns with Conditions

First, you seem to be confusing the SQL function count, which counts rows, and sum, which adds values.  I think you want to sum() the case statement results (0's and 1's), not count them.  Second, you should add "Else 0" to the first CASE - the default else-value is null, which might give you the same result, but doesn't make your intent clear.  Third, is the second case supposed to be counting total calls or just late calls?  If the former, then you don't need a sum(case ...), just a count() (with no case!); if the latter, then use sum(case ...).

Enthusiast

Re: Count Dates based on Dates in Two Other Date Columns with Conditions

Thanks for the reply!

 

I have modified the code as follows however I am getting ZERO records returned for CallsInRange.  Query seems to be excluding these calls alltogether.

Yes, CallsAll should count all calls, whereas, CallsInRange should only count calls that are within 180 days of the phase_dt OR oprty_crte_dt.

 

Any suggestions?

 

sum(case when d.oprty_crte_dt - CAST(a.call_dt AS date format 'MM/DD/YYYY') < 180 OR d.phase_dt - CAST(a.call_dt AS date format 'MM/DD/YYYY') < 180 then 1 else 0 end) as CallsInRange,
count(distinct(CAST(a.call_dt AS date format 'MM/DD/YYYY'))) as CallsAll
Highlighted
Junior Contributor

Re: Count Dates based on Dates in Two Other Date Columns with Conditions

Of course the calculated values you showed for  "# Days from Call Date" are wrong, but the CASE for CallsInRange is correct. If you got a wrong result it must be something else.

 

For CallsAll you simply need a count(*).

 

 

 

 

 

Enthusiast

Re: Count Dates based on Dates in Two Other Date Columns with Conditions

Thanks!  I believe you are right.  The data is corrupt.  Thanks everyone for their awesome support!