USE [zcus]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[IatricBarAcctTypeHxVw]')
                    AND [type] IN ( N'V' ) )
BEGIN
    DROP VIEW [dbo].[IatricBarAcctTypeHxVw]
END ;
GO

CREATE VIEW [dbo].[IatricBarAcctTypeHxVw]
/*********************************************************************************************************
  _____      _        _         _____           _                                                        
 |_   _|    | |      (_)       / ____|         | |                                                       
   | |  __ _| |_ _ __ _  ___  | (___  _   _ ___| |_ ___ _ __ ___  ___                                    
   | | / _` | __| '__| |/ __|  \___ \| | | / __| __/ _ \ '_ ` _ \/ __|                                   
  _| || (_| | |_| |  | | (__   ____) | |_| \__ \ ||  __/ | | | | \__ \                                   
 |_____\__,_|\__|_|  |_|\___| |_____/ \__, |___/\__\___|_| |_| |_|___/                                   
                                       __/ |                                                             
                                      |___/                                                              
                                                                    info@iatric.com or 978-805-4100
                                                                                                         
    Author:         Thomas Harlan
    Create Date:    December 2020
    Description:    View based on the BAR Account Type History screen
                    
    Test SQL:

        SELECT  * 
        FROM    zcus.dbo.IatricBarAcctTypeHxVw THX
        WHERE   THX.VisitID IN ( SELECT  VisitID
                                 FROM    livefocdb.dbo.RegAcct_Main RAM
                                 WHERE   RAM.AccountNumber = 'A00021355219' )
                AND
                THX.SourceID = 'SIP'
        ORDER BY LineUp ASC
        ;

        -- All Accounts where the end date is before the start date
        SELECT   THX.AccountNumber
                --,THX.LineUp
                ,[EditDate] = THX.OpenDateTime
                ,THX.EffectiveFromDateTime
                ,THX.EffectiveThruDateTime
                ,THX.EffectiveDays
                ,THX.MisLocID
                ,THX.BarAcctTypeID_Class
                ,THX.RegEventUserID
                ,THX.RegEventUserName
        FROM    zcus.dbo.IatricBarAcctTypeHxVw THX
        WHERE   THX.EffectiveThruDateTime < THX.EffectiveFromDateTime
                AND
                THX.SourceID = 'SIP'
                --AND
                --THX.AccountNumber IN ( 'A00021284682','A00021285838','A00021292560','A00021298211' )
                AND
                THX.OpenDateTime BETWEEN GETDATE()-30 AND GETDATE()
        ORDER BY THX.EffectiveDays ASC,THX.AccountNumber,THX.LineUp ASC
        ;   

**********************************************************************************************************/
AS

WITH cteLastEdit AS (
                    SELECT   BAA.SourceID
                            ,BAA.VisitID
                            ,[LastEditDate] = MAX(BAA.AccountTypeEditDateID)
                    FROM    livefocdb.dbo.BarAcct_AcctTypes BAA
                    GROUP BY BAA.SourceID
                            ,BAA.VisitID
                    ) -- List of Accounts and the last edit dates on account history sets
SELECT   AAT.SourceID                           AS SourceID -- 
        ,AAT.VisitID                            AS VisitID -- BarAcct.ObjectID
        ,RAM.AccountNumber
        ,AAT.AccountTypeEditDateID              AS OpenDateTime -- BarAcct.AcctTypeEditDate - Open Date on the screen
        ,AAT.AccountTypeEffectiveDateTimeID     AS EffectiveFromDateTime -- BarAcct.AcctTypeEffDateTime
        ,EffectiveThruDateTime = COALESCE( DATEADD(ms,-3,NXE.AccountTypeEffectiveDateTimeID)
                                          ,ART.RegistrationTypeDischargeDateTime )
        ,EffectiveDays         = DATEDIFF(day,AAT.AccountTypeEffectiveDateTimeID,COALESCE( NXE.AccountTypeEffectiveDateTimeID,ART.RegistrationTypeDischargeDateTime,GETDATE()) )
        ,AAT.RegistrationEventLocation_MisLocID AS MisLocID -- BarAcct.RegEventLocation
        ,MLM.[Name]                             AS MisLocID_Name -- MisLoc.Name
        ,MSM.MisSvcID                           AS MisSvcID -- BarAcct.RegEventService
        ,MSM.[Name]                             AS MisSvcID_Name -- MisSvc.Name
        ,AAT.AccountTypeByDate_BarAcctTypeID    AS BarAcctTypeID -- BarAcct.AcctTypeByDate
        ,ATM.[Name]                             AS BarAcctTypeID_Name -- BarAcctType.Name
        ,ATM.RegistrationClass                  AS BarAcctTypeID_Class
        -- REG data elements
        ,AAT.RegistrationEventDate              AS RegistrationEventDate -- BarAcct.RegEventDate
        ,AAT.RegistrationEventSequenceNumber    AS RegistrationEventSequenceNumber -- BarAcct.RegEventSeqNum
        ,RTM.MisRegTypeID                       AS RegTypeID
        ,RTM.[Name]                             AS RegTypeID_Name
        ,RTM.Class                              AS RegTypeID_Class
        ,REE.User_UnvUserID                     AS RegEventUserID
        ,MPN.NameStored                         AS RegEventUserName
        -- Other data elements
        ,AAT.AccountTypeLtcConversion           AS AccountTypeLtcConversion -- BarAcct.AcctTypeLtcConv
        ,AAT.RowUpdateDateTime                  AS RowUpdateDateTime
        ,LineUp = ROW_NUMBER() OVER ( PARTITION BY AAT.SourceID,AAT.VisitID
                                      ORDER BY AAT.AccountTypeEffectiveDateTimeID ASC )
        ,LineDn = ROW_NUMBER() OVER ( PARTITION BY AAT.SourceID,AAT.VisitID
                                      ORDER BY AAT.AccountTypeEffectiveDateTimeID DESC )

FROM    livefocdb.dbo.BarAcct_AcctTypes AAT
        JOIN      cteLastEdit                             CLE ON ( CLE.SourceID      = AAT.SourceID AND
                                                                   CLE.VisitID       = AAT.VisitID AND
                                                                   CLE.LastEditDate  = AAT.AccountTypeEditDateID )
        LEFT JOIN livefocdb.dbo.BarAcctType_Main          ATM ON ( ATM.BarAcctTypeID = AAT.AccountTypeByDate_BarAcctTypeID AND 
                                                                   ATM.SourceID      = AAT.SourceID )
        LEFT JOIN livefocdb.dbo.MisLoc_Main               MLM ON ( MLM.MisLocID      = AAT.RegistrationEventLocation_MisLocID AND 
                                                                   MLM.SourceID      = AAT.SourceID )
        LEFT JOIN livefocdb.dbo.RegAcct_Main              RAM ON ( RAM.SourceID      = AAT.SourceID AND 
                                                                   RAM.VisitID       = AAT.VisitID )
        LEFT JOIN livefocdb.dbo.RegAcct_RegistrationTypes ART ON ( ART.SourceID      = RAM.SourceID AND 
                                                                   ART.VisitID       = RAM.VisitID AND
                                                                   ART.RegistrationTypeKey_MisRegTypeID = RAM.RegistrationType_MisRegTypeID )
        LEFT JOIN livefocdb.dbo.RegEvents_Events          REE ON ( REE.SourceID      = AAT.SourceID AND
                                                                   REE.VisitID       = AAT.VisitID AND
                                                                   REE.DateID        = AAT.RegistrationEventDate AND
                                                                   REE.SequenceNumberID = AAT.RegistrationEventSequenceNumber )
        LEFT JOIN livefocdb.dbo.MisRegType_Main           RTM ON ( RTM.MisRegTypeID  = REE.RegistrationType_MisRegTypeID AND 
                                                                   RTM.SourceID      = REE.SourceID )
        LEFT JOIN livefocdb.dbo.MisPerson_Names           MPN ON ( MPN.SourceID      = REE.SourceID AND
                                                                   MPN.UnvUserID     = REE.User_UnvUserID )
        LEFT JOIN livefocdb.dbo.MisSvc_Main               MSM ON ( MSM.MisSvcID      = COALESCE(AAT.RegistrationEventService_MisSvcID,RAM.ServiceInpatient_MisSvcID,RAM.ServiceOutpatient_MisSvcID)  AND 
                                                                   MSM.SourceID      = COALESCE(AAT.SourceID,RAM.SourceID) )
        OUTER APPLY ( SELECT TOP 1
                             BAT.AccountTypeEffectiveDateTimeID
                      FROM   livefocdb.dbo.BarAcct_AcctTypes BAT
                      WHERE  BAT.SourceID = AAT.SourceID
                             AND
                             BAT.VisitID = AAT.VisitID
                             AND
                             BAT.AccountTypeEditDateID = AAT.AccountTypeEditDateID
                             AND
                             BAT.AccountTypeEffectiveDateTimeID > AAT.AccountTypeEffectiveDateTimeID
                      ORDER BY BAT.AccountTypeEffectiveDateTimeID DESC
                    ) NXE -- Get the next Account Type Line after this one
;

/* end of code */
GO