USE [EForTDB]
GO
/****** Object:  StoredProcedure [FOREX].[sp_GetAccountEquity]    Script Date: 10/22/2019 3:58:48 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [FOREX].[sp_GetAccountEquity]
                        @loginId INT,
	                    @startDate datetime,
	                    @endDate datetime,
	                    @timeFrame INT
                    AS

                    BEGIN
                    SELECT 0 AS Id, aa.LoginId, cc.[Open], aa.High, aa.Low, bb.[Close], aa.Volume, aa.TimeSlot, GETDATE() AS CreatedAt, GETDATE() AS UpdatedAt, GETDATE() AS DeletedAt, CONVERT(BIT,0) AS IsDeleted
                    FROM (
                    SELECT b.LoginId, MIN(b.Low) as Low, MAX(b.High) as High, SUM(b.Volume) as Volume,
                    -- Add the rounded seconds back onto epoch to get rounded time
                    DATEADD(
                        MINUTE,
                        (DATEDIFF(MINUTE, '1990-01-01 00:00:00', b.TimeSlot) / @timeFrame) * @timeFrame,
                        '1990-01-01 00:00:00'
                    )      AS 'TimeSlot'
                    FROM FOREX.AccountEquity b
                    WHERE b.LoginId=@loginId AND (b.TimeSlot BETWEEN @startDate AND @endDate)
                    GROUP BY (DATEDIFF(MINUTE, '1990-01-01 00:00:00', b.TimeSlot) / @timeFrame), b.LoginId
                    ) aa

                    INNER JOIN (
	                    SELECT LoginId, [Close],
	                    DATEADD(
		                    MINUTE,
		                    (DATEDIFF(MINUTE, '1990-01-01 00:00:00', TimeSlot) / @timeFrame) * @timeFrame,
		                    '1990-01-01 00:00:00'
	                    )      AS 'TimeSlot'
	                    FROM FOREX.AccountEquity
	                    WHERE TimeSlot IN (
			                    SELECT MAX(TimeSlot)
			                    FROM FOREX.AccountEquity b
			                    GROUP BY (DATEDIFF(MINUTE, '1990-01-01 00:00:00', b.TimeSlot) / @timeFrame)
			                    )
                    )bb ON aa.TimeSlot = bb.TimeSlot and aa.LoginId = bb.LoginId

                    INNER JOIN (
	                    SELECT LoginId, [Open],
	                    DATEADD(
		                    MINUTE,
		                    (DATEDIFF(MINUTE, '1990-01-01 00:00:00', TimeSlot) / @timeFrame) * @timeFrame,
		                    '1990-01-01 00:00:00'
	                    )      AS 'TimeSlot'
	                    FROM FOREX.AccountEquity
	                    WHERE TimeSlot IN (
			                    SELECT MIN(TimeSlot)
			                    FROM FOREX.AccountEquity b
			                    GROUP BY (DATEDIFF(MINUTE, '1990-01-01 00:00:00', b.TimeSlot) / @timeFrame)
			                    )
                    ) cc ON aa.TimeSlot = cc.TimeSlot and aa.LoginId = cc.LoginId
                    END