Normalizing a table

I have a deprecated table that I cannot change. The values ​​in it can be changed from the legacy application (the application also cannot be changed). Due to the large table access from a new application (new requirement) I would like to create a temporary table that will hopefully speed up queries.

The actual requirement is to calculate the number of business days from X to Y. For example, give me all business days from January 1, 2001 to December 24, 2004. The table is used to denote which days are off as different companies may have different days off - it's not just Saturday + Sunday)

A temporary table will be created from a .NET program, every time the user enters the screen for this query (user can run the query multiple times, with different values, the table is created once), so I would like it to be as fast as possible. The approach below works in less than a second, but I've only tested it with a small dataset, and yet it takes probably almost half a second, which is not very user-friendly for the UI - although it's just an overhead for the first request.

The inherited table looks like this:

CREATE TABLE [business_days](
    [country_code] [char](3) ,
    [state_code] [varchar](4) ,
    [calendar_year] [int] ,
    [calendar_month] [varchar](31) ,
    [calendar_month2] [varchar](31) ,
    [calendar_month3] [varchar](31) ,
    [calendar_month4] [varchar](31) ,
    [calendar_month5] [varchar](31) ,
    [calendar_month6] [varchar](31) ,
    [calendar_month7] [varchar](31) ,
    [calendar_month8] [varchar](31) ,
    [calendar_month9] [varchar](31) ,
    [calendar_month10] [varchar](31) ,
    [calendar_month11] [varchar](31) ,
    [calendar_month12] [varchar](31) ,
misc.
)

      

        
        
        
      

    

Each month has 31 characters and any weekend (Saturday + Sunday + holiday) is marked with an X. Every half day is marked with an "H". For example, if the month starts on Thursday it will look like (Thursday + Friday, Saturday + Sunday marked with an X):

'  XX     XX ..'

      

        
        
        
      

    

I would like the new table to look like this:

create table #Temp (country varchar(3), state varchar(4), date datetime, hours int)

      

        
        
        
      

    

And I would like to only have rows for a few days that are disabled (marked with X or H from a previous request)

What I have done so far is this: Create a temporary staging table that looks like this:

create table #Temp_2 (country_code varchar(3), state_code varchar(4), calendar_year int, calendar_month varchar(31), month_code int)

      

        
        
        
      

    

To fill it, I have a union that basically combines calendar_month, calendar_month2, calendar_month3, etc.

Than I have a loop that goes through all the lines in # Temp_2, after each line is processed, it is removed from # Temp_2. There is a loop from 1 to 31 to process the string, and the substring (calendar_month, counter, 1) is checked for X or H, in which case there is an insert into the #Temp table. [edit added code]

Declare @country_code char(3)
Declare @state_code varchar(4)
Declare @calendar_year int
Declare @calendar_month varchar(31)
Declare @month_code int
Declare @calendar_date datetime
Declare @day_code int
WHILE EXISTS(SELECT * From #Temp_2) -- where processed = 0)
BEGIN
    Select Top 1 @country_code = t2.country_code, @state_code = t2.state_code, @calendar_year = t2.calendar_year, @calendar_month = t2.calendar_month, @month_code = t2.month_code From #Temp_2 t2 -- where processed = 0

    set @day_code = 1
    while @day_code <= 31
    begin
        if substring(@calendar_month, @day_code, 1) = 'X'
        begin
            set @calendar_date = convert(datetime, (cast(@month_code as varchar) + '/' + cast(@day_code as varchar) + '/' + cast(@calendar_year as varchar)))
            insert into #Temp (country, state, date, hours) values (@country_code, @state_code, @calendar_date, 8)
        end
        if substring(@calendar_month, @day_code, 1) = 'H'
        begin
            set @calendar_date = convert(datetime, (cast(@month_code as varchar) + '/' + cast(@day_code as varchar) + '/' + cast(@calendar_year as varchar)))
            insert into #Temp (country, state, date, hours) values (@country_code, @state_code, @calendar_date, 4)
        end

        set @day_code = @day_code + 1
    end
    delete from #Temp_2 where @country_code = country_code AND @state_code = state_code AND @calendar_year = calendar_year AND @calendar_month = calendar_month AND @month_code = month_code
    --update #Temp_2 set processed = 1 where @country_code = country_code AND @state_code = state_code AND @calendar_year = calendar_year AND @calendar_month = calendar_month AND @month_code = month_code
END

      

        
        
        
      

    

I am not an expert in SQL, so I would like to get some input on my approach and maybe even a much better suggestion.

After the temp table I am planning to do (dates will come from the table):

select cast(convert(datetime, ('01/31/2012'), 101) -convert(datetime, ('01/17/2012'), 101) as int) -  ((select sum(hours) from #Temp where date between convert(datetime, ('01/17/2012'), 101) and convert(datetime, ('01/31/2012'), 101)) / 8)

      

        
        
        
      

    

Aside from the table normalization solution, the other solution I have implemented so far is a function that does all this logic of getting weekdays by scanning the current table. It works pretty fast, but I hesitate to call the function if I can instead add a simpler query to get the result.

(I am currently trying to do this in MSSQL, but I will need to do the same for Sybase ASE and Oracle)