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.
Comments
Post a Comment