mthompo
Posts: 2
|
| Posted: 09/26/2006, 7:40 AM |
|
have been trying to get this right for ages
so far have a page that lists all cars on right side
and spaces for days of week [sun to sat] along columns
eg
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
but i want it to look like this
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
so that if the same car has two bookings for that week i will put them in
the same row - sounds easy, but i cant get it sorted
if it doesnt make sense let me know - mark
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"
asp
<%
'shows day headings in first row only
Function ShowWeek(dDate)
k = Weekday(dDate, 1)
i = 1
Do Until i = 8
dTemp = dTemp & "<td span class=mfitsml bgcolor=#c0c0c0 width=60 align=center>" & WeekDayName(Weekday(DateAdd("w", i-k, dDate), 1)) & "<br>" & DateAdd("w", i-k, dDate)& "</td>"
i = i + 1
Loop
ShowWeek = dTemp
End Function
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
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>
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>response.write x_cuslname%></td>
<%else%>
<td class = mfitsml>Free</span></td>
<%
end if
i = i + 1
Loop%>
</tr>
<%rscourtcars.movenext
loop%>
</table>
|
