write cus name if between dates 2

[mthompo]

have a loop that lists days of week
and writes cutomers name if the day equals
booking start date or booking end date
this is done using case statement

however i want the days between the start and end date to
be filled with the customers name but am stuck

courtdate  = dd/mm/yyyy
books/end  = dd/mm/yyyy

[code]
<% 
i = 1
k = Weekday(dDate, 1)
Do Until i = 8 
If i = k Then
courtdate= DateAdd("w", i-k, dDate) 
Else
courtdate = DateAdd("w", i-k, dDate)
End If
		
%>
<td class="mfitsml" align="center">
<%Select Case courtdate
Case x_bookst 
response.Write x_cuslname

Case x_bookend 
response.Write x_cuslname

'this is my case for between dates
Case courtdate > x_bookst AND courtdate < x_bookend
response.Write x_cuslname

Case else%>
<a href=<%Response.Write "custaddcourtcars.asp?carid=" & Server.URLEncode(x_carid) & "&courtdate=" & courtdate %> target="_blank" onClick="window.open(this.href, this.target, 'height=400,width=480,status=no,resizable=no,scrollbars=no');return false;"><span>Free</span></a>
<%end select%>

</td>
<%i = i + 1
Loop
rscourtcars.MoveNext
loop	
%>

 

[damber]

It doesn't on my example.



A smile is worth a thousand kind words. So smile, it's easy!

 

[damber]

try a response.write(ubound(aBookings,2)) before the first loop to see what the ubound is returning,, then post back.

A smile is worth a thousand kind words. So smile, it's easy!

 

[damber]

forget that - my mistake, you can leave the "-1" off.

I think I'd played around with the array in my example too much and the dim'd array wasn't matching the actual array size.

A smile is worth a thousand kind words. So smile, it's easy!

 

[chrissie1]

The i's and j's in your example have nothing to do with the i's and j's in my example

It's difficult if your not talking about the same thing.

HAVE ANOTHER STAR MY PATIENT FRIEND.

Christiaan Baes
Belgium

"My new site" - Me

 

[mthompo]

ok - put in the ubound statement

before this line
for i=0 to DateDiff("d",dStartDate,dEndDate)

and the value was 11

thanks chrissie - have tried to give damber another *
but i think it only let you give 1 now!

 

[damber]

Cheers Chrissie ;-)

mthompo,

Have you added that check before you test the next record ? as per this statement:

You should wrap that condition inside a check if the current record is the last record - e.g. compare j with ubound(...,2) -as it is essentially a lookahead and the next record may not be there (if it is the end of the array).

e.g.:
if ubound(abookings,2) >= j+1 then...


A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

damber - looks like thats it - this thread almost made me crazy - if it hadnt been for your patience
and skill i may well have done!!!

finished off with the following which works great
shame this is over have enjoyed it!
hope to help you some day [doubt it]

if ubound(abookings,2) >= j+1 then
if aBookings(1,j+1) = sCurrentReg then 
if datediff("d", dCurrDate , aBookings(3,j)) <= 0 then 
j = j + 1 
i = i - 1 
else
response.Write(freestr)    
end if
else
response.Write(freestr)
end if
else
response.Write(freestr)
end if

 

[damber]

Great.. it's finally working ?

And it's finally over ?? Don't worry - you have to be crazy to work in any depth in this kind of thing, so join the club... programming and computers can make normally sane people into raving loonies.. all because of a few letters in the wrong place.

Glad to be of help, and good luck with the project.

A smile is worth a thousand kind words. So smile, it's easy!

 

[chrissie1]

programming and computers can make normally sane people into raving loonies

Not me.

Christiaan Baes
Belgium

"My new site" - Me

 

[damber]

No, that's true... you've always been a raving loony !
(but we wouldn't have you any other way )

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

hi - have an issue with this page ..again

if you look at any week that is prior to weeks that have bookings it shows a line of FREES for each booking for that car

so if there are 3 bookings for a car there will be 3 lines for that car - until you move to the first week of bookings
whereby evrything will look fine
also, each page after the bookings will be fine - even if there are no bookings.

have tried adding a GROUP BY clause to the sql statement

SQL ="SELECT cus.lname, CC.reg, CB.bookst, CB.bookend, CC.carid, CB.courtcarbookid, tran.trandesc, CC.model "_   
& "FROM courtcar AS CC "_   
& "LEFT JOIN tran ON CC.tranid = tran.tranid "_   
& " LEFT JOIN courtcarbook AS CB ON CC.carid = CB.carid "_  
& " LEFT JOIN cus ON cus.cusid = CB.cusid "_
& " WHERE CC.display = 0 "_
& " GROUP BY CC.carid "_	
& "ORDER BY CC.carid, CB.bookst"

if i do this it stops the problem but something else happens - it will only show the first booking in the table for each car

hope this makes sense

my main question is do you think this is a SQL or code prob?
thanks again

 

[damber]

I can't see any date restrictions in your SQL code... you had that before, why have you taken them out ?

Without that the dataset will contain records / bookings that your script wont be using. So, a) what is the point of loading them ? and b) as this is based on positional record analysis (current vs last vs next) to determine the grouping, this will only confuse matters, as the end date in your script will be reached before it gets to the actual booking therefore will only show FREE, and wont consolidate.

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

thanks for your reply - took them out because i get EOF error and am not sure how to sort it

ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
/mfit/courtcars.asp, line 150

sql

