如何在sql server c＃中的每次插入时分配日期 [英] How to assign date at each insertion in sql server c#

问题描述

我在C＃工作。我在Sql Server中有列emp_id，sick_leaves，vacation_leaves，admin_leaves，from_date，to_date。用户可以使用表单将叶子分配给任何员工。叶子插入单元格中，并从datetimepicker中选取相应的日期并存储在数据库中。当同一个员工想要更多叶子时，叶子会被添加到他之前的叶子中。但问题是，当同一个员工在几天后想要更多的叶子时，如何在数据库中存储每个叶子。例如，2014年2月2日至2014年2月4日的2个sick_leaves。然后从2014年10月2日到2014年2月13日再发病3次。之后，我想每月和每年搜索离开，但我必须解决上述问题，以便以后做。希望你能理解这个问题。请尽快给我一些解决方案。在此先感谢。

解决方案

我会将休假日期分成一个单独的表格。



所以你有一个EmployeeLeave表和一个EmployeeLeaveDates表。然后，您可以在EmployeeLeaveDates表中插入条目来表示各个休假期间（带有表示不同类型休假的标志，例如病假与休假）。然后，您可以在EmployeeLeave表中保留总计的更新条目，或者您可以根据条目动态计算它。



EmployeeLeave表：

Leave_Id，Emp_Id，Total_Sick，Total_Vacation，Total_Admin



EmployeeLeaveDates表：

LeaveDates_Id，Leave_Id，Date_From，Date_To，Type



然后从一个对象点视图：



EmployeeLeave 对象将包含 EmployeeLeaveDates

的< blockquote>嘿那里。



制作RAW表 - 将每个缺席存储在新行中，如下所示：

1.疾病日期开始 日期结束

2.假期日期开始日期结束

3.疾病日期开始日期结束



然后，最后，制作一个过滤器或数据透视表，将所有内容分组为表现形式。



我做了非常相似的事情办法。结果是我在excel中链接了原始表并绘制了一个表示数据的数据透视表。



希望有所帮助。

I am working in C#. I have columns emp_id, sick_leaves, vacation_leaves, admin_leaves, from_date, to_date in Sql Server. User can assign leaves to any employee using a form. Leaves are inserted in cells and respective date is picked from datetimepicker and stored in database. when the same employee wants more leaves,leaves are added to his previous leaves. But the problem is when the same employee wants more leaves after some days, how to store each of leaves date wise in database. e.g 2 sick_leaves from 02/02/2014 to 04/02/2014. Then 3 more sick_leaves from 10/02/2014 to 13/02/2014. After that i want search leaves on monthly and annually basis, but i have to solve the above problem to do the later. Hope you understand the question. Please give me some solutions asap. Thanks in advance.

解决方案

I would split out the the leave dates into a seperate table.

So you have an EmployeeLeave table and an EmployeeLeaveDates table. Then you can insert entries into the EmployeeLeaveDates table to represent the individual leave periods (with a flag indicating different types of leave such as sick vs vacation). You can then either keep an updated entry of the total in your EmployeeLeave table or you can calculate it it on the fly based on the entries.

EmployeeLeave table:
Leave_Id, Emp_Id, Total_Sick, Total_Vacation, Total_Admin

EmployeeLeaveDates table:
LeaveDates_Id, Leave_Id, Date_From, Date_To, Type

Then from an object point of view:

EmployeeLeave object will have a collection of EmployeeLeaveDates

Hey there.

Make the RAW table - store each absence in new line like this:
1. sickness "date start" "date end"
2. vacation "date start" "date end"
3. sickness "date start" "date end"

Then, finally, make a filter or a pivot table grouping everything into presentational form.

I've done very similar thing the same way. The outcome was that I linked the raw table in excel and drew a pivot table, which represented data.

Hope that helps.

这篇关于如何在sql server c＃中的每次插入时分配日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！