add new fields to script

so i've inherited a script that is driving me nuts as i can see where to add two new fields. i added them to the desired table but this scripts is a job that drops and recreates the table and as a result my two new columns disappear after the script runs. i tried adding in certain areas but it fails

--current script and i need to add the following to the msaccess.register table:
--StatusType nvarchar(35) null
--MonthlyHoursBasedFee money null

SET QUERY_GOVERNOR_COST_LIMIT 0

declare @endDate as datetime
Set @endDate = CAST(getdate() as date)

If Object_Id('tempdb..#Register') is Not Null
      Drop table #Register
      
Select DISTINCT
      StatusDate
,      ContractType
,      ContractName
,      AnnualAllocatedHours
,      TotalHours
,      MonthlyHoursBasedFee
,      StatusType
,      [Address]
,      City
,      PostalCode
,      StateAbbreviation
,   IATACode3
,      RegisterID
,      ContractID
,      EntityID
,      EntityKeyID
,      ApplicableDate
,      HoursValue
,      CreditValue
,   MoneyValue
,      RegisterTypeLookupID
,      RegisterType
,      [Description]
,      ExpirationDate
,      DateCreated
,      CreatedByUserID
,      CreatedBy
,      IsEstimated
,      ItemTypeID
,      FutureHours
,      FutureCredit
,       MonthHours
,      MonthCredits
,      DisplayName
,      REGION  
,   IssueID
,   StartDates
,   EndDates
,   StatusTypeID
into #Register
From
(

SELECT
       C.StatusDate
,      C.ContractType
,      C.ContractName
,      C.AnnualAllocatedHours
,      C.TotalHours
,      C.MonthlyHoursBasedFee
,      ST.StatusType
,      C.[Address]
,      C.City
,      C.PostalCode
,      S.StateAbbreviation
,   CNTY.IATACode3
,      R.RegisterID
,      R.ContractID
,      R.EntityID    
,      R.EntityKeyID
,      R.ApplicableDate
,      R.HoursValue
,      R.CreditValue
,   R.MoneyValue
,      R.RegisterTypeLookupID
,      L.LookupValue AS RegisterType
,      R.[Description]
,      R.ExpirationDate
,      R.DateCreated
,      R.CreatedByUserID
,      U.DisplayName AS CreatedBy
,      R.IsEstimated
,      R.ItemTypeID
,      0 AS FutureHours
,      0 AS FutureCredit
,      0 AS MonthHours
,      0 AS MonthCredits
,      U.DisplayName
,      'REGION' = (CASE WHEN S.StateAbbreviation IN ('ME', 'VT', 'NH', 'MA', 'CT', 'RI', 'NY', 'PA', 'NJ', 'DE', 'WV', 'VA', 'MD', 'MI', 'OH', 'IL','IN', 'KY') THEN 'Northeast'
                        WHEN S.StateAbbreviation IN ('FL', 'GA', 'NC', 'SC', 'AL', 'TN', 'AR', 'MS', 'LA')THEN 'Southeast'
                        WHEN S.StateAbbreviation IN ('OK', 'TX', 'ND', 'SD', 'NE', 'KS', 'IA', 'MN', 'WI', 'MO')THEN 'Central'
                        WHEN S.StateAbbreviation IN ('WA', 'OR', 'ID', 'MT', 'WY', 'UT', 'CO', 'NV', 'CA', 'AZ', 'NM')THEN 'Western'
                              ELSE 'Other' END)  
, 0 AS IssueID
,  StartDates = C.StartDate
,  EndDates = C.EndDate
, C.StatusTypeID

FROM
ASTRO.dbo.tm_Register R with(NOLOCK)INNER JOIN ASTRO.dbo.tl_Lookups L with(NOLOCK) ON R.RegisterTypeLookupID = L.LookupID INNER JOIN
ASTRO.dbo.tm_Users U with(NOLOCK) ON R.CreatedByUserID = U.UserID INNER JOIN
ASTRO.dbo.tm_Contracts C with(NOLOCK) ON R.ContractID = C.ContractID INNER JOIN
ASTRO.dbo.ts_StatusTypes ST with(NOLOCK) ON C.StatusTypeID = ST.StatusTypeID LEFT OUTER JOIN
ASTRO.dbo.tl_States S with(NOLOCK) ON C.StateID = S.StateID LEFT OUTER JOIN
ASTRO.dbo.tl_Countries CNTY with(NOLOCK) ON C.CountryID = CNTY.CountryID
WHERE
      C.ContractName <> 'TEMPLATE'
AND NOT (C.ContractID IN (3, 656))
AND (C.StatusTypeID = 1
OR (C.StatusTypeID = 54 AND C.EndDate > @EndDate)
OR (C.StatusTypeID NOT IN (1,54) AND R.ApplicableDate > @EndDate))

                                     
UNION ALL
/* Get the Usage line items */

SELECT
      C.StatusDate
,      C.ContractType
,      C.ContractName
,      C.AnnualAllocatedHours
,      C.TotalHours
,      C.MonthlyHoursBasedFee
,      ST.StatusType
,      C.[Address]
,      C.City
,      C.PostalCode
,      S.StateAbbreviation
,      CNTY.IATACode3
,      ISNULL(F.FlightID, L.LegID) AS RegisterID
,      L.ContractID
,EntityID = CASE WHEN F.FlightID IS NOT NULL THEN 16 ELSE 13 END    
,ISNULL(F.FlightID, L.LegID) as EntityKeyID                        
,      ApplicableDate =
            Cast(ISNULL(F.DepartureTime, ASTRO.dbo.fn_GetUtcDateTime(L.DepartureTime))as date)

,      - ISNULL(F.ContractBaseHours, CASE WHEN F.FlightID IS NOT NULL THEN DATEDIFF(mi, F.DepartureTime, F.ArrivalTime) / 60.0
                                       WHEN L.UseCredits = 0 AND F.FlightID IS NULL THEN DATEDIFF(mi, L.DepartureTime, L.ArrivalTime) / 60.0
                                       ELSE 0 END) AS HoursValue
                                       
                                       
,      - ISNULL(F.ContractCreditHours, CASE WHEN F.FlightID IS NOT NULL THEN DATEDIFF(mi, F.DepartureTime, F.ArrivalTime) / 60.0
                                       WHEN L.UseCredits = 1 AND F.FlightID IS NULL THEN DATEDIFF(mi, L.DepartureTime, L.ArrivalTime) / 60.0
                                       ELSE 0 END) AS CreditsValue
                                       
                                       

,      0 AS MoneyValue
,      0 AS RegisterTypeLookupID
,      'Usage' AS RegisterType
,   'Trip # ' + T .TripNumber + '; ' + CASE WHEN F.FlightID IS NOT NULL THEN 'Flight: ' + F.Itinerary ELSE 'Leg: ' + O.AirportICAOCode + ' -> ' + D .AirportICAOCode END AS [Description]
,      NULL AS ExpirationDate
,   ISNULL(F.DateCreated, L.DateCreated) AS DateCreated
,      NULL AS CreatedByUserID
,      NULL AS CreatedBy
,      CASE WHEN F.FlightID IS NOT NULL THEN F.IsEstimated ELSE 1 END AS IsEstimated
,      1 AS ItemTypeID

,      /*FlightUsage*/
      (CASE WHEN F.FlightID IS NULL THEN ISNULL(F.ContractBaseHours,
            CASE WHEN L.UseCredits = 0 THEN ROUND(DATEDIFF(mi, L.DepartureTime, L.ArrivalTime) / 60.0, 1) ELSE 0 END)
                                    ELSE 0 END) AS FutureHours
,      FutureCredit = (CASE WHEN F.FlightID IS NULL THEN ISNULL(F.ContractCreditHours,
                                    CASE WHEN L.UseCredits = 1 THEN ROUND(DATEDIFF(mi, L.DepartureTime, L.ArrivalTime) / 60.0, 1) ELSE 0 END) ELSE 0 END)
                  
,      MonthHours = (CASE WHEN L.DepartureTime >= DateAdd(MONTH, - 1, GETDate()) AND L.DepartureTime <= GETDate() THEN ISNULL(F.ContractBaseHours,
                                    CASE WHEN L.UseCredits = 0 THEN ROUND(DATEDIFF(mi, L.DepartureTime, L.ArrivalTime) / 60.0, 1) ELSE 0 END) ELSE 0 END)
                                    
,      MonthCredits = (CASE WHEN L.DepartureTime >= DateAdd(MONTH, - 1, GETDate()) AND L.DepartureTime <= GETDate() THEN ISNULL(F.ContractCreditHours,
                                    CASE WHEN L.UseCredits = 1 THEN ROUND(DATEDIFF(mi, L.DepartureTime, L.ArrivalTime) / 60.0, 1) ELSE 0 END) ELSE 0 END)
                                     
,      'N/A' AS DisplayName
,      'REGION' = (CASE WHEN S.StateAbbreviation IN ('ME', 'VT', 'NH', 'MA', 'CT', 'RI', 'NY', 'PA', 'NJ', 'DE', 'WV', 'VA', 'MD', 'MI', 'OH', 'IL', 'IN','KY') THEN 'Northeast'
                      WHEN S.StateAbbreviation IN ('FL', 'GA', 'NC', 'SC', 'AL', 'TN', 'AR', 'MS', 'LA')THEN 'Southeast'
                      WHEN S.StateAbbreviation IN ('OK', 'TX', 'ND', 'SD', 'NE', 'KS', 'IA', 'MN', 'WI', 'MO')THEN 'Central'
                      WHEN S.StateAbbreviation IN ('WA', 'OR', 'ID', 'MT', 'WY', 'UT', 'CO', 'NV', 'CA', 'AZ', 'NM') THEN 'Western'
                                    ELSE 'Other' END)
, IssueID =(SELECT Max(IssueID) FROM ASTRO.dbo.tm_Issues I WHERE T .TripID = I.EntityKeyID)
,  StartDates = C.StartDate
,  EndDates = C.EndDate
, C.StatusTypeID
FROM  
ASTRO.dbo.tm_Legs L with(NOLOCK) INNER JOIN ASTRO.dbo.tm_Contracts C with(NOLOCK) ON L.ContractID = C.ContractID INNER JOIN
ASTRO.dbo.ts_StatusTypes ST with(NOLOCK) ON C.StatusTypeID = ST.StatusTypeID LEFT OUTER JOIN
ASTRO.dbo.tl_States S with(NOLOCK) ON C.StateID = S.StateID LEFT OUTER JOIN
ASTRO.dbo.tl_Countries CNTY with(NOLOCK) ON C.CountryID = CNTY.CountryID INNER JOIN
ASTRO.dbo.tm_Trips T with(NOLOCK) ON L.TripID = T .TripID INNER JOIN
ASTRO.dbo.tl_Airports O with(NOLOCK) ON L.OriginAirportID = O.AirportID INNER JOIN
ASTRO.dbo.tl_Airports D with(NOLOCK) ON L.DestinationAirportID = D .AirportID LEFT OUTER JOIN
     (SELECT /*      FlightUsage: */
            FLT.ArrivalTime
      ,      FLT.FlightID
      ,      FLT.LegID
      ,      FLT.DepartureTime
      ,   ContractBaseHours = CASE WHEN FLT.StatusTypeID = 44 THEN FLT.ContractBaseHours ELSE CASE WHEN LG.UseCredits = 0 THEN ROUND(DATEDIFF(mi,FLT.DepartureTime, FLT.ArrivalTime) / 60.0, 1) ELSE 0 END END
      ,      ContractCreditHours = CASE WHEN FLT.StatusTypeID = 44 THEN FLT.ContractCreditHours ELSE CASE WHEN LG.UseCredits = 1 THEN ROUND(DATEDIFF(mi,FLT.DepartureTime, FLT.ArrivalTime) / 60.0, 1) ELSE 0 END END
      ,      FLT.DateCreated
      ,   O.AirportICAOCode + ' -> ' + D .AirportICAOCode AS Itinerary
      ,      IsEstimated = CASE WHEN FLT.StatusTypeID = 44 THEN 0 ELSE 1 END
     FROM ASTRO.dbo.tm_Flights FLT with(NOLOCK) INNER JOIN ASTRO.dbo.tl_Airports O with(NOLOCK) ON FLT.OriginAirportID = O.AirportID INNER JOIN
     ASTRO.dbo.tl_Airports D with(NOLOCK) ON FLT.DestinationAirportID = D .AirportID INNER JOIN
     ASTRO.dbo.tm_Legs LG with(NOLOCK) ON FLT.LegID = LG.LegID
     WHERE IsIncluded = 1       
            ) F ON L.LegID = F.LegID
WHERE
L.StatusTypeID <> 13
AND T.StatusTypeID <> 13             
AND (F.FlightID IS NOT NULL OR T.StatusTypeID IN (10,120))

AND C.ContractName <> 'TEMPLATE'
AND NOT (C.ContractID IN (3, 656))
AND (C.StatusTypeID = 1
OR (C.StatusTypeID = 54 AND C.EndDate > @EndDate)      
OR (C.StatusTypeID NOT IN (1,54) AND ISNULL(F.DepartureTime, ASTRO.dbo.fn_GetUtcDateTime(L.DepartureTime)) > @EndDate))    

UNION ALL

SELECT
      C.StatusDate
,      C.ContractType
,      C.ContractName
,      C.AnnualAllocatedHours
,      C.TotalHours
,      C.MonthlyHoursBasedFee
,      ST.StatusType
,      C.[Address]
,      C.City
,      C.PostalCode
,      S.StateAbbreviation
,      CNTY.IATACode3
,      R.RegisterID
,      R.ContractID
,      R.EntityID    
,      R.EntityKeyID
,      ApplicableDate = dateadd(second, - 1, CONVERT(datetime,R.ExpirationDate))
,      R.HoursValue
,      CreditValue = - R.CreditValue
,      R.MoneyValue
,      R.RegisterTypeLookupID
,      L.LookupValue AS RegisterType
,      [Description] =      'Credit Expiration'      
,      R.ExpirationDate
,      R.DateCreated
,      R.CreatedByUserID
,   U.DisplayName AS CreatedBy
,      R.IsEstimated
,      R.ItemTypeID
,      0 AS FutureHours
,      0 AS FutureCredit
,      0 AS MonthHours
,      0 AS MonthCredits
,      U.DisplayName
,      'REGION' = (CASE WHEN S.StateAbbreviation IN ('ME', 'VT', 'NH', 'MA', 'CT', 'RI', 'NY', 'PA', 'NJ', 'DE', 'WV', 'VA', 'MD', 'MI', 'OH', 'IL', 'IN', 'KY')THEN 'Northeast'
                              WHEN S.StateAbbreviation IN ('FL', 'GA', 'NC', 'SC', 'AL', 'TN', 'AR', 'MS', 'LA') THEN 'Southeast'
                              WHEN S.StateAbbreviation IN ('OK','TX', 'ND', 'SD', 'NE', 'KS', 'IA', 'MN', 'WI', 'MO') THEN 'Central'
                              WHEN S.StateAbbreviation IN ('WA', 'OR', 'ID', 'MT', 'WY', 'UT', 'CO', 'NV', 'CA', 'AZ','NM') THEN 'Western'
                                    ELSE 'Other' END)  
, 0 AS IssueID
,  StartDates = C.StartDate
,  EndDates = C.EndDate
, C.StatusTypeID
FROM
ASTRO.dbo.tm_Register R with(NOLOCK) INNER JOIN ASTRO.dbo.tl_Lookups L with(NOLOCK) ON R.RegisterTypeLookupID = L.LookupID INNER JOIN
ASTRO.dbo.tm_Users U with(NOLOCK) ON R.CreatedByUserID = U.UserID INNER JOIN
ASTRO.dbo.tm_Contracts C with(NOLOCK) ON R.ContractID = C.ContractID INNER JOIN
ASTRO.dbo.ts_StatusTypes ST with(NOLOCK) ON C.StatusTypeID = ST.StatusTypeID LEFT OUTER JOIN
ASTRO.dbo.tl_States S with(NOLOCK) ON C.StateID = S.StateID LEFT OUTER JOIN
ASTRO.dbo.tl_Countries CNTY with(NOLOCK) ON C.CountryID = CNTY.CountryID
WHERE
R.ExpirationDate is not NULL

AND C.ContractName <> 'TEMPLATE'
AND NOT (C.ContractID IN (3, 656))
AND (C.StatusTypeID = 1
OR (C.StatusTypeID = 54 AND C.EndDate > @EndDate)        
OR (C.StatusTypeID NOT IN (1,54) AND C.StatusDate > @EndDate))    

)a

