Multi IF NOT - SQL Server Forums

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

Multi IF NOT

Author

Topic

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-15 : 03:30:02

Dear Gurus,

This is my question continues from Leave Application System I asked days ago.

I am trying to insert a record if the selected date is not Saturday or Sunday or Holiday.

User provides a date range, and the system will insert the record into the table if it is not Saturday/Sunday/Holiday

I've done the Holiday part:
IF NOT EXISTS (SELECT * FROM Holidays WHERE HolidayDate = @myDate) INSERT INTO Leaves (userName, LeaveDate) VALUES (...)

But I am not sure how to add multiple condition to check also if the date is not Saturday or Sunday,

I believe it should be something got to do with:
datepart(weekday, @myDate)
I am stucked...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-15 : 03:32:12

[code]
INSERT INTO Leaves (userName, LeaveDate)
SELECT @userName, @myDate
WHERE NOT EXISTS (SELECT * FROM HOLIDAY WHERE HolidayDate = @myDate)
AND datename(weekday, @myDate) not in ('Sunday', 'Saturday')
. . .
[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-15 : 03:39:11

or you can use IF . . .


IF  NOT EXISTS (SELECT * FROM Holidays WHERE HolidayDate = @myDate) 
AND datename(weekday, @myDate) not in ('Sunday', 'Saturday')
INSERT INTO Leaves (userName, LeaveDate) VALUES (...)

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

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-15 : 03:39:42

khtan,
Thanks, you are really an SQL walking dictionary.

Currently I am generating the date range with ASP (Date_From until Date_To). Which is simple to be done. Would it be possible to use SQL to count and loop two dates given instead of use ASP? then loop the above INSERT INTO Leaves?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-15 : 03:49:45

if you have a calendar table, that will be great. If not, you might want to consider building one. That might make things simpler for you since you are dealing with dates a lot in the Leave Application.

If you don't have a calendar table, you can use the F_TABLE_DATE to generate the dates

F_TABLE_DATE is a table function that returns lots of information. You can use it to exclude Sat / Sun. For excluding the holiday you will still to use not exists (select * ... )

example :


select DATE
from   F_TABLE_DATE(@from_date, @to_date)
where  . . . . 
and    not exists ( ... )

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

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-15 : 04:09:51

Thanks, the function looks so... complex... it is beyond my brain can understand. But I'll look into it and try to digest it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-15 : 04:13:42

quote:
Originally posted by calvinfoo

Thanks, the function looks so... complex... it is beyond my brain can understand. But I'll look into it and try to digest it.

You don't really need to understand how it works internally. Just need to know how to use it.

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

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-15 : 04:26:46

Thanks! I begin to understand this function. very useful to my Leave Application I must say.

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-15 : 04:54:00

Darn... a simple select statement below solves all my problem. the F_TABLE_Date is darn amazing

SELECT     F_TABLE_DATE.DATE, F_TABLE_DATE.WEEKDAY_NAME
FROM         dbo.F_TABLE_DATE(@date_from, @date_to) AS F_TABLE_DATE LEFT OUTER JOIN
                      dbo.Holidays ON F_TABLE_DATE.DATE = dbo.Holidays.HolidayDate
WHERE     (dbo.Holidays.HolidayDesc IS NULL) AND (F_TABLE_DATE.WEEKDAY_NAME <> 'Sun') AND (F_TABLE_DATE.WEEKDAY_NAME <> 'Sat')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-15 : 05:09:05

quote:
Darn... a simple select statement below solves all my problem. the F_TABLE_Date is darn amazing

Yup. Thanks to MVJ

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

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-18 : 21:51:48

Based on your suggestion, I managed to figure out a solution to my problem: User enters a date range, then it will automatically insert the date (minus off Sat, Sun, Public Holiday) into the Leaves Table. However, I also putting another check to ensure the selected date range must not be a date that the user has been applied before to ensure no double entry.

Example, if selected is 01/04 to 05/04, and 03/04 already applied before, then it will generate only 01/04, 02/04, 04/04 and 05/04

Based on my limited knowledge, this is what I came out:

INSERT INTO Leaves (UserInitial, ApproverInitial, LeaveTypeID, LeaveDurationID, Leave_Date, LeaveStatusID, Updated_By, Record_Date)
SELECT @myInitial, @ApproverInitial, 1, 1, Date, 1, @myInitial, GETDATE() FROM F_TABLE_DATE(@DateFrom, @DateTo) 
LEFT OUTER JOIN Holidays ON F_TABLE_DATE.DATE = Holidays.HolidayDate
LEFT OUTER JOIN (SELECT * FROM Leaves WHERE userInitial=@myInitial) AS myLeaves ON F_TABLE_DATE.DATE = myLeaves.Leave_Date
WHERE (F_TABLE_DATE.WEEKDAY_NAME <> 'Sat') AND (F_TABLE_DATE.WEEKDAY_NAME <> 'Sun') AND (Holidays.HolidayDate IS NULL)
AND myLeaves.Leave_date IS NULL

The SQL above works, but somehow I think it is not nessasary to have a nested SELECT:

(SELECT * FROM Leaves WHERE userInitial=@myInitial) AS myLeaves

Is there any suggestion to improve the above without using nested SELECT?

I tried replaced it a direct LEFT OUTER JOIN something like:

LEFT OUTER JOIN Leaves ON F_TABLE_DATE.DATE = Leaves.Leave_Date

but the result doesn't turn out as what I expected, and the result is confusing

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 22:05:47

Yes. Left Outer Join is fine.

You don't really need that nested select. You can just LEFT JOIN to the Leaves table


LEFT JOIN Leaves myLeaves ON myLeaves.userInitial = @myInistial and myLeaves.Leave_Date = F_TABLE_DATE.DATE

you can also use NOT EXISTS instead of LEFT JOIN to the Leaves table. You can also do likewise for the Holidays table.


INSERT INTO Leaves (UserInitial, ApproverInitial, LeaveTypeID, LeaveDurationID, Leave_Date, LeaveStatusID, Updated_By, Record_Date)
SELECT 	@myInitial, @ApproverInitial, 1, 1, DATE, 1, @myInitial, GETDATE() 
FROM 	F_TABLE_DATE(@DateFrom, @DateTo) 
	LEFT OUTER JOIN Holidays 	ON F_TABLE_DATE.DATE = Holidays.HolidayDate
	LEFT OUTER JOIN 
	(
		SELECT 	* 
		FROM 	Leaves 
		WHERE 	userInitial=@myInitial
	) AS myLeaves 			ON F_TABLE_DATE.DATE = myLeaves.Leave_Date
WHERE 	(F_TABLE_DATE.WEEKDAY_NAME <> 'Sat') 
AND 	(F_TABLE_DATE.WEEKDAY_NAME <> 'Sun') 
AND 	(Holidays.HolidayDate IS NULL)
AND 	myLeaves.Leave_date IS NULL
AND	NOT EXISTS
	(
		SELECT	*
		FROM	Leaves x
		WHERE	x.UserInitial	= @myInitial
		AND	x.Leave_Date	= F_TABLE_DATE.DATE
	)

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

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-18 : 22:40:29

Ok, it took me 20 minutes to test and figure out what your suggestion means.

1. Am I right that your suggestion of NOT EXISTS must referring to the same Leaves table? Else I don't quite understand what is the logic works if it is referring to another table? example: NOT EXISTS (SELECT * FROM Holidays)

2. Your suggestion still a nested SELECT?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 23:02:08

1. Yes. same Leaves table for the condition "selected date range must not be a date that the user has been applied"

for the applied date is not a holiday,


WHERE NOT EXISTS (SELECT * FROM Holidays WHERE HolidayDate = F_TABLE_DATE.DATE)

2. Yes. EXISTS() in a way it is a nested select. There is nothing wrong with using a nested select if that is a concern.

But if the nested select is just a "SELECT *", then it is unnecessary to do that.


LEFT OUTER JOIN (SELECT * FROM Leaves WHERE userInitial=@myInitial)

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

calvinfoo
Posting Yak Master

129 Posts

Posted - 2011-04-18 : 23:22:45

Thanks for the clarification. Now I understand it fully.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources