title: SQL:Reporting Over Time in Respect to SubTyping
body: <p>(Title could possibly need rewording; so feel free to edit)</p>
  
  <p>I am reviewing a work item, where we are tasked with converting a SAS program to a View. The program was used to produce a source file from our tables for another group as their data source. The intent of the View is to produce the same content as the previously created source file, so that the SAS program can be retired, and the other group can point to our view.</p>
  
  <p>The difficulty comes with the concept of subtyping that we (company) have. In terms of users, we technically have 3 types:</p>
  
  <ul>
  <li>Type 1: UG1</li>
  <li>Type 2: UG2</li>
  <li>Type 3: UG0
  
  <ul>
  <li>0 meaning that they do not currently belong to an active group</li>
  </ul></li>
  </ul>
  
  <p>One reporting item that was defined based on subtyping related to date/time is the user phone number. </p>
  
  <p>For example, a given user is transitioning between types (UG1 to UG2; creating two entries) and the effective date spans for each type's entry could overlap to where TODAY occurs in both. Some indicator associated only to the member is set to represent at each entry that they are of type UG2 (on a join the entry containing UG1 would also have an indicator to state the current type).</p>
  
  <p>When reporting on this user's phone number, we only want to see one phone number, but we have multiple phone numbers available (home, work, mobile, other). For the phone numbers themselves, we have a coalesce setup with the order of home, mobile, work, then other (if home has no value then check mobile and so on). In respect to subtyping, we must check for the phone numbers in relation to UG2, but if no phone number is returned then we will attempt to find a phone number from UG1, if that exists. Otherwise, the phone number is returned as null, and the report will display "No phone number.". We must also look in the other direction if we are presented with a user of type UG1.</p>
  
  <p>Note: For the table containing the phone numbers, there is also an indicator for the same subtyping (UG1 entries and UG2 entries). (basically user information created in reference to their subtyping at the time of creation or effective date span)</p>
  
  <p>So in short the logic is similar to this format:</p>
  
  <ul>
  <li>User Of Type UG1
  
  <ul>
  <li>COALESCE(home, mobile, work, other)
  
  <ul>
  <li>If no number selected then COALESCE(UG2(home, mobile, work, other))
  
  <ul>
  <li>If no number selected then null</li>
  </ul></li>
  </ul></li>
  </ul></li>
  <li>User Of Type UG2
  
  <ul>
  <li>COALESCE(home, mobile, work, other)
  
  <ul>
  <li>If no number selected then COALESCE(UG1(home, mobile, work, other))
  
  <ul>
  <li>If no number selected then null</li>
  </ul></li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>Users in UG0 are excluded. it was mainly listed as a possibility, but I can remove that if it is unclear.</p>
  
  <p>For the view, I had difficulties for the edge case users that existed in one subtyping, but never in the other. Given some limitations in respect to Views, no variables or create ##table (temp tables), I went with using common table expressions (cte).</p>
  
  <p>Each type has their own CTE, UG1Phone and UG2Phone, and a CTE is used to combine them. I have found that using a Full Outer Join gets all of the needed data, plus some extra null entries. Since null is something that can happen (not existing on one of the sides), I could not see a way to simply do a check to ignore nulls. I made a where statement that checked the subtyping indicator of the user and did a null check on the subtyping associated to the phone number entry (if user subtyping has only ever been UG1, then a full outer join would produce a null indicator based on the UG2 CTE).</p>
  
  <p>My main questions deal with the separation (UG1 CTE and UG2 CTE) and how I am grouping the data (Full Outer Join with many Case statements).</p>
  
  <p>As previously stated, I have a CTE for each user subtype and no clear roadmap to know if there will be additional subtypes. I could combine the CTEs and move the FULL OUTER JOIN, but that feels like it would become too messy with nesting sub-queries. Again, my reasoning for this style of join is to continue to the next subtyping if needed and available.</p>
  
  <p>Is there another way to produce that same result? I am aiming to reduce queries that look duplicated or pieces that look like they could be simplified (for each user CTE, the only difference is the indicator value; the combined CTE has a WHERE statement that is also based on the ordering logic as stated above).</p>
  
  <p>For the case statements, the View called for a case statement to determined which side of the FULL OUTER JOIN was used first (coalesce statements and other columns). Currently, each defined column contains a case statement to determine which table to check for the available data first.</p>
  
  <p>Is there a way to do this with one statement, or at least without adding the case statement per column? I am looking for a way to determine the order in which each column is used without doing the same check at each column. For example:</p>
  
  <pre><code>SELECT
  CASE 
  WHEN Indicator = 1 
      THEN ISNULL(UG1.effectiveDate, UG2.EffectiveDate)
  WHEN Indicator = 2
      THEN ISNULL(UG2.effectiveDate, UG2.EffectiveDate)
  ELSE
      NULL
  END AS [EffectiveDate]
  , CASE
  WHEN Indicator = 1
      THEN ISNULL(UG1.phoneNumber, UG2.phoneNumber)
  WHEN Indicator = 2
      THEN ISNULL(UG2.phoneNumber, UG1.phoneNumber)
  ELSE
      NULL
  END AS [PhoneNumber]
  , -- and so on
  </code></pre>
  
  <p>Feel free to ask anything on what I have above. For the items listed, I have found a working solution, but I feel like many of the individual pieces may not be clear for someone reviewing this down the road. So, my intent for this question is to define a clear way that this data should be grouped/joined to ensure that duplicate null data is removed and that data is checked in the correct order.</p>
  
  <hr>
  
  <p>(edit: Add Involved Table Structures)</p>
  
  <p>dbo.UsersCommonTable</p>
  
  <p>Columns:</p>
  
  <ul>
  <li>UserId VARCHAR</li>
  <li>CurrentUserTypeId INT</li>
  <li>EffectiveDate DATETIME</li>
  <li>TerminationDate DATETIME
  
  <ul>
  <li>With the concept of the changing CurrentUserTypeId, EffectiveDate is used to map Userinformation to a specific date span (existing between the Effective and Termination Dates)</li>
  </ul></li>
  </ul>
  
  <p>dbo.UserInformation</p>
  
  <p>Columns:</p>
  
  <ul>
  <li>UserId VARCHAR</li>
  <li>UserTypeId INT 
  
  <ul>
  <li>in reference to when it was made (EX: for a user in group
  one would have a UserTypeId of 1 in this table even if the UsersCommonTable has the user as a UserTypeId of 2</li>
  </ul></li>
  <li>HomePhone VARCHAR</li>
  <li>MobilePhone VARCHAR</li>
  <li>WorkPhone VARCHAR</li>
  <li>OtherPhone VARCHAR</li>
  <li>StartDate DATETIME</li>
  <li>EndDate DATETIME
  
  <ul>
  <li>can be null due to being current/not having a specified end date</li>
  <li>Start and End dates will only be in reference to when the user started using this information and when did they stop using this information, where applicable</li>
  </ul></li>
  <li>DateChanged DATETIME
  
  <ul>
  <li>DateChanged is the same as created date when first entered, and changes to represent updated on every change to the specific record (not great design, but it is what currently exists)</li>
  </ul></li>
  </ul>
  
  <p>dbo.UserReportView</p>
  
  <p>Columns:</p>
  
  <ul>
  <li>UserId INT</li>
  <li>PhoneNumber VARCHAR
  
  <ul>
  <li>When NULL, then replace with 'No Phone Number.'</li>
  </ul></li>
  <li>//more User Information</li>
  </ul>
  
  <p>Let me know if any changes need to be made, or a misunderstanding of what was asked.</p>
  
  <hr>
  
  <p>(edit: Add SAS specific code/logic and current requirements)</p>
  
  <p>The requirements are the same, except for the coalesce across phone numbers. Previously, HomePhone was the only column used, and the directional concept of user types was present. So, that logic looked more like this:</p>
  
  <ul>
  <li>User Of Type UG1
  
  <ul>
  <li>check if HomePhone is null
  
  <ul>
  <li>If null then check if UG2(HomePhone) is null
  
  <ul>
  <li>if still null then return 'No Phone Number.'</li>
  </ul></li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>and so on starting with UG2.</p>
  
  <pre><code>proc sql noprint;
  create table cc_user_list as
  select distinct
           UserID
          ,EffectiveDate as effdt2
          ,mdy(input(scanq(EffectiveDate ,1,'/'),2.), 
               input(scanq(EffectiveDate ,2,'/'),2.),
               input(scanq(EffectiveDate ,3,'/'),4.)) format yymmdd10. as effdt
          ,mdy(input(scanq(TerminationDate,1,'/'),2.), 
               input(scanq(TerminationDate,2,'/'),2.),
               input(scanq(TerminationDate,3,'/'),4.)) format yymmdd10. as termdt
  from imp.UsersCommonTable 
  where UserTypeId = 1 or UserTypeId = 2
  order by UserID ;
  quit;
  
  data cc_user_list;
    set cc_user_list;
    if effdt &lt;= today() &lt;= termdt;  /* Only keep currently Active cases */
  run; 
  
  proc sql noprint;
  create table User_phone_number as
  select distinct
          cats(UserID, '01') as User_no
         ,homephone as usedPhoneNumber
         ,datechanged
         ,startdate
         ,enddate
  from impl=.UserInformation 
  where (HomePhone ^= ' ') and
         UserTypeID = 1  and
         (datepart(enddate) &gt;= today() 
               or enddate = . ) 
  order by User_no ;
  quit;
  
      proc sql noprint;
  create table User_phone_number as
  select distinct
          cats(UserID, '01') as User_no
         ,homephone as otherPhoneNumber
         ,datechanged
         ,startdate
         ,enddate
  from imp.UserInformation
  where (HomePhone ^= ' ') and
         UserTypeID = 2  and
         (datepart(enddate) &gt;= today() 
               or enddate = . ) 
  order by User_no ;
  quit;
  
  data User_phone_number (drop=usedPhoneNumber);
    set User_phone_number;
    if usedPhoneNumber = ' ' and otherPhoneNumber^= ' ' then usedPhoneNumber= otherPhoneNumber;
  run;
  
  data User_phone_number (drop=usedPhoneNumber);
    set User_phone_number;
    if usedPhoneNumber = ' ' then usedPhoneNumber= 'No Phone Number.';
  run;
  </code></pre>
  
  <p>I shortened and reworded a good bit of the available code. Let me know if any of this does not make sense (I can somewhat translate some of SAS for the most part, but I have no clue what I am actually doing with making/changing a program in SAS)</p>
  
  <hr>
  
  <p>(edit: example data)</p>
  
  <p>in UsersCommon Table:</p>
  
  <pre><code>UserId CurrentUserTypeId EffectiveDate TerminationDate
    U001                 2    01/01/2018      09/30/2018
    U001                 2    09/01/2018      09/30/2019
  </code></pre>
  
  <p>in UserInformation Table:</p>
  
  <pre><code>UserId UserTypeId  StartDate   EndDate     HomePhone
    U001          1  02/01/2018  09/15/2018  1112223344
    U001          2  09/01/2018  09/30/2019  NULL
  </code></pre>
  
  <p>Results for report ran any day between 02/01/2018 - 09/15/2018:</p>
  
  <pre><code>UserId PhoneNumber
    U001 1112223344
  </code></pre>
  
  <p>Results for report ran any day after 09/15/2018:</p>
  
  <pre><code>UserId PhoneNumber
    U001 No Phone Number.
  </code></pre>
  
  <p>(Edit to include example of sets of queries in question)</p>
  
  <pre><code>CREATE VIEW [dbo].[UserReportView] AS
      WITH UG1Phone AS (
          SELECT
                  UserId
                  , UserTypeId
                  , HomePhone
                  , MobilePhone
                  , WorkPhone
                  , OtherPhone
                  , StartDate
                  , EndDate
          FROM
                  UserInformation uinfo
          INNER JOIN
                  (
                      SELECT
                              UserId
                              , UserTypeId
                              , MAX(DateChanged) LatestChanged
                      FROM
                              UserInformation
                      WHERE
                              UserTypeId = 1
                  ) Latest
          ON
                  uinfo.UserId = Latest.UserId
                  AND uinfo.UserTypeId = Latest.UserTypeId
                  AND uinfo.DateChanged = Latest.LatestChanged
          WHERE
                  uinfo.EndDate &gt;= GETDATE() OR uinfo.EndDate IS NULL)
      ),
      UG2Phone AS (
          SELECT
                  UserId
                  , UserTypeId
                  , HomePhone
                  , MobilePhone
                  , WorkPhone
                  , OtherPhone
                  , StartDate
                  , EndDate
          FROM
                  UserInformation uinfo
          INNER JOIN
                  (
                      SELECT
                              UserId
                              , UserTypeId
                              , MAX(DateChanged) LatestChanged
                      FROM
                              UserInformation
                      WHERE
                              UserTypeId = 2
                  ) Latest
          ON
                  uinfo.UserId = Latest.UserId
                  AND uinfo.UserTypeId = Latest.UserTypeId
                  AND uinfo.DateChanged = Latest.LatestChanged
          WHERE
                  uinfo.EndDate &gt;= GETDATE() OR uinfo.EndDate IS NULL)
      ),
      OutputPhone (UserId, ProvidedPhone, DateChanged, StartDate, EndDate, UserTypeId) AS (
          SELECT
                  CASE
                      WHEN UG1.OrderIndex = 1
                          THEN ISNULL(UG1.UserId, UG2.UserId)
                      WHEN UG2.OrderIndex = 1
                          THEN ISNULL(UG2.UserId, UG1.UserId)
                      ELSE
                          NULL
                  END As UserId
                  , CASE
                      WHEN UG1.OrderIndex = 1
                          THEN ISNULL(UG1.ProvidedNumber, UG2.ProvidedNumber)
                      WHEN UG2.OrderIndex = 1
                          THEN ISNULL(UG2.ProvidedNumber, UG1.ProvidedNumber)
                      ELSE
                          NULL
                  END As ProvidedNumber
                  , --and so on for specified columns
          FROM
                  (
                      SELECT
                      DISTINCT
                              CASE WHEN uct.CurrentUserTypeId = 1 THEN 1 ELSE 2 END OrderIndex
                              , ugp.UserId
                              , ugp.DateChanged
                              , ugp.StartDate
                              , ugp.EndDate
                              , ugp.UserTypeId
                              , COALESCE(ugp.HomePhone, ugp.MobilePhone, ugp.WorkPhone, ugp.OTherPhone) ProvidedNumber
                      FROM
                              UG1Phone ugp
                      INNER JOIN
                              UsersCommonTable uct
                      ON
                              ugp.UserId = uct.UserId AND GETDATE() BETWEEN uct.EffectiveDate and uct.TerminationDate
                  ) UG1
          FULL OUTER JOIN
                  (
                      SELECT
                      DISTINCT
                              CASE WHEN uct.CurrentUserTypeId = 2 THEN 1 ELSE 2 END OrderIndex
                              , ugp.UserId
                              , ugp.DateChanged
                              , ugp.StartDate
                              , ugp.EndDate
                              , ugp.UserTypeId
                              , COALESCE(ugp.HomePhone, ugp.MobilePhone, ugp.WorkPhone, ugp.OTherPhone) ProvidedNumber
                      FROM
                              UG2Phone ugp
                      INNER JOIN
                              UsersCommonTable uct
                      ON
                              ugp.UserId = uct.UserId AND GETDATE() BETWEEN uct.EffectiveDate and uct.TerminationDate
                  ) UG2
          ON
                  UG1.UserId = UG2.UserId
          WHERE
                  1 = CASE
                          WHEN UG1.OrderIndex = 1
                              THEN CASE WHEN UG1.UserTypeId IS NOT NULL THEN 1 ELSE 0 END
                          WHEN UG2.OrderIndex = 1
                              THEN CASE WHEN UG2.UserTypeId IS NOT NULL THEN 1 EKSE 0 END
                          ELSE
                              0
                          END
      ), --and so on to complete the rest of the report
  GO
  </code></pre>