CREATE CLUSTERED INDEX IX_Register
ON #Register
(ContractID,ApplicableDate)


If Object_Id('tempdb..#Register2') is Not Null
      Drop table #Register2
      
Create Table #Register2(StatusDate datetime,ContractType varchar(50) ,ContractName varchar(128),AnnualAllocatedHours int,TotalHours decimal(10,2),
MonthlyHoursBasedFee money,StatusType varchar(35),[Address] varchar(100),City varchar(50),PostalCode varchar(10),StateAbbreviation varchar(50),
IATACode3 varchar(5),RegisterID int,ContractID int,EntityID int,EntityKeyID int,ApplicableDate datetime2,HoursValue decimal(10,2),CreditValue decimal(10,2),
CreditsRemain decimal(10,2),MoneyValue decimal(10,2),RegisterTypeLookupID int,RegisterType varchar(100),[Description] varchar(max),ExpirationDate datetime,
DateCreated datetime,CreatedByUserID int,CreatedBy varchar(255),IsEstimated int,ItemTypeID int,FutureHours decimal(10,2),FutureCredit decimal(10,2),MonthHours decimal(10,2),
MonthCredits decimal(10,2),DisplayName varchar(255),REGION varchar(50),IssueID int,StartDates datetime,EndDates datetime)


DECLARE
@StatusDate datetime,@ContractType varchar(50),@ContractName varchar(128),@AnnualAllocatedHours int,@TotalHours decimal(10,2),
@MonthlyHoursBasedFee money,@StatusType varchar(35),@Address varchar(100),@City varchar(50),@PostalCode varchar(10),@StateAbbreviation varchar(50),
@IATACode3 varchar(5),@RegisterID int,@ContractIDs int,@EntityID int,@EntityKeyID int,@ApplicableDate date,@HoursValue decimal(10,2),@CreditValue decimal(10,2),
@MoneyValue decimal(10,2),@RegisterTypeLookupID int,@RegisterType varchar(100),@Description varchar(max),@ExpirationDate datetime,@DateCreated datetime,
@CreatedByUserID int,@CreatedBy varchar(255),@IsEstimated int,@ItemTypeID int,@FutureHours decimal(10,2),@FutureCredit decimal(10,2),@MonthHours decimal(10,2),
@MonthCredits decimal(10,2),@DisplayName varchar(255),@REGION varchar(50),@IssueID int,@StartDates datetime,@EndDates datetime

      ,@cv1 decimal(10,2)
      ,@cv2 decimal(10,2)
      ,@cv3 decimal(10,2)
      ,@pci int

DECLARE c1 CURSOR FOR

Select StatusDate,ContractType,ContractName,AnnualAllocatedHours,TotalHours,MonthlyHoursBasedFee,StatusType,[Address],City,PostalCode,StateAbbreviation,IATACode3,RegisterID,ContractID,
EntityID,EntityKeyID,ApplicableDate,HoursValue,
CreditValue,
MoneyValue,RegisterTypeLookupID,RegisterType,[Description],ExpirationDate,DateCreated,CreatedByUserID,CreatedBy,IsEstimated,ItemTypeID,FutureHours,FutureCredit,MonthHours,MonthCredits,
DisplayName,REGION,IssueID,StartDates,EndDates
from #Register
Where
ApplicableDate <= @EndDate AND ISNULL([Description],'') <> 'Credit Expiration'
Order by contractid,ApplicableDate,RegisterID,DateCreated

OPEN c1
FETCH Next FROM c1 INTO @StatusDate,@ContractType,@ContractName,@AnnualAllocatedHours,@TotalHours,@MonthlyHoursBasedFee,@StatusType,@Address,@City,@PostalCode,@StateAbbreviation,
@IATACode3,@RegisterID,@ContractIDs,@EntityID,@EntityKeyID,@ApplicableDate,@HoursValue,@CreditValue,@MoneyValue,@RegisterTypeLookupID,@RegisterType,@Description,@ExpirationDate,@DateCreated,
@CreatedByUserID,@CreatedBy,@IsEstimated,@ItemTypeID,@FutureHours,@FutureCredit,@MonthHours,@MonthCredits,@DisplayName,@REGION,@IssueID,@StartDates,@EndDates

