Segment billing statistics of MS SQL

Business Background: for example, data center management. You can set a special period of time for each day. You can set a discount between and, and a discount between and, off from PM to PM,

Since my business is not cross-day, it must be clear. There is no problem with cross-day discounts. I may not understand it. I should understand it after reading the example.

 

Discount table: discount

Create Table [DBO]. [discount] (
[ID] [int] Null,
[Stime] [datetime] Null, -- discount Start Time
[Etime] [datetime] Null, -- discount End Time
[Discount] [numeric] (18, 2) null -- discount
)

 

Consumption table: bytes

Create Table [DBO]. [partition] (
[ID] [int] Null,
[Userid] [nchar] (10) Collate chinese_prc_ci_as null, -- Other Business Associations
[Begintime] [datetime] Null, -- consumption Start Time
[Endtime] [datetime] Null, -- end time of consumption
[Price] [numeric] (18, 2) null -- actual consumption time
)

 

Insert data as follows:

Select * from discount

Id stime etime discount
------------------------------------------------------------------------------------------------
1 2010-12-22 09:00:00. 000 2010-12-22 11:00:00. 000 0.80
2 12:00:00. 000 14:00:00. 000 0.60
4 18:00:00. 000 22:00:00. 000 1.00
3 2010-12-22 22:00:00. 000 2010-12-23 07:00:00. 000 0.50

To view the effect, I set four discount segments in one day.

 

Select * From nation

Id userid begintime endtime price
----------------------------------------------------------------------------------------------------------
1 10003 06:30:00. 000 23:58:00. 000 null

 

