month total from start/end date... 1

[thompom]

hi,

i have a car booking system that stores the booking start and end dates along with car/cus details.
now i need to make a report that shows the cars booking efficiency [daysbooked/daysofmonth]
i am having issues with vehicles that the booking starts in one month and end in another month.
i can get the number of days between the start and enddate using datediff in my sql but this doesnt take into account the 'long' bookings - i dont think i can solve this using just sql - any advice welcome

so far...
sql

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

asp -

<%' 1st week
Response.Write("<table border=0 cellpadding=4 cellspacing=1>")
Response.Write("<tr>")
Response.Write("<td colspan =4 class = courtcarhead>Vehicle</td>")
for i=0 to DateDiff("m",dStartDate,dEndDate)
daydate = (dateadd("m",i,dStartDate))
daynumshort = Month(daydate)
monthtext = (MonthName(daynumshort,true))
dayshort = formatDate ("%M",(daynumshort))
dayshortsuf = daySuff(daynumshort)
    Response.Write "<td class = courtcarhead>" &monthtext& "</td>"
next
Response.Write("</tr>")

sCurrentReg = ""  
recCount = 0
'for j=0 to DateDiff("m",dStartDate,dEndDate)
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

    sCurrentReg = aBookings(0,j)
	sTran = aBookings(5,j)
	carid = aBookings(3,j)
	sMod = aBookings(6,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 nowrap=nowrap>" &(sMod)& "</td>"
	Response.Write "<td class = mfitsml>" &(sTran)& "</td>"


    for i=0 to DateDiff("m",dStartDate,dEndDate)
        dCurrDate = DateAdd("m", i, dStartDate)

		freestr = "<td class = courtcarfree width=120 align=center>0</td>"
'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	
        if isnull(aBookings(1,j)) or isnull(aBookings(2,j)) then
            response.Write(freestr)
        else
            if datediff("m",dCurrDate,(aBookings(1,j))) <= 0 and _
                datediff("m", dCurrDate,(aBookings(2,j))) >= 0 then
				'lname = (aBookings(0,j))
				'lname = Left(lname, 6) 
				monthbook = (aBookings(7,j))
				courtcarbookid = (aBookings(4,j))
				
                response.Write "<td class = courtcarlname width=120 align=center>" & monthbook &"</td>"  
            else
			if ubound(abookings,2) >= j+1 then
                if aBookings(0,j+1) = sCurrentReg then 
                    if datediff("m", dCurrDate , aBookings(2,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>")
%>

 

[nickdel]

Hi Thompom, maybe I've picked you up wrong on this but datediff in both asp and SQL can return the number of days even if they stretch across several months...

SQL

select datediff(dd,'2007/01/01','2007/07/06')

ASP

response.write datediff("d","2007/01/01","2007/07/06")

both return 186...

Nick

 

[Tarwn]

You didn't mention whether this is for SQL server or MS Access or what, so any SQL I post in my reply may or may not work (I'm also still on my first cup of coffee).

You basically have four situations:
Start and end date both occur inside your range
Start date occurs in the range, end date is afterwards
Start date is before the range, end date occurs in the range
Start date is before the range, end date occurs after the range

One option would be to join to a calendar table and do a count on dates that occur between the range and between the start and end date for a car. Basically something like:

SELECT CC.whatever, CB.whatever, ..., COUNT(CAL.DateField) AS NumDays, Cal.MonthField
FROM CourtCar CC
   LEFT JOIN CourtCarBook CB ON CC.car_id = CB.car_id
   LEFT JOIN Calendar CAL ON CAL.DateField >= CB.Bookst AND CAL.DateField <= CB.Bookend
   LEFT JOIN tran ON CC.tranid = tran.tranid
   LEFT JOIN cus ON cus.cusid = CBB.cusid
WHERE CAL.DateField >= '20070101'
   AND CAL.DateField <= '20071231'
   AND display = 0 
GROUP BY CC.whatever, CB.whatever, CAL.MonthField, ...
ORDER BY CC.carid ASC, CC.makeid ASC, month(CBB.bookst) ASC

AI had another couple solutions occur to my while writing the first part of this post, but neither are trivial when it comes to calculating the days in a month for the four cases I outlined above. I think that in this situation a calendar table could help you reduce the complexity immensely as well as keep the efficiency up.

-T

Best MS KB Ever:

http://support.microsoft.com/kb/555375

 

[thompom]

thanks for your reply,

so i need a table with all dates for the lifecycle of the product - if say thats 20 years[!?] then about 700 records

i didnt want to do it this way, but 700 records isnt too bad if its saving me trouble [which is what i need at my skill level]

will have a go this way - thanks tarwn...again

 

[nickdel]

thompom, just popping out for lunch but I have an excellent script to build a calendar table for you.

give me an hour and I'll post it for you.

Cheers

Nick

 

[nickdel]

thompom, here's all the scripts you need (this is assuming you are using SQL Server, can be adapted for Access quite easily).

create table

CREATE TABLE dbo.Calendar  
(  
    dt SMALLDATETIME NOT NULL 
        PRIMARY KEY CLUSTERED,  
     
    isWeekday BIT, 
    isHoliday BIT,  
    Y SMALLINT,  
    FY SMALLINT,  
    Q TINYINT,  
    M TINYINT,  
    D TINYINT,  
    DW TINYINT, 
    monthname VARCHAR(9), 
    dayname VARCHAR(9), 
    W TINYINT,
    UTCOffset TINYINT NULL,
    HolidayDescription VARCHAR(32)  
)

set days

SET NOCOUNT ON 
DECLARE @dt SMALLDATETIME 
SET @dt = '20200101' 
WHILE @dt < '20300101' 
BEGIN 
    INSERT dbo.Calendar(dt) SELECT convert(smalldatetime,@dt) 
    SET @dt = @dt + 1 
END

Set week days

UPDATE dbo.Calendar SET 
 
    isWeekday = CASE  
        WHEN DATEPART(DW, dt) IN (1,7)  
        THEN 0 
        ELSE 1 END, 
 
    isHoliday = 0, 
 
    Y = YEAR(dt),  
 
    FY = YEAR(dt), 
 
/* 
    FY = CASE  
        WHEN MONTH(dt) < 5 
        THEN YEAR(dt)-1  
        ELSE YEAR(dt) END, 
    */ 
 
    Q = CASE 
        WHEN MONTH(dt) <= 3 THEN 1 
        WHEN MONTH(dt) <= 6 THEN 2 
        WHEN MONTH(dt) <= 9 THEN 3 
        ELSE 4 END,  
 
    M = MONTH(dt),  
 
    D = DAY(dt),  
 
    DW = DATEPART(DW, dt),  
 
    monthname = DATENAME(MONTH, dt),  
 
    dayname = DATENAME(DW, dt),  
 
    W = DATEPART(WK, dt)

create function for setting week numbers

CREATE FUNCTION dbo.ISOWeek 
( 
    @dt SMALLDATETIME 
) 
RETURNS TINYINT 
AS 
BEGIN 
    DECLARE @ISOweek TINYINT 
 
    SET @ISOweek = DATEPART(WEEK,@dt)+1 
        -DATEPART(WEEK,RTRIM(YEAR(@dt))+'0104') 
 
    IF @ISOweek = 0 
    BEGIN 
        SET @ISOweek = dbo.ISOweek 
        ( 
            RTRIM(YEAR(@dt)-1)+'12'+RTRIM(24+DAY(@dt)) 
        ) + 1 
    END 
 
    IF MONTH(@dt) = 12 AND DAY(@dt)-DATEPART(DW,@dt) >= 28 
    BEGIN 
        SET @ISOweek=1 
    END 
 
    RETURN(@ISOweek) 
END 
GO

set the weeks using previous function

UPDATE Calendar SET W = dbo.ISOWeek(dt)

this is just to fix a couple of dates

SET NOCOUNT ON 
DECLARE @dt SMALLDATETIME 
DECLARE @offset TINYINT 
SET @offset = 5 
 
DECLARE c CURSOR 
    LOCAL STATIC READ_ONLY FOR  
    SELECT dt FROM dbo.Calendar ORDER BY dt 
 
OPEN c 
 
FETCH NEXT FROM c INTO @dt 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
    IF DATENAME(dw, @dt)='Sunday' 
        AND DATEPART(DAY, @dt) <= 7 
        AND DATENAME(MONTH, @dt) = 'April' 
         
        SET @offset = 4 
 
    IF DATENAME(dw, @dt)='Sunday' 
        AND DATEPART(DAY, @dt) >= 25 
        AND DATENAME(MONTH, @dt) = 'October' 
         
        SET @offset = 5 
 
    UPDATE dbo.Calendar SET UTCOffset = @offset WHERE dt = @dt 
 
    FETCH NEXT FROM c INTO @dt 
END 
 
CLOSE c 
DEALLOCATE c 
 
GO

Think thats about it, good luck!

Nick

 

[Tarwn]

Calendar tables do seem to be wasteful and it wasn't until recently I read a really good set of articles on them and realized why they can be a good thing.
Look at your current situation. If you had done the extra work in your ASP script then you would have been eating another half a second every time someone hit the page. Contrast that to the cost of the above query and 700 records (a few MB of hard drive space?). Basically it's trading a small amount of disk space (which is seriously cheap) to free up resources (CPU, RAM) that are more expensive (in terms of what the other fifty people accessing webpages, the database, or whatever are trying to do). While it is only a minor savings, the storage space for the table is even more minor.

Here's one link, I couldn't find the one I was specifically looking for though:

http://sqlserver2000.databases.aspf...nsider-using-an-auxiliary-calendar-table.html

Best MS KB Ever:

http://support.microsoft.com/kb/555375

 

[thompom]

hi - thanks for your replies, have been away over the weekend, i am using mysql so am trying to convert the sql
to mysql