Solved

add new fields to script

Posted on 2012-03-21
8
182 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:fwstealer
  • 4
  • 2
  • 2
8 Comments
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
First there is no msaccess.register table in the script you provided but #register temp table and my guess is that yo must add the columns in EACH select of the UNION ALL subquery and obviously in the one that sits on the top of all those selects and inserts INTO #register table.
0
 

Author Comment

by:fwstealer
Comment Utility
okay so i did something like that and below is the updated script - but it errors.

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

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 nvarchar(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 nvarchar(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 nvarchar(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 nvarchar(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 nvarchar(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),
MonthlyHoursBasedFee money,StatusType nvarchar (35),[RANK] int)

Insert Into #Register4(EntityKeyID,RegisterType,RegisterID,ContractType,ContractID,ContractName,AnniversaryDate,ApplicableDate,
StartDates,EndDates,HoursValue,HrsRemain,HrsRemainYr,CreditValue,CreditsRemain,MonthlyHoursBasedFee,StatusType,[RANK]) --************************* errors here!
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
      ,r.MonthlyHoursBasedFee
      ,r.StatusType
,      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

,hrs.MonthlyHoursBasedFee
,hrs.StatusType

      ,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
      ,StatusType
      ,MonthlyHoursBasedFee
from #Register4)hrs on ra.ContractID = hrs.ContractID AND ra.[RANK] = hrs.[RANK]
Order by
hrs.HrsRemainYr,hrs.ContractID

--Error: Cannot convert a char value to money. The char value has incorrect syntax.
0
 
LVL 39

Expert Comment

by:lcohan
Comment Utility
You must use SQL CAST/CONVERT functions on each column that has datatypes where implicit conversion will not work - check that "Implicit conversion"chart from CONVERT function:

http://msdn.microsoft.com/en-us/library/ms187928.aspx
0
 

Author Comment

by:fwstealer
Comment Utility
so are you suggesting something like the following under the #Register4?


      ,MonthlyHoursBasedFee = CAST(r.MonthlyHoursBasedFee as money)
      --,r.MonthlyHoursBasedFee
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 6

Expert Comment

by:netjgrnaut
Comment Utility
The msaccess.dbo.register table is created at the very end of the script, here...

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

Open in new window


If you add a couple of empty placeholder values into the select statement (where you want them to appear in the table, if you're into that sort of thing)...

...
, null AS StatusType 
...
, null AS MonthlyHoursBasedFee

Open in new window


...then alter the column types after the select statement

ALTER TABLE MSAccess.dbo.Register ALTER COLUMN StatusType NVARCHAR(35)
ALTER TABLE MSAccess.dbo.Register ALTER COLUMN MonthlyHoursBasedFee MONEY

Open in new window


You should have all the script generated data and your two new columns, with null values in every row.

Hope that helps!
0
 

Author Comment

by:fwstealer
Comment Utility
i start off with the two columns added to the table then this script runs

here is what i did towards the end of the script to include those columns and they need to contact the values as well.


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
,MonthlyHoursBasedFee
--,hrs.MonthlyHoursBasedFee
,StatusType
--,hrs.StatusType

      ,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
      ,MonthlyHoursBasedFee
      ,StatusType
from #Register4)hrs on ra.ContractID = hrs.ContractID AND ra.[RANK] = hrs.[RANK]
Order by
hrs.HrsRemainYr,hrs.ContractID
0
 
LVL 6

Accepted Solution

by:
netjgrnaut earned 500 total points
Comment Utility
The columns exist where?  In MSAccess.dbo.Register?  Or in one of the source ASTRO tables called in the select that builds temp table #Register?

If the columns are in the underlying ASTRO tables, then you need to SELECT it closer to the top of the script.  By the time you to get to #Register4, you're working off of several temp tables deep.

If the columns are in your MSAccess.dbo.Register, you destroy them just before the select.

Is this working for you?  I could be missing something...
0
 

Author Closing Comment

by:fwstealer
Comment Utility
gave me enough pointers to find the answer
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now