booked days per month query

[thompom]

Hi,

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 is producing all sorts of results

so far i have

SELECT CC.reg, CBB.bookst, CBB.bookend, CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model, concat(sum(datediff(cbb.bookend,cbb.bookst))) 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, CBB.bookst ASC

 

[thompom]

hi - think i have the results i need using the query below, days is giving the correct result, but as a negative - have seen the solution to this before - but cant remember how?

select CC.reg, CBB.bookst, CBB.bookend, CC.carid, CBB.courtcarbookid,tran.trandesc, CC.model,
case when year( CBB.bookst ) <> year( CBB.bookend )
       then datediff( makedate( year( CBB.bookend ), 1 ), CBB.bookend ) 
       else datediff( CBB.bookst, CBB.bookend ) end as days
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

thanks to CLFLAVA thread436-1197099

 

[piti]

datediff(date1, date2) returns date1-date2, so just switch the CBB.bookst and CBB.bookend expressions

 

[thompom]

thanks for your reply - your quite right i thought it was a mysql thing - instead it was a supidity thing!

i need to add 1 to the result so that the current day is included, should i do this in the mysql?

 

[thompom]

hi - all sorted, in case anyones interested

case when year( CBB.bookst ) <> year( CBB.bookend )
then datediff( makedate( year( CBB.bookend ), 1 ), CBB.bookst ) 
else datediff( CBB.bookend, CBB.bookst ) end + 1 as days

 

[thompom]

sorry - should be

Case when month( bookst ) <> month( bookend )then sum(datediff( makedate( month( bookend ), 1 ), bookst )+ 1) 
else sum(datediff( bookend, bookst )+ 1) end  as days, month(bookst)

 

[thompom]

hi,

have an issue that i dont think my sql handles correctly
the sql works fine if the booking start date and the booking end date are in the same month - but it cant handle bookings that last longer than a month, can someone help me change the sql to do this

so far

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

 

[piti]

mysql is doing only what is "told" to do - you are grouping the records by the month of booking start day ... so if the period extends to the next month(s) you will have only the day results for the month of the start
if you need correct data for all the months i think the only way is to do it month by month - can't think of any grouping that could split the period into two or more records for every month involved (well as i remember in pl/sql there is some function to achieve this, but oracle is not mysql ;-))

 

[thompom]

can you give me an example - am really stuck

 

[piti]

i could try, but this is just a hint not your code adapted ... for july (7th month) it could look like

select
sum(datediff(case when month(bookend) > 7 then '2007-07-31' else bookend end, case when monh(bookstart) < 7 then '2007-07-01' else bookstart end))
from tablename
where 7 between month(bookstart) and (bookned)

of course you can use functions like last_day(), have the month numeric representation in a variable, etc ...