Normalise Date Range (Date_from and Date_to)? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Normalise Date Range (Date_from and Date_to)?

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-12 : 03:37:30

Dear Gurus,
I am writing an ASP page for my company's Leave Application System

I doubt about the table design I should do. my ideas as below:

Design #1 - Table contains date range - FROM and TO.

[Table_Leaves]
([EmpID] [int],
[ApproverID] [int],
[LeaveType] [AnnualLeave, Emergency, etc],
[LeaveDate_From] [Date],
[LeaveDate_To] [Date],
[HalfDay] [Yes|No],
[Approved][Yes|No])

or should I normalise it (as below) Table contains only single date

Design #2

[Table_Leaves]
([EmpID] [int],
[ApproverID] [int],
[LeaveType] [AnnualLeave, Emergency, etc],
[LeaveDate] [Date],
[HalfDay] [Yes|No],
[Approved][Yes|No])

I think Design #2 is a better approach to store the data, however, in User Interface design point of view, if user apply 10 days for holiday, then how to allow User/Manager to see it as a DATE_FROM and DATE_TO instead of a table with 10 rows?

If I go for Design #1, Displaying to user would be a direct approach.

If I go for Design #2, it will be very ugly to display 10 rows of days instead of just simplified as a date range. How to overcome the display problem if I would go for Design #2?

Please advise.

McDebil
Starting Member

23 Posts

Posted - 2011-04-12 : 03:47:47

Use the design#1 table or add the days INT field to design# table. You use range of days, when recorded the holidays!

Design #2

[Table_Leaves]
([EmpID] [int],
[ApproverID] [int],
[LeaveType] [AnnualLeave, Emergency, etc],
[LeaveDate] [Date],
[Days] [int],
[HalfDay] [Yes|No],
[Approved][Yes|No])

McDebil

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-12 : 04:06:36

I don't quite understand your suggestion. You mean add [days] to define the number of days from the [LeaveDate] that the user is taking?

1. If so, how do you deal with non-working days or public holiday?
if user selects 23/12/2011 (Friday) to 30/12/2011 (Friday), the system should be able to tell 24/12 & 25/12 are saturday and Sunday (non-working day), and 26/12 (monday) is a replacement holiday. then total selected days are 7 actual days, but minus off Saturday & Sunday, and replacement holiday, it should be only 4 days of leaves.

2. Also, how do you suggest to compute half day if you use date range? Example 23/12/2011 (2nd half), then 28-31/12/2011 are full days?

McDebil
Starting Member

23 Posts

Posted - 2011-04-12 : 06:14:37

Your #2 desing as "normalization" is wrong: use 10 rows instead of 1 rows for one 10 day holiday and required large logic for compute this result. (eg: determine start and end date, if the data range contain weekend or public holiday.)

I suggest the followig example for you:

DECLARE @Table_Leaves TABLE
(
[EmpID] [int],
[ApproverID] [int],
[LeaveType] VARCHAR(50),
[LeaveDate] [Datetime],
[EndDate] [Datetime],
[DAYS] [int],
[HalfDay] [int],
[Approved] VARCHAR(3))

DECLARE @Table TABLE
(
[Holidays] DECIMAL(3,1),
[EmpID] [int],
[LeaveType] VARCHAR(50),
[LeaveDate] VARCHAR(10)
)

INSERT @Table_Leaves ([EmpID],[ApproverID],[LeaveType],[LeaveDate],[EndDate], [DAYS],[HalfDay],[Approved])
VALUES (0 ,0 ,'AnnualLeave', '2011-04-05', '2011-04-12', 6 ,2,'Yes')
INSERT @Table_Leaves ([EmpID],[ApproverID],[LeaveType],[LeaveDate],[EndDate], [DAYS],[HalfDay],[Approved])
VALUES (0 ,0 ,'Emergency', '2011-04-20','2011-04-20',1 ,0,'Yes')
INSERT @Table_Leaves ([EmpID],[ApproverID],[LeaveType],[LeaveDate],[EndDate], [DAYS],[HalfDay],[Approved])
VALUES (0 ,0 ,'Other', '2011-04-22','2011-04-22',1 ,1,'Yes')

