example function to categorize usage to different time of use pricing. Not complete because the data may cross over multiple categories. For example you might get usage from 12:31-1:01. 1 minute is at the peak rate and the other 29 minutes is on the shoulder rate.

shrug.sql

alter function Shrug (@d as datetime)
returns varchar(50)
begin
	declare @dw int , @hh int, @mi int
	Set @dw = (select datepart(dw,@d))
	set @hh = (select datepart(hh,@d))
	set @mi = (select datepart(mi,@d))
	-- week day & 13:00 up to and including 18:00 == peak
	-- offPeak any day before 8:00 and after 22
	-- shoulder anything else
	if (@dw in (2,3,4,5,6) and @hh >= 13 and (@hh < 18) or (@hh=18 and @mi=0))
		return 'Peak'
	else if (@hh <8 or @hh >=22 or (@hh=8 and @mi=0))
		return 'Off'
	return 'Other'
end
go

declare @v as table (d datetime, expected varchar(50))

insert into @v values ('9/29/2016  8:54 PM', 'Other' )
insert into @v values ('9/24/2016  1:11 PM', 'Other')
insert into @v values ('9/29/2016  1:11 PM', 'Peak')
insert into @v values ('9/24/2016  10:11 am', 'Other')
insert into @v values ('9/24/2016  10:33 Pm', 'Off')
insert into @v values ('9/27/2016  1:00 Pm', 'Peak')
insert into @v values ('9/27/2016  6:00 Pm', 'Peak')
insert into @v values ('9/27/2016  6:01 Pm', 'Other')
insert into @v values ('9/27/2016  8:00 am', 'Off')



select *, dbo.shrug(d) as computed from @v

--rollback tran

select top 1 * from xiEdi.vw_intervalUsage_KS where franchiseUtility like 'Mid%'

; with mycte as(
SELECT *, COALESCE(
              LAG(IntervalEnd) OVER (PARTITION BY MeterAlphanumeric ORDER BY IntervalEnd)
              ,DATEADD(HOUR,24, CAST( PeriodStart AS DATETIME) )           )
              AS IntervalStart, dbo.shrug(intervalEnd) as Categorization
               FROM xiEdi.vw_IntervalUsage_KS WHERE AccountAlphanumeric='7641068031'
			   and periodstart >= '8/1/2016' --and unitofMeasure='KWH'
)
select meterAlphanumeric, Categorization, sum(usageAmount)/2 sumKwh, min(periodStart) as startTime, max(periodEnd) as endtime from mycte where unitofMeasure='kwh' group by meterAlphanumeric,Categorization
--select top 50 * from mycte where unitOfMeasure='kwh'