WHILE @@FETCH_STATUS = 0
BEGIN
            
      IF @pci <> @ContractIds
            Begin
                  Set @cv1 = 0
                  Set @cv2 = 0
            End
            
            
            Set @cv1 = @CreditValue
            Set @cv2 = ISNULL(@cv1,0)+ISNULL(@cv2,0)
                  
            Insert Into #Register2(StatusDate,ContractType,ContractName,AnnualAllocatedHours,TotalHours,MonthlyHoursBasedFee,StatusType,[Address],
            City,PostalCode,StateAbbreviation,IATACode3,RegisterID,ContractID,EntityID,EntityKeyID,ApplicableDate,HoursValue,CreditValue,CreditsRemain,
            MoneyValue,RegisterTypeLookupID,RegisterType,[Description],ExpirationDate,DateCreated,CreatedByUserID,CreatedBy,IsEstimated,ItemTypeID,
            FutureHours,FutureCredit,MonthHours,MonthCredits,DisplayName,REGION,IssueID,StartDates,EndDates)

                                    Select @StatusDate,@ContractType,@ContractName,@AnnualAllocatedHours,@TotalHours,@MonthlyHoursBasedFee,@StatusType,
                                    @Address,@City,@PostalCode,@StateAbbreviation,@IATACode3,@RegisterID,@ContractIDs,@EntityID,@EntityKeyID,@ApplicableDate,
                                    @HoursValue,@CreditValue,@cv2,@MoneyValue,@RegisterTypeLookupID,@RegisterType,@Description,@ExpirationDate,
                                    @DateCreated,@CreatedByUserID,@CreatedBy,@IsEstimated,@ItemTypeID,@FutureHours,@FutureCredit,@MonthHours,@MonthCredits,@DisplayName,@REGION,@IssueID,@StartDates,@EndDates

      SET @pci = @ContractIds

FETCH Next FROM c1 INTO @StatusDate,@ContractType,@ContractName,@AnnualAllocatedHours,@TotalHours,@MonthlyHoursBasedFee,@StatusType,
@Address,@City,@PostalCode,@StateAbbreviation,@IATACode3,@RegisterID,@ContractIDs,@EntityID,@EntityKeyID,@ApplicableDate,@HoursValue,
@CreditValue,@MoneyValue,@RegisterTypeLookupID,@RegisterType,@Description,@ExpirationDate,@DateCreated,@CreatedByUserID,@CreatedBy,
@IsEstimated,@ItemTypeID,@FutureHours,@FutureCredit,@MonthHours,@MonthCredits,@DisplayName,@REGION,@IssueID,@StartDates,@EndDates

END
CLOSE c1
DEALLOCATE c1


CREATE CLUSTERED INDEX IX_Register
ON #Register2
(ContractID,ApplicableDate)




If Object_Id('tempdb..#Register3') is Not Null
      Drop table #Register3
      
Create Table #Register3(StatusDate datetime,ContractType varchar(50),ContractName varchar(128),AnnualAllocatedHours int,
TotalHours decimal(10,2),MonthlyHoursBasedFee money,StatusType varchar(35),[Address] varchar(100),City varchar(50),
PostalCode varchar(10),StateAbbreviation varchar(50),IATACode3 varchar(5),RegisterID int,ContractID int,EntityID int,EntityKeyID int,
ApplicableDate datetime2,HoursValue decimal(10,2),HrsRemainYr decimal(10,2),CreditValue decimal(10,2),CreditsRemain decimal(10,2),
MoneyValue decimal(10,2),RegisterTypeLookupID int,RegisterType varchar(100),[Description] varchar(max),ExpirationDate datetime,
DateCreated datetime,CreatedByUserID int,CreatedBy varchar(255),IsEstimated int,ItemTypeID int,FutureHours decimal(10,2),
FutureCredit decimal(10,2),MonthHours decimal(10,2),MonthCredits decimal(10,2),DisplayName varchar(255),REGION varchar(50),IssueID int,StartDates datetime,EndDates datetime)


DECLARE
@StatusDate3 datetime,@ContractType3 varchar(50),@ContractName3 varchar(128),@AnnualAllocatedHours3 int,@TotalHours3 int,
@MonthlyHoursBasedFee3 money,@StatusType3 varchar(35),@Address3 varchar(100),@City3 varchar(50),@PostalCode3 varchar(10),
@StateAbbreviation3 varchar(50),@IATACode33 varchar(5),@RegisterID3 int,@ContractIDs3 int,@EntityID3 int,@EntityKeyID3 int,
@ApplicableDate3 date,@HoursValue3 decimal(10,2),@CreditValue3 decimal(10,2),@CreditsRemain3 decimal(10,2),@MoneyValue3 decimal(10,2),
@RegisterTypeLookupID3 int,@RegisterType3 varchar(100),@Description3 varchar(max),@ExpirationDate3 datetime,@DateCreated3 datetime,
@CreatedByUserID3 int,@CreatedBy3 varchar(255),@IsEstimated3 int,@ItemTypeID3 int,@FutureHours3 decimal(10,2),
@FutureCredit3 decimal(10,2),@MonthHours3 decimal(10,2),@MonthCredits3 decimal(10,2),@DisplayName3 varchar(255),
@REGION3 varchar(50),@IssueID3 int,@StartDates3 datetime,@EndDates3 datetime

      ,@hv13 decimal(10,2)
      ,@hv23 decimal(10,2)
      ,@hv33 decimal(10,2)
      ,@pci3 int
      ,@pad3 datetime
      

      

DECLARE c3 CURSOR FOR

Select StatusDate,ContractType,ContractName,AnnualAllocatedHours,TotalHours,MonthlyHoursBasedFee,
StatusType,[Address],City,PostalCode,StateAbbreviation,IATACode3,RegisterID,ContractID,EntityID,EntityKeyID,ApplicableDate,HoursValue,
CreditValue,CreditsRemain,
MoneyValue,RegisterTypeLookupID,RegisterType,[Description],ExpirationDate,DateCreated,CreatedByUserID,
CreatedBy,IsEstimated,ItemTypeID,FutureHours,FutureCredit,MonthHours,MonthCredits,DisplayName,REGION,IssueID,StartDates,EndDates
from #Register2
Where
ApplicableDate <= @EndDate
Order by contractid,ApplicableDate,RegisterID,DateCreated

OPEN c3
FETCH Next FROM c3 INTO @StatusDate3,@ContractType3,@ContractName3,@AnnualAllocatedHours3,@TotalHours3,@MonthlyHoursBasedFee3,
@StatusType3,@Address3,@City3,@PostalCode3,@StateAbbreviation3,@IATACode33,@RegisterID3,@ContractIDs3,@EntityID3,@EntityKeyID3,
@ApplicableDate3,@HoursValue3,@CreditValue3,@CreditsRemain3,@MoneyValue3,@RegisterTypeLookupID3,@RegisterType3,@Description3,
@ExpirationDate3,@DateCreated3,@CreatedByUserID3,@CreatedBy3,@IsEstimated3,@ItemTypeID3,@FutureHours3,@FutureCredit3,
@MonthHours3,@MonthCredits3,@DisplayName3,@REGION3,@IssueID3,@StartDates3,@EndDates3

WHILE @@FETCH_STATUS = 0
BEGIN
      
Set @hv13 = @HoursValue3

      IF @pci3 <> @ContractIds3
            Begin
                  Set @hv23 = 0
            End
      
            
            IF @RegisterTypeLookupID3 <> 265
                  Begin
                        SET @hv23 = ISNULL(@hv23,0) + @hv13
                        
                  End
                  
            If @RegisterTypeLookupID3 = 265  AND ISNULL(@hv23,0) >= 0 AND ISNULL(@pad3,'1901-01-01') <> @ApplicableDate3
                  Begin
                        set @hv23 = @hv13
                        
                  End
                  
            If @RegisterTypeLookupID3 = 265  AND ISNULL(@hv23,0) >= 0 AND ISNULL(@pad3,'1901-01-01') = @ApplicableDate3
                  Begin
                        set @hv23 = ISNULL(@hv23,0) + @hv13
                  
                  End
      
            If @RegisterTypeLookupID3 = 265  AND ISNULL(@hv23,0) < 0
                  Begin
                        set @hv23 = (@hv23 + ABS(@hv23)) + @hv13
                        
                  End      
            

            
      
            Insert Into #Register3(StatusDate,ContractType,ContractName,AnnualAllocatedHours,TotalHours,MonthlyHoursBasedFee,StatusType,[Address],City,
            PostalCode,StateAbbreviation,IATACode3,RegisterID,ContractID,EntityID,EntityKeyID,ApplicableDate,HoursValue,HrsRemainYr,CreditValue,
            CreditsRemain,MoneyValue,RegisterTypeLookupID,RegisterType,[Description],ExpirationDate,DateCreated,CreatedByUserID,CreatedBy,
            IsEstimated,ItemTypeID,FutureHours,FutureCredit,MonthHours,MonthCredits,DisplayName,REGION,IssueID,StartDates,EndDates)

            Select @StatusDate3,@ContractType3,@ContractName3,@AnnualAllocatedHours3,@TotalHours3,@MonthlyHoursBasedFee3,@StatusType3,@Address3,
            @City3,@PostalCode3,@StateAbbreviation3,@IATACode33,@RegisterID3,@ContractIDs3,@EntityID3,@EntityKeyID3,@ApplicableDate3,@HoursValue3,@hv23,
            @CreditValue3,@CreditsRemain3,@MoneyValue3,@RegisterTypeLookupID3,@RegisterType3,@Description3,@ExpirationDate3,@DateCreated3,
            @CreatedByUserID3,@CreatedBy3,@IsEstimated3,@ItemTypeID3,@FutureHours3,@FutureCredit3,@MonthHours3,@MonthCredits3,@DisplayName3,@REGION3,@IssueID3,@StartDates3,@EndDates3

