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	
%>

 

[mthompo]

great advice damber [as usual]

thought i had it with this

SELECT cus.lname, CC.reg, CBB.bookst, CBB.bookend, CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model
FROM courtcar AS CC
LEFT JOIN(SELECT CB.bookst, CB.bookend, CB.carid, CB.courtcarbookid, CB.cusid
FROM courtcarbook AS CB
WHERE '07/10/2006' BETWEEN cb.bookst AND cb.bookend 
AND '01/10/2006' BETWEEN cb.bookst AND cb.bookend
AND cb.bookst BETWEEN '01/10/2006' AND '08/10/2006'
ORDER BY CB.carid, CB.bookst) AS CBB 
ON CC.carid = CBB.carid
LEFT JOIN tran ON CC.tranid = tran.tranid
LEFT JOIN cus ON cus.cusid = CBB.cusid
ORDER BY CC.carid

but still cant quite get the date bit right

SELECT CB.bookst, CB.bookend, CB.carid, CB.courtcarbookid 
FROM courtcarbook AS CB
WHERE '07/10/2006' BETWEEN cb.bookst AND cb.bookend 
AND '01/10/2006' BETWEEN cb.bookst AND cb.bookend
AND cb.bookst BETWEEN '01/10/2006' AND '08/10/2006'
ORDER BY CB.carid, CB.bookst

this needs to find if a weekstart date which is between a bookings start or end date
AND
if a weekend date which is between a bookings start or end date

 

[damber]

OK, give this a go - I've not tested in any way, so you'll need to sense check it for any typos etc.

SELECT cus.lname, CC.reg, CBB.bookst, CBB.bookend, CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model
FROM courtcar AS CC
LEFT JOIN
   (SELECT CB.bookst, CB.bookend, CB.carid, 
    CB.courtcarbookid, CB.cusid
    FROM courtcarbook AS CB
    [COLOR=red]WHERE  (cb.bookst BETWEEN '01/10/2006' AND '07/10/2006')
    OR     (cb.bookend BETWEEN '01/10/2006' AND '07/10/2006') [/color]
   ) AS CBB
ON CC.carid = CBB.carid
LEFT JOIN tran ON CC.tranid = tran.tranid
LEFT JOIN cus ON cus.cusid = CBB.cusid
ORDER BY CC.carid [COLOR=red]ASC, CBB.bookst ASC[/color]

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

 

[mthompo]

nice bit of sql - but am afraid it still adds
a row for each booking in the weeks to the first booking

thanks anyways - sql works great
thought i would post the asp im using - but should be the same as you originally created

<%' courtcar
'sql
sql= "SELECT cus.lname, CC.reg, CBB.bookst, CBB.bookend, CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model "_
& "FROM courtcar AS CC "_
& "LEFT JOIN "_
& "(SELECT CB.bookst, CB.bookend, CB.carid, CB.courtcarbookid, CB.cusid "_
& "FROM courtcarbook AS CB "_
& "WHERE (cb.bookst BETWEEN '15/10/2006' AND '21/10/2006') "_
& "OR (cb.bookend BETWEEN '15/10/2006' AND '21/10/2006') "_ 
& ") AS CBB "_
& "ON CC.carid = CBB.carid "_
& "LEFT JOIN tran ON CC.tranid = tran.tranid "_
& "LEFT JOIN cus ON cus.cusid = CBB.cusid "_
& "ORDER BY CC.carid ASC, CBB.bookst ASC"
'sql end

'courtcar table start - thanks damber
Response.Write("<table border=0 cellpadding=4 cellspacing=1>")
Response.Write("<tr>")
Response.Write("<td colspan =4 class = ticksml>Vehicle</td>")
for i=0 to DateDiff("d",dStartDate,dEndDate)
daydate = (dateadd("d",i,dStartDate))
daynumshort = Day(daydate)
dayshort = formatDate ("%D",(daydate))
dayshortsuf = daySuff(daynumshort)
Response.Write "<td class = ticksml>" & dayshort & " " &daynumshort& dayshortsuf & "</td>"
next
Response.Write("</tr>")

sCurrentReg = ""  
recCount = 0
for j=0 to UBound(aBookings, 2)
recCount = recCount + 1  
bgcolor = "#00cc66"
' Display alternate color for rows
If recCount Mod 2 <> 0 Then
bgcolor = "#cccccc"
End If
'if sCurrentReg <> "" then response.Write(vbcrlf) end if
    sCurrentReg = aBookings(1,j)
	sTran = aBookings(6,j)
	carid = aBookings(4,j)
	sMod = aBookings(7,j)

Response.Write("<tr bgcolor="& bgcolor & ">")
Response.Write "<td class = courtcarrowcount><img src=""images/cariconsml.gif"" border=0>"&(recCount)& "</td>"
Response.Write "<td class = courtcarreg>" &(sCurrentReg) & "</td>"
Response.Write "<td class = mfitsml>" &(sMod)& "</td>"
Response.Write "<td class = mfitsml>" &(sTran)& "</td>"


