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]

That's a messy way of doing it.. make it simple..

1. You have a known start and end date
2. You need to loop through each day from the start to the date and write out the name of the customer

So..

you need a loop for the number of days you want to output inside which you write out the info...

dim iNoOfDays: iNoOfDays = DateDiff("d",dStartDate, dEndDate)
for i = 0 to iNoOfDays
  response.write(DateAdd("d", dStartDate, i) & ": " & sCustomerName)
next

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

 

[mthompo]

thanks for your reply,

can you show me the messy way!

it still needs to be 7 slots so that the free day slots
are shown

eg

Car Sun13 Mon14 Tue15 Wed16 Thu17 Fri18 Sun19
1 FREE Smith Smith Smith FREE FREE FREE
2 Jon Jon Jon Jon Jon FREE FREE
3 FREE FREE FREE FREE FREE FREE FREE

i put that code in and just looped forever

 

[damber]

i put that code in and just looped forever

Copy and paste will do that to you... best to understand things first - the code runs fine if you set the variables correctly.

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

Exactly what the code does. Asking clear questions will help to get clear answers.

If you want to list a calendar time period (Start date to end date) instead of a booking time period, then you would base your loop on that.

Your recordset will need to be sorted in date sequence from the start date to the end date - you then need to compare the record against the current date to decide whether to output and move to the next record / iteration etc.

can you show me the messy way!

You're kidding right ? No.

for i=0 to DateDiff("d",dStartDate,dEndDate)
    dCurrentDate = DateAdd("d", i, dStartDate)
    Response.write(dCurrentDate & ": ")
    if oRS.EOF then
        response.Write("Free."& "<br/>")
    else
        if  datediff("d",dCurrentDate,Cdate(oRS.Fields("start_date"))) <= 0 and _
            datediff("d", dCurrentDate, cdate(oRS.Fields("end_date"))) >= 0 then
            response.Write(oRS.Fields("customer_name") & "<br />")
        else
            response.Write("Free" & "<br/>")
            oRS.MoveNext
        end if
    end if
next

You'll need to modify it to work in your code, and use the appropriate variables etc - you will also need to style it how you want aswell. The same principles will apply if you want to add loops for each booked resource (you don't ask for this, but imply it in your example).

If you want to see a working example by cutting and pasting, here is a sample based on an array instead of a database that you can simply copy and paste into an ASP file...

<%@LANGUAGE=VBScript%>
<% Option Explicit %>
<%
dim aBookings(3,3)

dim dStartDate, dEndDate, iCurrentRecord, i,dCurrDate
dStartDate = DateSerial(2006,08,01)
dEndDate = DateSerial(2006,09,01)
aBookings(0,0) = "Cust 1"
aBookings(0,1) = DateSerial(2006, 08,01)
aBookings(0,2) = DateSerial(2006, 08,02)
aBookings(1,0) = "Cust 2"
aBookings(1,1) = DateSerial(2006, 08,04)
aBookings(1,2) = DateSerial(2006, 08,06)
aBookings(2,0) = "Cust 3"
aBookings(2,1) = DateSerial(2006, 08,08)
aBookings(2,2) = DateSerial(2006, 08,15)

iCurrentRecord = 0
for i=0 to DateDiff("d",dStartDate,dEndDate)
    dCurrDate = DateAdd("d", i, dStartDate)
    Response.write(dCurrDate & ": ")
    if iCurrentRecord > uBound(aBookings,1) then
        response.Write("Free."& "<br/>")
    else
        if datediff("d",dCurrDate,Cdate(aBookings(iCurrentRecord,1))) <= 0 and _
            datediff("d", dCurrDate, cdate(aBookings(iCurrentRecord,2))) >= 0 then
            response.Write(aBookings(iCurrentRecord,0) & "<br />")
        else
            response.Write("Free" & "<br/>")
            iCurrentRecord = iCurrentRecord + 1
        end if
    end if
next

%>

which produces this:

