SQL Server Performance

T-SQL Date function or SP

Discussion in 'Getting Started' started by Freddy_09, Jan 4, 2009.

  1. Freddy_09 New Member

    Hi!
    I am working on a flight reservation system with SQL Server 2008 and need help on the following: A stored procedure or function is required to insert up to 365 rows (dates), resp. 366 in case of a leap year into the flight instance table. The base flight table holds the flight days (as weekday no.), e.g.: If flight no. 3520 flies on Mondays, Fridays and Sundays, then the frequency would be stored like: 157 (1=Monday, 2=Tuesday, 3=Wednesday etc). So, the SP or function must first check the base flight table and insert the rows(dates) into the flight instance table accordingly.
    Flight table
    Flight_no, Frequency
    3520 157
    Flight Instance table
    Flight_no Flight_Date
    3520 4/1/2008
    3520 5/1/2008
    3520 7/1/2008
    ....
    Any ideas or scripts would be greatly appreciated!
    Thanks!
  2. FrankKalis Moderator

    First of all, I think the way that you want to store the "frequency" is wrong. This should rather be in its own separate table, imho. Second, for this type of problems a date helper table is very helpful. Using both your problem can easily be solved, like:
    DECLARE @date TABLE (thedate smalldatetime, dow tinyint);
    DECLARE @flight TABLE (flight_no int);
    DECLARE @flight_frequency TABLE (flight_no int, frequency tinyint);
    DECLARE @flight_instance TABLE(flight_no int, flight_date smalldatetime);

    DECLARE @StartDate smalldatetime;
    SELECT @StartDate = '2007-12-31';

    INSERT INTO @date
    SELECT
    DATEADD(DAY, Number, @StartDate), DATEPART(weekday, DATEADD(DAY, Number, @StartDate))
    FROM
    dbo.Number N
    WHERE
    N.Number BETWEEN 0 AND 366;

    INSERT INTO @flight SELECT 3520;

    INSERT INTO @flight_frequency SELECT 3520, 1
    UNION ALL SELECT 3520, 5
    UNION ALL SELECT 3520, 7;

    INSERT INTO @flight_instance
    SELECT
    FF.flight_no,
    DT.thedate
    FROM
    @flight F
    JOIN
    @flight_frequency FF ON F.flight_no = FF.flight_no
    JOIN
    @date DT ON DT.dow = FF.frequency
    WHERE
    DT.thedate BETWEEN @StartDate AND DATEADD(YEAR, 1, @StartDate) - 1;

    SELECT
    *
    FROM
    @flight_instance
    ORDER BY
    flight_date;
    If you can not change the schema any longer, I would create a table-valued UDF to split the frequency value into its components. In that case the JOIN to populate the flight_instance table would look something like this:
    INSERT INTO @flight_instance
    SELECT
    FF.flight_no,
    DT.thedate
    FROM
    @flight F
    JOIN
    @date DT ON DT.dow = <your function TO split goes here>
    WHERE
    DT.thedate BETWEEN @StartDate AND DATEADD(YEAR, 1, @StartDate) - 1;


  3. Freddy_09 New Member

    Hi Frank!
    Thanks for the script. One small problem:
    DATEADD(DAY, Number, @StartDate), DATEPART(weekday, DATEADD(DAY, Number, @StartDate))
    FROM
    dbo.Number N

    Number is an invalid columndbo
    .Number N is an invalid object name
  4. FrankKalis Moderator

    Oops, sorry. dbo.Number is my internal numeric helper table. It is a table with just one integer column and the sequence from 0 to n. Just like a date table this is extremely useful. Anyway this table is just used to populate this date table. You don't really need it. You could basically use any technique to populate the date table.
  5. Freddy_09 New Member

    OK, but I'd like to test the script. I understand that this table is helpful and I would like to use it. Should the table be created first?
  6. FrankKalis Moderator

    Yes, that makes sense.
  7. Freddy_09 New Member

    So what are the next steps?
  8. FrankKalis Moderator

    Create the date and the number helper tables and then you should be able to run the script. Depending on if you can remodel the frequency attribute you to use either the first or the second suggestion then. That should be it.

Share This Page