Account every day, take out, the balance has been change, cycle for 24 hours, take every last one balance, to calculate the month or year, on average, how to do?
Cusid date time balance
001 20190801 10:05:00 10000
001 20190801 10:06:01 5000
001 20190801 15:15:00 250000
001 20190801 23:05:00 5000
001 20190802 09:10:00 100000
001 20190802 10:06:01 5000
001 20190802 15:15:00 250000
001 20190802 15:20:00 0
001 20190803 08:32:00 50000
.

CodePudding user response:

The test data

 -If not object_id (N 'Tempdb for.. # T ') is null 
Drop table # T 
Go 
Create table # T ([cusid] nvarchar (23), date [date], [time] time, [this] int) 
Insert # T 
Select N '001', '20190801', '10:05:00, 10000 union all 
Select N '001', '20190801', '10:06:01, 5000 union all 
Select N '001', '20190801', '15:15:00, 250000 union all 
Select N '001', '20190801', '23:05:00, 5000 union all 
Select N '001', '20190802', '09:10:00, 100000 union all 
Select N '001', '20190802', '10:06:01, 5000 union all 
Select N '001', '20190802', '15:15:00, 250000 union all 
Select N '001', '20190802', '15:20:00' 0 union all 
Select N '001', '20190803', '08:32:00' 50000 
Go 
- the end of the test data 
; WITH cte AS (
Select *, ROW_NUMBER () OVER (PARTITION BY the date the ORDER BY the time DESC) rn from # T 
) 
SELECT the MONTH (cte) date) AS the date, AVG (cte) balance) AS the balance FROM cte WHERE rn=1 GROUP BY the MONTH (cte) date) 

CodePudding user response:

I can not describe clearly, I want every day finally a balance, the average of the data, should be 8/1 three days finally a 5000 + 8/2 finally a 0 + 50000=55000/8/3 balance 3 on average

CodePudding user response:

refer to the second floor qq_43037081 response:

I could not describe clearly, I want every day finally a balance, the average of the data, should be 8/1 three days finally a 5000 + 8/2 finally a 0 + 50000=55000/8/3 balance on average 3 o

1 # wukong is what you need, you really look, its result is in, the date field in August, that is your mean value of 18333 is 55000/3

CodePudding user response:

Thank you oh, novice small white give you any trouble

CodePudding user response:

- test data
If not object_id (N 'Tempdb for.. # T ') is null
Drop table # T
Go
Create table # T ([cusid] nvarchar (23), date [date], [time] time, [this] int)
Insert # T
Select N '001', '20190801', '10:05:00, 10000 union all
Select N '001', '20190801', '10:06:01, 5000 union all
Select N '001', '20190801', '15:15:00, 250000 union all
Select N '001', '20190801', '23:05:00, 5000 union all
Select N '001', '20190802', '09:10:00, 100000 union all
Select N '001', '20190802', '10:06:01, 5000 union all
Select N '001', '20190802', '15:15:00, 250000 union all
Select N '001', '20190802', '15:20:00' 0 union all
Select N '001', '20190803', '08:32:00' 50000
Go
- account every day, take out, the balance has been change, cycle for 24 hours, take every last one balance, to calculate the month or year, on average, how to do?
- the average number of days according to the record day

The select cusid, SUM ([balance])/COUNT (*) as m_balance from (
Select * from # t a where not the exists (
Select * from # t
Where a.c usid=cusid and a. d. ate=date and a.t ime)) group by tt cusid