In the consumption table, I only check one record and give time during the test. (If I'm too lazy, it's faster to input the userid. If I'm lazy, it takes more time to test, because my business is more complex than this, this is just a basic algorithm, admit that I am lazy, continue), my ultimate goal is to calculate the consumption amount, the formula is as follows:

Consumption amount = consumption time/unit time * unit price * discount

(The consumption time is measured in minutes, and the unit time is also minute. The unit price is the amount of money per unit time. For example, if the user sets 15 minutes to 1 yuan, the unit time is 15, unit Price: 1 RMB)

 

I used to convert the consumption time into a discount and then calculate the consumption amount. The formula is changed as follows:

Consumption amount = consumption time * discount/unit time * unit price

 

There are some differences between the two algorithms, depending on the amount of time, because there is a conversion in the middle, this conversion can be ignored in my business, because the unit time is not very large, the data center is usually used as a settlement unit within 15 minutes. I tested it and it has little impact.

 

The stored procedure is as follows:

Create procedure [DBO]. [sp_geture]
@ Begintime datetime,
@ Endtime datetime,
@ Totalw.float
As
Declare
@ Tempetime datetime,
@ Tempstime datetime,
@ Outstart1_float,
@ Outend1_float,
@ Discountime float
Begin
-- Set @ begintime = getdate ()-0.3
-- Set @ endtime = getdate () + 0.1
Set @ total.pdf = 0;
 
Print @ begintime
Print @ endtime

If @ begintime = @ endtime
Begin
Set @ total.pdf = 0;
End
-- Determine whether the start time is within the Discount Period
Select @ outstartiterator = floor (datediff (MI, @ begintime, etime) * D. Discount), @ tempetime = D. etime
From discount d
Where sign (datediff (SS, D. stime, @ begintime ))! = Sign (datediff (SS, D. etime, @ begintime ))
Order by D. id;

 
-- If the preceding value is null, it indicates that the start time is not within any period of time and the consumption duration is normal.
If @ outstartprohibited is not null
Begin
Print 'start time :'
Set @ totalcounter = @ totalcounter + isnull (@ outstartcounter, 0 );
Print 'number of minutes after conversion: '+ rtrim (cast (@ totalw.as nvarchar (30 )));
End
Else
Set @ tempetime = @ begintime;
Print 'Total number of minutes after conversion: '+ rtrim (cast (@ totalw.as nvarchar (30) +' modify the start time: '+ rtrim (cast (@ tempetime as nvarchar (30 )));

 

-- Determine whether the end time is within the Discount Period
Select @ outenditerator = ceiling (datediff (MI, D. stime, @ endtime) * D. Discount), @ tempstime = D. stime
From discount d
Where sign (datediff (SS, D. stime, @ endtime ))! = Sign (datediff (SS, D. etime, @ endtime ))
Order by D. id;

-- If the preceding value is null, it indicates that the end time is not within any period of time and the consumption duration is normal.
If @ outendpipeline is not null or @ outendpipeline = 0
Begin
Print 'end time is in the discount range :'
Set @ totalfee = @ totalfee + isnull (@ outendfee, 0 );
Print 'number of minutes after conversion: '+ rtrim (cast (@ outendframes as nvarchar (30 )));
End
Else
Set @ tempstime = @ endtime;
Print 'Total number of minutes after conversion: '+ rtrim (cast (@ totalw.as nvarchar (30) +' modify the end time: '+ rtrim (cast (@ tempstime as nvarchar (30 )));

 

-- Calculate all the time in the time range, and total is the consumption time after the conversion in the discount range.
If @ tempetime >=@ tempstime
Begin
-- Print @ tempstime
-- Print @ tempetime
Set @ totaliter = ABS (ceiling (datediff (MI, @ begintime, @ endtime )))
Select @ totalcounter = @ totalcounter * D. Discount
From discount d
Where D. stime >=@ tempstime
And D. etime <= @ tempetime;
Print @ total.pdf;
End
Else
Begin
Print @ tempstime
Print @ tempetime
Set @ totalfee = ceiling (datediff (MI, @ tempetime, @ tempstime) + @ totalfee;
-- Calculates whether the consumption start time includes the discount time of the promotion period.
Select @ discountime = isnull (ceiling (sum (ceiling (datediff (MI, D. stime, D. etime)-ceiling (datediff (MI, D. stime, D. etime) * D. discount), 0)
From discount d
Where D. stime> @ tempetime
And D. etime <@ tempstime;
Print @ discountime;
-- Get the total consumption time beyond the last Discount Period
Set @ totalcounter = @ totalcounter-@ discountime;
Print @ total.pdf;
End

End

 

First look at the illustration:

1: If the consumption start time is within the discount period, set the start time to the discount End Time, and calculate the discount time in the discount period.			3: Get a new start time and end time based on 1 and 2. If other promotion segments are included, calculate the discount time of the promotion segments. The total time minus the discount time.			2: If the consumption end time is within the discount period, set the end time to the discount start time and calculate the discount time in the discount period.

 

The main algorithm is to cut off the time range that crosses the discount section and calculate the consumption time after the discount. Then you can calculate the remaining discount segments. The discount segments are part of the discount time less than the standard time, calculate the discount Time, And then subtract the total time.

 

Of course, I have also considered other cases. For example, if the consumption time does not include any time period, the consumption start time is in the discount period, and the consumption time period is within the Discount Period .. The stored procedures are included, and then the calculation takes a look at the test results:

-- Test segment timing
Declare
@ A datetime,
@ B datetime,
@ C float
Begin
Select @ A = begintime, @ B = endtime from hour;
Exec sp_getcounter @ A, @ B, @ C
End

 

12 22 2010 am -- input consumption Start Time
12 22 2010 PM -- input consumption end time
Total number of minutes after conversion: 0 change start time: 12 22 2010 AM
The end time is in the discount range:
Number of minutes after conversion: 59
Total number of minutes after conversion: 59 modification End Time: 12 22 2010 pm
12 22 2010 pm
12 22 2010 AM
72
917

 

 

The final result is 917 minutes, which is the converted time. This consumption time is just enough to calculate the consumption amount .. To continue my business, we have to make changes ,....