﻿
CREATE PROCEDURE [dbo].[USP_CRT_GetData]
	@pair  varchar(50),
	@start DATETIME,
	@end DATETIME, 
	@resolution varchar(10)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*
---- Example :
Exec [USP_CRT_GetData] 'EURUSD','2015-06-01', '2015-06-05','1'
*/


IF @resolution = '5' 
Begin
		SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_5Minutes]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]


	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '5Minutes'


    
End
ELSE IF @resolution = '15' 
Begin
		SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_15Minutes]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]

		SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '15Minutes'



End
ELSE IF @resolution = '30' 
Begin

	SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_30Minutes]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]


	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '30Minutes'



End
ELSE IF @resolution = '60' 
Begin

	SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_1Hour]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]


	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '1Hour'




End
ELSE IF @resolution = '120' 
Begin
		SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_2Hours]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]


	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '2Hours'


End
ELSE IF @resolution = '240' 
Begin

	SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_4Hours]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]


	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '4Hours'


End
ELSE IF @resolution = '480' 
Begin
		SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_8Hours]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]


	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '8Hours'


End
ELSE IF @resolution = 'D' 
Begin

	SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_1Day]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]

	
	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '1Day'



End
ELSE IF @resolution = 'W' 
Begin
		SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_1Week]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]


	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '1Week'


End
ELSE IF @resolution = 'M' 
Begin

		SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_1Month]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period]

	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '1Month'


End
ELSE
Begin
		---- 1 Minute
	SELECT 
		 [CRTD_Period] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'


	FROM [CRT_Data_1Minute]
	WHERE [CRTD_Period] >= @start
		AND [CRTD_Period] <= @end
		AND [CRTD_PairSymbol] = @pair
   ORDER BY [CRTD_Period]


   	SELECT 
		 [CRTD_CreationDate] as 'Timestamp'
		,[CRTD_BidOpeningValue] as 'BidOpen'
		,[CRTD_BidClosingValue] as 'BidClose'
		,[CRTD_BidLowValue] as 'BidLow'
		,[CRTD_BidHighValue] as 'BidHigh'

		,[CRTD_MidOpeningValue] as 'MidOpen'
		,[CRTD_MidClosingValue] as 'MidClose'
		,[CRTD_MidLowValue] as 'MidLow'
		,[CRTD_MidHighValue] as 'MidHigh'

	FROM [dbo].[CRT_Data_LiveChart] 
	WHERE  [CRTD_PairSymbol] = @pair
	and [CRTD_TargetName] = '1Minute'



End

RETURN 0