INSERT @Table
(
[Holidays]
, [EmpID]
, [LeaveType]
, [LeaveDate]
)

SELECT CAST(SUM([DAYS]) - SUM([HalfDay]) / 2.0 AS DECIMAL(3,1)), [EmpID], [LeaveType], CONVERT(VARCHAR(10),[LeaveDate], 120) AS StartedAt
FROM @Table_Leaves
GROUP BY [EmpID], [LeaveType], [LeaveDate]

SELECT [a].[Holidays]
, [a].[EmpID]
, [a].[LeaveType]
, [a].[LeaveDate]
, CONVERT(VARCHAR(10),[b].[EndDate], 120) [EndDate]
FROM @Table a
INNER JOIN @Table_Leaves b
ON [a].[EmpID] = [b].[EmpID] AND a.[LeaveDate] = [b].[LeaveDate]

McDebil

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-12 : 21:42:22

I fully agree with you that using 1 row instead of 10 rows for single request is better in the sense of taking less row and processing power. But can it be achievable all situation?

In a worst case scenario what if the date range covers all public holiday, saturday and Sunday, and two half days? is it possible to store it within one row?
Example:
30/04/2011 = Fri
01/05/2011 = Sat (Labour day, holiday)
02/05/2011 = Sun
03/05/2011 = Mon (replacement holiday for Labour day, not working)
04/05/2011 = Tue
05/05/2011 = Wed
06/05/2011 = Thu
07/05/2011 = Fri
08/05/2011 = Sat
09/05/2011 = Sun
10/05/2011 = Mon

The user decides to take from 30/4 to 08/05 Monday:
30/04 = 2nd Half
04-07/05 = Full day
10/05 = 1st Half

Total of (0.5 + 4 + 0.5) 5.0 Annual Leave taken.
If follow your design it will need 3 rows of data instead of 1 row, because I can't think of a way store the data that contains mixture of half day and full leave in a row of data that using date range in a single row. I can only compute and track a list of holiday date or non-working day easily

If the program computes it takes total 5.0 days Annual Leave, then how do you track the history of it is 0.5 + 4 + 0.5 instead of 5 days in a row? and knowing 30/4 and 10/05 is taken half day but not the other days?

Please advise and correct me if I am wrong

McDebil
Starting Member

23 Posts

Posted - 2011-04-13 : 00:46:45

HI, look the following extended example:

30/04/2011 = Fri half holiday
01/05/2011 = Sat (Labour day, holiday)
02/05/2011 = Sun
03/05/2011 = Mon (replacement holiday for Labour day, not working)
04/05/2011 = Tue National day (excluded fom annual leave)
05/05/2011 = Wed holiday
06/05/2011 = Thu holiday
07/05/2011 = Fri holiday
08/05/2011 = Sat
09/05/2011 = Sun
10/05/2011 = Mon half holiday

The user decides to take from 30/4 to 08/05 Monday:
30/04 = 2nd Half
01/05 = Labour day, holiday
04/ = National day
05-07/05 = Full day
10/05 = 1st Half

Total of (0.5 + 4 + 0.5) 5.0 Annual Leave taken. -> 6 days with 2 half days inside

INSERT @Table_Leaves ([EmpID],[ApproverID],[LeaveType],[LeaveDate],[EndDate], [DAYS],[HalfDay],[Approved])
VALUES
(0
,0
,'AnnualLeave'
, '2011-04-30' --Start of holiday
, '2011-05-10' --End day of holiday
, 6 -- 6 working days within holiday period
,2 -- 2 half days
,'Yes')

Maintaining and processing the non standard working and public holidays in the database is wery complicated. This schema can store all required information as need as well.

McDebil

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-13 : 01:33:31

I see you put 2 under HalfDay column to indicate two of the applied days are half day.

so it is (6 days) - (2 days x 0.5) = 5 days.

But it only tell at most "out of 6 days, 2 days are half day". But it still didn't track which day is half day or full day. That's the problem I am trying to tell you.

I am not worry about non-standard working and public holiday at this moment, I am sure it can be solved easily with a seperate holiday list table.

McDebil
Starting Member

23 Posts

Posted - 2011-04-13 : 01:52:45

"But it only tell at most "out of 6 days, 2 days are half day". But it still didn't track which day is half day or full day. That's the problem I am trying to tell you."
Add column to the table and register half days as coma separated string.

"I am not worry about non-standard working and public holiday at this moment, I am sure it can be solved easily with a seperate holiday list table."
Yes, you can store this in separated table and you can use the weekdays for definition of standard working days, but chain the working days and non woking days with half days within the holiday period is very complicated and hold lot of points of errors in the process.

McDebil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-13 : 01:57:51

i would go for design #2. It is more flexible and able to cater for all your requirement.

For presentation of the design #2, it all depends on the requirement. What and how does the user want to see the information.

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-13 : 02:11:39

very likely I will go for design #2 too as what khtan said.

But on presentation, I haven't figure out how to present it to be less messy.

Using the above example, if a user applies that way, it will use total of 6 rows for one request alone. and the manager will start to scream at me, imagine the manager has more than 20 users under his supervision.

If the table stores based on Design #2, how do present it as date_range-based instead of date-based? I am not sure how would it be possible to create a view or something similar like that.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-13 : 02:24:53

you don't have to show 6 rows. You can show 1 row, take the min and max of the date as start and end of the leave and show the effective days.

something like


select  EmpID, 
        LeaveFrom = min(LeaveDate), LeaveTo = max(LeaveDate), Days = sum(case when HalfDay = 0 then 1 else 0.5 end)
from   [Table_Leaves]
group by EmpID

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-13 : 02:32:03

i see. This answered my concern. Thanks for the advice.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-13 : 02:47:41

you will probably need another column in that table to identify and to group the same leave application together



select  EmpID,
        LeaveFrom = min(LeaveDate), LeaveTo = max(LeaveDate), Days = sum(case when HalfDay = 0 then 1 else 0.5 end)
from   [Table_Leaves]
group by EmpID, LeaveApplicationID

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-13 : 02:58:28

yeah, I am thinking of what should I do with the grouping. I still yet to figure out, should i group it like what you suggested, or should I group it just based on status?

Once the user fulled up the draft and submited, the StatusID will be changed from "Draft" to "Pending Approval"

group by EmpID, StatusID

-- StatusID are referring to Draft, Pending Approval, Aprroved, Rejected, etc...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-13 : 03:04:43

it might looks ok for StatusID equal to Draft or Pending Approval, the most you might have 2 or 3 outstanding leave application per employee. But It will not work for Approved or Rejected.

Put in some sample data for each StatusID for an employee and run the query and see the result yourself.

KH
[spoiler]Time is always against us[/spoiler]

McDebil
Starting Member

23 Posts

Posted - 2011-04-13 : 03:08:04

Ok, I think my mind is too complicated:

And how look which type of [LeaveType] registered?
And how look the different holiday periods?
If I have holiday at 2011-01-02 (1 day Emergency) and I have holiday 2011-01-13 - 2011-01-17 (3 days Annual leave) and I have holiday 2011-01-21 - 2011-01-30 (6 days Annual leave) you look one holiday period with 10 days.
For me this don't too informative: LeaveFrom = 2011-01-21, LeaveTo = 2011-01-30 and Days = 10

McDebil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-13 : 03:17:31

What to present and how to present will depends on who are the user and information is required.

A simple 1 line of FROM .. TO .. & DAYS might be sufficient for the Manager or maybe HR Dept to have an overview of all leave application of its subordinate or employee. Or this might just serve as on overview listing to the employee of all the leave application by the employee. A more details (with individual dates) view maybe presented when the employee double click into it.

It all goes down to user requirement.

To me the fundamental is the table design should be able to capture all the necessary information required

KH
[spoiler]Time is always against us[/spoiler]

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-13 : 03:25:34

i don't quite understand why you say it will not work for Approved or Rejected. But I'll do what your suggestion later and see how the result goes.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources