CREATE PROCEDURE dbo.GenerateMISTotals @FromDate DateTime
AS
-- =============================================
-- Author:		SG
-- Create date: 2015-09-04
-- Description:	Creates totals of MIS per day. SystemDate is the Picasso date + the current system time (hour, minutes)
-- =============================================
-- 2016-03-11 SG: Time in systemDate was not correct
BEGIN
   Declare @SystemDateStr Char(14);
   Declare @SystemDate DateTime;
   Declare @DateStamp VarChar(8)
   Declare @ReferenceNumber Int 
   Declare @BookedPrice Money
   Declare @ActualPrice Money
   Declare @AlreadyRunToday int
   
   SELECT @SystemDateStr = SYSTEMDATE FROM System WITH (NOLOCK) WHERE NUMBER = 1
   SET @SystemDateStr = @SystemDateStr + ' 00:00'
   SET @SystemDate = Convert(DateTime, @SystemDateStr)

   SET @SystemDate = DATEADD(hour, DATEPART(hour, GETDATE()), @SystemDate) 
   SET @SystemDate = DATEADD(minute, DATEPART(minute, GETDATE()), @SystemDate)
   Print '@FromDate = ' + convert(char(23), @FromDate) + ' @SystemDate = ' + convert(char(23), @SystemDate, 126);
   
   Declare MISTotalsCur Cursor Fast_Forward Read_only For 
   SELECT Datestamp, ReferenceNumber, SUM(BookedPrice) as BookedPrice, SUM(ActualPrice) as ActualPrice
          from Mistable WITH (NOLOCK)
          where DateStamp >= @FromDate
          group by DateStamp, ReferenceNumber
   Open MISTotalsCur
   
   Fetch Next FROM MISTotalsCur INTO @DateStamp, @ReferenceNumber, @BookedPrice, @ActualPrice           
   WHILE(@@FETCH_STATUS <> -1) 
   Begin
     INSERT INTO MISTotals values(@SystemDate, @DateStamp, @ReferenceNumber, @BookedPrice, @ActualPrice)
     Fetch Next FROM MISTotalsCur INTO @DateStamp, @ReferenceNumber, @BookedPrice, @ActualPrice           
   End
   Close MISTotalsCur
   Deallocate MISTotalsCur  
END
