Opportunities to improve performance of SQL server

exec sp_executesql '              
select                    cdf.TransactionCashDepositFinancialID,                    fg.FinancialGroupID,                    fg.Description,                    fg.OperationEnum,                    fg.SortOrder,                    fg.Status,                    sum(cdf.Amount) as Amount,                    sum(cdf.Quantity) as Quantity              
from                    tbtransactioncashdeposit cd with (nolock)              
join tbtransactioncashdepositfinancial cdf with (nolock)              on cd.TransactionCashDepositID=cdf.TransactionCashDepositID              
join dbo.fn_GetFinancialTypeGroups(#) fg              on fg.FinancialGroupID=cdf.FinancialGroupID or fg.ChildFinancialGroupID=cdf.FinancialGroupID               
where                    cd.TransactionCashDepositID = @p0                    
and fg.ZoneID is null                    
and fg.Status != #              
group by                    cdf.TransactionCashDepositFinancialID,                    fg.FinancialGroupID,                    fg.Description,                    fg.OperationEnum,                    fg.SortOrder,                    fg.Status              
Union               
select                    cdf.TransactionCashDepositFinancialID,                      fg.FinancialGroupID,                    fg.Description,                    fg.OperationEnum,                    fg.SortOrder,                    fg.Status,                    sum(cdf.Amount) as Amount,                    sum(cdf.Quantity) as Quantity              
from                    tbtransactioncashdeposit cd with (nolock)              
join tbtransactioncashdepositfinancial cdf with (nolock)              on cd.TransactionCashDepositID=cdf.TransactionCashDepositID              
join dbo.fn_GetFinancialTypeGroups(#) fg              on fg.FinancialGroupID=cdf.FinancialGroupID or fg.ChildFinancialGroupID=cdf.FinancialGroupID              
LEFT JOIN tbEntityZone ez with (nolock)              on ez.ZoneID = fg.ZoneID               
where                    cd.TransactionCashDepositID = @p0                    
and ez.EntityID = cd.EntityID                    and fg.Status != #              
group by                    cdf.TransactionCashDepositFinancialID,                    fg.FinancialGroupID,                    fg.Description,                    fg.OperationEnum,                    fg.SortOrder,                    fg.Status              order by                  fg.SortOrder',N'@p0 bigint',@p0=#

exec sp_executesql 'DECLARE @IDList VARCHAR(MAX)                          
SELECT @IDList = COALESCE(@IDList +$, $) + CONVERT(varchar(20),ForecastId)                          
FROM (                           
SELECT ForecastId, ROW_NUMBER() OVER (PARTITION BY EntityId, BusinessDay ORDER BY GenerationDate DESC) RowNumber                            
FROM Forecasting.Forecast WITH(NOLOCK)                           
WHERE EntityId = @p0                            
AND BusinessDay >= CONVERT(date, @p1)                            
AND BusinessDay <= CONVERT(date, @p2)                           ) f 
WHERE RowNumber = #                            
DECLARE @Query NVARCHAR(MAX) = $ + @p3 + $ + @IDList + $ + @p3 + $                            
EXEC sp_executesql @Query,N''@p0 datetime,@p1 datetime'',@p0=@p1,@p1=@p2',N'@p0 bigint,@p1 datetime,@p2 datetime,@p3 nvarchar(4000)',@p0=#,@p1=$,@p2=$,@p3=$

exec sp_executesql 'EXEC [dbo].[MXC_Financial_GetCustomerSalesNotLinkedToCustomer] @p0, @p1, @p2',N'@p0 bigint,@p1 datetime2(7),@p2 datetime2(7)',@p0=#,@p1=$,@p2=$

exec sp_executesql 'EXEC [dbo].[MXC_Inventory_GetTransactionSalesOrderDetails] @transactionSalesOrderId = @p0',N'@p0 bigint',@p0=#

exec sp_executesql 'DECLARE @TradingDayOpen DATETIME                      DECLARE @TradingDayClose DATETIME                      SELECT @TradingDayOpen=DayOpen, @TradingDayClose=DayClose FROM  dbo.fn_GetTradingForCalendarDay(@p0, @p1)                      Select                            fg.FinancialGroupID,          fg.Description,          fg.OperationEnum,          fg.SortOrder,          fg.Status,                                    case when FunctionEnum = 1 then          case when amts.Quantity = 0           then Cast(0 as decimal)          else Cast(round(amts.Amount/amts.Quantity,?) as decimal(18,2))         end                            else amts.Amount                             end as Amount,          amts.Quantity                      From dbo.fn_GetFinancialTypeGroups(@p2) fg                      INNER JOIN dbo.fn_GetFinancialTypeAmounts(@p2, @p0, @TradingDayOpen, @TradingDayClose, ?, ?) amts                          ON fg.FinancialGroupID = amts.FinancialGroupID                      Order BY fg.SortOrder',N'@p0 bigint,@p1 datetime,@p2 smallint',@p0=?,@p1=?,@p2=?

exec sp_executesql 'SELECT this_.SalesItemMetricDetailId as SalesIte1_124_0_, this_.SalesItemId as SalesIte2_124_0_, this_.IntervalStart as Interval3_124_0_, this_.LastYearTransactionCount as LastYear4_124_0_, this_.ManagerTransactionCount as ManagerT5_124_0_, this_.RawTransactionCount as RawTrans6_124_0_, this_.SystemTransactionCount as SystemTr7_124_0_, this_.ServiceType as ServiceT8_124_0_, this_.MgrDetailsChanged as MgrDetai9_124_0_, this_.ForecastId as ForecastId124_0_ FROM Forecasting.SalesItemMetricDetail this_ WHERE ((this_.ForecastId = @p0 and this_.IntervalStart >= @p1) and this_.IntervalStart < @p2)',N'@p0 bigint,@p1 datetime,@p2 datetime',@p0=#,@p1=$,@p2=$

exec [dbo].[qryMMSBankingTransaction_GetChangeFundRebank] @EntityID=#,@CalendarDay=$,@ForCalendarDay=#

exec [dbo].[qryMMSSyncToken_CheckExists] @SyncToken=$

exec sp_executesql 'SELECT this_.SalesItemMetricDetailId as SalesIte1_121_0_, this_.SalesItemId as SalesIte2_121_0_, this_.IntervalStart as Interval3_121_0_, this_.LastYearTransactionCount as LastYear4_121_0_, this_.ManagerTransactionCount as ManagerT5_121_0_, this_.RawTransactionCount as RawTrans6_121_0_, this_.SystemTransactionCount as SystemTr7_121_0_, this_.ServiceType as ServiceT8_121_0_, this_.MgrDetailsChanged as MgrDetai9_121_0_, this_.ForecastId as ForecastId121_0_ FROM Forecasting.SalesItemMetricDetail this_ WHERE ((this_.ForecastId = @p0 and this_.IntervalStart >= @p1) and this_.IntervalStart < @p2)',N'@p0 bigint,@p1 datetime,@p2 datetime',@p0=#,@p1=$,@p2=$

exec [dbo].[qryMMSComboBoxReference_CustomerSearch_ByEntityID] @SearchText=$,@EntityID=#,@searchActiveCustomersOnly=#

exec sp_executesql '      DECLARE @mintranItemID BIGINT, @maxtranItemID BIGINT        SELECT        @mintranItemID = MIN(TransactionItemID),        @maxtranItemID = MAX(TransactionItemID)      FROM tbTransactionItem WITH(NOLOCK, FORCESEEK)      WHERE       EntityID = @p0 AND       BusinessDay = @p1                    IF @mintranItemID IS NULL OR @maxtranItemID IS NULL RETURN        SELECT       ti.ItemID AS ItemId,       ti.BusinessDay AS BusinessDay,       DATEADD(mi, DATEDIFF(mi, #, ti.TransactionTime) / # * #, #) as IntervalStart,       SUM(ti.Quantity) As Quantity,                      tsi.InOutDel as ServiceType      FROM tbTransactionItem ti WITH(NOLOCK)      JOIN tbEntityItem      ei WITH(NOLOCK)      ON       ti.EntityID = ei.EntityID AND       ti.ItemID = ei.ItemID                  JOIN tbTransactionSalesItem tsi WITH(NOLOCK)                  ON                      ti.TransactionSalesItemID = tsi.TransactionSalesItemID      WHERE       (@p2 = # OR ti.ItemID = @p2) AND       ti.EntityID = @p0 AND       ti.BusinessDay = @p1 AND       ei.InUse = # AND       (ti.TransactionItemID >= @mintranItemID AND ti.TransactionItemID <= @maxtranItemID)       GROUP BY       ti.ItemID,       ti.BusinessDay,       DATEADD(mi, DATEDIFF(mi, #, ti.TransactionTime) / # * #, #),                      tsi.InOutDel',N'@p0 bigint,@p1 datetime2(7),@p2 bigint',@p0=#,@p1=$,@p2=#

exec sp_executesql 'DECLARE @IDList VARCHAR(MAX)                          SELECT @IDList = COALESCE(@IDList +$, $) + CONVERT(varchar(20),ForecastId)                          FROM (                           SELECT ForecastId, ROW_NUMBER() OVER (PARTITION BY EntityId, BusinessDay ORDER BY GenerationDate DESC) RowNumber                            FROM Forecasting.Forecast WITH(NOLOCK)                           WHERE EntityId = @p0                            AND BusinessDay >= CONVERT(date, @p1)                            AND BusinessDay <= CONVERT(date, @p2)                           ) f WHERE RowNumber = #                            DECLARE @Query NVARCHAR(MAX) = $ + @p3 + $ + @IDList + $ + @p3 + $                            EXEC sp_executesql @Query,N''@p0 datetime,@p1 datetime'',@p0=@p1,@p1=@p2',N'@p0 bigint,@p1 datetime,@p2 datetime,@p3 nvarchar(4000)',@p0=#,@p1=$,@p2=$,@p3=$

exec [dbo].[qryMMS_Forecasting_TransactionItemIntervalAggregate]

declare @p1 int  set @p1=#  exec sp_prepexec @p1 output,N'@p0 datetime,@p1 bigint','                  DECLARE @rowcount INT = #                    WHILE @rowcount > #                      BEGIN                          DELETE TOP (#) detail                           FROM [Forecasting].[InventoryItemMetricDetail] detail                                        JOIN [Forecasting].[Forecast] forecast WITH (NOLOCK) ON detail.ForecastId = forecast.ForecastId                          WHERE forecast.EntityId = @p1                                AND forecast.BusinessDay < @p0                                                SET @rowcount = @@ROWCOUNT                        END',@p0=$,@p1=#  select @p1

exec [dbo].[qryMMSComboBoxReference_CustomerSearch_ByEntityID] @SearchText=$,@EntityID=#,@searchActiveCustomersOnly=#

exec sp_executesql 'SELECT              dcb.ItemId,              dcb.EntityID,              dcb.PurchaseExclReturnsValue,              dcb.PurchaseExclReturnsQty,              dcb.PurchaseReturnsValue,              dcb.PurchaseReturnsQty,              dcb.TransferInValue,              dcb.TransferInQty,              dcb.TransferOutValue,              dcb.TransferOutQty,              dcb.BOMMakeValue,              dcb.BOMMakeQty,              dcb.BOMUseValue,              dcb.BOMUseQty,              dcb.SalesValue,              dcb.SalesQty,              dcb.WasteValue,              dcb.WasteQty,              dcb.SamplingValue,              dcb.SamplingQty,              dcb.OtherAdjustValue,              dcb.OtherAdjustQty,              dcb.DispatchValue,              dcb.DispatchQty,              dcb.FinishedWasteValue,              dcb.FinishedWasteQty            FROM dbo.fn_PopulateDailyClosingBalance(@EntityID, @StartDate, @EndDate) AS dcb            JOIN tbEntityDailyStockBalance AS e with (nolock)              ON dcb.EntityID=e.EntityID            JOIN tbEntityItemDailyStockBalance AS ei with (nolock)              ON dcb.EntityID=ei.EntityID              AND dcb.ItemID=ei.ItemID            WHERE ei.LastBusinessDay>=e.LastBusinessDay            ORDER BY dcb.ItemID  ',N'@EntityID bigint,@StartDate datetime,@EndDate datetime',@EntityID=#,@StartDate=$,@EndDate=$

exec sp_executesql '              select                    cdf.TransactionCashDepositFinancialID,                    fg.FinancialGroupID,                    fg.Description,                    fg.OperationEnum,                    fg.SortOrder,                    fg.Status,                    sum(cdf.Amount) as Amount,                    sum(cdf.Quantity) as Quantity              from                    tbtransactioncashdeposit cd with (nolock)              join tbtransactioncashdepositfinancial cdf with (nolock)              on cd.TransactionCashDepositID=cdf.TransactionCashDepositID              join dbo.fn_GetFinancialTypeGroups(#) fg              on fg.FinancialGroupID=cdf.FinancialGroupID or fg.ChildFinancialGroupID=cdf.FinancialGroupID               where                    cd.TransactionCashDepositID = @p0                    and fg.ZoneID is null                    and fg.Status != #              group by                    cdf.TransactionCashDepositFinancialID,                    fg.FinancialGroupID,                    fg.Description,                    fg.OperationEnum,                    fg.SortOrder,                    fg.Status              Union               select                    cdf.TransactionCashDepositFinancialID,                      fg.FinancialGroupID,                    fg.Description,                    fg.OperationEnum,                    fg.SortOrder,                    fg.Status,                    sum(cdf.Amount) as Amount,                    sum(cdf.Quantity) as Quantity              from                    tbtransactioncashdeposit cd with (nolock)              join tbtransactioncashdepositfinancial cdf with (nolock)              on cd.TransactionCashDepositID=cdf.TransactionCashDepositID              join dbo.fn_GetFinancialTypeGroups(#) fg              on fg.FinancialGroupID=cdf.FinancialGroupID or fg.ChildFinancialGroupID=cdf.FinancialGroupID              LEFT JOIN tbEntityZone ez with (nolock)              on ez.ZoneID = fg.ZoneID               where                    cd.TransactionCashDepositID = @p0                    and ez.EntityID = cd.EntityID                    and fg.Status != #              group by                    cdf.TransactionCashDepositFinancialID,                    fg.FinancialGroupID,                    fg.Description,                    fg.OperationEnum,                    fg.SortOrder,                    fg.Status              order by                  fg.SortOrder',N'@p0 bigint',@p0=#

exec sp_executesql 'EXEC [dbo].[MXC_Inventory_GetTransactionSalesOrderDetails] @transactionSalesOrderId = @p0',N'@p0 bigint',@p0=#

declare @p1 int  set @p1=#  exec sp_prepexec @p1 output,N'@p0 datetime,@p1 bigint','                  DECLARE @rowcount INT = #                    WHILE @rowcount > #                      BEGIN                          DELETE TOP (#) detail                           FROM [Forecasting].[SalesItemMetricDetail] detail                                        JOIN [Forecasting].[Forecast] forecast WITH (NOLOCK) ON detail.ForecastId = forecast.ForecastId                          WHERE forecast.EntityId = @p1                                AND forecast.BusinessDay < @p0                                                SET @rowcount = @@ROWCOUNT                        END',@p0=$,@p1=#  select @p1

declare @p1 int  set @p1=#  exec sp_prepexec @p1 output,N'@p0 bigint,@p1 datetime,@p2 datetime','SELECT this_.OnAccountSalePaymentId as OnAccoun1_165_2_, this_.AppliedAmount as AppliedA2_165_2_, this_.CheckNumber as CheckNum3_165_2_, this_.CreditAppliedFlag as CreditAp4_165_2_, this_.IsDeposit as IsDeposit165_2_, this_.CreatedTimestamp as CreatedT6_165_2_, this_.OnAccountSaleId as OnAccoun7_165_2_, this_.TransactionControlInfoID as Transact8_165_2_, oasalias1_.OnAccountSaleId as OnAccoun1_164_0_, oasalias1_.Description as Descript2_164_0_, oasalias1_.EntityId as EntityId164_0_, oasalias1_.InvoiceNumber as InvoiceN4_164_0_, oasalias1_.UncollectedFlag as Uncollec5_164_0_, oasalias1_.Paid as Paid164_0_, oasalias1_.Type as Type164_0_, oasalias1_.UncollectibleTimestamp as Uncollec8_164_0_, oasalias1_.PaidInFullTimestamp as PaidInFu9_164_0_, oasalias1_.ReversalFlag as Reversa10_164_0_, oasalias1_.TransactionSummaryID as Transac11_164_0_, oasalias1_.CustomerId as CustomerId164_0_, ( SELECT SUM(tbOnaccountSalePayment.AppliedAmount) FROM tbOnaccountSalePayment WHERE tbOnaccountSalePayment.OnAccountSaleId = oasalias1_.OnAccountSaleId ) as formula1_0_, tcialias2_.TransactionControlInfoID as Transact1_167_1_, tcialias2_.ClerkName as ClerkName167_1_, tcialias2_.EntityID as EntityID167_1_, tcialias2_.POSTransactionID as POSTrans4_167_1_, tcialias2_.TransactionDate as Transact5_167_1_, tcialias2_.ControlInfoCount as ControlI6_167_1_, tcialias2_.ControlInfoAmount as ControlI7_167_1_, tcialias2_.OtherInfo2 as OtherInfo8_167_1_, tcialias2_.OtherInfo3 as OtherInfo9_167_1_, tcialias2_.CurrentShiftNumber as Current10_167_1_, tcialias2_.BusinessDay as Busines11_167_1_, tcialias2_.ClerkID as ClerkID167_1_, tcialias2_.ControlInfoID as Control13_167_1_, tcialias2_.SystemTransactionID as SystemT14_167_1_ FROM tbOnAccountSalePayment this_ inner join tbOnAccountSale oasalias1_ on this_.OnAccountSaleId=oasalias1_.OnAccountSaleId inner join tbTransactionControlInfo tcialias2_ on this_.TransactionControlInfoID=tcialias2_.TransactionControlInfoID WHERE ((tcialias2_.EntityID = @p0 and tcialias2_.BusinessDay >= @p1) and tcialias2_.BusinessDay <= @p2)',@p0=#,@p1=$,@p2=$  select @p1

DECLARE @Month DATETIME    SELECT @Month =   convert(DATETIME,(CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE()-#)-#),GETDATE()-#),#)))      DECLARE @SCStores TABLE (EntityID BIGINT)    INSERT INTO @SCSTores    SELECT e.EntityID FROM [chick-fil-a].dbo.tbEntity e WITH (NOLOCK)    JOIN [chick-fil-a].dbo.tbEntityZone ez WITH (NOLOCK)    ON ez.EntityID = e.EntityID    JOIN [chick-fil-a].dbo.tbZone z WITH (NOLOCK)    ON z.ZoneID = ez.ZoneID    WHERE z.Zone in ($)    and e.Status = $    and e.OpenDate <= GetDate()      DECLARE @CountsCreated INT = (SELECT COUNT(*) FROM [chick-fil-a].dbo.tbTransactionStockCountLocatiON tscl WITH (nolock) where ApplicablePeriod >= @Month AND tscl.Status = $ AND tscl.StockCountType = $ and tscl.EntityID IN (SELECT EntityID FROM @SCStores))    DECLARE @CountsApplied INT = (SELECT COUNT(*) FROM [chick-fil-a].dbo.tbTransactionStockCountLocatiON tscl WITH (nolock) where ApplicablePeriod >= @Month AND tscl.Status = $ AND tscl.StockCountType = $ and tscl.EntityID IN (SELECT EntityID FROM @SCStores))      DECLARE @STockCounts TABLE (EntityID BIGINT, CreateDate DATETIME, ApplyDate DATETIME, ClerkName VARCHAR(75), CountName VARCHAR(75), CountStatus VARCHAR(18), ApplicablePeriod DATETIME, StartTime DATETIME, EndTime DATETIME, Mobile BIT)    INSERT INTO @STockCounts    select EntityID, CreateDate, ApplyDate, ClerkName, StockCountName, Status, ApplicablePeriod, STartTime, EndTime, CASE WHEN StartTime IS NOT NULL OR EndTime IS NOT NULL THEN 1 ELSE 0 END AS [Mobile]    FROM [chick-fil-a].dbo.tbTransactionStockCountLocatiON WITH (nolock)    where StockCountType = $    AND ApplicablePeriod >= @Month and EntityID in (select EntityID from @SCStores)      DECLARE @MobileCounts INT = (SELECT COUNT(*) FROM @StockCounts WHERE Mobile = #)    DECLARE @WebCounts INT = (SELECT COUNT(*) FROM @STockCounts WHERE Mobile = #)      DECLARE @NoCounts INT    SELECT @NoCounts = COUNT(*)    from @SCStores e    left Join @STockCounts sc    on sc.EntityID = e.EntityID    where sc.ApplicablePeriod IS NULL               DECLARE @TotalStores  INT = (SELECT COUNT(*) from @SCStores)        DECLARE @MultiCountStores TABLE (EntityID BIGINT, Counts INT)    INSERT INTO @MultiCountStores    SELECT tscl.EntityID, COUNT(*) Counts    FROM [chick-fil-a].dbo.tbTransactionStockCountLocation tscl WITH (NOLOCK)    JOIN @SCStores e    ON e.EntityID = tscl.EntityID    WHERE tscl.StockCountType = $    AND ApplicablePeriod = @Month    GROUP BY tscl.EntityID    HAVING COUNT(*) > #         DECLARE @MultiCounts INT = (SELECT COUNT(*) FROM @MultiCountStores)      SELECT  'BELOW IS A LIST OF EACH STORES CURRENT STATUS.'    SELECT     @NoCounts as [NotStarted],     @CountsCreated as [Open],     @CountsApplied as [Applied],     @WebCounts as [WebCounts],     @MobileCounts as [MobileCounts],     @WebCounts + @MobileCounts AS [TotalCounts],     @TotalStores as [TotalStores],     @MultiCounts AS [StoresWithMultiCounts]      select e.Entity as Restaurant,      z.Zone  as [OrderingType],      sc.CreateDate,      sc.ApplyDate,      sc.ClerkName,      CASE WHEN Mobile = 1 THEN 'Mobile' ELSE 'Web' END AS [Source],      ISNULL(sc.CountName,$) as [CountName],      ISNULL(sc.CountStatus,$)  as [Status],      sc.ApplicablePeriod,      ISNULL(mcs.counts, #) AS [NumOfCounts],      sc.StartTime,      sc.EndTime    from [chick-fil-a].dbo.tbEntity e with (nolock)    join [chick-fil-a].dbo.tbEntityZone ez with (nolock)     on ez.EntityID = e.EntityID    join [chick-fil-a].dbo.tbZone z with (nolock)     on z.ZoneID = ez.ZoneID    left join @STockCounts sc     on sc.EntityID = e.EntityID    left JOIN @MultiCountStores mcs     on mcs.EntityID = e.EntityID    where  e.Status = $ and  e.OpenDate <= GETDATE() and     z.Zone like $     and e.EntityID IN (SELECT EntityID from @SCStores)    order by ISNULL(sc.CountStatus,$), e.Entity

declare @p1 int  set @p1=#  exec sp_prepexec @p1 output,N'@p0 bigint,@p1 datetime,@p2 datetime','SELECT this_.OnAccountSalePaymentId as OnAccoun1_153_2_, this_.AppliedAmount as AppliedA2_153_2_, this_.CheckNumber as CheckNum3_153_2_, this_.CreditAppliedFlag as CreditAp4_153_2_, this_.IsDeposit as IsDeposit153_2_, this_.CreatedTimestamp as CreatedT6_153_2_, this_.OnAccountSaleId as OnAccoun7_153_2_, this_.TransactionControlInfoID as Transact8_153_2_, oasalias1_.OnAccountSaleId as OnAccoun1_152_0_, oasalias1_.Description as Descript2_152_0_, oasalias1_.EntityId as EntityId152_0_, oasalias1_.InvoiceNumber as InvoiceN4_152_0_, oasalias1_.UncollectedFlag as Uncollec5_152_0_, oasalias1_.Paid as Paid152_0_, oasalias1_.Type as Type152_0_, oasalias1_.UncollectibleTimestamp as Uncollec8_152_0_, oasalias1_.PaidInFullTimestamp as PaidInFu9_152_0_, oasalias1_.ReversalFlag as Reversa10_152_0_, oasalias1_.TransactionSummaryID as Transac11_152_0_, oasalias1_.CustomerId as CustomerId152_0_, ( SELECT SUM(tbOnaccountSalePayment.AppliedAmount) FROM tbOnaccountSalePayment WHERE tbOnaccountSalePayment.OnAccountSaleId = oasalias1_.OnAccountSaleId ) as formula0_0_, tcialias2_.TransactionControlInfoID as Transact1_155_1_, tcialias2_.ClerkName as ClerkName155_1_, tcialias2_.EntityID as EntityID155_1_, tcialias2_.POSTransactionID as POSTrans4_155_1_, tcialias2_.TransactionDate as Transact5_155_1_, tcialias2_.ControlInfoCount as ControlI6_155_1_, tcialias2_.ControlInfoAmount as ControlI7_155_1_, tcialias2_.OtherInfo2 as OtherInfo8_155_1_, tcialias2_.OtherInfo3 as OtherInfo9_155_1_, tcialias2_.CurrentShiftNumber as Current10_155_1_, tcialias2_.BusinessDay as Busines11_155_1_, tcialias2_.ClerkID as ClerkID155_1_, tcialias2_.ControlInfoID as Control13_155_1_, tcialias2_.SystemTransactionID as SystemT14_155_1_ FROM tbOnAccountSalePayment this_ inner join tbOnAccountSale oasalias1_ on this_.OnAccountSaleId=oasalias1_.OnAccountSaleId inner join tbTransactionControlInfo tcialias2_ on this_.TransactionControlInfoID=tcialias2_.TransactionControlInfoID WHERE ((tcialias2_.EntityID = @p0 and tcialias2_.BusinessDay >= @p1) and tcialias2_.BusinessDay <= @p2)',@p0=#,@p1=$,@p2=$  select @p1

exec sp_executesql 'SELECT this_.SalesItemMetricDetailId as SalesIte1_121_0_, this_.SalesItemId as SalesIte2_121_0_, this_.IntervalStart as Interval3_121_0_, this_.LastYearTransactionCount as LastYear4_121_0_, this_.ManagerTransactionCount as ManagerT5_121_0_, this_.RawTransactionCount as RawTrans6_121_0_, this_.SystemTransactionCount as SystemTr7_121_0_, this_.ServiceType as ServiceT8_121_0_, this_.MgrDetailsChanged as MgrDetai9_121_0_, this_.ForecastId as ForecastId121_0_ FROM Forecasting.SalesItemMetricDetail this_ WHERE ((this_.ForecastId = @p0 and this_.IntervalStart >= @p1) and this_.IntervalStart < @p2)',N'@p0 bigint,@p1 datetime,@p2 datetime',@p0=#,@p1=$,@p2=$

exec sp_executesql 'EXEC [dbo].[MXC_Financial_GetCustomerSalesNotLinkedToCustomer] @p0, @p1, @p2',N'@p0 bigint,@p1 datetime2(7),@p2 datetime2(7)',@p0=#,@p1=$,@p2=$

DECLARE @MismatchedSettlements TABLE (EntityID BIGINT, BusinessDay DATETIME, ClerkID BIGINT, ClerkName VARCHAR(255), ShiftID BIGINT, RegisterNumber INT, TransactionCashDepositID BIGINT, Comment VARCHAR(255))  DECLARE @LiteSum TABLE (SettlementID BIGINT, Gross Money, Net Money, Tax MONEY, Discount MONEY, GiftCard MONEY, ServiceFee MONEY)  DECLARE @TLD TABLE (SettlementID BIGINT, Gross Money, Net MONEY, Tax MONEY, GiftCard MONEY, Discount MONEY) DECLARE @TLDI TABLE (SettlementID BIGINT, Gross MONEY, Net MONEY, Tax MONEY, GiftCard MONEY, Discount MONEY)    INSERT INTO @MismatchedSettlements  select EntityID, BusinessDay, ClerkID, ClerkName, ShiftID, RegisterNumber, RefID, Comment  from tbValidation with (nolock)  WHERE ValidationType = #  and  Status = #  and  BusinessDay >= CONVERT(VARCHAR(10),GETDATE()-#, #) and BusinessDay <= CONVERT(VARCHAR(10), GETDATE(), #)    INSERT INTO @LiteSum   select m.TransactionCashDepositID,    ROUND(SUM(CASE WHEN ci.ControlInfo = $ THEN tci.ControlInfoAmount ELSE # END)     - SUM(CASE WHEN ci.ControlInfo in ($) THEN tci.ControlInfoAmount ELSE # END)     - SUM(CASE WHEN ci.ControlINFO in ($) THEN tci.ControlInfoAmount ELSE # END), #) as [LiteSum Gross],    ROUND(SUM(CASE WHEN ci.ControlInfo = $ THEN tci.ControlInfoAMount ELSE # END), #) AS [LiteSum Net],    ROUND(SUM(CASE WHEN ci.ControlInfo = $ THEN tci.ControlInfoAmount ELSE # END), #) AS [LiteSum Tax],    ROUND(SUM(CASE WHEN ControlInfo = $ THEN ControlInfoAmount ELSE # END), #) AS [LiteSum Discount],    ROUND(SUM(CASE WHEN ControlInfo in ($) THEN ControlInfoAmount ELSE # END), #) AS [LiteSum GiftCard],    ROUND(SUM(CASE WHEN ControlInfo = $ THEN tci.ControlInfoAmount ELSE # END), #) as [Service Fee]      from @MismatchedSettlements m  join tbTransactionSummary ts with (nolock)  on ts.ClerkID = m.ClerkID  and ts.BusinessDay = m.BusinessDay  and ts.EntityID = m.EntityID  and ts.CurrentShiftNumber = m.ShiftID  join tbTransactionControlInfo tci with (nolock)   on tci.SystemTransactionID = ts.TransactionSummaryID  join tbControlInfo ci with (nolock)  on ci.ControLInfoID = tci.ControLInfoID     Where ci.ControlInfoSystemCode = $    GROUP BY m.EntityID, m.BusinessDay, m.TransactionCashDepositID    INSERT INTO @TLD  SELECT m.TransactionCashDepositID,    ROUND(SUM(tld.SALES_INCL_GST_INCL), #),    ROUND(SUM(tld.Sales_Excl_GST_EXCL), #),    ROUND(SUM(tld.GSTAmount), #),    0,    0    FROM @MismatchedSettlements m  join tbTransactionCashDeposit c with (NOLOCK)   on c.TransactionCashDepositID = m.TransactionCashDepositID  join tbTransactionSummary tld with (nolock)   on tld.EntityID = m.EntityID   and tld.BusinessDay = m.BusinessDay   and tld.ClerkIDName = m.ClerkName   and tld.TransactionDate >= c.OpenDate   and tld.TransactionDate <= c.CloseDate    GROUP BY m.TransactionCashDepositID    Update @TLD  SET GiftCard = ROUND(ctrl.GiftCard, #),  Discount = ROUND(ctrl.Discount, #)  FROM @TLD t  Join (    SELECT m.TransactionCashDepositID as [SEttlementID],     SUM(CASE WHEN ci.ControlInfo in ($) THEN tci.ControlInfoAmount ELSE # END) as [GiftCard],       SUM(CASE WHEN ControlInfo in ($) THEN ControlInfoAmount ELSE # END)  as [Discount]    FROM @MismatchedSettlements m    join tbTransactionCashDeposit c with (nolock)    on c.TransactionCashDepositID = m.TransactionCashDepositID    join tbTransactionControlInfo tci with (nolock)    on tci.TransactionDate >= c.OpenDate    and tci.TransactionDate <= c.CloseDate    and tci.EntityID = m.EntityID    and tci.ClerkName = m.ClerkName    join tbControlInfo ci with (nolock)    on ci.ControlInfoID = tci.ControlInfoID        Where tci.BusinessDay >= CONVERT(VARCHAR(10), GETDATE(), #) and tci.BusinessDay <= CONVERT(VARCHAR(10), GETDATE(), #)    group by m.TransactionCashDepositID   ) ctrl  on ctrl.SEttlementID = t.SettlementID    INSERT INTO @TLDI  SELECT m.TransactionCashDepositID,    ROUND(SUM(tld.Sales_Incl_GST_Incl), #),    ROUND(SUM(tld.Sales_Excl_GST_Excl), #),    ROUND(SUM(tld.GSTAmount), #),    0,    0    FROM @MismatchedSettlements m  JOIN tbTransactionSummary tld with (nolock)   on tld.EntityID = m.EntityID   and tld.ClerkID = m.ClerkID   and tld.BusinessDay = m.BusinessDay   and tld.CurrentShiftNumber = m.ShiftID    GROUP BY m.TransactionCashDepositID    Update @TLDI  SET GiftCard = ROUND(ctrl.GiftCard, #),  Discount = ROUND(ctrl.Discount, #)  FROM @TLDI t  Join (    SELECT m.TransactionCashDepositID as [SEttlementID],     SUM(CASE WHEN ci.ControlInfo in ($) THEN tci.ControlInfoAmount ELSE # END) as [GiftCard],       SUM(CASE WHEN ControlInfo in ($) THEN ControlInfoAmount ELSE # END)  as [Discount]    FROM @MismatchedSettlements m    join tbTransactionCashDeposit c with (nolock)    on c.TransactionCashDepositID = m.TransactionCashDepositID    join tbTransactionSummary ts with (nolock)    on ts.EntityID = m.EntityID    and ts.BusinessDay = m.BusinessDay    and ts.ClerkID = m.ClerkID    and ts.CurrentShiftNumber = m.ShiftID    join tbTransactionControlInfo tci with (nolock)    on tci.SystemTransactionID = ts.TransactionSummaryID    join tbControlInfo ci with (nolock)    on ci.ControlInfoID = tci.ControlInfoID        Where tci.BusinessDay >= CONVERT(VARCHAR(10), GETDATE(), #)    and tci.BusinessDay <= CONVERT(VARCHAR(10), GETDATE(), #)    group by m.TransactionCashDepositID       ) ctrl  on ctrl.SEttlementID = t.SettlementID      select e.Entity as [Restaurant],    CONVERT(VARCHAR(10), m.BusinessDay, 101) as [Business Day],    m.ClerkID,    m.ClerkName,    m.ShiftID,    m.RegisterNumber,    c.OpenDate  as [Cashier Open],    c.CloseDate  as [Cashier Close],    CASE     WHEN t.Net = ls.Net and t.Tax < ls.Tax THEN 'PIF In LiteSum But Not In TLD'     WHEN DATEDIFF(SS,c.OpenDate, c.CloseDate) < 2 THEN 'Fault Tolerant Shift Open Equals Shift Close'     WHEN t.Gross <> ls.Gross AND e.StoreNumber like '7%' THEN 'Server vs. Cashier at Dwarf House'     WHEN t.Gross <> i.Gross THEN 'Name Change'     WHEN t.Gross = ls.Gross THEN 'TLD Transactions With Larger Transaction ID Processed After Settlement'     WHEN (t.Gross is not null and ((ls.Gross - i.Gross) = ls.ServiceFee)) THEN 'PIF Not Refunded in LiteSum'     Else 'Unknown'    END AS [Likely Cause],    m.Comment,    ls.Gross  as [LiteSum Gross],    t.Gross   as [TLD Gross],    i.Gross   AS [TLD Gross by ID],    ls.Net   as [LiteSum Net],    t.Net   AS [TLD Net],    i.Net   AS [TLD Net by ID],    ls.Tax   AS [LiteSum Tax],    t.Tax   AS [TLD Tax],    i.Tax   AS [TLD Tax by ID],    ls.Discount  as [LiteSum Discount],    t.Discount  as [TLD Discount],    i.Discount  as [TLD Discount by ID],    ls.GiftCard  as [LiteSum Giftcard],    t.GiftCard  as [TLD Giftcard],    i.GiftCard  as [TLD Giftcard by ID]         from @MismatchedSettlements m  join tbEntity e with (nolock)   on e.EntityID = m.EntityID  join tbTransactionCashDeposit c with (NOLOCK)   on c.TransactionCashDepositID = m.TransactionCashDepositID   or (     m.TransactionCashDepositID = #     and c.ClerkName = m.ClerkName      and c.CashDepositDate = m.BusinessDay      and c.EntityID = m.EntityID     and c.ShiftNumber = m.ShiftID    )  left join @LiteSum ls   on ls.SettlementID = m.TransactionCashDepositID  left join @TLD t   on t.SettlementID = m.TransactionCashDepositID  join @TLDI i   on i.SettlementID = m.TransactionCashDepositID  order by e.Entity, m.BusinessDay