Anyone done anything similar? Need Help!

• Rob Sawnic

  SSC Eights!

  Points: 940

  More actions

  March 9, 2004 at 12:35 pm

  #161698

  I need help on how to approach this.

  I have a table that contains trip

  information.

  ---table trip_information-----

  trip_start_date

  trip_end_date

  dest_start_date

  dest_end_date

  I have a 2nd table that contains

  holidays.

  ---table holidays_information-----

  holiday_date

  I have a 3rd table that contains

  exception days.

  ---table exception_days-----

  code......exception

  sa........every Saturday

  su........every Sunday

  ss........Saturday and Sunday

  What I'm trying to do is determine total drive time

  minus the exception days and holidays.

  For Example:

  If a car drove:

  trip_start_date = 1/1/00 00:00

  trip_end_date = 1/4/00 00:00

  dest_start_date = 1/5/00 12:00

  dest_end_date = 1/5/00 16:00

  I would have trip = 3 days and dest = 4 hrs.

  But I want to subtract the exception days and holidays.

  So for example, if my exception was Sunday and

  1/3/00 fell on a Sunday, I would subtract the time

  that I drove on Sunday from the 3 days and 4 hrs.

  Also, I would subtract the holidays if it were in that time period.

• Jonathan

  SSC-Insane

  Points: 20427

  More actions

  March 9, 2004 at 1:39 pm

  #498046

  There are lots of ways to do this. 

  • You could have your weekend table populated with a row for each weekend day and then just subtract your start date from your end date and then subtract the count of rows in each table that fall between those two dates.
  • You could use a numbers table and join that to create a derived table with a row for each date between the start and end dates.  Then exclude and subtract those rows that meet your exception criteria and count what's left.
  • You could write CASE expressions with formulas to account for the weekends and then subtract the count of holidays between the start and end dates.  That's what I do.

  
  
  --Jonathan