01/08/2006: Cust 1
02/08/2006: Cust 1
03/08/2006: Free
04/08/2006: Cust 2
05/08/2006: Cust 2
06/08/2006: Cust 2
07/08/2006: Free
08/08/2006: Cust 3
09/08/2006: Cust 3
10/08/2006: Cust 3
11/08/2006: Cust 3
12/08/2006: Cust 3
13/08/2006: Cust 3
14/08/2006: Cust 3
15/08/2006: Cust 3
16/08/2006: Free
17/08/2006: Free
18/08/2006: Free
19/08/2006: Free
20/08/2006: Free
21/08/2006: Free
22/08/2006: Free
23/08/2006: Free
24/08/2006: Free
25/08/2006: Free
26/08/2006: Free
27/08/2006: Free
28/08/2006: Free
29/08/2006: Free
30/08/2006: Free
31/08/2006: Free
01/09/2006: Free

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

 

[mthompo]

damber - you must excuse my flippent comments it was last thing friday!
this is one of the best replies i have ever seen
i hope you make tipmaster - and appreciate your patience

 

[damber]

No problem, glad to be of help. I hope it works out for you. It should be reasonably simple to change it to lay out horizontally and add the same principle for the "car" rows from that example, but if you get stuck port back with your code and we'll take a look.

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

 

[mthompo]

hi damber - am stuck again

with the code below each booking is on its own row
but i want the reg of the car and all bookings for that car on one row - thanks again

example
reg Sun1st Mon2nd Tue3rd Wed4th Thu5th Fri6th
HJ06TYH SMITH SMITH FREE BOB BOB BOB
HJ55TFG BILL BILL FREE FREE JON JON
HJ06UYH FREE FREE TREV TREV TREV TREV

at the moment i get

reg Sun1st Mon2nd Tue3rd Wed4th Thu5th Fri6th
HJ06TYH SMITH SMITH FREE FREE FREE FREE
HJ06TYH FREE FREE FREE BOB BOB BOB
HJ55TFG BILL BILL FREE FREE FREE FREE
HJ55TFG FREE FREE FREE FREE JON JON
etc...

sql

courtcarssql1 = "SELECT courtcar.model, courtcar.reg, courtcar.carid, courtcarbook.courtcarbookid, courtcarbook.bookst, courtcarbook.bookend, tran.trandesc, cus.fname, cus.lname, cus.add1, cus.pcode "_
& "FROM courtcar "_
& "LEFT JOIN tran ON courtcar.tranid = tran.tranid "_
& "LEFT JOIN courtcarbook ON courtcar.carid = courtcarbook.carid "_
& "LEFT JOIN cus ON cus.cusid = courtcarbook.cusid "_
& "GROUP BY courtcarbook.courtcarbookid, courtcar.carid "_
& "ORDER BY courtcar.makeid, courtcar.tranid, courtcar.carid"

asp/html

 <%	
'court cars loop
 Do While (Not rscourtcars.Eof)

x_courtcarbookid = rscourtcars("courtcarbookid")
x_carid = rscourtcars("carid")
x_mod = rscourtcars("model")
x_reg = rscourtcars("reg")
x_tran = rscourtcars("trandesc")
x_bookst = rscourtcars("bookst")
x_bookend = rscourtcars("bookend")
x_cusfname = rscourtcars("fname")
x_cuslname = rscourtcars("lname")
x_cusadd1 = rscourtcars("add1") 
x_cuspostc = rscourtcars("pcode") 

i = 1
k = Weekday(dDate, 1)
%>
<tr>
<td class="mfitsml">
<%Response.Write(x_reg)%></td>
<td class="mfitsml">
<%Response.Write(x_mod)%></td> 
<td class="mfitsml">
<%Response.Write(x_tran)%></td>
<%'day loop
Do Until i = 8
If i = k Then
courtdate= DateAdd("w", i-k, dDate) 
Else
courtdate = DateAdd("w", i-k, dDate)
End If

If (x_bookst = "" Or IsNull(x_bookst)) Then%>
<td class = mfitsml><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></td>
   <%else
if  datediff("d",courtdate,Cdate(x_bookst)) <= 0 and _
datediff("d", courtdate,Cdate(x_bookend)) >= 0 then%>
<td class = mfitsml><a href=<%Response.Write "custaddcourtcars.asp?bookid=" & Server.URLEncode(x_courtcarbookid) & "&courtdate=" & courtdate %> target="_blank" onClick="window.open(this.href,this.target, 'height=400,width=480,status=no,resizable=no,scrollbars=no');return false;"> 
<span class="notes"><%response.write x_cuslname%></span></a></td>
   <%else%>
<td class = mfitsml><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></td>		
   <%end if
    end if
i = i + 1
Loop%>

</td>
<%rscourtcars.MoveNext%>
</tr>
<%loop%>

 

[mthompo]

output from the sql statement is

Corsa,HJ55JTF,2,NULL,NULL,NULL,Manual,NULL,NULL,NULL,NULL
Corsa,HJ06TYH,3,21,2006-08-13,2006-08-24,Manual,John,Smith,400 poole road,DH181DZ
Corsa,HJ06TYH,3,17,2006-08-25,2006-08-26,Manual,John,Smith,400 poole road,DH181DZ
Corsa,HJ06TST,5,NULL,NULL,NULL,Manual,NULL,NULL,NULL,NULL
Corsa,HJ06WTG,4,NULL,NULL,NULL,Auto,NULL,NULL,NULL,NULL

please help

 

[damber]

Quick list of things to consider:

1. ReSort your SQL to ORDER BY Car id (ASC) then by date (ASC)
2. Include a WHERE clause which selects dates BETWEEN the range you want.
3. Create a variable called iCurrentCarID, which is set to -1 to start with.
4. Create an outer loop (in respect of the example provided previously) based on the recordset
5. Loop through the recordset and check the current records car id against the iCurrentCarID value, if they are the same, then continue the inner loop (the one that deals with the date range and the bookings as shown previously), otherwise move to the next record and add a new line in your output
6. Update the iCurrentCarID with the current after each record
7. When a new car id occurs, reset the inner loops variables to start again.

The general concept is to have two loops, one that checks whether it is a new car (and creates a new line and resets the current date etc) and the other that uses the dates to fill in against a predefined date period (what you want to display). This relies on the data in the recordset being ordered correctly, otherwise it wont work.

Have a go at building something along those lines (don't worry about the other stuff in your page - just build a simple sample script - it'll make is easier to debug). If you have a specific issue, post back with the specific code and error.

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

 

[mthompo]

hi,

am still stuck on this even with all the help ive been given - cant seem to get my head round it and now i have two apps that need this format
i have tried but cant get two records with the same carid to be on the same line

'courtcars sql
courtcarssql1 = "SELECT courtcar.model, courtcar.reg, courtcar.carid, courtcarbook.courtcarbookid, courtcarbook.bookst, courtcarbook.bookend, tran.trandesc, cus.fname, cus.lname, cus.add1, cus.pcode, courtcarbook.carid AS carbookid "_
& "FROM courtcar "_
& "LEFT JOIN tran ON courtcar.tranid = tran.tranid "_
& "LEFT JOIN courtcarbook ON courtcar.carid = courtcarbook.carid "_
& " LEFT JOIN cus ON cus.cusid = courtcarbook.cusid "_
& "GROUP BY courtcar.carid, courtcarbook.courtcarbookid "_
& "ORDER BY courtcar.carid, courtcarbook.bookst"

'& "AND courtcarbook.bookst <= "&[datesql]_
'& " AND courtcarbook.bookend >= "&[datesql]_
'response.Write(datesql)

Set rscourtcars = Server.CreateObject("ADODB.Recordset")
rscourtcars.CursorLocation = 3
rscourtcars.Open courtcarssql1, conn, 1, 2 
%>
<table>
<tr>
<td colspan = 3 bgcolor=#c0c0c0 class="courtcar">Vehicle</td>
<% = ShowWeek(dDate) %>
</tr>
<% 'court cars loop
x_caridtmp = 1
Do While (Not rscourtcars.Eof)
x_carid = rscourtcars("carid")
x_bookst = rscourtcars("bookst")
x_courtcarbookid = rscourtcars("courtcarbookid")
x_bookend = rscourtcars("bookend")
x_cusfname = rscourtcars("fname")
x_cuslname = rscourtcars("lname")
x_cusadd1 = rscourtcars("add1") 
x_cuspostc = rscourtcars("pcode") 
x_mod = rscourtcars("model")
x_reg = rscourtcars("reg")
x_tran = rscourtcars("trandesc")
%>
<tr>
<td class="mfitsml"><%Response.Write(x_reg)%></td>
<td class="mfitsml"><%Response.Write(x_mod)%></td> 
<td class="mfitsml"><%Response.Write(x_tran)%></td>

<%rscourtcars.movenext
if not rscourtcars.eof then
x_caridnext = rscourtcars("carid")
end if

i = 1
k = Weekday(dDate, 1)
Do Until i = 8
courtdate= DateAdd("w", i-k, dDate) 

if datediff("d",courtdate,x_bookst) <= 0 and datediff("d",courtdate,x_bookend) >= 0 then%>
<td class = mfitsml><a href=<%Response.Write "custaddcourtcars.asp?bookid=" & Server.URLEncode(x_courtcarbookid) & "&courtdate=" & courtdate %> target="_blank" onClick="window.open(this.href, this.target, 'height=400,width=480,status=no,resizable=no,scrollbars=no');return false;"> 
<span class="notes"><%response.write x_cuslname%></span></a></td>

<%else if x_caridnext <> x_carid then%>

<td class = mfitsml><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 class="notes">Free</span></a></td> 
<%
end if
end if
i = i + 1
Loop%>
</tr>
<%loop%>
</table>
[code]

 

[damber]

I don't see two nested loops in your code, as per my last post .. you'll need them to make this work.

As these things can sometimes become a bit blinding (wood for the trees scenario), one of the best things to do is to walk away from the computer and say hello to your long lost friends - Mr Pen and Mr Paper..

Really.. Write out a list of data like your recordset - and in the specified order that I noted in my last post. Then manually (yes you, not the computer) write each entry one by one into the slots/layout you want it to be in.

And whilst you are doing it, take every single step in your mind slowly and write it down... e.g.:

1. Read Car ID
2. Have I seen this ID before ?
3. No: Create new line for this new car id
4. Read Start Date
5. Read End Date
6. Is my current date position inside this range?
7. Yes: add as a column and enter the customer ID
8. etc....etc...

Going through it step by step yourself can help you understand the process that your program needs to go through.

Put it into a flowchart diagram if it helps.

You may also want to think of the displaying of data in a different way.. the example I presented is only vertically listed because of the linefeeds I added - take them out and it will be one big line. Put a linefeed in at the appropriate place and it will be split into rows.. do this where the car id changes and add the car id value after the linefeed (i.e. at the beginning of the new line) and you're not too far from what you want.

As mentioned before.. start simple - take all the other stuff out of your page, including html - this will make your code easier to read - once you get it working, then put the other bits back in.

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

 

[mthompo]

give me a clue is the new loop inside or outside the i=8 loop?

 

[damber]

4. Create an outer loop (in respect of the example provided previously) based on the recordset
5. Loop through the recordset and check the current records car id against the iCurrentCarID value, if they are the same, then continue the inner loop (the one that deals with the date range and the bookings as shown previously), otherwise move to the next record and add a new line in your output

I think these two points will help.

Have you written down your manual process yet ? This will help aswell.

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

 

[mthompo]

i tried putting the date restrictions on the sql statement
althought i dont see how this will help apart from
performance issues when getting the rs

courtcarssql1 = "SELECT courtcar.model, courtcar.reg, courtcar.carid, courtcarbook.courtcarbookid, courtcarbook.bookst, courtcarbook.bookend, tran.trandesc, cus.fname, cus.lname, cus.add1, cus.pcode, courtcarbook.carid AS carbookid "_
& "FROM courtcar "_
& "LEFT JOIN courtcarbook ON courtcar.carid = courtcarbook.carid"_
& " LEFT JOIN cus ON cus.cusid = courtcarbook.cusid"_
& " AND courtcarbook.bookst >= "&[datesql]_
& " AND courtcarbook.bookend <= "&[datesql]_
& " LEFT JOIN tran ON courtcar.tranid = tran.tranid "_
& " GROUP BY courtcar.carid, courtcarbook.courtcarbookid "_
& "ORDER BY courtcar.carid, courtcarbook.bookst"

but for some reason it doesnt show [lname] anymore
it does know the record exists because it leaves the
space where the lname should go - is this really necessary at this stage

thanks for bearing with me - have been on it 5hours today
but am getting nowhere

 

[damber]

read my last post.
and especially the post before that.
and the one before that.

The most important thing is start with the basics.. do it bit by bit.. and think the process through manually before trying to code it.

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

 

[mthompo]

think im getting there
have couple of qs - since im using your example with a recordset i have an issue with this line

if iCurrentRecord > uBound(aBookings,1) then

