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

5 comments:

  1. Hi, What if I want to exclude Holidays based on DateDim table. Not just Weekends also other holidays defined in the Dim table.

    ReplyDelete
  2. hi sir i need help regarding query please share email id sir

    ReplyDelete
  3. hi sir i need help regarding query please share email id sir my email id is sreekanth.you@gmail.com

    ReplyDelete
  4. Hello, I am trying to sort two tags based on :30 and 1 minute intervals I tried a mix of what you have but it did not work. Would you be able to assist me or provide further documentation?

    ReplyDelete