dateStamp loop

[onressy]

Hi and thanks for reading this. i have a table that has seasonal rate dates for a particular property, RateStartDate and RateEndDate. The user can assign many seasonal rates and set the date range for each, setting the RateStartDate and RateEndDate. The table also contains day of week columns "Sun", "Mon", ... till "Sat"
The user has the option to set a rate for these days.

For every new seasonal rate date range i'm creating a new xml rate node.

My issue is that i can't seem to visualize how to iterate thru the RateStartDate and RateEndDate to set the rate node
price for the week days that are in the RateStartDate to RateEndDate range.

this is the train of thought i can so far precieve:

strSQL = "Select * From Rate Where RateGroupID=" & strRateGroupID
Set objRS = DbExecuteReturn(strSQL)

rateNode=" "

If NOT objRS.EOF Then
  If objRS("Sun") > 0 Then
   Dim rStartDate, rEndDate
  	Do While objRS("Sun") > 0
	rStartDate = WeekdayName(Weekday(objRS("RateStartDate")))
	rEndDate = WeekdayName(Weekday(objRS("RateEndDate")))
	
	rateNode = rateNode & encodeXML("<rate start=""" & rStartDate & """ stop=""" & rEndDate & """ price=""" & objRS("RateValue") & """ />")
	
	objRS.MoveNext
	Loop
  Else
  ....
  End If

Else
...
End If

 

[onressy]

Example: if RateStartDate = 15-09-2006 and RateEndDate=15-10-2006
and lets say "wed" rate is 200 and "sat" rate is 210 and the default cost if no week day rate is set is: 150, how would i seperate the rates into their respective time ranges

i'm creating a new xml rate node for every rate different in its timeStamp

hope this makes sense?

 

[onressy]

this is what i thought of so far:

this is kinda what i'm thinking but my thinking does not seperate the rate node into the specific time ranges specific for that rate, nor does it determine the days the week rate do not apply and set the "Cost" value for those ranges............?[ X(]

strSQL = "Select * From Rate Where RateGroupID=" & strRateGroupID
Set objRS = DbExecuteReturn(strSQL)

rateNode=" "

If NOT objRS.EOF Then
  If objRS("Sun") > 0 Then
   Dim rStartDate, rEndDate
      Do While objRS("Sun") > 0
    rStartDate = WeekdayName(Weekday(objRS("RateStartDate")))
    rEndDate = WeekdayName(Weekday(objRS("RateEndDate")))
' if the range from rStartDate to rEndDate includes sunday then keep in mind what date ranges are left from the main rStartDate to rEndDate range
    
    rateNode = rateNode & encodeXML("<rate start=""" & rStartDate & """ stop=""" & rEndDate & """ price=""" & objRS("Sun") & """ />")
    
    objRS.MoveNext
    Loop

  ElseIf objRS("Mon") > 0 Then
      Do While objRS("Mon") > 0
    rStartDate = WeekdayName(Weekday(objRS("RateStartDate")))
    rEndDate = WeekdayName(Weekday(objRS("RateEndDate")))
' if the range from rStartDate to rEndDate includes monday then keep in mind what date ranges are left from the main rStartDate to rEndDate range
    
    rateNode = rateNode & encodeXML("<rate start=""" & rStartDate & """ stop=""" & rEndDate & """ price=""" & objRS("Mon") & """ />")
    
    objRS.MoveNext
    Loop

  ElseIf objRS("Tue") > 0 Then
      Do While objRS("Tue") > 0
    rStartDate = WeekdayName(Weekday(objRS("RateStartDate")))
    rEndDate = WeekdayName(Weekday(objRS("RateEndDate")))
' if the range from rStartDate to rEndDate includes tuesday then keep in mind what date ranges are left from the main rStartDate to rEndDate range
    
    rateNode = rateNode & encodeXML("<rate start=""" & rStartDate & """ stop=""" & rEndDate & """ price=""" & objRS("Tue") & """ />")
    
    objRS.MoveNext
    Loop

ElseIf ' check for the rest of the week days
  ....

Else

'the left over date ranges here, if not continuous then each their own rate node for their time span

rateNode = rateNode & encodeXML("<rate start=""" & rStartDate & """ stop=""" & rEndDate & """ price=""" & objRS("Cost") & """ />")
End If

End If

 

[onressy]

Srry, i just realized that i'm calling an xml child element a node, as i'm refering to rate node when the rate aspect of the xml shema is a child element, hope the terminology isn't confusing.