CREATE PROCEDURE [dbo].[Budget] @RunDate VARCHAR(8), @HotelNumberStr VARCHAR(100), @HotelCustomerNumberStr VARCHAR(100), @ExcludeAccountGroupsStr VARCHAR(100) ='', 
  @ExcludeTurnoverGroupsStr VARCHAR(100) = '', @FK_DepartmentNumberCSV VARCHAR(1000), @APerDepartment BIT = 0, @APricesInThousands BIT = 0, @AHideEmpty BIT = 0, @AIncludeAllotment BIT = 1 AS
SET NOCOUNT ON

/*
  CREATED BY Kent Johansen on 20051015

  CHANGES:
    KJ 20240301: Bug: Fix for "Last year budget not correct". Case #CR241588
    KJ 20220506: Allotment added req #4059
    KJ 20210614: Ext: "Hide empty lines".
    KJ 20170327: Bug: Fix for "Wrong amount of booked".
    KJ 20170317: Ext: Fix for "Only select from mistableView for future dates"
    KJ 20150821: Ext: Added prices in thousands
    KJ 20141020: Bug: Fix for "Wrong amount in Year booked and yesr booked if running financial year other than 1/1-31/12". Case #CR077532
    KJ 20120911: Bug: Fix for "Wrong amount in year corrected". Case #CR046754  
    KJ 20120709: Bug: MISTableView rewrite
    KJ 20120614: Bug: Fix for "Budget from MIS shows to low figures".
    KJ 20120613: Bug: Fix for "Get data from MisView changed".
    OF 20120227: Bug: Wrong calculation of mis data
    OF/KJ 20120222: Bug: Real packages was missing from MisTableView.
    KJ 20120216: Ext: Use MISTableView to select data.
    KJ 20111128: Ext: Always use MIS to calc forecast. Req #1079
    KJ 20111026: Bug: Fix for "Year to date should not include currentday in forecast part". Case #CR035438
    KJ 20111018: Ext: Added "Last year to data". Req #1052
    KJ 20110922: Ext: Added possible to override the BudgetPerDepartment setting. Req #1031
    KJ 20110922: Ext: Disable the get data from MISTable for now. Not implemented in local yet.
    KJ 20110919: Ext: Added @AFromForecastDaily to select from ForecastDaily og MISTable. Req #984
    KJ 20110525: Bug: Fix for division by zero exception
    KJ 20110519: Ext: Added turnover yeat to data, budget year to date and index year to date. Req# 903
    KJ 20110509: Ext: Added @IndexLastYearThisYear. req# 903
    KJ 20100420: Bug: Fix for "Don't show amount in day actualy in future"
    KJ 20100225: Bug: Proper Fix for "Index amount is negative"
    KJ 20100210: Bug: Fix for "Index amount is negative"
    KJ 20090914: Bug: Fix for "Only show budget for selected departments if any"
    KJ 20090818: Bug: Fix for "Year budget nor correct when fiscal year difference from 1/1-31/12"
    KJ 20090817: Bug: Fix for "Result not sorted"
    KJ 20090812: Ext: Enable budget per department
    KJ 20080826: Bug: Fix for "Yesterday moth index wrong if not turnover on day"
    KJ 20080730: Bug: Fix for "Corrected year not correct"
    KJ 20080728: Ext: Only show turnovergroup for selected hotels.
    KJ 20080704: Bug: Fix for "Correctedmonth wrong"
    KJ 20080508: Ext: Added support for financial year other than calendar year
    KJ 20080325: Ext: Handle AdditionalSalesFromTurnover ratio NOT in percent
    KJ 20070822: Added SummaryGroupNumber to Groupname.
    KJ 20070820: Changed result fields to type INT
    KJ 20070817: Bug: Fix for "Additionalsales should be calculated based on Booked, and not forecast."
    KJ 20070817: Bug: Fix for "AdditionalSales depends on Domino"
    KJ 20070813: Added "Added BudgetPercentCalcByForecast. Percent depends on hotelsetup setting. (Additional+forecast)"
    KJ 20070510: Bug: Fix for "Wrong index on total line."
    KJ 20070509: Bug Fix for Index is negativ.
    KJ 20070502: Bug fix for Wrong month index and year index.
    KJ 20070420: Fix for Month turnover not correct - do not include todays turnover.
    KJ 20070419: Bug: Fix for "Year/budget forecast calculates for hole year/budget. Should only calc from systemdate and the rest of year/month".
    KJ 20061221: Fix for Year index (10) wrong
    KN 20060914: Added Forecast for month and year
    KJ 20051112: Added HotelCustomerNumber to the select from AccountDaily
    KJ 20051111: Budget now per hotel
    KJ 20051027: Added @ExcludeAccountGroupsStr and @ExcludeTurnoverGroupsStr as input parameter
    KJ 20051023: Hotelnumber added
*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

DECLARE @PercentCalcByForecast BIT
DECLARE @FinacialYearStart CHAR(8)
DECLARE @FinacialYearEnd CHAR(8)
DECLARE @EnableBudgetPerDepartment BIT

SELECT @PercentCalcByForecast = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'BudgetPercentCalcByForecast'
SET @PercentCalcByForecast = ISNULL(@PercentCalcByForecast, 0)

SELECT @FinacialYearStart = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'FinancialYearStart'
IF ISNULL(@FinacialYearStart, '') = ''
  SET @FinacialYearStart = SUBSTRING(@RunDate, 1, 4) + '0101'

SELECT @FinacialYearEnd = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'FinancialYearEnd'
IF ISNULL(@FinacialYearEnd, '') = ''
  SET @FinacialYearEnd = SUBSTRING(@RunDate, 1, 4) + '1231'

SELECT @EnableBudgetPerDepartment = CONVERT(BIT, KeyValue) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'EnableBudgetPerDepartment'
SET @EnableBudgetPerDepartment = ISNULL(@EnableBudgetPerDepartment, 0)

DECLARE @tmpYear CHAR(4)
DECLARE @Year1 INT
DECLARE @Year2 INT
DECLARE @LastYear1 INT
DECLARE @LastYear2 INT
DECLARE @tmpStartDate CHAR(8)
DECLARE @tmpEndDate CHAR(8)

DECLARE @YearMonthStart CHAR(8)
DECLARE @YearMonthEnd CHAR(8)
DECLARE @LastYearMonthStart CHAR(8)
DECLARE @LastYearMonthEnd CHAR(8)

DECLARE @Month CHAR(2)
DECLARE @MonthCount INT
DECLARE @MonthCountStr CHAR(2)
DECLARE @LastYear CHAR(4)
DECLARE @Temp INT
DECLARE @CurrentDateStr CHAR(8)
DECLARE @RunDateLastYear CHAR(8)

DECLARE @tmpMonthStart INT
DECLARE @tmpMonthEnd INT
DECLARE @Month1Str VARCHAR(50)
DECLARE @Month2Str VARCHAR(50)

DECLARE @DepartmentNumber SMALLINT
DECLARE @DepartmentName VARCHAR(50)

SET @Month1Str = ''
SET @Month2Str = ''

SET @tmpYear = SUBSTRING(@RunDate, 1, 4)
SET @tmpStartDate = SUBSTRING(@RunDate, 1, 4) + SUBSTRING(@FinacialYearStart, 5, 4)
SET @tmpEndDate = SUBSTRING(@RunDate, 1, 4) + SUBSTRING(@FinacialYearEnd, 5, 4)

SET @tmpMonthStart = CONVERT(INT, SUBSTRING(@FinacialYearStart, 5, 2))
SET @tmpMonthEnd = CONVERT(INT, SUBSTRING(@FinacialYearEnd, 5, 2))

IF @tmpStartDate < @RunDate -- between startYear and 31/12
BEGIN
  SET @YearMonthStart = SUBSTRING(@RunDate, 1, 4) + SUBSTRING(@tmpStartDate, 5, 2)+'01'
  SET @YearMonthEnd = CONVERT(CHAR(4), (CONVERT(INT, SUBSTRING(@RunDate, 1, 4))+1)) + SUBSTRING(@tmpEndDate, 5, 2)+'31'
  SET @LastYearMonthStart = CONVERT(CHAR(4), (CONVERT(INT, SUBSTRING(@RunDate, 1, 4))-1)) + SUBSTRING(@tmpStartDate, 5, 2)+'01'
  SET @LastYearMonthEnd = SUBSTRING(@RunDate, 1, 4) + SUBSTRING(@tmpEndDate, 5, 2)+'31'

  IF @tmpMonthStart <> 1
  BEGIN
    SET @Year1 = @tmpYear
    SET @Year2 = @tmpYear+1
    SET @LastYear1 = @tmpYear-1
    SET @LastYear2 = @tmpYear
  END
END
ELSE
BEGIN
  SET @YearMonthStart = CONVERT(CHAR(4), (CONVERT(INT, SUBSTRING(@RunDate, 1, 4))-1)) + SUBSTRING(@tmpStartDate, 5, 2)+'01'
  SET @YearMonthEnd = SUBSTRING(@RunDate, 1, 4) + SUBSTRING(@tmpEndDate, 5, 2)+'31'
  SET @LastYearMonthStart = CONVERT(CHAR(4), (CONVERT(INT, SUBSTRING(@RunDate, 1, 4))-2)) + SUBSTRING(@tmpStartDate, 5, 2)+'01'
  SET @LastYearMonthEnd = CONVERT(CHAR(4), (CONVERT(INT, SUBSTRING(@RunDate, 1, 4))-1)) + SUBSTRING(@tmpEndDate, 5, 2)+'31'
  IF @tmpMonthStart <> 1
  BEGIN
    SET @Year1 = @tmpYear-1
    SET @Year2 = @tmpYear
    SET @LastYear1 = @tmpYear-2
    SET @LastYear2 = @tmpYear-1
  END
END

IF @tmpMonthStart <> 1
BEGIN
  WHILE @tmpMonthStart <= 12
  BEGIN
    SET @Month1Str = @Month1Str + CONVERT(VARCHAR, @tmpMonthStart) + ','
    SET @tmpMonthStart = @tmpMonthStart + 1
  END
  WHILE @tmpMonthEnd >= 1
  BEGIN
    SET @Month2Str = @Month2Str + CONVERT(VARCHAR, @tmpMonthEnd) + ','
    SET @tmpMonthEnd = @tmpMonthEnd - 1
  END
  IF @Month1Str <> ''
    SET @Month1Str = SUBSTRING(@Month1Str, 1, LEN(@Month1Str)-1)
  IF @Month2Str <> ''
    SET @Month2Str = SUBSTRING(@Month2Str, 1, LEN(@Month2Str)-1)
END

SET @Month = SUBSTRING(@RunDate, 5, 2)

SET @Temp = @tmpYear - 1
SET @LastYear = @Temp

SELECT @CurrentDateStr = SYSTEMDATE FROM System WITH  (NOLOCK) WHERE NUMBER = 1

SET @RunDateLastYear = @LastYear + @Month + SUBSTRING(@RunDate, 7, 2)

IF @ShowPrint = 1
BEGIN
  PRINT '@RunDate'
  PRINT @RunDate
  PRINT '@tmpYear'
  PRINT @tmpYear
  PRINT '@LastYear1'
  PRINT @LastYear1
  PRINT '@LastYear2'
  PRINT @LastYear2
  PRINT '@YearMonthStart'
  PRINT @YearMonthStart
  PRINT '@YearMonthEnd'
  PRINT @YearMonthEnd
  PRINT '@LastYearMonthStart'
  PRINT @LastYearMonthStart
  PRINT '@LastYearMonthEnd'
  PRINT @LastYearMonthEnd
  PRINT '@Month'
  PRINT @Month
  PRINT '@LastYear'
  PRINT @LastYear
  PRINT '@Month1Str'
  PRINT @Month1Str
  PRINT '@Month2Str'
  PRINT @Month2Str
END

CREATE TABLE #BudgetReport
(
  GroupNumber INT,
  GroupName VARCHAR(50),
  AccountGroupGroupName VARCHAR(50),
  TurnoverDay INT,
  TurnoverMonth INT,
  BudgetMonth INT,
  BookedMonth INT,
  ForecastMonth INT,
  CorrectedMonth INT,
  IndexMonth INT,
  IndexMonthYesterDay INT,
  TurnoverMonthLastYear INT,
  BudgetMonthLastYear INT,
  IndexTurnoverMonthLastYear INT,
  TurnoverYear INT,
  BudgetYear INT,
  BookedYear INT,
  ForecastYear INT,
  CorrectedYear INT,
  IndexYear INT,
  TurnoverLastYear INT,
  BudgetLastYear INT,
  IndexLastYear INT,
  FK_DepartmentNumber SMALLINT,
  DepartmentName VARCHAR(50),
  IndexLastYearThisYear INT,
  TurnoverYearToDate INT,
  BudgetYearToDate INT,
  IndexYearToDate INT,
  TurnoverLastYearToDate INT
)

DECLARE @TurnoverDay MONEY
DECLARE @TurnoverMonth MONEY
DECLARE @BudgetMonth INT
DECLARE @ForecastMonth INT
DECLARE @BookedMonth INT
DECLARE @CorrectedMonth INT
DECLARE @TotalMonth INT
DECLARE @IndexMonth INT
DECLARE @IndexMonthYesterDay INT
DECLARE @TurnoverMonthLastYear MONEY
DECLARE @IndexTurnoverMonthLastYear INT
DECLARE @TurnoverYear MONEY
DECLARE @BudgetYear MONEY
DECLARE @ForecastYear MONEY
DECLARE @BookedYear MONEY
DECLARE @CorrectedYear MONEY
DECLARE @TotalYear MONEY
DECLARE @IndexYear INT
DECLARE @TurnoverLastYear MONEY
DECLARE @BudgetLastYear MONEY
DECLARE @IndexLastYear INT
DECLARE @IndexLastYearThisYear INT
DECLARE @SummaryGroupNumber INT
DECLARE @tmp INT
DECLARE @TurnoverYearToDate INT
DECLARE @BudgetYearToDate INT
DECLARE @IndexYearToDate INT
DECLARE @TurnoverLastYearToDate INT

DECLARE @Ratio REAL
DECLARE @RatioInPercent BIT
--DECLARE @ForecastYearMonth Money
DECLARE @BookedYearMonth MONEY

DECLARE @BudgetMonthLastYear MONEY

DECLARE @TurnOverGroupNumber INT
DECLARE @TurnoverGroupName VARCHAR(50)
DECLARE @AccountGroupGroupName VARCHAR(50)

DECLARE @CentralBookingType INT
SELECT @CentralBookingType = CONVERT(INTEGER, ISNULL(KeyValue, 0)) FROM HotelSetup WITH (NOLOCK) WHERE SearchKey LIKE '%CentralBookingType%'

SET @APerDepartment = ISNULL(@APerDepartment, 0)

SELECT SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat) AS Amount,
DateStamp, HotelCustomerNumber, DepartmentNumber, AccountNumber
INTO #MISTable
FROM MISTableView
WHERE (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
GROUP BY DateStamp, HotelCustomerNumber, DepartmentNumber, AccountNumber, ProductWareLinkNumber
HAVING ISNULL(SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.BookedOnlyPackageExVat), 0) <> 0

IF @EnableBudgetPerDepartment = 0 OR @APerDepartment = 0
BEGIN
  IF @CentralBookingType < 7
  BEGIN
    DECLARE TurnGroupNumber_cur CURSOR FOR
      SELECT TurnoverGroups.Number, TurnoverGroups.Name, SummaryAccounts.Text, SummaryAccounts.Number
      FROM TurnoverGroups WITH (NOLOCK)
      INNER JOIN SummaryAccounts ON SummaryAccounts.Number = TurnoverGroups.SummaryAccount
      WHERE IsNull(SummaryAccounts.Number, -1) NOT IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ExcludeAccountGroupsStr))
      AND IsNull(TurnoverGroups.Number, -1) NOT IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ExcludeTurnoverGroupsStr))
      ORDER BY TurnoverGroups.SummaryAccount, TurnoverGroups.Number
  END
  ELSE
  BEGIN
    DECLARE TurnGroupNumber_cur CURSOR FOR
      SELECT distinct TurnoverGroups.Number, TurnoverGroups.Name, SummaryAccounts.Text, SummaryAccounts.Number 
      FROM TurnoverGroups WITH (NOLOCK)
      INNER JOIN SummaryAccounts ON SummaryAccounts.Number = TurnoverGroups.SummaryAccount
      INNER JOIN Accounts a WITH (NOLOCK) ON a.TurnoverGroupNumber = TurnoverGroups.Number
      INNER JOIN AccountDepartmentXtab adx WITH (NOLOCK) ON adx.AccountNumber = a.AccountNumber
      INNER JOIN Departments d WITH (NOLOCK) ON d.PK_Department = adx.FK_Department
        AND D.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))
      WHERE IsNull(TurnoverGroups.HotelNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))
      AND IsNull(SummaryAccounts.Number, -1) NOT IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ExcludeAccountGroupsStr))
      AND IsNull(TurnoverGroups.Number, -1) NOT IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ExcludeTurnoverGroupsStr))
      ORDER BY TurnoverGroups.Number, TurnoverGroups.Name, SummaryAccounts.Text, SummaryAccounts.Number
  END
END
ELSE
BEGIN
  DECLARE TurnGroupNumber_cur CURSOR FOR
    SELECT TurnoverGroups.Number, TurnoverGroups.Name, SummaryAccounts.Text, SummaryAccounts.Number
    FROM TurnoverGroups WITH (NOLOCK)
    INNER JOIN SummaryAccounts ON SummaryAccounts.Number = TurnoverGroups.SummaryAccount
    WHERE IsNull(SummaryAccounts.Number, -1) NOT IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ExcludeAccountGroupsStr))
    AND IsNull(TurnoverGroups.Number, -1) NOT IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ExcludeTurnoverGroupsStr))
    ORDER BY TurnoverGroups.SummaryAccount, TurnoverGroups.Number
END
 
OPEN TurnGroupNumber_cur

FETCH NEXT FROM TurnGroupNumber_cur INTO @TurnOverGroupNumber, @TurnoverGroupName, @AccountGroupGroupName, @SummaryGroupNumber

IF @EnableBudgetPerDepartment = 1 AND @APerDepartment = 1
BEGIN
  SELECT PK_Department, Name INTO #Department 
  FROM Departments WITH (NOLOCK)
  WHERE ((@FK_DepartmentNumberCSV <> '' AND PK_Department IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV))) 
  OR (@FK_DepartmentNumberCSV = '' AND HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelNumberStr))))

  DECLARE db_dep_cur CURSOR LOCAL STATIC FOR
    SELECT PK_Department, Name FROM #Department
  OPEN db_dep_cur
END

WHILE (@@FETCH_STATUS = 0)
BEGIN
  IF @EnableBudgetPerDepartment = 1 AND @APerDepartment = 1
    FETCH FIRST FROM db_dep_cur INTO @DepartmentNumber, @DepartmentName
  ELSE
  BEGIN
    SET @DepartmentNumber = -1
    SET @DepartmentName = ''
  END

  WHILE (@EnableBudgetPerDepartment = 1 AND @APerDepartment = 1 AND @@FETCH_STATUS = 0) OR @DepartmentNumber = -1 
  BEGIN
    IF @ShowPrint = 1
    BEGIN
      PRINT 'Department INFO'
      PRINT @DepartmentNumber
      PRINT @DepartmentName
    END

    SET @TurnoverDay = 0
    SET @TurnoverMonth = 0
    SET @BudgetMonth = 0
    SET @ForecastMonth = 0
    SET @BookedMonth = 0
    SET @CorrectedMonth = 0
    SET @IndexMonth = 0
    SET @IndexMonthYesterDay = 0
    SET @TurnoverMonthLastYear = 0
    SET @IndexTurnoverMonthLastYear = 0
    SET @TurnoverYear = 0
    SET @BudgetYear = 0
    SET @ForecastYear = 0
    SET @BookedYear = 0
    SET @CorrectedYear = 0
    SET @CorrectedYear = 0
    SET @IndexYear = 0
    SET @TurnoverLastYear = 0
    SET @BudgetLastYear = 0
    SET @IndexLastYear = 0
    SET @BudgetMonthLastYear = 0
    SET @TurnoverLastYearToDate = 0
  
    SET @Ratio = 0

    --Day turnover (1)
    SELECT @TurnoverDay = SUM(Turnoverdebit) - SUM(TurnoverCredit)
    FROM AccountDaily WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE Date LIKE(@RunDate)
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND Date <= @CurrentDateStr
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))

    --Month turnover (2)
    SELECT @TurnoverMonth = SUM(Turnoverdebit) - SUM(TurnoverCredit)
    FROM AccountDaily WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE Date LIKE(@tmpYear+@Month+'__')
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND Date < @CurrentDateStr
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))

    --Month budget (3)
    SELECT @BudgetMonth = SUM(Amount) 
    FROM BudgetMonth WITH (NOLOCK) 
    WHERE Year = @tmpYear AND Month = @Month 
    AND TurnoverGroup = @TurnoverGroupNumber
    AND ((@CentralBookingType < 7) OR (ISNULL(HotelNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))

    -- Booked month
    SELECT @BookedMonth = ISNULL(SUM(Amount),0)
    FROM #MISTable WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON #MISTable.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE DateStamp LIKE (@tmpYear+@Month+'__')
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND DateStamp >= @CurrentDateStr
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND DepartmentNumber = @DepartmentNumber))

    --Month Corrected
    SELECT @ForecastMonth = ISNULL(SUM(Amount),0)
    FROM #MISTable WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON #MISTable.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE DateStamp LIKE (@tmpYear+@Month+'__')
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND DepartmentNumber = @DepartmentNumber))

    Select @Ratio = isnull(ratio,0), @RatioInPercent = RatioInPercent From AdditionalSalesFromTurnover WITH (NOLOCK)
    WHERE YearMonth = @tmpYear+@Month AND TurnoverGroup = @TurnoverGroupNumber
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))

    IF ISNULL(@RatioInPercent, 0) = 1    
      SET @CorrectedMonth = @BookedMonth * (@Ratio/100)
    ELSE
      SET @CorrectedMonth = @Ratio

    SET @TotalMonth = (ISNULL(@TurnoverMonth,0)*-1) + ISNULL(@BookedMonth,0) + ISNULL(@CorrectedMonth,0)

    --Month index (4)
    IF @BudgetMonth > 0
    BEGIN
      IF @PercentCalcByForecast = 1
        SET @IndexMonth = CONVERT(MONEY, (@TotalMonth*-1) / CONVERT(MONEY, @BudgetMonth) * 100)
      ELSE
        SET @IndexMonth = CONVERT(MONEY, (@TurnoverMonth) / CONVERT(MONEY, @BudgetMonth) * 100)
    END

    --Yesterday Month index (5)
    IF @BudgetMonth > 0
    BEGIN
      IF @PercentCalcByForecast = 1
        SET @IndexMonthYesterDay = ((@TotalMonth*-1 - ISNULL(@TurnoverDay, 0))/ @BudgetMonth) * 100
      ELSE
        SET @IndexMonthYesterDay = ((@TurnoverMonth - ISNULL(@TurnoverDay, 0)) / @BudgetMonth) * 100
    END

    --Turnover same month last year (6)
    SELECT @TurnoverMonthLastYear = SUM(Turnoverdebit) - SUM(TurnoverCredit)
    FROM AccountDaily WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE Date LIKE(@LastYear+@Month+'__')
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))

    --Index same month last year (7)
    SELECT @BudgetMonthLastYear = Amount 
    FROM BudgetMonth WITH (NOLOCK) 
    WHERE Year = @LastYear AND Month = @Month AND TurnoverGroup = @TurnoverGroupNumber
    AND ((@CentralBookingType < 7) OR (ISNULL(HotelNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))

    IF @BudgetMonthLastYear > 0
      SET @IndexTurnoverMonthLastYear = (@TurnoverMonthLastYear / @BudgetMonthLastYear) * 100

    --Year turnover (8)
    SELECT @TurnoverYear = SUM(Turnoverdebit) - SUM(TurnoverCredit)
    FROM AccountDaily WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE ((@Month1Str = '' and Date LIKE(@tmpYear+'____')) OR (@Month1Str <> '' AND Date >= @YearMonthStart AND Date <= @YearMonthEnd))
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))
    AND Date < @CurrentDateStr

    --Year budget (9)
    SELECT @BudgetYear = SUM(Amount) 
    FROM BudgetMonth WITH (NOLOCK) 
    WHERE ((@Month1Str = '' and Year = @tmpYear) OR (@Month1Str <> '' AND ((Year = @Year1 AND Month IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@Month1Str))) OR (Year = @Year2 AND Month IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@Month2Str))))))
    AND TurnoverGroup = @TurnoverGroupNumber
    AND ((@CentralBookingType < 7) OR (ISNULL(HotelNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))

    --Year Forecast
    SELECT @BookedYear = ISNULL(SUM(Amount),0)
    FROM #MISTable WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON #MISTable.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE ((@Month1Str = '' and DateStamp LIKE(@tmpYear+'____')) OR (@Month1Str <> '' AND ((DateStamp >= @YearMonthStart AND DateStamp <= @YearMonthEnd))))
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND DateStamp >= @CurrentDateStr
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND DepartmentNumber = @DepartmentNumber))

    --Year Corrected
    SELECT @ForecastYear = ISNULL(SUM(Amount),0)
    FROM #MISTable WITH (NOLOCK) 
    inner join Accounts WITH (NOLOCK) ON #MISTable.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE ((@Month1Str = '' and DateStamp LIKE(@tmpYear+'____')) OR (@Month1Str <> '' AND DateStamp >= @YearMonthStart AND DateStamp <= @YearMonthEnd))
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND DepartmentNumber = @DepartmentNumber))

    SET @MonthCount = 1
  
    IF @ShowPrint = 1
      Print 'Year Corrected'

    IF @Month1Str = ''
    BEGIN
      WHILE @MonthCount < 13
      BEGIN
        SET @MonthCountStr = CONVERT(CHAR(2), @MonthCount)
        IF LEN(@MonthCountStr) = 1
          SET @MonthCountStr = '0' + @MonthCountStr
  
        SET @Ratio = 0
        SELECT @Ratio = ISNULL(ratio,0), @RatioInPercent = RatioInPercent FROM AdditionalSalesFromTurnover WITH (NOLOCK)
        WHERE YearMonth = @tmpYear+@MonthCountStr AND TurnoverGroup = @TurnoverGroupNumber
        AND ((@CentralBookingType < 7) OR (ISNULL(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
        AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
          (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))
    
        SELECT @BookedYearmonth = ISNULL(SUM(Amount),0)
        FROM #MISTable WITH (NOLOCK) 
        INNER JOIN Accounts WITH (NOLOCK) ON #MISTable.AccountNumber = Accounts.AccountNumber  AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
        WHERE DateStamp LIKE (@tmpYear+@MonthCountStr+'__')
        AND ((@CentralBookingType < 7) OR (ISNULL(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
        AND DateStamp >= @CurrentDateStr
        AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
          (@DepartmentNumber > -1 AND DepartmentNumber = @DepartmentNumber))

        IF ISNULL(@RatioInPercent, 0) = 1
          SET @CorrectedYear = @CorrectedYear + (ISNULL(@BookedYearMonth,0) * (ISNULL(@Ratio,0) / 100))
        ELSE
          SET @CorrectedYear = @CorrectedYear + ISNULL(@Ratio,0)

        IF @ShowPrint = 1
        BEGIN
          PRINT 'Ratio'
          PRINT @Ratio
          PRINT 'YearCorrected'
          PRINT convert(varchar,@CorrectedYear)
        END

        SET @MonthCount = @MonthCount+1
      END
    END
    ELSE
    BEGIN
      WHILE @tmpMonthStart <= 12
      BEGIN
        SET @MonthCountStr = CONVERT(CHAR(2), @tmpMonthStart)
        IF LEN(@MonthCountStr) = 1
          SET @MonthCountStr = '0' + @MonthCountStr

        SELECT @Ratio = ISNULL(ratio,0), @RatioInPercent = RatioInPercent FROM AdditionalSalesFromTurnover WITH (NOLOCK)
        WHERE YearMonth = @Year1+@MonthCountStr AND TurnoverGroup = @TurnoverGroupNumber
        AND ((@CentralBookingType < 7) OR (ISNULL(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
        AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
          (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))
    
        SELECT @BookedYearmonth = ISNULL(SUM(Amount),0)
        FROM #MISTable WITH (NOLOCK) 
        INNER JOIN Accounts WITH (NOLOCK) ON #MISTable.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
        WHERE DateStamp LIKE (CONVERT(CHAR(4), @Year1)+@MonthCountStr+'__')
        AND ((@CentralBookingType < 7) OR (ISNULL(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
        AND DateStamp >= @CurrentDateStr
        AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
          (@DepartmentNumber > -1 AND DepartmentNumber = @DepartmentNumber))

        IF ISNULL(@RatioInPercent, 0) = 1
          SET @CorrectedYear = @CorrectedYear + (ISNULL(@BookedYearMonth,0) * (ISNULL(@Ratio,0) / 100))
        ELSE
          SET @CorrectedYear = @CorrectedYear + ISNULL(@Ratio,0)

        IF @ShowPrint = 1
        BEGIN
          PRINT '@Year1+@MonthCountStr'
          PRINT @Year1+@MonthCountStr
          PRINT 'Ratio'
          PRINT @Ratio
          PRINT 'YearCorrected'
          PRINT convert(varchar,@CorrectedYear)
        END
        SET @tmpMonthStart = @tmpMonthStart + 1
      END

      WHILE @tmpMonthEnd >= 1
      BEGIN
        SET @MonthCountStr = CONVERT(CHAR(2), @tmpMonthEnd)
        IF LEN(@MonthCountStr) = 1
          SET @MonthCountStr = '0' + @MonthCountStr

        SELECT @Ratio = ISNULL(ratio,0), @RatioInPercent = RatioInPercent FROM AdditionalSalesFromTurnover WITH (NOLOCK)
        WHERE YearMonth = @Year2+@MonthCountStr AND TurnoverGroup = @TurnoverGroupNumber
        AND ((@CentralBookingType < 7) OR (ISNULL(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
        AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
          (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))
    
        SELECT @BookedYearmonth = ISNULL(SUM(Amount),0)
        FROM #MISTable WITH (NOLOCK) 
        INNER JOIN Accounts WITH (NOLOCK) ON #MISTable.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
        WHERE DateStamp LIKE (CONVERT(CHAR(4), @Year2)+@MonthCountStr+'__')
        AND ((@CentralBookingType < 7) OR (ISNULL(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
        AND DateStamp >= @CurrentDateStr
        AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
          (@DepartmentNumber > -1 AND DepartmentNumber = @DepartmentNumber))

        IF ISNULL(@RatioInPercent, 0) = 1
          SET @CorrectedYear = @CorrectedYear + (ISNULL(@BookedYearMonth,0) * (ISNULL(@Ratio,0) / 100))
        ELSE
          SET @CorrectedYear = @CorrectedYear + ISNULL(@Ratio,0)

        IF @ShowPrint = 1
        BEGIN
          PRINT '@Year2+@MonthCountStr'
          PRINT @Year1+@MonthCountStr
          PRINT 'Ratio'
          PRINT @Ratio
          PRINT 'YearCorrected'
          PRINT convert(varchar,@CorrectedYear)
        END
        SET @tmpMonthEnd = @tmpMonthEnd - 1
      END
    END

    SET @TotalYear = (ISNULL(@TurnoverYear,0)*-1) + ISNULL(@BookedYear,0) + ISNULL(@CorrectedYear,0)

    --Year index (10)
    IF @BudgetYear > 0
    BEGIN
      IF @PercentCalcByForecast = 1
        SET @IndexYear = (@TotalYear*-1 / @BudgetYear) * 100
      ELSE
        SET @IndexYear = (@TurnoverYear / @BudgetYear) * 100
    END

    --Last year turnover (11)
    SELECT @TurnoverLastYear = SUM(Turnoverdebit) - SUM(TurnoverCredit)
    FROM AccountDaily WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE ((@Month1Str = '' and Date LIKE(@LastYear+'____')) OR (@Month1Str <> '' AND Date >= @LastYearMonthStart AND Date <= @LastYearMonthEnd))
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))

    --Last year budget (12)
    SELECT @BudgetLastYear = SUM(Amount) 
    FROM BudgetMonth WITH (NOLOCK) 
    WHERE ((@Month1Str = '' and Year = @LastYear) OR (@Month1Str <> '' AND ((Year = @LastYear1 AND Month IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@Month1Str))) AND (Year = @LastYear2 AND Month IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@Month2Str))))))
    AND TurnoverGroup = @TurnoverGroupNumber
    AND ((@CentralBookingType < 7) OR (ISNULL(HotelNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))

    --Last year index (13)
    IF @BudgetLastYear > 0
      SET @IndexLastYear = (@TurnoverLastYear / @BudgetLastYear) * 100
    
    -- LastYear/this year Index (14)
    IF @TurnoverLastYear <> 0
    BEGIN
      IF @PercentCalcByForecast = 1
        SET @IndexLastYearThisYear = (@TotalYear*-1 / @TurnoverLastYear) * 100
      ELSE
        SET @IndexLastYearThisYear = (@TurnoverYear / @TurnoverLastYear) * 100
    END    
        
    -- Turnover year to date
    SET @tmp = 0
    SELECT @tmp = SUM(Turnoverdebit) - SUM(TurnoverCredit)
    FROM AccountDaily WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE ((@Month1Str = '' and Date LIKE(@tmpYear+'____')) OR (@Month1Str <> '' AND Date >= @YearMonthStart AND Date <= @YearMonthEnd))
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))
    AND DATE < @RunDate
    AND Date < @CurrentDateStr
    
    SET @tmp = @tmp * -1
    
    SELECT @TurnoverYearToDate = ISNULL(SUM(Amount),0)
    FROM #MISTable WITH (NOLOCK) 
    inner join Accounts WITH (NOLOCK) on #MISTable.AccountNumber = Accounts.AccountNumber  AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE ((@Month1Str = '' and DateStamp LIKE(@tmpYear+'____')) OR (@Month1Str <> '' AND DateStamp >= @YearMonthStart AND DateStamp <= @YearMonthEnd))
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND DepartmentNumber = @DepartmentNumber))
    AND DateStamp <= @RunDate
    AND DateStamp > @CurrentDateStr

    SET @TurnoverYearToDate = ISNULL(@TurnoverYearToDate, 0) + @tmp

    -- Budget year to date
    SELECT @BudgetYearToDate = SUM(Amount) 
    FROM BudgetMonth WITH (NOLOCK) 
    WHERE ((@Month1Str = '' and Year = @tmpYear) OR (@Month1Str <> '' AND ((Year = @Year1 AND Month IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@Month1Str))) OR (Year = @Year2 AND Month IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@Month2Str))))))
    AND TurnoverGroup = @TurnoverGroupNumber
    AND ((@CentralBookingType < 7) OR (ISNULL(HotelNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))
    AND Year = @tmpYear AND Month <= @Month 

    -- Index Year to date
    IF @BudgetYearToDate > 0
      SET @IndexYearToDate = (CONVERT(MONEY, @TurnoverYearToDate) / CONVERT(MONEY, @BudgetYearToDate)) * 100
    ELSE
      SET @IndexYearToDate = 0
    
    --Last year turnover to date
    SELECT @TurnoverLastYearToDate = SUM(Turnoverdebit) - SUM(TurnoverCredit)
    FROM AccountDaily WITH (NOLOCK) 
    INNER JOIN Accounts WITH (NOLOCK) ON AccountDaily.AccountNumber = Accounts.AccountNumber AND Accounts.TurnoverGroupNumber = @TurnoverGroupNumber
    WHERE ((@Month1Str = '' and Date LIKE(@LastYear+'____')) OR (@Month1Str <> '' AND Date >= @LastYearMonthStart AND Date <= @LastYearMonthEnd))
    AND ((@CentralBookingType < 7) OR (IsNull(HotelCustomerNumber,-1) IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@HotelCustomerNumberStr))))
    AND ((@DepartmentNumber = -1 AND (@FK_DepartmentNumberCSV = '' OR FK_DepartmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@FK_DepartmentNumberCSV)))) OR 
      (@DepartmentNumber > -1 AND FK_DepartmentNumber = @DepartmentNumber))
    AND Date <= @RunDateLastYear

    SET @TurnoverDay = @TurnoverDay * -1
    SET @TurnoverMonth = @TurnoverMonth * -1
    SET @IndexMonth = @IndexMonth * -1
    SET @IndexMonthYesterDay = @IndexMonthYesterDay * -1
    SET @TurnoverMonthLastYear = @TurnoverMonthLastYear * -1
    SET @IndexTurnoverMonthLastYear = @IndexTurnoverMonthLastYear * -1
    SET @TurnoverYear = @TurnoverYear * -1
    IF @IndexYear < 0 SET @IndexYear = @IndexYear * -1
    SET @TurnoverLastYear = @TurnoverLastYear * -1
    SET @IndexLastYear = @IndexLastYear * -1
    SET @TurnoverLastYearToDate = @TurnoverLastYearToDate * -1

    INSERT INTO #BudgetReport (GroupNumber, GroupName, AccountGroupGroupName, DepartmentName, FK_DepartmentNumber, TurnoverDay, TurnoverMonth, BudgetMonth, BookedMonth, ForecastMonth, CorrectedMonth, IndexMonth, IndexMonthYesterDay, TurnoverMonthLastYear, BudgetMonthLastYear, IndexTurnoverMonthLastYear, TurnoverYear, BudgetYear, BookedYear, ForecastYear, CorrectedYear, IndexYear, TurnoverLastYear, BudgetLastYear, IndexLastYear, IndexLastYearThisYear, TurnoverYearToDate, BudgetYearToDate, IndexYearToDate, TurnoverLastYearToDate)
    VALUES (@TurnoverGroupNumber, @TurnoverGroupName, CONVERT(VARCHAR, @SummaryGroupNumber) + ': ' + @AccountGroupGroupName, @DepartmentName, @DepartmentNumber, ISNULL(@TurnoverDay,0), ISNULL(@TurnoverMonth,0), ISNULL(@BudgetMonth,0), ISNULL(@BookedMonth,0), ISNULL(@TotalMonth, 0), ISNULL(@CorrectedMonth,0), ISNULL(@IndexMonth,0), ISNULL(@IndexMonthYesterDay,0), ISNULL(@TurnoverMonthLastYear,0), ISNULL(@BudgetMonthLastYear,0), ISNULL(@IndexTurnoverMonthLastYear,0), ISNULL(@TurnoverYear,0), ISNULL(@BudgetYear,0), ISNULL(@BookedYear,0), ISNULL(@TotalYear,0), ISNULL(@CorrectedYear,0), ISNULL(@IndexYear,0), ISNULL(@TurnoverLastYear,0), ISNULL(@BudgetLastYear,0), ISNULL(@IndexLastYear,0), ISNULL(@IndexLastYearThisYear, 0), ISNULL(@TurnoverYearToDate, 0), ISNULL(@BudgetYearToDate, 0), ISNULL(@IndexYearToDate, 0), ISNULL(@TurnoverLastYearToDate, 0))

    IF @EnableBudgetPerDepartment = 1 AND @APerDepartment = 1 
      FETCH NEXT FROM db_dep_cur INTO @DepartmentNumber, @DepartmentName
    ELSE
      SET @DepartmentNumber = -99 -- to break while for db_dep_cur
  END
  FETCH NEXT FROM TurnGroupNumber_cur INTO @TurnOverGroupNumber, @TurnoverGroupName, @AccountGroupGroupName, @SummaryGroupNumber
END

CLOSE TurnGroupNumber_cur
DEALLOCATE TurnGroupNumber_cur

IF @APricesInThousands = 1
BEGIN
  SELECT --* 
    GroupNumber,
    GroupName,
    AccountGroupGroupName,
    ROUND(TurnoverDay, -3)/1000 AS TurnoverDay,
    ROUND(TurnoverMonth, -3)/1000 AS TurnoverMonth,
    ROUND(BudgetMonth, -3)/1000 AS BudgetMonth,
    ROUND(BookedMonth, -3)/1000 AS BookedMonth,
    ROUND(ForecastMonth, -3)/1000 AS ForecastMonth,
    ROUND(CorrectedMonth, -3)/1000 AS CorrectedMonth,
    IndexMonth,
    IndexMonthYesterDay,
    ROUND(TurnoverMonthLastYear, -3)/1000 AS TurnoverMonthLastYear,
    ROUND(BudgetMonthLastYear, -3)/1000 AS BudgetMonthLastYear,
    IndexTurnoverMonthLastYear,
    ROUND(TurnoverYear, -3)/1000 AS TurnoverYear,
    ROUND(BudgetYear, -3)/1000 AS BudgetYear,
    ROUND(BookedYear, -3)/1000 AS BookedYear,
    ROUND(ForecastYear, -3)/1000 AS ForecastYear,
    ROUND(CorrectedYear, -3)/1000 AS CorrectedYear,
    IndexYear,
    ROUND(TurnoverLastYear, -3)/1000 AS TurnoverLastYear,
    ROUND(BudgetLastYear, -3)/1000 AS BudgetLastYear,
    IndexLastYear,
    FK_DepartmentNumber,
    DepartmentName,
    IndexLastYearThisYear,
    ROUND(TurnoverYearToDate, -3)/1000 AS TurnoverYearToDate,
    ROUND(BudgetYearToDate, -3)/1000 AS BudgetYearToDate,
    IndexYearToDate,
    ROUND(TurnoverLastYearToDate, -3)/1000 AS TurnoverLastYearToDate
  FROM #BudgetReport WITH (NOLOCK) 
  WHERE (ISNULL(@AHideEmpty, 0) = 0 OR (
    TurnoverDay <> 0 OR
    TurnoverMonth <> 0 OR
    BookedMonth <> 0 OR
    TurnoverYear <> 0 OR
    BookedYear <> 0
  ))
  ORDER BY DepartmentName, FK_DepartmentNumber, GroupNumber 
END
ELSE
BEGIN
  SELECT *
  FROM #BudgetReport WITH (NOLOCK) 
  WHERE (ISNULL(@AHideEmpty, 0) = 0 OR (
    TurnoverDay <> 0 OR
    TurnoverMonth <> 0 OR
    BookedMonth <> 0 OR
    TurnoverYear <> 0 OR
    BookedYear <> 0
  ))
  ORDER BY DepartmentName, FK_DepartmentNumber, GroupNumber
END

DROP TABLE #BudgetReport
IF @EnableBudgetPerDepartment = 1 AND @APerDepartment = 1
  DROP TABLE #Department
  
DROP TABLE #MISTable