May 25, 2008 at 8:35 pm
Hi,
Please help me out to get next working date for the given date(excluding holidays and Sat and sundays also).
Ex : 23rd May 2008 is current working day if i want to add 2 days for this it should get 27th May 2008 (after excluding sat and sun)
Current Working Day No days Next Working Date
23-May-2008 2 27-May-2008
22-May-2008 4 28-May-2008
22-May-2008 9 04-June-2008 if any holiday on 4th June it should retrun 5th June.
Thanks for your help.
May 25, 2008 at 10:30 pm
Hi
It is better to maintain holiday table,with date and description.
Thanks
jaypee.s
May 25, 2008 at 11:23 pm
Hi
I agree with jaypee... Its much better to keep a holiday table. Its a flexible design and also the querying to find out the next working day will be easier.
"Keep Trying"
May 25, 2008 at 11:25 pm
Thanks for ur reply
Can you give some example
May 26, 2008 at 1:11 am
Hi,
to get the next working day after the immediate sat,sun you can use the following query:
Declare @dDate as datetime
set @dDate = '30 may 2008'
select case datepart(dw,cast(@dDate as datetime)+1)
when 1 then @dDate+2 when 7 then @dDate+3 else cast(@dDate+1 as varchar(50)) end
As far as getting the next working day after a holiday is concerened you need to fetch the holiday date from a holiday table wherein you have the holiday dates stored.
Hope this helps
Regards
Avaneesh Bajoria.
May 26, 2008 at 1:44 am
Hi,
I have modified from the previous post by including holidays. You need to create a holiday table with the holiday dates stored.
DECLARE @dDate AS DATETIME
DECLARE @sContinue AS CHAR(1)
SET @dDate = '30 May 2008'
SELECT @dDate = CASE DATEPART(DW,CAST(@dDate AS DATETIME) + 1)
WHEN 1 THEN @dDate+2
WHEN 7 THEN @dDate+3
ELSE CAST(@dDate+1 AS VARCHAR(50))
END
SET @sContinue = 'Y'
WHILE @sContinue = 'Y'
BEGIN
IF EXISTS (SELECT HOLIDAY_DATE FROM HOLIDAY_TABLE WHERE
CONVERT(VARCHAR(11),HOLIDAY_DATE, 106) = @dDate)
BEGIN
SELECT @dDate = CASE DATEPART(DW,CAST(@dDate AS DATETIME) + 1)
WHEN 1 THEN @dDate+2
WHEN 7 THEN @dDate+3
ELSE CAST(@dDate+1 AS VARCHAR(50))
END
END
ELSE
BEGIN
SET @sContinue = 'N'
END
END
PRINT CONVERT(VARCHAR(11),@dDate, 106)
Hope you find it helpful.
Regards,
Jasmin Tang
May 26, 2008 at 5:43 am
Hi
Jasmin's code looks good. A simple holiday table would have the following self-exlpanatory columns.
HolidayID - Identity column
HolidayDate - Datetime
Description.
You could go further and insert all the satrudays & sundays also in the table so then all you need to do is just check with the holiday table to know whether the date is a holiday.
You can also keep other details like whether a saturday was declared a working day if you want to extend the functionality.
"Keep Trying"
May 26, 2008 at 6:33 pm
Thanks for every one help
I resolved the issue using below
Declare @dt smalldatetime
declare @no tinyint
SET @dt = '23 May 2008'
SET @no = 2
While @no > 0
BEGIN
IF DatePart(dw,@dt) IN (6,7)
BEGIN
END
ELSE
BEGIN
IF EXISTS ( SELECT 1 FROM HOLIDAY WHERE HOLIDAY_DATE = @dt)
BEGIN
SET @no = @no - 1
END
ELSE
BEGIN
SET @no = @no - 1
END
END
END
SELECT @dt
May 26, 2008 at 8:53 pm
Calendars are a frequently asked question and an answer can be found at
SQL = Scarcely Qualifies as a Language
May 27, 2008 at 11:42 am
Chirag (5/26/2008)
A simple holiday table would have the following self-exlpanatory columns.HolidayID - Identity column
HolidayDate - Datetime
Description.
An identity column? Why? What purpose does it serve? Under what circumstances would you ever use it?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
May 27, 2008 at 5:13 pm
Numeric primary key... Holiday date can appear more than once depending on things like Country or State.
--Jeff Moden
May 28, 2008 at 3:07 am
Numeric primary key...
Yes, thank you, but I've known about Identity fields for, gee, several months now.
Holiday date can appear more than once depending on things like Country or State.
Oh, sorry. I didn't notice the Country or State fields in the table definition given.
OK, sarcasm aside, show me a query that would use that field -- a real-world query that I might expect to see in production code, even for a table with Country or State columns. For example, here is the body of a simplified function that would return the number of business days between two dates using the Holiday table.
declare @Result int;
select @Result = DateDiff( dd, @StartDate, @EndDate ) - Count(*)
from dbo.Holiday
where HolidayDate between @StartDate and @EndDate;
return @Result;
How could this be written in such a way as to use the Identity field? (BTW, I know the answer will be wrong if both dates are in the same weekend. This is for illustration only.)
My whole point is that the Identity field serves no purpose -- it cannot even be used. So why have it?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
May 28, 2008 at 6:29 am
Sarcasm forgiven... I know you just can't help it 😉
I absolutely agree that it will be mostly as static table and it will likely never be needed. But some shops have a "standard" that requires a numeric PK (clustered or not) in the form of an IDENTITY column and that the column name should be in the form of tablenameID. Doesn't hurt a thing and on larger, non-static tables, it doesn't take much room compared to the rest of the table. On those larger tables, it makes for nasty fast joins compared to some natural key and the "user" never sees it. They might even go further by having a "dual key"... a numeric PK and an UNIQUE index (alternate key). It does make for some nasty fast joins on the larger tables.
Having the IDENTITY column on this table is almost as useful as having an IDENTITY column on a State abbreviation table... it's not. But, to simplify their standard, they make these rules for all tables, even the smaller definition tables like a Holiday or State table. It really doesn't hurt anything and the "users" will never see it.
Personally, I'd rather see a shop error in this direction than to have no standard for table conventions or, worse, a standard that is so complicated that no one uses it.
--Jeff Moden
May 28, 2008 at 6:47 am
Tomm Carr (5/27/2008)
An identity column? Why? What purpose does it serve? Under what circumstances would you ever use it?
an Identity column is required for replication, this would be a good reason...
May 29, 2008 at 12:58 pm
Sarcasm forgiven... I know you just can't help it 😉
Yeah. I have to attend a Sarcasm Management class as part of the terms of my release.
I absolutely agree that it will be mostly as static table and it will likely never be needed. But some shops have a "standard" that requires a numeric PK (clustered or not) in the form of an IDENTITY column and that the column name should be in the form of tablenameID.
Unfortunately, that is true. However, for those of us fortunate enough not to have to work under such insanity, where this may be a practice but not a requirement, where reason still prevails, I bring a small ray of hope.
Doesn't hurt a thing and on larger, non-static tables, it doesn't take much room compared to the rest of the table.
It's funny, really. One of the most often used arguments against the use of composite keys is that it uses more space as a FK. Now you say that wasted space is acceptable as long as it is relatively small and yet no one takes you to task for that wasted space. Methinks there are some people around here that pick and choose their arguments to support their preconceived notions rather than try to find a Better Way.
Moreover, I disagree with the premise. The addition of autoincrement fields to rdbms's has been, overall, a most helpful feature. However, like most any feature, just because it is useful in some contexts doesn't mean it is useful in every context. That doesn't even apply to an RDBMS in general. Look at all the contortions we have to go through when working with data of a hierarchical nature.
Where it is helpful and the benefits outweigh the costs, a feature such as Identity fields should be used. When you make it ubiquitous, force it into all contexts, it is then used where it is not necessary, meaning there are no benefits -- only costs. Sometimes those costs can be quite large.
On those larger tables, it makes for nasty fast joins compared to some natural key and the "user" never sees it.
Define "nasty." Sure, a join using just numeric column can be a bit more efficient that some other data types. If you're having a problem with performance and using an Identity field brings you down to acceptable levels, go ahead and use it. You have increased the complexity of your design a bit, decreased the maintainability a bit, but at least you've gotten something in return. And don't forget the fact that the use of a natural key can eliminate some joins altogether. It doesn't matter how fast a "nasty fast" join is, it doesn't beat no join at all.
They might even go further by having a "dual key"... a numeric PK and an UNIQUE index (alternate key).
Might?!? Surely you jest! Anyone claiming any modicum of ability in relational design had better properly identity all natural keys and define them appropriately. There is nothing might about it -- it's an iron clad requirement.
This is one reason I don't use any Identity fields at all in my initial designs. I lay out the schema using only natural keys, test the design and only then go through and add Identity fields where they make sense. This forces me to make the effort (many times the tedious effort) to identity and define all the natural keys.
Personally, I'd rather see a shop error in this direction than to have no standard for table conventions...
Requiring an Identity key field on every table is not a standard. A naming convention is a standard. Defining where Identity fields are useful (and should be used) and where they are less than useful (and should not be used) is Best Practices. I can't think of one feature of an RDBMS (or any other tool for that matter) that maintains its usefulness in every context where it could be applied. Just because something is useful here doesn't mean it is useful there.
Someone said that an Identity key field is required for replication. I'm not a DBA, so I can't quote chapter and verse from the DBA Bible, but I do work with databases, and I do a lot of final testing using replicated databases. These replicated databases only have about 50% of the tables defined with Identity key fields (a figure I consider to be in the "normal" range). I've had no DBA approach me about any difficulties in the replication process and yet, obviously, the replication is getting done. So I have a feeling someone is trying to spit in my eye and call it rain. 😛
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply