sql - Calculating time in a price list -

i have price list table in sql server 2008r2 , want calculate price service according time service has been made.

timefrom           |timeto             |price --------           |------             |----- 1900-01-01 00:00:00|1900-01-01 07:00:00|20.00 1900-01-01 07:00:00|1900-01-01 19:00:00|15.00 1900-01-01 19:00:00|1900-01-02 00:00:00|20.00 

this pricelist shows different price during night time starting @ 19:00 , lasting until 07.00 , daytime 07:00 19:00.

minutes have rounded , down quarter hours. there more take care of, such minimum notice periode (@vorlaufzeit) , if weekday of weekend. both conditions met , not problem. problem first , last record, have round and/or round down, incorrect. both lines have 1 in loop , should corrected correctly in both updates, not.

so, example service 2016-11-04 10:50 (rounding down 10:45 0.25 hours) 2016-11-04 19:25 (rounded 19:30 0.5 hours) 0.25+8+0.5= 8.75 hours , costs 8.25*15 + 0.5*20 = 133.75.

i tried code, not bring me correct result. first , last record have round or down. correct, when there full hours.

        declare @dauer int     declare @x int  --loopcounter für stunden     declare @y int  --loopcounter für tageszahler     declare @anfangszeit datetime     declare @anfangsstunde datetime     declare @endzeit datetime     declare @vorlaufzeit int  --in minuten     declare @erstezeitvon datetime     declare @summeanzstunden decimal(8,2)     declare @minimumzeit int     declare @zeitvonvollestunde int  -- aus 07:25 mach 7 uhr     declare @zeitbisvollestunde int       declare @anfangsdatumzeit datetime     declare @enddatumzeit datetime      declare @anfangsdatumzeitloop datetime     declare @anfangszeitloop datetime     declare @tageszaehler int      set @anfangsdatumzeit = @datumvon+@zeitvon     set @enddatumzeit = @datumbis+@zeitbis     set @tageszaehler=datediff(day,@anfangsdatumzeit, @enddatumzeit)      declare @t1 table ( preisid int, anzstunden decimal(5,2), preis decimal(8,2), anfangszeit datetime, prüfzeit datetime, startzeit datetime, endezeit datetime, vorlaufzeit int, dauer int, preisft decimal(8,2), datzeitvon datetime, datzeitbis datetime, tageszaehler int )      -- insert statements procedure here       set @zeitvonvollestunde=datediff(hour, '00:00:00', @zeitvon)      set @zeitbisvollestunde=datediff(minute, '00:00:00', @zeitbis)       set @dauer=ceiling(datediff(minute, @anfangsdatumzeit, @enddatumzeit)/60.00)    set @vorlaufzeit=datediff(minute,@bestelldatum, @anfangsdatumzeit)            set @x = 0  if datediff(minute, @anfangsdatumzeit, @enddatumzeit) > 360     begin             while (@x <=@dauer)  --z.b. 13              begin                          --set @y = datediff(day,@anfangsdatumzeit,@anfangsdatumzeitloop)                     set @y = datediff(day,@anfangsdatumzeit,dateadd(hour,@x, @anfangsdatumzeit))                      set @anfangsdatumzeitloop=dateadd(hour,@x, @anfangsdatumzeit)                      set @anfangszeitloop=dateadd(hour,@x, @zeitvon)                                insert @t1 ( preisid, anzstunden, preis , anfangszeit, prüfzeit, datzeitvon , datzeitbis  )                                select top 1 preisid, 1, preis, @anfangszeitloop, @anfangsdatumzeitloop, zeitvon,  zeitbis                                   dbo.mypricetable                                    sdlid=@leistungsart   --sdlid                                         , wochentag=case when datepart(dw,@anfangsdatumzeitloop) < 6 'w' else 's' end  --wochentag                                          , @vorlaufzeit between vorlaufzeitvon , vorlaufzeitbis  --vorlaufzeit in minuten                                         , @dauer*60 between dauervon , dauerbis   --dauerinminuten                                          , @anfangszeitloop between zeitvon ,  zeitbis --sucht die von/bis zeitgruppe                                  order zeitvon              set @x = @x + 1             end      --check , udate of first record in @t1 rounding down 15 minutes     update @t1 set anzstunden= anzstunden + convert(decimal(6, 2), round(((datediff(minute,[dbo].[sfroundtohourparts](@anfangsdatumzeit,1), [dbo].[sfroundtohourparts](@anfangsdatumzeit,4)) + 7) / 60.00) / 25, 2) * 25)     @t1 c c.prüfzeit=(select top 1 prüfzeit @t1 order prüfzeit)       --check , udate of last record in @t1 rounding 15 minutes     update @t1 set anzstunden= round(convert(decimal(5,2),datepart(minute,@enddatumzeit)+7)/60/25,2)*25     @t1 c c.prüfzeit=(select top 1 prüfzeit @t1 order prüfzeit desc)        end      select * @t1 order prüfzeit 

thanks help! michael

this code bit verbose, wanted share produces desired result of 133.75

declare @x table (    timefrom datetime  , timeto   datetime  , price    decimal(14,4) );  insert @x (timefrom, timeto, price)   values ('1900-01-01t00:00:00', '1900-01-01t07:00:00', 20.00)        , ('1900-01-01t07:00:00', '1900-01-01t19:00:00', 15.00)        , ('1900-01-01t19:00:00', '1900-01-02t00:00:00', 20.00) ;  -- should have own, physical tally table! declare @numbers table (    number tinyint ); insert @numbers (number) select distinct        number   master.dbo.spt_values  number between 0 , 255 ;  declare @start datetime = '2016-11-04t10:50:00'       , @end   datetime = '2016-11-04t19:25:00' ;  -- first, let's rounding of our inputs declare @rounded_start_time time       , @rounded_end_time   time ;   -- illustrate steps round time quarters... might not simplest method; works! /* select @start start      , dateadd(hh, datediff(hh, 0, @start), 0) truncate_hour      , round(datepart(mi, @start) / 15.0, 0) * 15 rounded_mins      , dateadd(mi, round(datepart(mi, @start) / 15.0, 0) * 15, dateadd(hh, datediff(hh, 0, @start), 0)) truncate_hour_then_add_mins ; */  set @rounded_start_time = dateadd(mi, round(datepart(mi, @start) / 15.0, 0) * 15, dateadd(hh, datediff(hh, 0, @start), 0)); set @rounded_end_time   = dateadd(mi, round(datepart(mi, @end  ) / 15.0, 0) * 15, dateadd(hh, datediff(hh, 0, @end  ), 0));  print 'start: ' + format(@rounded_start_time, 'hh:mm'); print 'end:   ' + format(@rounded_end_time  , 'hh:mm');  --select * --from   @x --;  ; intervals (   select number * 15 minute_increments        , dateadd(mi,  number *      15, 0) interval_start        , dateadd(mi, (number + 1) * 15, 0) interval_end     @numbers    number >= 0   ,    number <  24 * 4 --number of 15 minute increments in day ) , costed_intervals (   select intervals.interval_start        , intervals.interval_end        , cast(intervals.interval_start time) interval_start_time        , cast(intervals.interval_end   time) interval_end_time        , x.price / 4.0 interval_price     @x x    inner     join intervals       on intervals.interval_end   <= x.timeto      , intervals.interval_start >=  x.timefrom ) , applicable_intervals (   select interval_start        , interval_end        , interval_start_time        , interval_end_time        , interval_price     costed_intervals    interval_start_time < @rounded_end_time   ,    interval_end_time   > @rounded_start_time ) select sum(interval_price) total_price   applicable_intervals ; 

this use lot of cleaning , optimising. works when start , end times within same day, among other bugs , fun stuff.