当某些字段为空时查找月平均值 [英] Finding Monthly averages when some are empty fields

问题描述

所以基本上我需要一个看起来几乎像这样的表

-----------------------------
姓名|姓|金额|最后日期|单位数|
-----------------------------
Duard |戴维斯| R12000 | 2012/02/05 | 54
瑞克福特| R5500 | 2012/02/06 | 22
Fey |摩根| | |

因此，这应该是足够的数据.

我需要检查最近三个月的数据(金额(将它们加到ang以获得平均值))，然后将其与本月的平均值进行比较.

因此应该是John:最近三个​​月的平均值= R 4500，这个月的平均值= R8500.

因此，当获取空日期(代表用户不使用系统的月份)时，棘手的部分就出现了.在这种情况下，需要将月份重新设置为用户最后一次使用系统的月份，然后将其计算为平均值. >
所以我从4个变量开始

将ThisMonth设置为date = dateTime.now()
将LastMonth1设置为date = thismonth.days.add(-1)
将LastMonth2设置为date = thismonth.days.add(-2)
将LastMonth3设为日期= thismonth.days.add(-3)

因此，当LastMonth1的月份为空时，必须再次将其设置为最接近的使用月份，但还要相应地更改其他两个月份.

有任何建议吗?

So basically i need I have a table that looks almost like this

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Name | Surname | Amount | LastDated | Number of units |
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Duard | Davids | R12000 | 2012/02/05 | 54
Rick | Ford | R5500 | 2012/02/06 | 22
Fey | Morgan | | |

So this should be enough data.

I need to check the last three months data(amounts(adding them up ang getting the average)) and compare it to this months average.

so it would be John : last three months average = R 4500 ,This months average = R 8500.

so the tricky part comes in when getting empty dates(which represent the month the user didnt use the system. in which case the months need to be set back to when the user last used the system and then calculated as an average.

so I started with 4 variables

dim ThisMonth as date = dateTime.now()
dim LastMonth1 as date = thismonth.days.add(-1)
dim LastMonth2 as date = thismonth.days.add(-2)
dim LastMonth3 as date = thismonth.days.add(-3)

so when the month is empty for the LastMonth1 it has to be set back to the closest used month again,but also change the other two accordingly.

any suggestions?

推荐答案

好吧，我可以建议您使用SQL Sum函数.在这里您可以找到有关它的更多信息.

http://www.w3schools.com/sql/sql_func_sum.asp [ http://msdn.microsoft.com/en-us/library/ms187810.aspx [ ^ ]

干杯

Well I can suggest you to use SQL Sum function. Here you can find more info about it.

http://www.w3schools.com/sql/sql_func_sum.asp[^]

Don''t worry about null as they are ignored. Here you can find more details.

http://msdn.microsoft.com/en-us/library/ms187810.aspx[^]

Cheers

这篇关于当某些字段为空时查找月平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！