插入所有月份的行，日期/时间范围为&在SQL和VB.NET中，将金额添加到各行，直到当前范围行为每个月的更改 [英] Insert rows of all months b/n a date range & add amount to the rows till the current range row for each change of month in SQL and VB.NET
问题描述
尊敬的老年人！
我正在为一个使用''VB.Net& SQL Server-2012''.
数据库和表格详细信息如下:
SQL数据库，名为" DB_COLLECTOR "，
名为"费用支付"的表，
共6列，名为:
''S_No'' Int Primary key identity(1,1),
''Date_Start'' datetime Null,
''Date_End'' datetime Null,
''Prefixed_Fee'' decimal(10) Null,
''Paid_Amount'' decimal(10) Null,
''Balance'' decimal(10) Null
我有 2种形式:
第一个表单采用人名，Deal_Start_Date，Deal_End_Date，monthly_Fee"，该数据将数据保存到数据库中的"Customer_Master"表中.
第二个表单带有人名，付款金额"，该数据将数据保存到费用付款"表中.
当我输入
Deal_Start_Date为``01-04-2018''&
Deal_End_Date为``31-03-2019''
为名为"John"的客户
并将每月固定为$ 50.00的费用固定到第一表格，
需要帮助:
1)它应自动在``Fee_Payment''表中添加12行，每行的``Date_Start''为``01-Apr-2018''&除保存到``Customer_Master''的相关数据外，``Date_End''为``2018年4月30日''.
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 Null Null Null <br /> 2 01-May-2018 31-May-2018 Null Null Null <br /> 3 01-Jun-2018 30-Jun-2018 Null Null Null <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
2)如果日期在``2018年4月1日至2018年4月30日之间''，则应将``空''自动替换为``$ 50.00''到2018年4月行的``Prefixed_Fee''列中2018''，即当前日期为``2018年4月2日''.
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 50.00 Null 50.00 <br /> 2 01-May-2018 31-May-2018 Null Null Null <br /> 3 01-Jun-2018 30-Jun-2018 Null Null Null <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
3)如果客户John支付了$ 125.00，则应通过填写PreFixed_Fee(FIRST)&的第1行中提到的金额，从$ 125.00中分配$ 50.00到2018年4月的第一行.然后从余额金额$ 50.00到2018年5月的第二行(SECOND)&到2018年6月(THIRD)的余额为$ 25.00.
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 50.00 50.00 0.00 <br /> 2 01-May-2018 31-May-2018 50.00 50.00 0.00 <br /> 3 01-Jun-2018 30-Jun-2018 50.00 25.00 25.00 <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
在此先感谢！
我尝试过的事情:
我什么都没想到，即使尝试尝试也没有，因此没有添加尝试过的示例代码.因此，我对此表示歉意.请帮忙！
50.00转换为第一表单，
需要帮助:
1)它应自动在``Fee_Payment''表中添加12行，每行的``Date_Start''为``01-Apr-2018''&除保存到``Customer_Master''的相关数据外，``Date_End''为``2018年4月30日''.
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 Null Null Null <br /> 2 01-May-2018 31-May-2018 Null Null Null <br /> 3 01-Jun-2018 30-Jun-2018 Null Null Null <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
2)如果日期在' '2018年4月1日至2018年4月30日''，即当前日期为``2018年4月2日''.
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 50.00 Null 50.00 <br /> 2 01-May-2018 31-May-2018 Null Null Null <br /> 3 01-Jun-2018 30-Jun-2018 Null Null Null <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
3)如果约翰向客户支付了
125.00，则应分配
Respected Seniors!
I am working for an ongoing project that uses ''VB.Net & SQL Server-2012''.
The DB & Table details are below:
SQL Database named ''DB_COLLECTOR'',
Table named ''Fee_Payment'',
Total 6 Columns named:
''S_No'' Int Primary key identity(1,1),
''Date_Start'' datetime Null,
''Date_End'' datetime Null,
''Prefixed_Fee'' decimal(10) Null,
''Paid_Amount'' decimal(10) Null,
''Balance'' decimal(10) Null
I have 2 Forms:
1st Form takes ''Person Name, Deal_Start_Date, Deal_End_Date, Monthly_Fee'' that saves datas to ''Customer_Master'' Table in DB &
2nd Form takes ''Person Name, Payment_Amount'' that saves datas to ''Fee_Payment'' table.
When I enter a
Deal_Start_Date as ''01-04-2018'' &
Deal_End_Date as ''31-03-2019''
for a Customer named ''John''
with a monthly fee fixed as $50.00 to 1st Form,
Help Required:
1) it should AUTOMATICALLY add 12 Rows in ''Fee_Payment'' table with each row having ''Date_Start'' as ''01-Apr-2018'' & ''Date_End'' as ''30-Apr-2018, apart from the relevant datas saved to ''Customer_Master''.
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 Null Null Null <br /> 2 01-May-2018 31-May-2018 Null Null Null <br /> 3 01-Jun-2018 30-Jun-2018 Null Null Null <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
2) it should AUTOMATICALLY replace ''Null'' with ''$50.00'' to ''Prefixed_Fee'' Column of Apr-2018 Row if the date has come under the range of ''01-Apr-2018 to 30-Apr-2018'' i.e. if the current date is ''02-Apr-2018''.
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 50.00 Null 50.00 <br /> 2 01-May-2018 31-May-2018 Null Null Null <br /> 3 01-Jun-2018 30-Jun-2018 Null Null Null <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
3) If customer, John had paid $125.00, it should allocate $50.00 from $125.00 to first row of Apr-2018 by filling the amount mentioned in row-1 of PreFixed_Fee (FIRST) & then from Balance amount, $50.00 to second row of May-2018 (SECOND) & Balance amount of $25.00 to Jun-2018 (THIRD).
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 50.00 50.00 0.00 <br /> 2 01-May-2018 31-May-2018 50.00 50.00 0.00 <br /> 3 01-Jun-2018 30-Jun-2018 50.00 25.00 25.00 <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
Thanks alot in Advance!
What I have tried:
Nothing comes to my mind, even to try something, thus no sample codes added that was tried. Hence, I apologize for the same. Please help!
50.00 to 1st Form,
Help Required:
1) it should AUTOMATICALLY add 12 Rows in ''Fee_Payment'' table with each row having ''Date_Start'' as ''01-Apr-2018'' & ''Date_End'' as ''30-Apr-2018, apart from the relevant datas saved to ''Customer_Master''.
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 Null Null Null <br /> 2 01-May-2018 31-May-2018 Null Null Null <br /> 3 01-Jun-2018 30-Jun-2018 Null Null Null <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
2) it should AUTOMATICALLY replace ''Null'' with ''
50.00'' to ''Prefixed_Fee'' Column of Apr-2018 Row if the date has come under the range of ''01-Apr-2018 to 30-Apr-2018'' i.e. if the current date is ''02-Apr-2018''.
S_No Date_Start Date_End Prefixed_Fee Paid_Amount Balance <br /> ---- ---------- -------- ------------ ----------- ------- <br /> 1 01-Apr-2018 30-Apr-2018 50.00 Null 50.00 <br /> 2 01-May-2018 31-May-2018 Null Null Null <br /> 3 01-Jun-2018 30-Jun-2018 Null Null Null <br /> .. .... .... .. .. .. <br /> 12 01-Mar-2019 31-Mar-2019 Null Null Null
3) If customer, John had paid
125.00, it should allocate
这篇关于插入所有月份的行，日期/时间范围为&在SQL和VB.NET中，将金额添加到各行，直到当前范围行为每个月的更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
