How to assign date at each insertion in sql server c#

Question

0.00/5 (No votes)

See more:

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.

Posted 4-Feb-14 22:36pm

rana_sonu1

Add a Solution

Comments

Prasad Avunoori 5-Feb-14 4:55am

You supposed to change your Database design.

Follow this link.
http://www.databaseanswers.org/data_models/employees_leave_record_keeping/index.htm

2 solutions

Solution 2

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.

Posted 4-Feb-14 22:58pm

MK-Gii

Updated 4-Feb-14 23:01pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pheonyx · Accepted Answer · 2014-02-04T22:53:00

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