An interesting task
-
I have a table named TB_HOLIDAY in my database. This table contains the following columns
Column NameData TypeVALUE
YEARINT2003
JANVARCHAR1101100000110000011000001100000
FEBVARCHAR1100000110000011000001100000222
MARVARCHAR1100000110000011000001100000110
APRVARCHAR0000110000011000011110000110002
MAYVARCHAR1011000001100000110000011000101
JUNVARCHAR1000001110000110000011000001102
JULVARCHAR0000110000011000001100000110000
AUGVARCHAR1110000011000001100000110000011
SEPVARCHAR0000011000001100000110000011002
OCTVARCHAR0001100000110000011000001100000
NOVVARCHAR1100000110000011000001100000112
DECVARCHAR0000011000001100000110001111000
The data values represent 1 for a holiday and 0 for a normal working day.
My task is to calculate the number of working days from a given date to another date.
There could be records for more than 1 year.
Every 1 in the value represents a non-working day. Every 0 represents a working day. Every 2 represents a dummy value for a day which is not present in that particular month. Every value contains 31 characters. So if a particular month has 30 days, then the 31st value is represented by a 2.
This has to be a stored procedure.
This is what I have written so far.
Declare @Occurrence int
Declare @lBusinessDays int
Declare @lDaysDiff int
Declare @lBalMonth varchar(1000)
Declare @lBMonth varchar(3)
Declare @lEMonth varchar(3)
Declare @lSQLStat2 varchar(4000)
Declare @lSQLStat1 varchar(4000)
Declare @iStartDate datetime
Declare @iEndDate datetime
Declare @lMonth1Values varchar(31)
Declare @lMonth2Values varchar(31)
Declare @lBYear int
Declare @lEYear int
Declare @lBDay int
Declare @lEDay int
Declare @lValue varchar(31)
Declare @Month1Length int
Declare @Month2Length int
Declare @lDays int
Declare @lFirstDay int
Declare @dummymonth varchar(2)
Declare @dummyday varchar(2)
Declare @dummyyear varchar(4)
Declare @dummydate datetime
Declare @lBalMonths int
Declare @lBalYears int
Declare @lDiffMonth int
Set @iStartDate = '01-Aug-2003'
Set @iEndDate = '15-Sep-2003'
Set @lBYear = DatePart(yyyy,@iStartDate) --Starting Year
Set @lEYear = DatePart(yyyy,@iEndDate) --Ending Year
Set @lBMonth = SUBSTRING(DateName(mm,@iStartDate),1,3) --Starting Date Month
Set @lEMonth = SUBSTRING(DateName(mm,@iEndDate),1,3) --Ending Date Month
Set @lBDay = DatePart(dd,@iStartDate)
Set @lEDay = DatePart(dd,@iEndDate)
Set @lDaysDiff = DateDiff(dd,@iStartDate,@iEndDate)
Set @dummyyear = Convert(varchar,@lBYear)
Set @dummyday = Convert(varchar,@lEDay)
Set @lDiffMonth = DateDiff(mm,@iStartDate,@iEndDate)
Print 'The number of days difference is ' + Convert(varchar,@lDaysDiff)
If (@lEMonth = @lBMonth) And (@lEYear = @lBYear) --The start / end dates lie in the same month of the same year
Begin
Print 'Same Month Same Year. Easiest Option.'
Set @lSqlStat1 = 'Declare CurMonths1 CURSOR FOR SELECT ' + Upper(@lBMonth)
+ ' FROM TB_HOLIDAY WHERE Year = ' + Convert(varchar,@lBYear)
Exec (@lSqlStat1)
Set @lDays = 0--Initialize the days to 0
OPEN CurMonths1
FETCH NEXT FROM CurMonths1 INTO @lMonth1Values
WHILE @@FETCH_STATUS = 0
BEGIN
Set @lValue = SUBSTRING(@lMonth1Values,@lBDay,@lEDay)
Print 'String Passed To ' + @lValue
Exec OF_SP_COUNTER @lValue,'1',@Occurrence OUTPUT
FETCH NEXT FROM CurMonths1 INTO @lMonth1Values
END
CLOSE CurMonths1
DEALLOCATE CurMonths1
Set @lBusinessDays = @lDaysDiff - @Occurrence
Print 'No of Working Days' + Convert(varchar,@lBusinessDays)
Return
End
If (@lEYear = @lBYear) --Same Year
Begin
Print 'Same Year.'
Set @lSqlStat1 = 'Declare CurMonths1 CURSOR FOR SELECT ' + Upper(@lBMonth)
+ ' FROM TB_HOLIDAY WHERE Year = ' + Convert(varchar,@lBYear)
Exec (@lSqlStat1)
Set @lDays = 0--Initialize the days to 0
Set @lFirstDay = 1 --First Day of the month is always 1
OPEN CurMonths1
FETCH NEXT FROM CurMonths1 INTO @lMonth1Values
While @@FETCH_STATUS = 0
BEGIN
PRINT @lMonth1Values + '~~'
Set @Month1Length = Len(@lMonth1Values)
Set @lValue = SUBSTRING(@lMonth1Values,@lBDay,Len(@lMonth1Values))
Exec OF_SP_COUNTER @lValue,'1',@Occurrence OUTPUT
Set @lDays = @lDays + @Occurrence
FETCH NEXT FROM CurMonths1 INTO @lMonth1Values
END
Print 'No of Days in the first month ' + Convert(varchar,@lDays)
CLOSE CurMonths1
DEALLOCATE CurMonths1
--Now for the Ending month
Set @lSqlStat2 = 'Declare CurMonths2 CURSOR FOR SELECT ' + Upper(@lEMonth)
+ ' FROM TB_HOLIDAY WHERE Year = ' + Convert(varchar,@lEYear)
Exec (@lSqlStat2)
OPEN CurMonths2
FETCH NEXT FROM CurMonths2 INTO @lMonth2Values
While @@FETCH_STATUS = 0
BEGIN
--PRINT @lMonth2Values + '~Month 2 Values'
Set @Month2Length = Len(@lMonth2Values)
WHILE @lFirstDay <= @lEday /* Increment the first day till the End Day */
BEGIN
--Print 'End Day ' + Convert(Varchar,@lEDay)
Set @lValue = SUBSTRING(@lMonth2Values,@lFirstDay,@lEDay)
--PRINT 'Got The Value --> ' + @lValue
If SUBSTRING(@lValue,1,1) = '1'
BEGIN
Set @lDays = @lDays + 1 /* Increment for 1 found */
END
Set @lFirstDay = @lFirstDay + 1
END
FETCH NEXT FROM CurMonths2 INTO @lMonth2Values
END
CLOSE CurMonths2
DEALLOCATE CurMonths2
Set @lBalMonths = DateDiff(mm,@iStartdate,@iEndDate)
Print 'Balance Months -- ' +Convert(varchar,@lBalMonths)
Set @lBalMonths = @lBalMonths - 1 --Subtract Beginning Month
Set @lSqlStat1 = 'SELECT '
if @lBalMonths >= 2 --Check if gap is more than or equal to 2 months
Begin --Check if the balance months are more than 2
Print 'Gap is more than or equal to 2 months. Same Year.' + Convert(varchar,@lBalMonths)
Set @lSqlStat2 = '' --Nullify the string
While @lBalMonths != 0
Begin
--1 Added For Compensating the ending month
Set @dummymonth = Convert(varchar,@lBalMonths + Datepart(mm,@iStartDate))
--Adding 2 is essential to ensure counting from correct date
Set @dummydate = Convert(datetime,@dummyday + '/'+ @dummymonth + '/' + @dummyyear,103)
Set @lSqlStat2 = @lSqlStat2 + UPPER(SUBSTRING(DateName(mm,@dummydate),1,3)) + '+'
Set @lBalMonths = @lBalMonths - 1
Print '@lBalMonths - ' + Convert(varchar,@lBalMonths)
End
--End Of String Comma Removal
Set @lSqlStat2 = SUBSTRING(@lSqlStat2,1,Len(@lSqlStat2)-1)
Set @lSqlStat1 = @lSqlStat1 + @lSqlStat2 + ' FROM TB_HOLIDAY WHERE YEAR = '
+ Convert(varchar,@lEYear)
Print @lSqlStat1
Set @lSqlStat1 = 'Declare BalMonths Cursor FOR ' + @lSqlStat1
Exec (@lSqlStat1)
Open BalMonths
FETCH NEXT FROM BalMonths INTO @lBalMonth
WHILE @@FETCH_STATUS = 0
BEGIN
Exec OF_SP_COUNTER @lBalMonth,'1',@Occurrence OUTPUT
Set @lDays = @lDays + @Occurrence
FETCH NEXT FROM BalMonths INTO @lBalMonth
END
CLOSE BalMonths
DEALLOCATE BalMonths
End --If gap is 2 or more months
Else --If gap is only 1 month
Begin
Print 'Gap is equal to 1 month. Same Year.'
Set @lSqlStat2 = '' --Nullify the string
Set @dummyday = 1
Set @dummyyear = @lEYear
Set @dummymonth = Convert(varchar,@lBMonth) --Next Month Should be after the current month
Set @dummydate = Convert(datetime,@dummyday + '/'+ @dummymonth + '/' + @dummyyear,103)
Set @lSqlStat2 = @lSqlStat2 + UPPER(SUBSTRING(DateName(mm,@dummydate),1,3)) + '+'
--End Of String Comma Removal
Set @lSqlStat2 = SUBSTRING(@lSqlStat2,1,Len(@lSqlStat2)-1)
Set @lSqlStat1 = @lSqlStat1 + @lSqlStat2 + ' FROM TB_HOLIDAY WHERE YEAR = '
+ Convert(varchar,@lEYear)
Print @lSqlStat1
Set @lSqlStat1 = 'Declare BalMonths Cursor FOR ' + @lSqlStat1
Exec (@lSqlStat1)
Open BalMonths
FETCH NEXT FROM BalMonths INTO @lBalMonth
WHILE @@FETCH_STATUS = 0
BEGIN
Exec OF_SP_COUNTER @lBalMonth,'1',@Occurrence OUTPUT
Set @lDays = @lDays + @Occurrence
FETCH NEXT FROM BalMonths INTO @lBalMonth
END
CLOSE BalMonths
DEALLOCATE BalMonths
End
End --Same Year / Different Months
-
Is there a question you are asking? Not quite sure what you're looking for...Are you asking whether there is a simpler way of determining the number of days between a holiday?
One question...
Why are you storing the values in a varchar datatype? Why not store the dates in a smalldatetime field? You could structure the table like so:
CREATE TABLE TB_HOLIDAY
(dteHoliday SMALLDATETIME NOT NULL)
, then populate it with the actual dates of the holidays. It seems to me with this structure it would be easier to do DATE arithmetic. To solve the problem of the non-working days, you could either make another table to cross-reference in a query that comprises the non-working days, or use the day of week in the DATE arithmetic to filter out Saturdays and Sundays.
Hope I'm on the right track. Let me know...
-
Well my friend, the reason the dates are stored the way they are stored, is L E G A C Y
I do not have an interface to insert the holidays and for the user it would mean just another maintainence job. I am not authorised to do any further changes. However I could probably write a trigger which would populate the holiday dates in another table. That is certainly possible.
The existing table TB_HOLIDAY is used by a lot of VB programs, so it would not be possible to make any changes to it.
I want to know what I have written is going to work for all possible cases where the year is the same.
Cheers,
Abhijit
-
Here is another way to do it which looks a bit simpler.
declare @StartDate datetime,
@EndDate datetime
set @StartDate = '20030115'
set @EndDate = '20030120'
declare@Year int,
@Calender char(372),
@PreDays int,
@PostDays int,
@WorkingDays int,
@NonWorkingDays int,
@DummyDays int,
@Counter int,
@Day char(1)
set @WorkingDays = 0
set @NonWorkingDays = 0
set @DummyDays = 0
declare calender cursor local for
selectYear,
Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec
fromTB_Holiday
whereYear between datepart(yy, @StartDate) and datepart(yy, @EndDate)
open calender
fetch next from calender into @Year, @Calender
while @@fetch_status = 0
begin
if datepart(yy, @StartDate) = @Year
begin
-- trim preceeding days from @Calender
set @PreDays = ((datepart(mm, @StartDate) - 1) * 31) + (datepart(dd, @StartDate) - 1)
set @Calender = right(@Calender, len(@Calender) - @PreDays)
end
if datepart(yy, @EndDate) = @Year
begin
-- trim post days from @Calender
set @PostDays = ((12 - datepart(mm, @EndDate)) * 31) + (31 - datepart(dd, @EndDate))
set @Calender = left(@Calender, len(@Calender) - @PostDays)
end
set @Counter = 1
while @Counter < len(@Calender)
begin
set @Day = substring(@Calender, @Counter, 1)
if @Day = '0'
set @WorkingDays = @WorkingDays + 1
if @Day = '1'
set @NonWorkingDays = @NonWorkingDays + 1
if @Day = '2'
set @DummyDays = @DummyDays + 1
set @Counter = @Counter + 1
end
fetch next from calender into @Year, @Calender
end
close calender
deallocate calender
select@WorkingDays as WorkingDays,
@NonWorkingDays as NonWorkingDays,
@DummyDays as DummyDays
-
Paul's script does indeed look simpler, however, I don't see any OF_SP_COUNTER call in it. Is this procedure essential, or can you add it to Paul's script...
-
Thanks a lot Paul, I am still testing out that script.
OF_SP_COUNTER is a generic procedure which counts for the occurrence of a character in a particular string. For e.g -'JPIPES','P' will return 2 since there are 2 P's in that string. We use it for a lot of stuff. However its not essential over here.
Cheers!
Abhijit
-
well0549 your solution will only work for SQL2000 and I have no idea which version abhi_develops is currently using.
Everyone seems to be knocking how this data is stored but thinking about it can anyone else think of a better way to stored information for every day of the year using a total of only 376 bytes? You could substitute the 0,1,2 for any alphanumeric which gives you the ability of storing any number of single statuses for days of the year. (This one will probably open a can of worms!)
-
Paul,
be creative...
If a memtable doesn't work.....
Use a temp table.....
Solution is still the best......
-
.....if a temp table, just look at all that IO!!!
-
Minor correction to my code, the line below....
while @Counter < len(@Calender)
...should read......
while @Counter <= len(@Calender)
....sorry abhi_develops
-
Paul,
A couple of commets on your assumptions.....
We don't know if the poster of this thread uses 7. If he uses 2000, I think my solution is the best yet, because it is short and easy to maintain.
Even if he has SQL 7 we don't know what kind of harddisk he has. And all that IO ... Is this really true....(Is nothing bufferd somewhere.....) I think the server would write a few pages in Tempdb. (Remember that it is not much data !!!!!!!!) ( I could be wrong here but don't think so...... )
Furthermore, Please let the poster decide if something works or not.....
-
well0549,
I do not believe that I said your code would not work. The reason I made the comment regarding SQL2000 was that abhi_develops's initial post states that this has to be a stored procedure (this could imply that functions (i.e. SQL2000) is not available to him.
I have also carried out some comparisions on one of our development servers to compare your code and my own.
Your code (memtable) took 2443ms.
Your code (temp table) took 3866ms.
My code (as post) took 10ms.
Please feel free to compare them for yourself, I think that all the code you need is in the posts here.
-
The performance of my code could be further improved by using a STATIC cursor. This performance gain would be most evident when the date ranges spanned multiple years since all the data for the cursor is returned in a single operation instead of each FETCH.
BTW I know I spelt calendar wrong in my code....WHOOPS!
-
What about this option? Is this a simple, easy to follow method? The only problem with this solution is it will only handle a span of 21 years between start and end date.
SET NOCOUNT ON
drop table holiday
go
create TABLE holiday
(YEAR INT,
JAN VARCHAR(31),
FEB VARCHAR(31),
MAR VARCHAR(31),
APR VARCHAR(31),
MAY VARCHAR(31),
JUN VARCHAR(31),
JUL VARCHAR(31),
AUG VARCHAR(31),
SEP VARCHAR(31),
OCT VARCHAR(31),
NOV VARCHAR(31),
DEC VARCHAR(31))
-- STUFF SOME DATA IN IT ........
INSERT INTO HOLIDAY VALUES
(2003,'1101100000110000011000001100000',
'1100000110000011000001100000222',
'1100000110000011000001100000110',
'0000110000011000011110000110002',
'1011000001100000110000011000101',
'1000001110000110000011000001102',
'0000110000011000001100000110000',
'1110000011000001100000110000011',
'0000011000001100000110000011002',
'0001100000110000011000001100000',
'1100000110000011000001100000112',
'0000011000001100000110001111000')
INSERT INTO HOLIDAY VALUES
(2002,'1101100000110000011000001100000',
'1100000110000011000001100000222',
'1100000110000011000001100000110',
'0000110000011000011110000110002',
'1011000001100000110000011000101',
'1000001110000110000011000001102',
'0000110000011000001100000110000',
'1110000011000001100000110000011',
'0000011000001100000110000011002',
'0001100000110000011000001100000',
'1100000110000011000001100000112',
'0000011000001100000110001111000')
INSERT INTO HOLIDAY VALUES
(2001,'1101100000110000011000001100000',
'1100000110000011000001100000222',
'1100000110000011000001100000110',
'0000110000011000011110000110002',
'1011000001100000110000011000101',
'1000001110000110000011000001102',
'0000110000011000001100000110000',
'1110000011000001100000110000011',
'0000011000001100000110000011002',
'0001100000110000011000001100000',
'1100000110000011000001100000112',
'0000011000001100000110001111000')
declare @dt1 datetime
declare @dt2 datetime
declare @m1 int
declare @d1 int
declare @y1 int
declare @m2 int
declare @d2 int
declare @y2 int
declare @h1 char(8000)
-- start date
set @dt1 = '01/01/2001'
-- end date
set @dt2 = '01/01/2003'
set @m1 = datepart(mm,@dt1)
set @y1 = datepart(yy,@dt1)
set @d1 = datepart(dd,@dt1)
set @m2 = datepart(mm,@dt2)
set @y2 = datepart(yy,@dt2)
set @d2 = datepart(dd,@dt2)
set @h1 = ''
-- put together all years
select @h1 = rtrim(@h1) + jan+feb+mar+apr+may+jun+jul+aug+sep+oct+nov+dec from holiday
where year >= @y1 and year <= @y2
-- truncate beginning and end of holidays based on start and end date
set @h1=substring(@h1,(@m1-1)*31+@d1,(((@y2-@y1)* 372)+(@m2-1)*31+@d2)-((@m1-1)*31+@d1)+1)
-- remove non-work dayse
set @h1=replace(replace(@h1,'1',''),'2','')
print 'The number of work days is: ' + cast(len(@h1) as cha
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Commenting on paul.....
I don't know what kind of server you have but on my laptop.....
for a 10 year period I came to 120 for yours and 450 for mine...
so approx 4 times faster. But I don't get the big difference
you got.....
But I think Gregs solution is the best.....
( simple fast )
And because of that I removed my solution....
Edited by - well0549 on 02/11/2003 09:21:10 AM
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply