MINUS HOLIDAY FROM TRANSACTION DATE

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

MINUS HOLIDAY FROM TRANSACTION DATE

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-03-29 : 04:09:48

table transaction

username, leavedatefrom, leavedateto
______________________________________
abc 3/2/2012 3/12/2012

table holiday

holidayfrom holidayto holidayname
_______________________________________
3/5/2012 3/9/2012 holiday 1

Let say i want to generate report from datefrom: 3/1/2012 to dateto: 3/7/2012.

How do calculate how many leave day a user applied between the date datefrom: 3/1/2012 to dateto: 3/7/2012??

thank in advances.

i had get the data which minus the weekend correctly,
now how do i minus out the holidays?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-29 : 04:14:43

do you have a calendar table ?

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-03-29 : 04:17:56

quote:
Originally posted by khtan

do you have a calendar table ?

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

khtan, what do you mean by calendar table?
i have the holiday table and transaction table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-29 : 04:35:31

refer to your last thread. I mention that http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=172795

A calendar table basically is a table that contains all the dates that your application may required. It may contain past and future dates. You can google that for further information. There are lots of reference on that all over the places.

From what you have posted so far, you deals a lot of dates and you need to take into consideration of weekends, holidays etc. It is best that you have one. It will make your query much easier and you will be able to handle more complex requirement easily

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-03-29 : 04:45:15

quote:
Originally posted by khtan

refer to your last thread. I mention that http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=172795

A calendar table basically is a table that contains all the dates that your application may required. It may contain past and future dates. You can google that for further information. There are lots of reference on that all over the places.

From what you have posted so far, you deals a lot of dates and you need to take into consideration of weekends, holidays etc. It is best that you have one. It will make your query much easier and you will be able to handle more complex requirement easily

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

thank khtan,
actually i manage to get the transaction days with minus out the saturday and sunday.

Yet, i can't minus out the holidays days from the transaction days.
the link you provided is too complicated or difficult for me.
sorry about that.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-29 : 04:53:37

Beside having your own calendar table, seriously you are missing out a lot if you don't have that function.

This is how you can make use of F_TABLE_DATE to get the dates between 2012-03-01 and 2012-03-15, excluding weekends (Sat / Sun) and the dates that you defined in your holiday table.


select	[DATE]
from	dbo.F_TABLE_DATE('2012-03-01', '2012-03-15') d
where	WEEKDAY_NAME	not in ('Sat', 'Sun')
and	not exists
	(
		select	*
		from	holiday x
		where	x.holidayfrom	<= d.[DATE]
		and	x.holidayto	>= d.[DATE]
	)

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-03-29 : 05:28:44

quote:
Originally posted by khtan

Beside having your own calendar table, seriously you are missing out a lot if you don't have that function.

This is how you can make use of F_TABLE_DATE to get the dates between 2012-03-01 and 2012-03-15, excluding weekends (Sat / Sun) and the dates that you defined in your holiday table.

select [DATE]
from dbo.F_TABLE_DATE('2012-03-01', '2012-03-15') d
where WEEKDAY_NAME not in ('Sat', 'Sun')
and not exists
(
select *
from holiday x
where x.holidayfrom <= d.[DATE]
and x.holidayto >= d.[DATE]
)

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

thank your reply khtan,
i will spend time on exloring it, thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-29 : 05:35:24

Actually, you don't have to understand how the underlying logic in F_TABLE_DATE works, just know how to use it

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-04-02 : 04:06:40

quote:
Originally posted by khtan

Actually, you don't have to understand how the underlying logic in F_TABLE_DATE works, just know how to use it

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

Thank you khtan,
just to clarify that i need to create a table call F_TABLE_DATE manually?
i din't found the code to create the table.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-02 : 04:16:52

F_TABLE_DATE is not a table. It is a function. This link will contain the codes to create the function.

For your requirement, you either create your own calendar table or this F_TABLE_DATE function. F_TABLE_DATE is a table valued function

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-04-02 : 04:23:43

quote:
Originally posted by khtan

F_TABLE_DATE is not a table. It is a function. This link will contain the codes to create the function.

For your requirement, you either create your own calendar table or this F_TABLE_DATE function. F_TABLE_DATE is a table valued function

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

thank for your information, khtan.
thank you

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-04-02 : 05:00:57

quote:
Originally posted by khtan

F_TABLE_DATE is not a table. It is a function. This link will contain the codes to create the function.

For your requirement, you either create your own calendar table or this F_TABLE_DATE function. F_TABLE_DATE is a table valued function

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

Mr khtan,

thank for your patient, i interested to learn about this F_TABLE_DATE yet i am not clear on using it.

I had a select statement for example:

Select A.totalLeave_Consumed from table A
how do i minus the A.totalLeave_Consumed with the holiday in using F_TABLE_DATE?

i need to insert the query as below:??

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_DATE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_DATE]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function dbo.F_TABLE_DATE
(
@FIRST_DATE datetime,
@LAST_DATE datetime
)

Select sum(A.totalLeave_Consumed - dbo.F_TABLE_DATE.Holidays) from table A
inner join????
..........................................???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-02 : 05:08:18

the F_TABLE_DATE is a function. You only need to run it once and the function will be created. And you can use it subsequently.

go to that link and copy the text in between these and paste into your query window and run. That's all. The function will be created


if exists (select * from dbo.sysobjects 
where id = object_id(N'[dbo].[F_TABLE_DATE]')
and xtype in (N'FN', N'IF', N'TF'))

. . . .

GRANT  SELECT  ON [dbo].[F_TABLE_DATE]  TO [public]
GO

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-02 : 05:09:32

and for using it in your context, see my post on Posted - 03/29/2012 : 04:53:37

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-04-02 : 05:13:48

quote:
Originally posted by khtan

the F_TABLE_DATE is a function. You only need to run it once and the function will be created. And you can use it subsequently.

go to that link and copy the text in between these and paste into your query window and run. That's all. The function will be created

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_DATE]')
and xtype in (N'FN', N'IF', N'TF'))

. . . .

GRANT SELECT ON [dbo].[F_TABLE_DATE] TO [public]
GO

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

i copy and paste the code below but with error message:

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_DATE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_DATE]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function dbo.F_TABLE_DATE
(
@FIRST_DATE datetime,
@LAST_DATE datetime
)

Msg 102, Level 15, State 1, Procedure F_TABLE_DATE, Line 5
Incorrect syntax near ')'.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-02 : 05:27:34

that is not the complete query to create the function. You need to copy the entire stuff. About 700+ lines

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-04-02 : 05:35:45

quote:
Originally posted by khtan

that is not the complete query to create the function. You need to copy the entire stuff. About 700+ lines

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

Mr khtan,

Yes, i copy the whole code and run it once with successful....
do i need to run it for every database or no need?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-04-02 : 05:36:50

Of-course need lar

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

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2012-04-02 : 05:52:08