booked days per month query

[burnside]

Hi - long time viewer, first time poster

i have a car booking system that stores the startdate and the enddate of a booking as well as car/customer details.
i want to produce an efficiency report that shows how many days the car is booked per month.

i am having trouble getting the results - the column that sums the days between the booking start and end date works ok if the start and enddate are in the the same month, but i cant figure out how to get it to work for bookings that last longer than a month

SELECT CC.reg, CBB.bookst, CBB.bookend, CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model, case when month( CBB.bookst ) <> month( CBB.bookend )then concat(sum(datediff( makedate( month( CBB.bookend ), 1 ), CBB.bookst )+ 1)) else concat(sum(datediff( CBB.bookend, CBB.bookst )+ 1)) end as days, month(CBB.bookst) as month, count(cc.carid) as count 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 '20070101' AND '20071231') OR (cb.bookend BETWEEN '20070101' AND '20071231') OR (cb.bookst <= '20070101') AND (cb.bookend >= '20071231')) AS CBB ON CC.carid = CBB.carid LEFT JOIN tran ON CC.tranid = tran.tranid LEFT JOIN cus ON cus.cusid = CBB.cusid WHERE display = 0 GROUP BY CC.carid, month(CBB.bookst) ORDER BY CC.carid ASC, CC.makeid ASC, month(CBB.bookst) ASC 

[code green]

Not sure why you are doing this

sum(datediff

DATEDIFF returns the difference in days between two dates. Make sure the fields are of type DATE or DATETIME

[burnside]

hi thanks for your reply -
the sum(datediff) is to provide a total of the number of days that are booked
for that month
i know how many days are in each month so i can work out a percentage

however, the sql doesnt take into account wether the booking stretches over two [or more] months so the results arent accurate, it would need to sum the days booked for the month, then if the rest of the booking is in another month
it would have to sum for the next month

if im not being clear please let me know

heres the sql again in a better format

SELECT CC.reg, CBB.bookst, CBB.bookend, CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model, case when month( CBB.bookst ) <> month( CBB.bookend )then concat(sum(datediff( makedate( month( CBB.bookend ), 1 ), CBB.bookst )+ 1)) else concat(sum(datediff( CBB.bookend, CBB.bookst )+ 1)) end as days, 
month(CBB.bookst) as month, count(cc.carid) as count 
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 '20070101' AND '20071231') 
OR (cb.bookend BETWEEN '20070101' AND '20071231') 
OR (cb.bookst <= '20070101') AND (cb.bookend >= '20071231')) 
AS CBB 
ON CC.carid = CBB.carid 
LEFT JOIN tran ON CC.tranid = tran.tranid 
LEFT JOIN cus ON cus.cusid = CBB.cusid 
WHERE display = 0 
GROUP BY CC.carid, month(CBB.bookst) 
ORDER BY CC.carid ASC, CC.makeid ASC, month(CBB.bookst) ASC
 

[burnside]

...also the fields bookst and bookend are date format

[code green]

the sum(datediff) is to provide a total of the number of days that are booked
for that month

Please refer to my quote

DATEDIFF returns the difference in days between two dates

I think you only want the results for a particular month. But you still don't need sum. Can you change the DATEDIFF so date1 = a date in the record and date2 = date at end of or beginning of month?

[burnside]

hi - thanks for your reply, can you give me an example - i am new to this

[code green]

I really don't like using aliases unless absolutely neccesary, that is a table is being JOINed twice.
It makes your query difficult to read.
If your date data is in DATETIME format why are you using this?

concat(sum(datediff( makedate( month( CBB.bookend ), 1 ), CBB.bookst )+ 1)) 

I am sorry but I can't figure out the reasoning behind this.
Using the MONTH() function is casting the result to number of months.
And why MAKEDATE() when the fields are already in DATETIME format?
DATEDIFF(). This function takes two dates and returns the difference between the two in days.
The date can be as read from a record or text in the DB standard format . ie 2007-07-06. Please explain what is wrong with this?

DATEDIFF(CBB.bookst,CBB.bookend)

[burnside]

thanks for your reply - have changed the sql and your right it makes no difference to the result!
i am still stuck as what to do if the booking runs over a month because the query would need to keep totals for the next month as well as the current month

SELECT CC.reg, CBB.bookst, CBB.bookend, CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model, 
sum(datediff( CBB.bookend, CBB.bookst )+ 1) as days, 
month(CBB.bookst) as month 
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 '20070101' AND '20071231') 
OR (cb.bookend BETWEEN '20070101' AND '20071231') OR (cb.bookst <= '20070101') 
AND (cb.bookend >= '20071231')) 
AS CBB ON CC.carid = CBB.carid 
LEFT JOIN tran ON CC.tranid = tran.tranid 
LEFT JOIN cus ON cus.cusid = CBB.cusid 
WHERE display = 0 
GROUP BY CC.carid, month(CBB.bookst) 
ORDER BY CC.carid ASC, CC.makeid ASC, month(CBB.bookst) ASC