HeadAche.... ARRRGGHHH

I have a BIG problem...<br>I am really stuck on how to achieve this for aa hotel booking application.<br><br>Here is a rate card for the hotel:-<br><br>Jan 8th - March 31st<br> Daily Rate = 19.00<br> Week Rate = 125.00<br>April 1st - June 30th<br> Daily Rate = 20.00<br> Week Rate = 130.00<br>July 1st - Sept. 17th<br> Daily Rate = 22.00<br> Week Rate = 140.00<br>Sept 18th - Dec 3rd<br> Daily Rate = 20.00<br> Week Rate = 140.00<br>Dec 4th - Jan 7th<br> Daily Rate = 21.00<br> Week Rate = 135.00<br><br>If someone stays for 10 days, they are charged a week rate and then 3 day rates... should someone stay for 14 days, they are charged 2 week rates, etc.<br><br>If someone wants to stay 1 week and that stay overlaps rate periods ie, from dec 1st then they are charged the week rate as it is on their arrival day. If they were staying for 2 weeks from dec 1st, then the 1st week would be at one rate and the second week at a different rate.<br><br>If someone was staying for 10 days for instance from dec 1st, then they would be charged 1 week @ 140.00 and 3 days at 21.00.<br><br>I hope the above is understandable.<br><br>If I know the start date and the end date, how the hell can work out the cost??<br><br>Any help would sure save me from buying more valium!!<br><br>Rob

Telll you what, go get the valium and send it to me. Then I'll do it for you myself.<br>seriously though,<br>Use the .NEt date functions. If you dont' understand an elegant way, just walk in loops of seven adding one day to the date and comparing it to the departure date.<br>You need<br><br>OverallTotal<br>WeeklyTotal<br> loop through adding the daily rate to weekly total, if loop counter mod 7 = 0 then set weeklyTotal= WeeklyRate, and add that to the Overall total. loop until date=DepartureDate. <br><br>Get it?

I sorted it finally!!! hooray....<br><br>here's how:-<br><br>Dim Reader As SqlClient.SqlDataReader<br> Dim iDays, iWeeks, BBDayRate, BBWeekRate As Integer<br> Dim dtarrive As Date = ArrivalDate.SelectedDate<br> Dim dtleave As Date = CDate(ArrivalDate.SelectedDate).AddDays(NumberofNi ghts.SelectedValue - 1)<br> Dim HBWeekRate, HBDayRate As Integer<br> iBB.Text = 0<br> ihb.Text = 0<br> Dim count As Integer<br> SqlCommand.CommandText = "Select basisid, basistext From RoomBasis where HotelID = " & HotelID<br> SqlConn.Open()<br> Reader = SqlCommand.ExecuteReader<br> RoomBasis.Items.Clear()<br> Dim tmpItem As New ListItem(">>")<br> tmpItem.Value = ""<br> RoomBasis.Items.Add(tmpItem)<br> While Reader.Read<br> Dim item As New ListItem<br> item.Value = Reader(0)<br> item.Text = Reader(1)<br> RoomBasis.Items.Add(item)<br> End While<br> Reader.Close()<br> SqlConn.Close()<br> iWeeks = Int(DateDiff(DateInterval.Day, dtarrive, dtleave) / 7) ' No of full weeks<br> iDays = (DateDiff(DateInterval.Day, dtarrive, dtleave)) - (iWeeks * 7) + 1<br> If iDays = 7 Then<br> iDays = 0<br> iWeeks += 1<br> End If<br> Dim flag As Boolean = True<br> Dim tmpDate As Date = dtarrive<br> SqlConn.Open()<br> While iWeeks > 0<br> Dim Lookup_date As New SqlClient.SqlParameter("@Lookup_date", tmpDate.ToLongDateString)<br> Lookup_date.SqlDbType = SqlDbType.SmallDateTime<br> Dim hotel_ID As New SqlClient.SqlParameter("@Hotel_ID", HotelID)<br> hotel_ID.SqlDbType = SqlDbType.Int<br> SqlCommand.CommandType = CommandType.StoredProcedure<br> SqlCommand.Parameters.Clear()<br> SqlCommand.CommandText = "GetPrice"<br> SqlCommand.Parameters.Add(Lookup_date)<br> SqlCommand.Parameters.Add(hotel_ID)<br> Reader = SqlCommand.ExecuteReader<br> Reader.Read()<br> iBB.Text = CInt(iBB.Text) + CInt(Reader("bbweekrate"))<br> ihb.Text = CInt(ihb.Text) + CInt(Reader("hbweekrate"))<br> Reader.Close()<br> SqlCommand.Dispose()<br> tmpDate = tmpDate.AddDays(7)<br> iWeeks = (iWeeks - 1)<br> End While<br> While tmpDate <= dtleave<br> Dim Lookup_date As New SqlClient.SqlParameter("@Lookup_date", tmpDate.ToLongDateString)<br> Lookup_date.SqlDbType = SqlDbType.SmallDateTime<br> Dim hotel_ID As New SqlClient.SqlParameter("@Hotel_ID", HotelID)<br> hotel_ID.SqlDbType = SqlDbType.Int<br> SqlCommand.CommandType = CommandType.StoredProcedure<br> SqlCommand.Parameters.Clear()<br> SqlCommand.CommandText = "GetPrice"<br> SqlCommand.Parameters.Add(Lookup_date)<br> SqlCommand.Parameters.Add(hotel_ID)<br> Reader = SqlCommand.ExecuteReader<br> Reader.Read()<br> iBB.Text = CInt(iBB.Text) + CInt(Reader("bbdayrate"))<br> ihb.Text = CInt(ihb.Text) + CInt(Reader("hbdayrate"))<br> Reader.Close()<br> SqlCommand.Dispose()<br> tmpDate = tmpDate.AddDays(1)<br> End While<br> SqlConn.Close()<br> Page6.Visible = False<br> Page7.Visible = True