have tried to change it to

if iCurrentRecord > uBound(rscourtcars,1) then

and

if iCurrentRecord > uBound(x_bookst) then

but get a type mismatch error
know that ubound returns largest value for that part of array - how would i change that for my rs?

in your example both your records have values but i have a list of cars that i need displayed even if a car has no bookings. at the moment i get a null value error for this line

for i=0 to DateDiff("d",x_bookst,x_bookend)

can you show me how to take this into account

 

[mthompo]

hi,

have got my db recordset working with your code
but despite writing it down and looking at your posts bit by bit i cant get it to do what i want
i guess im just thick!

%><table>
 <tr>
  <td colspan = 3 bgcolor=#c0c0c0 class="courtcar">Vehicle</td>
	<% = ShowWeek(dDate) %>
 </tr>
<% 'court cars loop
x_carid = -1

Do While (Not rscourtcars.Eof)
'if x_caridtmp <> x_carid then

x_carid = rscourtcars("carid")
x_bookst = rscourtcars("bookst")
x_courtcarbookid = rscourtcars("courtcarbookid")
x_bookend = rscourtcars("bookend")
x_cusfname = rscourtcars("fname")
x_cuslname = rscourtcars("lname")
x_cusadd1 = rscourtcars("add1") 
x_cuspostc = rscourtcars("pcode") 
x_mod = rscourtcars("model")
x_reg = rscourtcars("reg")
x_tran = rscourtcars("trandesc")

if x_carid <> x_caridold then
%>
<tr>
<td class="mfitsml"><%Response.Write(x_reg)%></td>
<td class="mfitsml"><%Response.Write(x_mod)%></td> 
<td class="mfitsml"><%Response.Write(x_tran)%></td>
<%
end if

rscourtcars.movenext
if not rscourtcars.eof then
x_caridnext = rscourtcars("carid")
end if

i = 1
k = Weekday(dDate, 1)
'response.Write k
'Do While x_carid = x_caridold

for i=0 to DateDiff("d",firstday,lastday)

Do While x_caridold = x_caridnext
x_caridold = x_carid
courtdate = DateAdd("d", i, firstday)

if iCurrentRecord > (x_bookst) then
response.Write("<td>Free</td>") 
else
If datediff ("d",courtdate,x_bookst) <= 0 and datediff("d",courtdate,x_bookend) >= 0 then
Response.write "<td>" & x_cuslname & "</td>"
else
Response.Write ("<td>Free</td>")
end if
end if

if not rscourtcars.eof then
rscourtcars.movenext
x_caridnext = rscourtcars("carid")
end if
loop

next
'x_caridold = x_carid
if x_caridold <> x_carid then%>
</tr>
<%end if
loop%>

</table>

 

[damber]

but get a type mismatch error
know that ubound returns largest value for that part of array - how would i change that for my rs?

You need to loop through the recordset differently and keep track of the current record index - e.g.:

i = 0
while not oRS.EOF

  oRS.MoveNext
  i=i+1
wend

Even better, use GetRows to return an array from your recordset, then you can use the same approach I've outlined above... plus it is a more efficient (faster) way of processing your recordset data.

in your example both your records have values but i have a list of cars that i need displayed even if a car has no bookings. at the moment i get a null value error for this line

Test for null before getting into the date checks - if null, then the car is free.

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

 

[damber]

...also, remember - the key is that you need two loops.. one to loop through the records (individual bookings) and the second to loop through the date range for each row.

The tricky bit is that more than one row can have booked date ranges within your required date range - therefore you will need to manage the current position of the loop and recordset and move forward when necessary by changing the outer loop(for your records) without breaking out of the inner loop(for your dates).

The code I posted above does the date looping for you - now you need to wrap that up in a loop for your recordset.

Then you need to format so that it creates line breaks after each new registration it finds in the recordset (keep track of this in a variable).

Finally you will need to handle the tricky bit, which requires you to move forwards in the recordset loop without breaking the date loop - this can be done in a for...next... loop by +1 to the iterator variable (e.g. i=i+1).

There are some other checks (e.g. for dates and nulls etc) but once you get the above fundamentals done on the structure, you can play with conditions to make data appear correctly.

Post back with some code that does the above.

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

 

[damber]

How are things going,.. how far have you got ?

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