Difficulties in collecting bills

Problem

The values of the date fields in several tables are both date and time. For example, at on June 18, April 22, 2013, the contents of these tables on each day should be summarized into a record and inserted into the new table.

Solution

1. Create a temporary table for each required table and summarize the content of a single table within one day;

2. Insert the summary into the new table.

This method is implemented through the stored procedure. The Code is as follows:

If (object_id ('proc _ Sum', 'P') is not null)
Drop proc proc_sum
Go

Create proc proc_sum
(
-- Operator's logon Record Number
@ Operator_login_id int
)
As
-- Today's date
Declare @ datenow as date
-- Operator name
Declare @ operator_name as varchar (20)
-- Number of people on the machine today
Declare @ loginno as integer
-- Number of registrants today
Declare @ registerno as integer
-- Number of people who return cards today
Declare @ returnno as integer
-- Daily recharge count
Declare @ rechargeno as integer
-- Refund amount today
Declare @ returnmoney as real
-- Today's consumption amount
Declare @ paymoney as real
-- Daily Recharge Amount
Declare @ rechargemoney as real
-- Amount due today
Declare @ lookmoney as real
-- Real profit today
Declare @ makemoney as real

Begin Tran T1
-- Number of top-up users
If object_id ('tempdb. DBO. # rechargemoney1 ') is not null
Drop table # rechargemoney1 -- delete a temporary table
Select card_recharge_money, convert (varchar (10), card_recharge_time, 120) as date into # rechargemoney1 from card_recharge_record
If object_id ('tempdb. DBO. # rechargemoney2 ') is not null
Drop table # rechargemoney2 -- delete a temporary table
Select sum (card_recharge_money) as rechargemoney, count (*) as rechargeno, date into # rechargemoney2 from # rechargemoney1 group by date

-- Number of people who return the amount of cards
If object_id ('tempdb. DBO. # returnmoney1') is not null
Drop table # returnmoney1 -- delete a temporary table
Select card_return_money, convert (varchar (10), card_return_time, 120) as date into # returnmoney1 from card_return_record
If object_id ('tempdb. DBO. # returnmoney2') is not null
Drop table # returnmoney2 -- delete a temporary table
Select sum (card_return_money) as returnmoney, count (*) as returnno, date into # returnmoney2 from # returnmoney1 group by date

-- Time of the number of people who spent the amount on the machine
If object_id ('tempdb. DBO. # paymoney1 ') is not null
Drop table # paymoney1 -- delete a temporary table
Select user_cost, convert (varchar (10), user_login_record.user_login_time, 120) as date into # paymoney1 from Bill, user_login_record where Bill. bill_id = user_login_record. bill_id
If object_id ('tempdb. DBO. # paymoney2 ') is not null
Drop table # paymoney2 -- delete a temporary table
Select sum (user_cost) as userpay, count (*) as userloginno, date into # paymoney2 from # paymoney1 group by date

-- Total number of registrants
If object_id ('tempdb. DBO. # userregister1 ') is not null
Drop table # userregister1 -- delete a temporary table
Select user_register_id, convert (varchar (10), user_register_time, 120) as date into # userregister1 from user_register_record
If object_id ('tempdb. DBO. # userreigster2 ') is not null
Drop table # userreigster2 -- delete a temporary table
Select count (*) as registerno, date into # userreigster2 from # userregister1 group by date

Set @ datenow = convert (varchar (10), getdate (), 120)

-- Determines whether there are today's checkout records in the daysum table
If exists (select * From daysum where daysum. daysum_date = @ datenow)
Begin
Delete from daysum where daysum. daysum_date = @ datenow
End

-- Insert date and Operator name
Select @ operator_name = operator_info.operator_name from operator_info, operator_login_record where region = region and operator_login_record. operator_login_id = @ operator_login_id
Insert into daysum (daysum_date, operator_name) values (@ datenow, @ operator_name)

-- Insert Recharge Amount and recharge count
Select @ rechargemoney = rechargemoney, @ rechargeno = rechargeno from # rechargemoney2 where date = @ datenow
Update daysum set daysum_rechargemoney = @ rechargemoney, daysum_rechargeno = @ rechargeno where daysum_date = @ datenow

-- Insert the refund amount and number of people
Select @ returnmoney = returnmoney, @ returnno = returnno from # returnmoney2 where date = @ datenow
Update daysum set daysum_returnmoney = @ returnmoney, daysum_returnno = @ returnno where daysum_date = @ datenow

-- Insert the consumption amount and number of users on the machine
Select @ paymoney = userpay, @ loginno = userloginno from # paymoney2 where date = @ datenow
Update daysum set daysum_paymoney = @ paymoney, daysum_loginno = @ loginno where daysum_date = @ datenow

-- Insert the total number of registered users today
Select @ registerno = registerno from # userreigster2 where date = @ datenow
Update daysum set daysum_registerno = @ registerno where daysum_date = @ datenow

-- Insert the total money of today
Set @ lookmoney = @ rechargemoney-@ returnmoney
Update daysum set daysum_lookmoney = @ lookmoney where daysum_date = @ datenow

-- Insert the profit Field
Set @ makemoney = @ paymoney
Update daysum set daysum_makemoney = @ makemoney where daysum_date = @ datenow

If @ error <> 0
Rollback Tran T1 -- execution error
Else
Commit Tran T1 -- execution failed
Go