SET @pci3 = @ContractIds3
SET @pad3 = @ApplicableDate3
FETCH Next FROM c3 INTO @StatusDate3,@ContractType3,@ContractName3,@AnnualAllocatedHours3,@TotalHours3,
@MonthlyHoursBasedFee3,@StatusType3,@Address3,@City3,@PostalCode3,@StateAbbreviation3,@IATACode33,
@RegisterID3,@ContractIDs3,@EntityID3,@EntityKeyID3,@ApplicableDate3,@HoursValue3,@CreditValue3,@CreditsRemain3,
@MoneyValue3,@RegisterTypeLookupID3,@RegisterType3,@Description3,@ExpirationDate3,@DateCreated3,
@CreatedByUserID3,@CreatedBy3,@IsEstimated3,@ItemTypeID3,@FutureHours3,@FutureCredit3,@MonthHours3,
@MonthCredits3,@DisplayName3,@REGION3,@IssueID3,@StartDates3,@EndDates3
END
CLOSE c3
DEALLOCATE c3

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

If Object_Id('tempdb..#Register3b') is Not Null
      Drop table #Register3b
      
Create Table #Register3b(StatusDate datetime,ContractType varchar(50),ContractName varchar(128),AnnualAllocatedHours int,TotalHours decimal(10,2),
MonthlyHoursBasedFee money,StatusType varchar(35),[Address] varchar(100),City varchar(50),PostalCode varchar(10),
StateAbbreviation varchar(50),IATACode3 varchar(5),RegisterID int,ContractID int,EntityID int,EntityKeyID int,ApplicableDate datetime2,
HoursValue decimal(10,2),HrsRemain decimal(10,2),HrsRemainYr decimal(10,2),CreditValue decimal(10,2),CreditsRemain decimal(10,2),
MoneyValue decimal(10,2),RegisterTypeLookupID int,RegisterType varchar(100),[Description] varchar(max),ExpirationDate datetime,DateCreated datetime,
CreatedByUserID int,CreatedBy varchar(255),IsEstimated int,ItemTypeID int,FutureHours decimal(10,2),FutureCredit decimal(10,2),MonthHours decimal(10,2),
MonthCredits decimal(10,2),DisplayName varchar(255),REGION varchar(50),IssueID int,StartDates datetime,EndDates datetime)

Insert Into #Register3b(StatusDate,ContractType,ContractName,AnnualAllocatedHours,TotalHours,MonthlyHoursBasedFee,StatusType,
[Address],City,PostalCode,StateAbbreviation,IATACode3,RegisterID,ContractID,EntityID,EntityKeyID,ApplicableDate,HoursValue,HrsRemain,
HrsRemainYr,CreditValue,CreditsRemain,MoneyValue,RegisterTypeLookupID,RegisterType,[Description],ExpirationDate,DateCreated,
CreatedByUserID,CreatedBy,IsEstimated,ItemTypeID,FutureHours,FutureCredit,MonthHours,MonthCredits,DisplayName,REGION,IssueID,StartDates,EndDates)

Select
StatusDate,ContractType,ContractName,AnnualAllocatedHours,TotalHours,MonthlyHoursBasedFee,StatusType,[Address],City,PostalCode,StateAbbreviation,
IATACode3,RegisterID,ContractID,EntityID,EntityKeyID,ApplicableDate,HoursValue,HrsRemain = 0, HrsRemainYr,CreditValue,CreditsRemain,MoneyValue,
RegisterTypeLookupID,RegisterType,[Description],ExpirationDate,DateCreated,CreatedByUserID,CreatedBy,IsEstimated,ItemTypeID,FutureHours,
FutureCredit,MonthHours,MonthCredits,DisplayName,REGION,IssueID,StartDates,EndDates
from #Register3

