 --SELECT * FROM tbEntity WHERe Entity LIKE '%Denton%'
 --1041
 
 --GET the event profile and adjustments
 
 SELECT TOP 1000 [EventProfileId]
      ,[EntityId]
      ,[Name]
      ,[Source]
      ,[SalesItemsIntervals]
  FROM [JATesting].[Forecasting].[EventProfile]
  WHERe EntityID=1041
 --3228
 
SELECT TOP 1000 [EventProfileHistoryId]
      ,[IntervalStart]
      ,[TransactionAdjustment]
      ,[SalesAdjustment]
  FROM [JATesting].[Forecasting].[EventAutoAdjustment]
  WHERE [EventProfileHistoryId] IN (SELECT TOP 1000 [EventProfileHistoryId]
  FROM [JATesting].[Forecasting].[EventProfileHistory]
  WHERE EventProfileId=3228) ANd IntervalStart BETWEEN '2020-03-13 00:00:00' AND '2020-03-13 23:59:59'
 
 --Get the forecast ID
 SELECT TOP 100 * FROM Forecasting.Forecast WHERe ENtityID=1041 AND BUSINESSDAY IN 
 ( '2020-03-13')
 ORDER BY BusinessDay DESC


/* update script to make forecast look imported

UPDATE Forecasting.Forecast
SET ExternalGenerationDate = '2020-03-17 06:29:47.000', ForecastBAsis=1
WHERE ENtityID=1041 AND BUSINESSDAY IN 
 ( '2020-03-13')

*/

--get the Actual sales totals from forecast evaluator
--for interval nett sales and gross actual use the below
 SELECT
                 ts.BusinessDay AS BusinessDay,
                    DATEADD(mi, DATEDIFF(mi, 0, ts.TransactionDate) / 15 * 15, 0) as IntervalStart,
                    ts.InOutDel AS ServiceType,
                    SUM(ts.Sales_INCL_GST_INCL) AS Sales,
                    SUM(CASE WHEN r.TransactionSummaryID IS NULL THEN ts.Sales_EXCL_GST_INCL - ts.NonSales ELSE 0 END) AS GrossActual,
                    SUM(ts.TransactionCount) As Transactions
                FROM
                    tbTransactionSummary ts WITH(NOLOCK)
                    OUTER APPLY (SELECT TOP 1 tr.TransactionSummaryID
                        FROM tbTransactionRefund tr WITH(NOLOCK) 
                        WHERE tr.TransactionSummaryID = ts.TransactionSummaryID) r
                WHERE
                    ts.EntityID = 1041 AND
                    ts.BusinessDay = '2020-03-13'  
                    
                GROUP BY
                    ts.BusinessDay,
                    DATEADD(mi, DATEDIFF(mi, 0, ts.TransactionDate) / 15 * 15, 0),
                    ts.InOutDel
               ORDER BY IntervalStart
 --Example Data for 06:15 Sales = 22.42 GrossActual = 25.17                   
 
 --Get the rawsales forecast   
SELECT * FROM Forecasting.MetricDetail WHERe ForecastId=3632965
--the adjustment should be forecasting actualsales / rawsales



