Wednesday, 10 August 2016

MSSQL - Query that loads data by 15 minutes intervals


Query to group by time intervals - group by 15 minutes time intervals


Watch this example on YouTube
select count(id) as NumberOfOrders,
DatePart(Year, MyDate) as TransactionYear,
DatePart(MONTH, MyDate) as TransactionMonth,
DatePart(DAY, MyDate) As TransactionDate,
DatePart(Hour, MyDate) As TransactionHour,
DatePart(Minute, MyDate)/15 As TransactionInterval,
Convert(Varchar, DatePart(Hour, MyDate)) + Case DatePart(Minute, MyDate) /15 When 0 Then ':00' When 1 Then ':15'
When 2 Then ':30' When 3 Then ':45' End as TransInterval
From IntervalTest
Group By
DatePart(Year, MyDate) ,
DatePart(MONTH, MyDate),
DatePart(DAY, MyDate),
DatePart(Hour, MyDate) ,
DatePart(Minute, MyDate)/15

No comments:

Post a Comment