UPDATE b
SET HrsRemain = a.HrsRemain
FROM
(SELECT
SUM(HoursValue) as HrsRemain
,ContractID
,ContractName
FROM
#Register3b
Group by
ContractID,ContractName)a inner join #Register3b b on a.ContractID = b.ContractID


If Object_Id('tempdb..#Register4') is Not Null
      Drop table #Register4

Create Table #Register4(EntityKeyID int,RegisterType varchar(100),RegisterID int,ContractType varchar(50),ContractID int,
ContractName varchar(128),AnniversaryDate datetime,ApplicableDate datetime,DateCreated datetime,StartDates datetime,EndDates datetime,
HoursValue decimal(10,2),HrsRemain decimal(10,2),HrsRemainYr decimal(10,2),CreditValue decimal(10,2),CreditsRemain decimal(10,2),[RANK] int)

Insert Into #Register4(EntityKeyID,RegisterType,RegisterID,ContractType,ContractID,ContractName,AnniversaryDate,ApplicableDate,
StartDates,EndDates,HoursValue,HrsRemain,HrsRemainYr,CreditValue,CreditsRemain,[RANK])
Select DISTINCT
       r.EntityKeyID
      ,r.RegisterType
      ,r.RegisterID
      ,r.ContractType
      ,r.ContractID
      ,ContractName = LTRIM(RTRIM(Replace(r.ContractName,'*','')))
      ,AnniversaryDate = dateadd(year,1,r.StartDates)
      ,r.ApplicableDate
      ,r.StartDates
      ,r.EndDates
      ,r.HoursValue
      ,r.HrsRemain
      ,r.HrsRemainYr
      ,r.CreditValue
      ,r.CreditsRemain
,      DENSE_RANK () OVER
      (PARTITION BY r.ContractID ORDER BY
       r.contractid,r.ApplicableDate,r.RegisterID,r.DateCreated)AS 'RANK'      
from #Register3b r  
Where
r.ApplicableDate < CAST(@EndDate as date)
order by r.ContractID,10


IF EXISTS(SELECT name FROM MSAccess.dbo.sysobjects WHERE name = N'Register' AND xtype='U')
      DROP TABLE MSAccess.dbo.Register


SELECT
       hrs.ContractType
      ,hrs.ContractID
      ,hrs.ContractName
      ,hrs.AnniversaryDate
      
      ,HoursValue
      ,HrsRemain= ROUND(HrsRemain,1)
      ,HrsRemainYr = ROUND(HrsRemainYr,1)
      ,CreditValue
      ,CreditsRemain = ROUND(CreditsRemain,1)
      
      ,hrs.StartDates
      ,hrs.EndDates
      ,hrs.ReportRunDate
      ,FraxLeaseMonthsLftContractYr = CASE WHEN hrs.ContractType in('Fractional Program','Lease Program') THEN      
      CASE WHEN ROUND(ABS(DATEDIFF(dd,hrs.AnniversaryDate,getdate())/30.0),1) < 1 THEN 1 ELSE
                                          ROUND(ABS(DATEDIFF(dd,hrs.AnniversaryDate,getdate())/30.0),1) END
                                          ELSE 0 END
                                          
      ,CardMonthsLftContractYr = CASE WHEN hrs.ContractType NOT in('Fractional Program','Lease Program') THEN      
      CASE WHEN ROUND(ABS(DATEDIFF(dd,hrs.EndDates,getdate())/30.0),1) < 1 THEN 1 ELSE
                                          ROUND(ABS(DATEDIFF(dd,hrs.EndDates,getdate())/30.0),1) END
                                          ELSE 0 END

                                    
      ,EstMthlyUsage = ROUND(hrs.HrsRemainYr / CASE WHEN ROUND(ABS(DATEDIFF(dd,hrs.AnniversaryDate,getdate())/30.0),1) < 1 THEN 1 ELSE
                                          ROUND(ABS(DATEDIFF(dd,hrs.AnniversaryDate,getdate())/30.0),1) END,1)
                                          
      
      ,ContractLastYr = CASE WHEN ABS(DATEDIFF(dd,hrs.EndDates,getdate())) <365 THEN 1 ELSE
                                    0 END
            
      into MSAccess.dbo.Register
FROM
(Select
       ContractID
,      Max([RANK]) as [RANK]
from #Register4  
Group by
       ContractID ) ra inner join
(Select
       ContractType
      ,ContractID
      ,ContractName
      ,AnniversaryDate
      ,HoursValue
      ,HrsRemain = ROUND(HrsRemain,1)
      ,HrsRemainYr = ROUND(HrsRemainYr,1)
      ,CreditValue
      ,CreditsRemain = ROUND(CreditsRemain,1)
      ,[RANK]
      ,StartDates
      ,EndDates
      ,ReportRunDate = @EndDate      
from #Register4)hrs on ra.ContractID = hrs.ContractID AND ra.[RANK] = hrs.[RANK]
Order by
hrs.HrsRemainYr,hrs.ContractID