8 Replies Latest reply on Nov 5, 2013 12:18 PM by kettan

    Count distinct records that fit multiple date criteria

    Caleb Smith

      I've found a number of threads that almost answer this question, but not quite. If I missed the appropriate thread, please feel free to just point me there!

       

      I'm trying to count the number of distinct records that were "active" within a date range. Basically, I'm trying to show how many records were approved within the date range, and then the total count of all records that were active within that same range (see screenshot of what I want the graph to look like: blue bars are new approvals, orange line is total count of active records, columns are approval dates by fiscal year).

       

      Attached is a some sample data.

       

      Fiscal year = 06/30 - 07/01

      "Active" in this case means the span of time between Approval Dt and Terminate Dt/Expire Dt (whichever is earlier).

       

      For example, take this record:

       

      ID: 123

      Approval Dt: 7/1/2000

      Terminate Dt: 08/21/2003

      Expire Dt: 09/27/2014

       

      This record would be considered "active" for FY2001 (7/1/2000 - 6/30/2001), FY2002, FY2003, and FY2004 (7/1/2003 - 6/30/2004). We don't consider the Expire Dt in this case because it's later than the Terminate Dt and we only care about the earlier of the two.

       

      I'm not sure how I could do this with a calculated field. Any help is appreciated. Thanks!

        • 1. Re: Count distinct records that fit multiple date criteria
          kettan

          This can be done with a CROSS JOIN between your data and a table having the fiscal periods.

          See more in attached files and SQL below.

           

          SELECT d.[ID], d.[Approve Dt], d.[Terminate Dt], d.[Expire Dt]

          , f.[Fiscal Year], f.[Fiscal Year Start], f.[Fiscal Year End]

          FROM [data$] d, ['fiscal year$'] f

          WHERE d.[Approve Dt] <= f.[Fiscal Year End]

          and iif(d.[Terminate Dt] < d.[Expire Dt], d.[Terminate Dt], d.[Expire Dt])  >= f.[Fiscal Year Start]

           

          Ps. I had to clean a few dates in the provided Excel source

          1 of 1 people found this helpful
          • 2. Re: Count distinct records that fit multiple date criteria
            Caleb Smith

            Thanks, kettan! I think this is what I want but am unsure how to incorporate the custom SQL for this into the SQL I've already got. I'm joining multiple tables already for other views and need them all in the same workbook for the dashboards I plan to create with this. Below is my current SQL...how should I splice your solution into the existing query? It's probably important to note that I'm using an extract here, I'm not connecting to DB "live" because of performance.

             

            The top part is what your SQL would look like with my actual field names. Below that is the SQL that Tableau generates to query my Oracle DB.

             

            [snipped the SQL to save space and because it's re-posted elsewhere in the thread]

            • 3. Re: Re: Count distinct records that fit multiple date criteria
              kettan

              Something like this should work:

               

              ...

              FROM "ERESDW1"."HUM_RSR_PROJECT" "HUM_RSR_PROJECT"

                LEFT JOIN "ERESDW1"."HUM_RSR_DRUG_TRIAL_PHASE" "HUM_RSR_DRUG_TRIAL_PHASE" ON ("HUM_RSR_PROJECT"."HUM_RSR_PROJECT_ID" = "HUM_RSR_DRUG_TRIAL_PHASE"."HUM_RSR_PROJECT_ID")

                LEFT JOIN "ERESDW1"."HUM_RSR_EXT_SPONSOR" "HUM_RSR_EXT_SPONSOR" ON ("HUM_RSR_PROJECT"."HUM_RSR_PROJECT_ID" = "HUM_RSR_EXT_SPONSOR"."HUM_RSR_PROJECT_ID")

                LEFT JOIN "ERESDW1"."HUM_RSR_INT_SPONSOR" "HUM_RSR_INT_SPONSOR" ON ("HUM_RSR_PROJECT"."HUM_RSR_PROJECT_ID" = "HUM_RSR_INT_SPONSOR"."HUM_RSR_PROJECT_ID")

                LEFT JOIN "ERESDW1"."HUM_RSR_PROJ_FUNCTION" "HUM_RSR_PROJ_FUNCTION" ON ("HUM_RSR_PROJECT"."HUM_RSR_PROJECT_ID" = "HUM_RSR_PROJ_FUNCTION"."HUM_RSR_PROJECT_ID")

                CROSS JOIN (

                  select 2001 as "Fiscal Year", to_date('2001-07-01','YYYY-MM-DD') as "Fiscal Year Start", to_date('2002-06-30','YYYY-MM-DD') as "Fiscal Year End" from dual

                  union all select 2002, to_date('2002-07-01','YYYY-MM-DD'), to_date('2003-06-30','YYYY-MM-DD') from dual

                  union all select 2003, to_date('2003-07-01','YYYY-MM-DD'), to_date('2004-06-30','YYYY-MM-DD') from dual

                  union all select 2004, to_date('2004-07-01','YYYY-MM-DD'), to_date('2005-06-30','YYYY-MM-DD') from dual

                  union all select 2005, to_date('2005-07-01','YYYY-MM-DD'), to_date('2006-06-30','YYYY-MM-DD') from dual

                  union all select 2006, to_date('2006-07-01','YYYY-MM-DD'), to_date('2007-06-30','YYYY-MM-DD') from dual

                  union all select 2007, to_date('2007-07-01','YYYY-MM-DD'), to_date('2008-06-30','YYYY-MM-DD') from dual

                  union all select 2008, to_date('2008-07-01','YYYY-MM-DD'), to_date('2009-06-30','YYYY-MM-DD') from dual

                  union all select 2009, to_date('2009-07-01','YYYY-MM-DD'), to_date('2010-06-30','YYYY-MM-DD') from dual

                  union all select 2010, to_date('2010-07-01','YYYY-MM-DD'), to_date('2011-06-30','YYYY-MM-DD') from dual

                  union all select 2011, to_date('2011-07-01','YYYY-MM-DD'), to_date('2012-06-30','YYYY-MM-DD') from dual

                  union all select 2012, to_date('2012-07-01','YYYY-MM-DD'), to_date('2013-06-30','YYYY-MM-DD') from dual

                  union all select 2013, to_date('2013-07-01','YYYY-MM-DD'), to_date('2014-06-30','YYYY-MM-DD') from dual

                  union all select 2014, to_date('2014-07-01','YYYY-MM-DD'), to_date('2015-06-30','YYYY-MM-DD') from dual

                  union all select 2015, to_date('2015-07-01','YYYY-MM-DD'), to_date('2016-06-30','YYYY-MM-DD') from dual

                  union all select 2016, to_date('2016-07-01','YYYY-MM-DD'), to_date('2017-06-30','YYYY-MM-DD') from dual

                  union all select 2017, to_date('2017-07-01','YYYY-MM-DD'), to_date('2018-06-30','YYYY-MM-DD') from dual

                  union all select 2018, to_date('2018-07-01','YYYY-MM-DD'), to_date('2019-06-30','YYYY-MM-DD') from dual

                  union all select 2019, to_date('2019-07-01','YYYY-MM-DD'), to_date('2020-06-30','YYYY-MM-DD') from dual

                  union all select 2020, to_date('2020-07-01','YYYY-MM-DD'), to_date('2021-06-30','YYYY-MM-DD') from dual

              ) f

              WHERE d.[Approve Dt] <= f.[Fiscal Year End]

              and iif(d.[Terminate Dt] < d.[Expire Dt], d.[Terminate Dt], d.[Expire Dt])  >= f.[Fiscal Year Start]

               

              FIY, I have not renamed [Approve Dt], [Terminate Dt], [Expire Dt]

               

              Ps. I hope this will work and look forward to see the green correct answer 

              • 4. Re: Count distinct records that fit multiple date criteria
                Caleb Smith

                Hi Kettan,

                 

                No luck, unfortunately.   I'm getting the following when I try to use the SQL:

                 

                "Oracle database error 1747: ORA-01747: invalid user.table.column, table.column, or column specification"

                 

                I tried using the query you posted above (with the renames for Approve Dt, Terminate Dt, and Expire Dt) and then I tried to use the following, but still no luck. I'm wondering if I'm going to have to approach this with calculated fields rather than SQL. Could the issue be that I'm using an extract?

                 

                -----------------------------------

                 

                [snipped to save space]

                • 5. Re: Count distinct records that fit multiple date criteria
                  kettan

                  I am quite sure this will work — it just needs some debugging.

                   

                  I see that I made a mistake, because IIF is not an Oracle function and needs to be replaced with something Oracle understands. I will rewrite the condition so it works in Oracle.

                  • 6. Re: Count distinct records that fit multiple date criteria
                    kettan

                    Could you try this query?   (of course replace SELECT * with your columns)

                     

                    SELECT *

                    FROM "ERESDW1"."HUM_RSR_PROJECT" "HUM_RSR_PROJECT"

                      LEFT JOIN "ERESDW1"."HUM_RSR_DRUG_TRIAL_PHASE" "HUM_RSR_DRUG_TRIAL_PHASE" ON ("HUM_RSR_PROJECT"."HUM_RSR_PROJECT_ID" = "HUM_RSR_DRUG_TRIAL_PHASE"."HUM_RSR_PROJECT_ID")

                      LEFT JOIN "ERESDW1"."HUM_RSR_EXT_SPONSOR" "HUM_RSR_EXT_SPONSOR" ON ("HUM_RSR_PROJECT"."HUM_RSR_PROJECT_ID" = "HUM_RSR_EXT_SPONSOR"."HUM_RSR_PROJECT_ID")

                      LEFT JOIN "ERESDW1"."HUM_RSR_INT_SPONSOR" "HUM_RSR_INT_SPONSOR" ON ("HUM_RSR_PROJECT"."HUM_RSR_PROJECT_ID" = "HUM_RSR_INT_SPONSOR"."HUM_RSR_PROJECT_ID")

                      LEFT JOIN "ERESDW1"."HUM_RSR_PROJ_FUNCTION" "HUM_RSR_PROJ_FUNCTION" ON ("HUM_RSR_PROJECT"."HUM_RSR_PROJECT_ID" = "HUM_RSR_PROJ_FUNCTION"."HUM_RSR_PROJECT_ID")

                    CROSS JOIN (

                        select 2001 as "Fiscal Year", to_date('2001-07-01','YYYY-MM-DD') as "Fiscal Year Start", to_date('2002-06-30','YYYY-MM-DD') as "Fiscal Year End" from dual

                        union all select 2002, to_date('2002-07-01','YYYY-MM-DD'), to_date('2003-06-30','YYYY-MM-DD') from dual

                        union all select 2003, to_date('2003-07-01','YYYY-MM-DD'), to_date('2004-06-30','YYYY-MM-DD') from dual

                        union all select 2004, to_date('2004-07-01','YYYY-MM-DD'), to_date('2005-06-30','YYYY-MM-DD') from dual

                        union all select 2005, to_date('2005-07-01','YYYY-MM-DD'), to_date('2006-06-30','YYYY-MM-DD') from dual

                        union all select 2006, to_date('2006-07-01','YYYY-MM-DD'), to_date('2007-06-30','YYYY-MM-DD') from dual

                        union all select 2007, to_date('2007-07-01','YYYY-MM-DD'), to_date('2008-06-30','YYYY-MM-DD') from dual

                        union all select 2008, to_date('2008-07-01','YYYY-MM-DD'), to_date('2009-06-30','YYYY-MM-DD') from dual

                        union all select 2009, to_date('2009-07-01','YYYY-MM-DD'), to_date('2010-06-30','YYYY-MM-DD') from dual

                        union all select 2010, to_date('2010-07-01','YYYY-MM-DD'), to_date('2011-06-30','YYYY-MM-DD') from dual

                        union all select 2011, to_date('2011-07-01','YYYY-MM-DD'), to_date('2012-06-30','YYYY-MM-DD') from dual

                        union all select 2012, to_date('2012-07-01','YYYY-MM-DD'), to_date('2013-06-30','YYYY-MM-DD') from dual

                        union all select 2013, to_date('2013-07-01','YYYY-MM-DD'), to_date('2014-06-30','YYYY-MM-DD') from dual

                        union all select 2014, to_date('2014-07-01','YYYY-MM-DD'), to_date('2015-06-30','YYYY-MM-DD') from dual

                        union all select 2015, to_date('2015-07-01','YYYY-MM-DD'), to_date('2016-06-30','YYYY-MM-DD') from dual

                        union all select 2016, to_date('2016-07-01','YYYY-MM-DD'), to_date('2017-06-30','YYYY-MM-DD') from dual

                        union all select 2017, to_date('2017-07-01','YYYY-MM-DD'), to_date('2018-06-30','YYYY-MM-DD') from dual

                        union all select 2018, to_date('2018-07-01','YYYY-MM-DD'), to_date('2019-06-30','YYYY-MM-DD') from dual

                        union all select 2019, to_date('2019-07-01','YYYY-MM-DD'), to_date('2020-06-30','YYYY-MM-DD') from dual

                        union all select 2020, to_date('2020-07-01','YYYY-MM-DD'), to_date('2021-06-30','YYYY-MM-DD') from dual

                    ) f

                    WHERE "HUM_RSR_INITIAL_APPROVAL_DT" <= f."Fiscal Year End"

                    and case when "HUM_RSR_TERMINATE_DT" < "HUM_RSR_EXPIRE_DT" then "HUM_RSR_TERMINATE_DT" else "HUM_RSR_EXPIRE_DT" end  >= f."Fiscal Year Start"

                    • 7. Re: Count distinct records that fit multiple date criteria
                      Caleb Smith

                      Awesome! I think that's going to work. I need to do some validation to make sure the results are accurate but at first glance it looks very good. Thanks!