﻿CREATE PROCEDURE [dbo].[USP_CRT_GetData_LastBars]
      @pair  varchar(50),
      @toDate DATETIME = Null, 
      @numOfBars int,
      @resolution varchar(10)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


IF @toDate Is Null
      SET @toDate = GetDate()


/*
---- Example :
Exec [USP_CRT_GetData_LastBars] 'EURUSD','2016-05-23 15:25', 25,'W'
*/




IF @resolution = '5' 
Begin

           SELECT Top (@numOfBars) 
                             [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'

      into #Charts_5
      FROM [CRT_Data_5Minutes]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period] Desc

      SELECT * FROM #Charts_5
    ORDER BY [Timestamp]

      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 Top (@numOfBars) 
                             [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'

           into #Charts_15
           FROM [CRT_Data_15Minutes]
           WHERE 1 = 1
                 AND [CRTD_Period] <= @toDate
                 AND [CRTD_PairSymbol] = @pair
           ORDER BY [CRTD_Period] Desc


           SELECT * FROM #Charts_15
           ORDER BY [Timestamp]

           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 Top (@numOfBars) 
            [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'

      into #Charts_30
      FROM [CRT_Data_30Minutes]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period] Desc


      SELECT * FROM #Charts_30
    ORDER BY [Timestamp]


      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 Top (@numOfBars) 
            [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'

      into #Charts_60
      FROM [CRT_Data_1Hour]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period] Desc

      SELECT * FROM #Charts_60
      ORDER BY [Timestamp]


      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 Top (@numOfBars) 
            [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'

      into #Charts_120
      FROM [CRT_Data_2Hours]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period] Desc

      SELECT * FROM #Charts_120
    ORDER BY [Timestamp]


      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 Top (@numOfBars) 
            [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'

      into #Charts_240
      FROM [CRT_Data_4Hours]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period] Desc


      SELECT * FROM #Charts_240
      ORDER BY [Timestamp]


      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 Top (@numOfBars) 
            [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'

      into #Charts_480
      FROM [CRT_Data_8Hours]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period] Desc

      SELECT * FROM #Charts_480
    ORDER BY [Timestamp]


      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 Top (@numOfBars) 
            [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'

      into #Charts_D
      FROM [CRT_Data_1Day]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period] Desc


      SELECT * FROM #Charts_D
    ORDER BY [Timestamp]

      
      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 Top (@numOfBars) 
            [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'

      into #Charts_W
      FROM [CRT_Data_1Week]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period] Desc

      SELECT * FROM #Charts_W
      ORDER BY [Timestamp]


      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 Top (@numOfBars) 
            [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'

      into #Charts_M
      FROM [CRT_Data_1Month]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
    ORDER BY [CRTD_Period] Desc


      SELECT * FROM #Charts_M
    ORDER BY [Timestamp]

      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 Top (@numOfBars) 
            [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'

      into #Charts
      FROM [CRT_Data_1Minute]
      WHERE 1 = 1
           AND [CRTD_Period] <= @toDate
           AND [CRTD_PairSymbol] = @pair
   ORDER BY [CRTD_Period] Desc

   SELECT * FROM #Charts
   ORDER BY [Timestamp]


     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