for i=0 to DateDiff("d",dStartDate,dEndDate)
dCurrDate = DateAdd("d", i, dStartDate)
freestr = "<td class = courtcarfree width=120 align=center><a href='custaddcourtcars.asp?carid=" & (carid) & "&bookst=" & (dCurrDate) & "'target=""_blank"" onClick=""window.open(this.href, this.target, 'height=400,width=480,status=no,resizable=no,scrollbars=no');return false;""><img src=""images/bookbut.gif"" border=0></td>"
		
if isnull(aBookings(2,j)) or isnull(aBookings(3,j)) then
            response.Write(freestr)
        else
if datediff("d",dCurrDate,(aBookings(2,j))) <= 0 and _
datediff("d", dCurrDate,(aBookings(3,j))) >= 0 then
	lname = (aBookings(0,j))
	lname = Left(lname, 8) 
	courtcarbookid = (aBookings(5,j))
response.Write "<td class = courtcarlname width=120 align=center><a href='custaddcourtcars.asp?bookid=" & (courtcarbookid) & "'target=""_blank"" onClick=""window.open(this.href, this.target, 'height=400,width=480,status=no,resizable=no,scrollbars=no');return false;""><span class=courtcarlname>" & (lname) & "</span></a></td>"  
            else
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
end if
end if
next
Response.Write("</tr>")
Next
Response.Write("</table>")

table CB contents

create database if not exists `servbook`;

USE `servbook`;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';

/*Table structure for table `courtcarbook` */

DROP TABLE IF EXISTS `courtcarbook`;

CREATE TABLE `courtcarbook` (
  `courtcarbookid` int(11) NOT NULL auto_increment,
  `carid` int(11) default '0',
  `bookst` date default NULL,
  `bookend` date default NULL,
  `cusid` int(11) default '0',
  `courtcarnotes` varchar(100) default NULL,
  `userid` int(5) default '0',
  PRIMARY KEY  (`courtcarbookid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `courtcarbook` */

insert into `courtcarbook` (`courtcarbookid`,`carid`,`bookst`,`bookend`,`cusid`,`courtcarnotes`,`userid`) values (20,13,'2006-10-16','2006-10-17',11,NULL,11),(21,13,'2006-10-19','2006-10-19',11,NULL,11),(22,13,'2006-10-21','2006-10-21',5,NULL,11);

 

[mthompo]

...one more thing, i think its an asp issue because
if i run that query with a week prior to the first booking
i only get one row per car

 

[damber]

OK, lets confirm a few things:

1. The SQL should ALWAYS produce:

a) at least 1 record for each car id
b) a row for each unique customer booking for the car
c) a sorted list by car id and date booked from
d) a filtered list to only include bookings where either the start date OR the end date are inside the date window you specify

2. The ASP should:

a) iterate through SORTED records using the car id as a grouping value - if this is always in order, the ASP should only add a line terminator (move to the next output row) if a DIFFERENT value is found.
b) based on the date range you specify and used to filter the SQL recordset, you should create date slots, and compare against the current records to and from date to enter the booking id into the date slot.
c) when the current records dates are in the past move to the next record.


Check these one by one starting from the top.

Let me know specifically which one is not working.

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

 

[mthompo]

ok - think it was a date issue in the sql
preliminary tests show this works fine



'sql

sql= "SELECT cus.lname, CC.reg, CBB.bookst, CBB.bookend, CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model "_
& "FROM courtcar AS CC "_
& "LEFT JOIN "_
& "(SELECT CB.bookst, CB.bookend, CB.carid, CB.courtcarbookid, CB.cusid "_
& "FROM courtcarbook AS CB "_
& "WHERE (cb.bookst BETWEEN '"&dstartdatesql&"' AND '"&denddatesql&"') "_
& "OR (cb.bookend BETWEEN '"&dstartdatesql&"' AND '"&denddatesql&"') "_ 
& "OR (cb.bookst < '"& dstartdatesql& "') "_ 
& "AND (cb.bookend > '"&denddatesql&"')"_ 
& ") AS CBB "_
& "ON CC.carid = CBB.carid "_
& "LEFT JOIN tran ON CC.tranid = tran.tranid "_
& "LEFT JOIN cus ON cus.cusid = CBB.cusid "_
& "ORDER BY CC.carid ASC, CBB.bookst ASC"

 

[damber]

phew !

I thought for a second there this thread might need an extra server to store all the posts...;-)

When you've finished you'll have to post a link (if public) or a screenshot of the completed (working!) functionality, so that I know you're not just winding me up !

Hope the rest of the project was easier !

Oh, and one last thing,.. if you're using SQL Server etc then you may want to move this SQL into a stored procedure or View for performance reasons.

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