get data for candle chart as per specific interval from sql server

Query for get data as per specific interval
Assume tablename=UserOrders,column=Price,column for date=CreatedDate, column for confirm order=Status, column for delete row status=IsActive:

Query:

For @interval you can pass any int value.(for ex. for 1 day interval you can pass 1440)

DECLARE @interval INT

SELECT DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,0,CreatedDate)/@interval)*@interval,0) [date],MAX(Price) high,Min(Price) as low,

       ISNULL((SELECT TOP 1 Price FROM UserOrders WHERE DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,0,CreatedDate)/@interval)*@interval,0)=DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,0,U.CreatedDate)/@interval)*@interval,0) AND Status=2 ORDER BY CreatedDate asc),0) [open],

       ISNULL((SELECT TOP 1 Price FROM UserOrders WHERE DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,0,CreatedDate)/@interval)*@interval,0)=DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,0,U.CreatedDate)/@interval)*@interval,0) AND Status=2 ORDER BY CreatedDate desc),0) [close]

       from UserOrders U where IsActive=1 AND Status=2 GROUP BY DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,0,U.CreatedDate)/@interval)*@interval,0)

If You Enjoyed This, Take 5 Seconds To Share It

• 
• 
• 
• 
• 
•