SQL ="SELECT cus.lname, CC.reg, CB.bookst, CB.bookend, CC.carid, CB.courtcarbookid, tran.trandesc, CC.model "_   
& "FROM courtcar AS CC "_   
& "LEFT JOIN tran ON CC.tranid = tran.tranid "_   
& " LEFT JOIN courtcarbook AS CB ON CC.carid = CB.carid "_  
& " LEFT JOIN cus ON cus.cusid = CB.cusid "_
& " WHERE CC.display = 0 "_
& " AND CB.bookst <= '" & dstartdate & "' AND CB.bookend >= '" & denddate & "' " _
& " GROUP BY CC.carid "_	
& "ORDER BY CC.carid, CB.bookst"

which produces

SELECT cus.lname, CC.reg, CB.bookst, CB.bookend, CC.carid, CB.courtcarbookid, tran.trandesc, CC.model FROM courtcar AS CC LEFT JOIN tran ON CC.tranid = tran.tranid LEFT JOIN courtcarbook AS CB ON CC.carid = CB.carid LEFT JOIN cus ON cus.cusid = CB.cusid WHERE CC.display = 0 AND CB.bookst <= '10/15/2006' AND CB.bookend >= '10/21/2006' GROUP BY CC.carid ORDER BY CC.carid, CB.bookst

 

[mthompo]

line 150 is

aBookings = rs.GetRows()

also added this code which takes uk date 17/10/2006
and changes it to 20061015 to put into sql statement
is this the way you would do it?

'split date in to parts
motdaynum1 = day(whenend)
motmonth = month(whenend)
motyear = year(whenend)
'add 0 in front of single digit day/month
daysql = motdaynum1
if daysql < 10 then 
daysql = "0" & daysql
end if
monthsql = motmonth
if monthsql < 10 then 
monthsql = "0" & monthsql
end if
denddate = motyear & monthsql & daysql

 

[mthompo]

...meant 'changes it to 20061017'

 

[damber]

Your problem is with the SQL code, you need to put the where condition in a nested select for the join to the bookings table. Think of it as you need to join the list of cars with a filtered list of bookings.. so filter your bookings first, then join it to the cars.

At the moment, you are making a big dataset and then filtering on that - which means the existing car id's are removed due to the where condition not being met.. kind of negating your left joins. You may also be able to say "where it is between these dates or is null" (as this is likely to be the value if no bookings exist).

e.g.

SELECT a.fld1, a.fld2 
FROM TableA as a
LEFT JOIN 
(SELECT fld3, fld4 FROM TableB WHERE fld4 between x and y) as b 
ON a.fld1 = b.fld3
etc.....

or

SELECT a.fld1, a.fld2, b.fld3, b.fld4
FROM TableA as a
LEFT JOIN TableB as b 
ON a.fld1 = b.fld3
WHERE (fld4 between x and y) or fld4 is null
etc.....

Either way, this is more of a SQL question, so I recommend you ask the question in the SQL forum, so that the question will help others searching for a SQL relevant answer in the future. Plus the experts in there will give you better answers than I would.

As for the date, that's a different question - raise it separately, otherwise people wont be able to find it when they search.


A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

am trying to find someone to help with the sql - but have noticed an issue with putting the date clause in - if a booking runs longer than a week then it will not get shown
at all - not sure how to sort this without removing the clause

 

[damber]

If you check whether the start OR the end date is between the dates you selected then this will capture any that occur during that period.

A smile is worth a thousand kind words. So smile, it's easy!

 

[mthompo]

thanks have changed it to this

SQL ="SELECT cus.lname, CC.reg, CB.bookst, CB.bookend, CC.carid, CB.courtcarbookid, tran.trandesc, CC.model "_   
& "FROM courtcar AS CC "_   
& "LEFT JOIN tran ON CC.tranid = tran.tranid "_   
& " LEFT JOIN courtcarbook AS CB ON CC.carid = CB.carid"_ 
& " OR '" & x_bookst & "' BETWEEN bookst AND bookend " _
& " OR '" & x_bookend & "' BETWEEN bookst AND bookend" _
& " LEFT JOIN cus ON cus.cusid = CB.cusid"_
& " WHERE CC.display = 0"_	
& " ORDER BY CC.carid, CB.bookst"

but still get prob where extra vehicle row
per booking on day that is equal to the day of
the actual booking

eg

March
reg Sun1st Mon2nd Tue3rd Wed4th Thu5th Fri6th
HJ06TYH SMITH SMITH FREE BOB BOB BOB
HJ55TFG FREE FREE FREE FREE FREE FREE
HJ06UYH FREE FREE FREE FREE FREE FREE

but if i go to February 1st I get

February
reg Sun1st Mon2nd Tue3rd Wed4th Thu5th Fri6th
HJ06TYH FREE FREE FREE FREE FREE FREE
HJ06TYH FREE FREE FREE FREE FREE FREE
HJ55TFG FREE FREE FREE FREE FREE FREE
HJ06UYH FREE FREE FREE FREE FREE FREE

 

[damber]

Best way to debug SQL is directly in the database - don't inspect your ASP code results. Then you will see why this is happening.

Strip it back to the basics:

1. Produce a query that shows you the bookings you want.
e.g. SELECT x,y FROM bookings where (start_date between d1 and d2) OR (end_date between d1 and d2)
2. Review this result and make sure it only has records that you want to see.
3. Now do the same for the cars.
4. Now join them together ... where you would put a table in the join statement include the select statement (inside ( )'s ) and alias as some_table - and continue with the join condition (e.g. x=y)
5. Now add other joins and your where conditions (for the resulting dataset only)

If you've ever use views, then you might think about how you reference a view in your code... similar to a table -making the view essentially a nested SELECT statement.

have a look at the examples I added earlier - you'll need to include a between clause for both dates, but they're still valid.


A smile is worth a thousand kind words. So smile, it's easy!