March 23, 2009 at 7:51 am
In my application, I have a table that defines the holiday
The table tbl_holidays structure and test data is as follows:
id StartsEnds
1 24/12/2008 28/12/2008
2 01/01/2009 01/01/2009
3 23/03/2009 23/03/2009
I have requirement that based on the holiday defined one exe should or should not run
Ex. Id=1 where we have holidays from 24/12/2008 to 28/12/2008, the exe should NOT run on 25,26 ,27,28 and 29
Id =2 where we have holiday on first of year (01/01/2009) exe should not run on 02/01/2009
Id =3 where we have defined holiday on March 23 then exe should not run on March 24 and so on
for this requirement, I want to write something like
if not exists(select convert(varchar(12),starts),convert(varchar(12),ends+1) from tbl_holidays
where getdate() >=convert(varchar(12),starts)
begin
Execute some code
else don't execute some other code
Please give me EXACT query / solution
March 23, 2009 at 8:08 am
Hi,
Is this task in a stored procedure that you call from a scheduler (SQL Agent)?
Do you use xp_cmdshell to execute the .exe?
If it is already the way you are executing the executable file, then you can simply add a if statement that validates your date before you execute the .exe
If you are looking for a code snippet, then post back with clearer requirements.
Hope it helps,
Cheers,
J-F
March 23, 2009 at 8:24 am
Here is a small snippet that might get you started.
Quick hint, when you have a table with datetimes, do not bother converting them to string to compare them, it's a lot easier to just compare them in the datetime type.
DECLARE @tbl_holidays TABLE(
ID INT NOT NULL,
Starts DATETIME NOT NULL,
Ends DATETIME NOT NULL
)
INSERT INTO @tbl_holidays
(ID,
Starts,
Ends)
SELECT 1,
'2009-03-22',
'2009-03-24'
UNION ALL
SELECT 2,
'2009-03-26',
'2009-03-30'
DECLARE @Today DATETIME
SELECT @Today = DATEADD(dd,DATEDIFF(d,0,GETDATE()),0)
SELECT @Today
IF EXISTS (SELECT 1
FROM @tbl_holidays hol
WHERE @Today BETWEEN hol.starts AND hol.ends)
BEGIN
PRINT 'Holiday'
END
ELSE
BEGIN
PRINT 'Not an holiday'
END
SELECT *
FROM @tbl_holidays
Hope it helps,
If it does not fit your requirements, please tell me what is wrong,
Cheers,
J-F
March 23, 2009 at 11:04 am
My requirement is not to excute exe. I just want to do something if it's holiday and something else if it's not.
i am looking for exact query. pls give me exact query
March 23, 2009 at 11:06 am
This query does exactly what you are looking for, just add your code (what you want to do) between the begin/end of the if statement.
Cheers,
J-F
March 23, 2009 at 11:13 am
But I have predefined table tbl_holidays. I do not want to insert data in that.
Please note that if Jan 01 to Jan 10 is holiday defined in table. Then
I don't want to execute certain code on Jan 02 to Jan 11.
Similarly, if Jan 15 is defined holiday then don't want to execute certin code on Jan 16.
If Jan 26 is holiday then don't execute code on Jan 27
So holiday table looks like
Id Start End
1 Jan 01 09 Jan 10 09
2 Jan 15 09 Jan 15 09
3 Jan 26 09 Jan 26 09
March 23, 2009 at 12:19 pm
So, you do not want to execute code when yesterday was an holiday, just do a -1 day to the "@Today" Variable, in the code I gave you, and you are set.
And by the way, I gave you a sample working solution, if you want, just remove the @TblHolidays I created to put your own table. All you need to do is replace the table with your current table, and change the variable to a parameter for a stored procedure maybe?
Hope it helps!
Cheers,
J-F
March 23, 2009 at 1:50 pm
J-F gave you a complete working sample. To include the day after the "end" date just add "+ 1".
-- Copied from J-F
SELECT 1
FROM @tbl_holidays hol
WHERE @Today BETWEEN hol.starts AND hol.ends + 1
Greets
Flo
January 19, 2010 at 3:59 am
Hi,
I need material of sql server,repeatedly asking interview questions.If any one has the material kindly send it to me..
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply