USE [master]
GO


ALTER DATABASE [EDD] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [EDD] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [EDD] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [EDD] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [EDD] SET ARITHABORT OFF 
GO
ALTER DATABASE [EDD] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [EDD] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [EDD] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [EDD] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [EDD] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [EDD] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [EDD] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [EDD] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [EDD] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [EDD] SET  DISABLE_BROKER 
GO
ALTER DATABASE [EDD] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [EDD] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [EDD] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [EDD] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [EDD] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [EDD] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [EDD] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [EDD] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [EDD] SET  MULTI_USER 
GO
ALTER DATABASE [EDD] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [EDD] SET DB_CHAINING OFF 
GO
ALTER DATABASE [EDD] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [EDD] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [EDD] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [EDD] SET ACCELERATED_DATABASE_RECOVERY = OFF  
GO
ALTER DATABASE [EDD] SET QUERY_STORE = ON
GO
ALTER DATABASE [EDD] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200, WAIT_STATS_CAPTURE_MODE = ON)
GO

USE [EDD]
GO

/****** Create EDDTrracking table - to track row counts between DAU refresh cycles******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==============================================================================
    Object Name:    dbo.EDDTracking

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Table to hold persistent data

==============================================================================*/
CREATE TABLE [dbo].[EDDTracking](
	[MonitoredDate] [date] NULL,
	[TableName] [varchar](max) NULL,
	[BeginningRowCount] [int] NULL,
	[Pulltime] [datetime2](7) NULL,
	[CheckRowCount] [int] NULL,
	[PullTime2] [datetime2](7) NULL,
	[Check3RowCount] [int] NULL,
	[PullTime3] [datetime2](7) NULL,
	[Check4RowCount] [int] NULL,
	[PullTime4] [datetime2](7) NULL,
	[Check5RowCount] [int] NULL,
	[PullTime5] [datetime2](7) NULL,
	[Check6RowCount] [int] NULL,
	[PullTime6] [datetime2](7) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Create view to read DAU tracking and present ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==============================================================================
    Object Name:    dbo.v_EDDChangeTracker

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        View representing DAU tracking changes across monitored tables

==============================================================================*/
CREATE VIEW [dbo].[v_EDDChangeTracker]
AS
SELECT        TOP (100) PERCENT MonitoredDate, TableName, BeginningRowCount, CheckRowCount, CheckRowCount - BeginningRowCount AS FirstChangeSet, Check3RowCount, Check3RowCount - CheckRowCount AS SecondChangeSet, 
                         Check4RowCount, Check4RowCount - Check3RowCount AS ChangeSet4, Check5RowCount, Check5RowCount - Check4RowCount AS ChangeSet5, Check6RowCount, Check6RowCount - Check5RowCount AS ChangeSet6, 
                         Check6RowCount - BeginningRowCount AS DailyChangeSet
FROM            dbo.EDDTracking
ORDER BY MonitoredDate DESC, TableName
GO

/****** Create View for Daily DAU Monitoring and feeding to PA flow email ******/
/*==============================================================================
    Object Name:    dbo.v_EDDDAUPrevDay

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        View representing prior day DAU changes for monitored tables

==============================================================================*/
CREATE VIEW [dbo].[v_EDDDAUPrevDay]
AS(
SELECT MonitoredDate, TableName, DailyChangeSet
FROM v_EDDChangeTracker
WHERe MonitoredDate = CAST(DATEADD(d, -1, GETDATE()) as date)
ANd DailyChangeSet <> 0
)
GO

/****** Create EDDLog Table for general logging ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*==============================================================================
    Object Name:    dbo.EDDLog

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Table to hold persistent data

==============================================================================*/
CREATE TABLE [dbo].[EDDLog](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[DateTimeStamp] [datetime2](7) NULL,
	[LogDescription] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


/****** Create Views that feed into EDD Base Report v2026 ******/
/****** used to handle parameterization of the roles ******/
/*==============================================================================
    Object Name:    dbo.CLIENTCRS

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        View representing client and staff role assignments

==============================================================================*/
CREATE VIEW [dbo].[CLIENTCRS]
AS
SELECT ClientIdent, StaffIDent, FirmClientStaffAssignmentName
FROM CCH_ENT..CLIENTCRS
GO;

/*==============================================================================
    Object Name:    dbo.STAFF

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        View representing staff identifiers and staff names

==============================================================================*/
CREATE VIEW [dbo].[STAFF] 
AS
SELECT StaffIdent, StaffName
FROM CCH_ENT..STAFF
GO

/****** Create user defined functions to handle phone formatting for CRM style queries and migrations ******/
/*==============================================================================
    Object Name:    dbo.fnRemoveNonNumericCharacters

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Function to remove non-numeric characters from a string

==============================================================================*/
CREATE   FUNCTION [dbo].[fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
RETURNS VARCHAR(1000)
AS
BEGIN
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
    BEGIN
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    END
    RETURN @strText
END
GO

/*==============================================================================
    Object Name:    dbo.FormatPhoneNumber

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Function to format a phone number for display

==============================================================================*/
CREATE   FUNCTION [dbo].[FormatPhoneNumber](@phoneNumber VARCHAR(10))
RETURNS VARCHAR(14)
BEGIN
RETURN
	CASE WHEN LEN(@phoneNumber) = 10 
		THEN '(' + SUBSTRING(@phoneNumber, 1, 3) + ') ' + 
           SUBSTRING(@phoneNumber, 4, 3) + '-' + 
           SUBSTRING(@phoneNumber, 7, 4)
		WHEN LEN(@phoneNumber) = 7
		THEN SUBSTRING(@phoneNumber, 1, 3) + '-' +
			SUBSTRING(@phoneNumber, 4, 4)
		ELSE NULL
		END
END
GO

/****** UB WIP Tables and Proc ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Create UB WIP Detail and UB WIP Analysis tables ******/ 
/*==============================================================================
    Object Name:    dbo.EDD_UBWIP_Detail

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Table to hold persistent data

==============================================================================*/
CREATE TABLE [dbo].[EDD_UBWIP_Detail](
	[PeriodEnd] [date] NULL,
	[ClientIdent] [varchar](38) NULL,
	[ClientIdSubId] [nvarchar](100) NULL,
	[ClientSortName] [nvarchar](200) NULL,
	[AgingAmount] [decimal](18, 2) NULL,
	[DaysOld] [int] NULL,
	[0-30] [decimal](18, 2) NULL,
	[31-60] [decimal](18, 2) NULL,
	[61-90] [decimal](18, 2) NULL,
	[91-120] [decimal](18, 2) NULL,
	[120+] [decimal](18, 2) NULL,
	[Over 90] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

/*==============================================================================
    Object Name:    dbo.EDD_UBWIP_Analysis

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Table to hold persistent data

==============================================================================*/
CREATE TABLE [dbo].[EDD_UBWIP_Analysis](
	[ClientIdent] [varchar](38) NULL,
	[PeriodEnd] [date] NULL,
	[TotalUB] [decimal](18, 2) NULL,
	[Under90] [decimal](18, 2) NULL,
	[Over90] [decimal](18, 2) NULL,
	[PctOver90] [float] NULL,
	[ChangeOver90] [decimal](18, 2) NULL
) ON [PRIMARY]
GO

/****** UB WIP Refresh Proc ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*==============================================================================
    Object Name:    dbo.RefreshUBWIP

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Procedure to refresh UB WIP detail and analysis tables

==============================================================================*/
CREATE OR ALTER   PROCEDURE [dbo].[RefreshUBWIP]
    @startDate DATE = NULL,
    @endDate   DATE = NULL
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO EDD..EDDLog (DateTimeStamp, LogDescription)
    VALUES (SYSDATETIME(), 'Began Daily UB WIP Refresh');

    -- Defaults: full history rebuild
    IF @startDate IS NULL SET @startDate = EOMONTH(CONVERT(DATE, '2020-01-01'));
    IF @endDate   IS NULL SET @endDate   = EOMONTH(GETDATE());

    -- Normalize to month-end just in case
    SET @startDate = EOMONTH(@startDate);
    SET @endDate   = EOMONTH(@endDate);

    -- Clear existing data for the date range
    DELETE FROM dbo.EDD_UBWIP_Detail
    WHERE PeriodEnd >= @startDate
      AND PeriodEnd <= @endDate;

    DELETE FROM dbo.EDD_UBWIP_Analysis
    WHERE PeriodEnd >= @startDate
      AND PeriodEnd <= @endDate;

    INSERT INTO EDD..EDDLog (DateTimeStamp, LogDescription)
    VALUES (SYSDATETIME(), 'Old data removed');

    DECLARE @currentDate DATE = @startDate;

    WHILE @currentDate <= @endDate
BEGIN
    IF OBJECT_ID('tempdb..#DetailRows') IS NOT NULL
        DROP TABLE #DetailRows;

    ;WITH NettedWIP AS
    (
        SELECT
            v.ClientIdent,
            v.AgingDate,
            SUM(v.AgingAmount) AS AgingAmount
        FROM CCH_ENT..UVW_WIPAgingDetail v               --UPDATE DB
        WHERE v.BalanceDate <= @currentDate
        GROUP BY
            v.ClientIdent,
            v.AgingDate
        HAVING SUM(v.AgingAmount) <> 0
    )
    SELECT
        @currentDate AS PeriodEnd,
        CAST(n.ClientIdent AS VARCHAR(38)) AS ClientIdent,
        c.ClientIdSubId,
        c.ClientSortName,
        n.AgingAmount,
        DATEDIFF(DAY, n.AgingDate, @currentDate) + 1 AS DaysOld,
        CASE
            WHEN DATEDIFF(DAY, n.AgingDate, @currentDate) + 1 <= 30
            THEN n.AgingAmount ELSE 0
        END AS [0-30],
        CASE
            WHEN DATEDIFF(DAY, n.AgingDate, @currentDate) + 1 BETWEEN 31 AND 60
            THEN n.AgingAmount ELSE 0
        END AS [31-60],
        CASE
            WHEN DATEDIFF(DAY, n.AgingDate, @currentDate) + 1 BETWEEN 61 AND 90
            THEN n.AgingAmount ELSE 0
        END AS [61-90],
        CASE
            WHEN DATEDIFF(DAY, n.AgingDate, @currentDate) + 1 BETWEEN 91 AND 120
            THEN n.AgingAmount ELSE 0
        END AS [91-120],
        CASE
            WHEN DATEDIFF(DAY, n.AgingDate, @currentDate) + 1 > 120
            THEN n.AgingAmount ELSE 0
        END AS [120+],
        CASE
            WHEN DATEDIFF(DAY, n.AgingDate, @currentDate) + 1 > 90
            THEN n.AgingAmount ELSE 0
        END AS [Over 90]
    INTO #DetailRows
    FROM NettedWIP n
    INNER JOIN CCH_ENT..CLIENT c                 ---UPDATE DB
        ON n.ClientIdent = c.ClientIdent;

    INSERT INTO dbo.EDD_UBWIP_Detail
    (
        PeriodEnd,
        ClientIdent,
        ClientIdSubId,
        ClientSortName,
        AgingAmount,
        DaysOld,
        [0-30],
        [31-60],
        [61-90],
        [91-120],
        [120+],
        [Over 90]
    )
    SELECT
        PeriodEnd,
        ClientIdent,
        ClientIdSubId,
        ClientSortName,
        AgingAmount,
        DaysOld,
        [0-30],
        [31-60],
        [61-90],
        [91-120],
        [120+],
        [Over 90]
    FROM #DetailRows;

    INSERT INTO dbo.EDD_UBWIP_Analysis
    (
        ClientIdent,
        PeriodEnd,
        TotalUB,
        Under90,
        Over90
    )
    SELECT
        d.ClientIdent,
        d.PeriodEnd,
        SUM(d.AgingAmount) AS TotalUB,
        SUM(d.AgingAmount) - SUM(d.[Over 90]) AS Under90,
        SUM(d.[Over 90]) AS Over90
    FROM #DetailRows d
    GROUP BY
        d.ClientIdent,
        d.PeriodEnd;

    SET @currentDate = EOMONTH(DATEADD(MONTH, 1, @currentDate));
END;

    INSERT INTO EDD..EDDLog (DateTimeStamp, LogDescription)
    VALUES (SYSDATETIME(), 'EDD_UBWIP_Detail and EDD_UBWIP_Analysis updated');

    ;WITH PrevPeriod AS
    (
        SELECT
            a.ClientIdent,
            a.PeriodEnd,
            a.TotalUB,
            a.Over90,
            LAG(a.Over90) OVER
            (
                PARTITION BY a.ClientIdent
                ORDER BY a.PeriodEnd
            ) AS PrevOver90
        FROM dbo.EDD_UBWIP_Analysis a
    )
    UPDATE a
    SET
        a.PctOver90    = CAST(a.Over90 AS FLOAT) / NULLIF(a.TotalUB, 0),
        a.ChangeOver90 = a.Over90 - p.PrevOver90
    FROM dbo.EDD_UBWIP_Analysis a
    INNER JOIN PrevPeriod p
        ON  a.ClientIdent = p.ClientIdent
        AND a.PeriodEnd   = p.PeriodEnd;

    INSERT INTO EDD..EDDLog (DateTimeStamp, LogDescription)
    VALUES (SYSDATETIME(), 'UB WIP Percentages updated');
END
GO

/****** Historical AR Aging to feed AR aging graphs ******/
/* 1. Create the table where we will persist the data */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*==============================================================================
    Object Name:    dbo.ARAGingDetail

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Table to hold persistent data

==============================================================================*/
CREATE TABLE [dbo].[ARAGingDetail](
	[ClientIdent] [numeric](38, 0) NULL,
	[ClientIdSubId] [nvarchar](20) NULL,
	[ClientSortNAme] [nvarchar](max) NULL,
	[AgingAmount] [decimal](19, 2) NULL,
	[0-30] [decimal](19, 2) NULL,
	[31-60] [decimal](19, 2) NULL,
	[61-90] [decimal](19, 2) NULL,
	[91-120] [decimal](19, 2) NULL,
	[Over 120] [decimal](19, 2) NULL,
	[AsOfDate] [date] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


/*2. Base process to bring up the data results*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



/*==============================================================================
    Object Name:    dbo.HistoricalARAgingBaseProcess

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Procedure to generate AR aging detail results for a given as-of date

==============================================================================*/
CREATE OR ALTER PROCEDURE [dbo].[HistoricalARAgingBaseProcess]
	@mEnddate DATETIME
	

AS	
BEGIN
	SET NOCOUNT ON;
	declare @mAddone as int = 0,
			@mShowFC bit = 1,
			@mBucketDays int = 30


	SELECT
		c.ClientIdent,
		c.ClientIdSubId,
		c.ClientSortName,
		sub.AgingAmount,
		sub.B1 AS '0-30',
		sub.B2 AS '31-60',
		sub.B3 AS '61-90',
		sub.B4 AS '91-120',
		sub.B5 AS 'Over 120',
		@mEnddate AS AsOfDate
	FROM

		(SELECT
			ClientIdent,
			SUM(AgingAmount) AS AgingAmount,
			SUM(CASE WHEN DATEDIFF(d,AgingBucketDate,@mEnddate) BETWEEN 0                                 AND (@mBucketdays * 1 + @mAddone) THEN AgingAmount ELSE 0 END) as B1,
			SUM(CASE WHEN DATEDIFF(d,AgingBucketDate,@mEnddate) BETWEEN (@mBucketdays * 1 + 1 + @mAddone) AND (@mBucketdays * 2 + @mAddone) THEN AgingAmount ELSE 0 END) as B2,
			SUM(CASE WHEN DATEDIFF(d,AgingBucketDate,@mEnddate) BETWEEN (@mBucketdays * 2 + 1 + @mAddone) AND (@mBucketdays * 3 + @mAddone) THEN AgingAmount ELSE 0 END) as B3,
			SUM(CASE WHEN DATEDIFF(d,AgingBucketDate,@mEnddate) BETWEEN (@mBucketdays * 3 + 1 + @mAddone) AND (@mBucketdays * 4 + @mAddone) THEN AgingAmount ELSE 0 END) as B4,
			SUM(CASE WHEN DATEDIFF(d,AgingBucketDate,@mEnddate) < 0 OR DATEDIFF(d,AgingBucketDate,@mEnddate) >= (@mBucketdays * 4 + 1 + @mAddone) THEN AgingAmount ELSE 0 END) as B5

		FROM CCH_ENT..UVW_ARAgingDetail
		WHERE
			/*the filtering date must be set to BalanceDate in order to match CCH Axcess report total */
			BalanceDate <= @mEnddate
			AND (CASE WHEN document LIKE 'FC%' OR applyto LIKE 'FC%' THEN @mShowFC ELSE 1 END) = 1
		GROUP BY ClientIdent
		HAVING SUM(AgingAmount) <> 0
		) AS sub

	LEFT JOIN CCH_ENT..CLIENT c ON c.ClientIdent = sub.ClientIdent
	


	ORDER BY ClientIdSubId
END
GO



/* Historical run of the data (run once)*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*==============================================================================
    Object Name:    dbo.HistoricARAgingPopulateARAgingDetTbl_INITIAL_LOAD

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Procedure to perform the initial historical load of AR aging detail data

==============================================================================*/
CREATE OR ALTER PROCEDURE [dbo].[HistoricARAgingPopulateARAgingDetTbl_INITIAL_LOAD]


AS
BEGIN


INSERT INTO EDD..EDDLog (DateTimeStamp, LogDescription)
VALUES (GETDATE(), 'Historical AR populate persistent table - initial load began');

/*to clear entries from earlier today*/
DELETE FROM EDD..ARAgingDetail
WHERE AsOfDate = '1980-01-01'  ----OR APPROPRIATE MIN(BalanceDate) in the UVW_ARAgingDetail view


/*to populate initial info*/
DECLARE @AsOFDate date
        ,@Runstart DATETIME2(7) = SYSDATETIME()
        ,@Runend DATETIME2(7)
        ,@RuntimeSeconds BIGINT
        ,@Runtime_mmss VARCHAR(16)
        ,@RowCount INT;

WHILE @AsOFDate <= DATEADD(day, -1, SYSDATETIME()) ---- through yesterday
BEGIN 

    INSERT INTO EDD..ARAGingDetail
    EXEC [dbo].[HistoricalARAgingBaseProcess]
		    @mEnddate = @AsOfDate

    SET @AsOFDate = DATEADD(d, 1, @AsOfDate)

END

/*log details*/
SET @Runend = SYSDATETIME();
SET @RuntimeSeconds = DATEDIFF_BIG(SECOND, @RunStart, @RunEnd);
SET @Runtime_mmss = CONCAT(
    @RuntimeSeconds / 60,
    ':',
    RIGHT('00' + CONVERT(VARCHAR(2), @RuntimeSeconds % 60), 2)
);

SET @RowCount = (
    SELECT COUNT(*) 
    FROM EDD..ARAgingDetail 
    WHERE AsOFDate <= cast(@RunEnd AS DATE)
);


INSERT INTO EDD..EDDLog (DateTimeStamp, LogDescription)
VALUES
(
  SYSDATETIME(),
  CONCAT(
    'Historical AR populate persistent table - initial load complete, duration: ',
    @Runtime_mmss,
    ', rows updated: ',
    FORMAT(@RowCount, 'N0')
  )
);


END
GO


/****** DAILY RUN - PRODUCTION ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*==============================================================================
    Object Name:    dbo.HistoricARAgingPopulateARAgingDetTbl

    Author:         Eric Nichols
    Company:        Eric Does Data, LLC
    Created:        2019-2025
    Last Modified:  2026

    Description:
        Procedure to populate the daily AR aging detail table

==============================================================================*/
CREATE PROCEDURE [dbo].[HistoricARAgingPopulateARAgingDetTbl]


AS
BEGIN


INSERT INTO EDD..EDDLog (DateTimeStamp, LogDescription)
VALUES (GETDATE(), 'Historical AR populate persistent table - daily update began');

/*to clear entries from earlier today*/
DELETE FROM EDD..ARAgingDetail
WHERE AsOfDate = CAST(SYSDATETIME() as DATE)


/*to populate latest info for today*/
DECLARE @AsOFDate date = SYSDATETIME()
        ,@Runstart DATETIME2(7) = SYSDATETIME()
        ,@Runend DATETIME2(7)
        ,@RuntimeSeconds BIGINT
        ,@Runtime_mmss VARCHAR(16)
        ,@RowCount INT;

WHILE @AsOFDate <= SYSDATETIME()
BEGIN 

    INSERT INTO EDD..ARAGingDetail
    EXEC [dbo].[HistoricalARAgingBaseProcess]
		    @mEnddate = @AsOfDate

    SET @AsOFDate = DATEADD(d, 1, @AsOfDate)

END

/*log details*/
SET @Runend = SYSDATETIME();
SET @RuntimeSeconds = DATEDIFF_BIG(SECOND, @RunStart, @RunEnd);
SET @Runtime_mmss = CONCAT(
    @RuntimeSeconds / 60,
    ':',
    RIGHT('00' + CONVERT(VARCHAR(2), @RuntimeSeconds % 60), 2)
);

SET @RowCount = (
    SELECT COUNT(*) 
    FROM EDD.dbo.ARAgingDetail 
    WHERE AsOFDate = cast(@RunEnd AS DATE)
);


INSERT INTO EDD..EDDLog (DateTimeStamp, LogDescription)
VALUES
(
  SYSDATETIME(),
  CONCAT(
    'Historical AR populate persistent table - daily update complete, duration: ',
    @Runtime_mmss,
    ', rows updated: ',
    FORMAT(@RowCount, 'N0')
  )
);


END
GO


USE [master]
GO
ALTER DATABASE [EDD] SET  READ_WRITE 
GO