Hi Guys,

What's the most efficient way in SQL to get the number of days of a reservation that fall
between a date range.
For example a record stores the arrival and departure dates:

ARRIVE        DEPART
-----------------------------
2007-01-01   2007-01-15

Now I want to calculate the number of days that fall into in my reports date range, say
2006-12-25 to 2007-01-10

Yeah OK I know it's 10 days but I'm trying to work out the most efficient way and of
course the other scenarios where the reservation covers the whole report range, falls
inside the report range and where the arrival falls in the report range but the departure
date is after.

Here's the sort of thing, just wonder how this can be efficiently done in SQL?

if (ARRIVAL >= PICKFROM && DEPARTURE <= PICKTO)
 TotalNightsQuery->FieldValues["TotalNights"] = DEPARTURE - ARRIVAL;

if (ARRIVAL <= PICKFROM && DEPARTURE >= PICKTO)
 TotalNightsQuery->FieldValues["TotalNights"] = PICKTO - PICKFROM;

if (ARRIVAL <= PICKFROM && DEPARTURE <= PICKTO)
 TotalNightsQuery->FieldValues["TotalNights"] = DEPARTURE - PICKFROM;

if (ARRIVAL >= PICKFROM && DEPARTURE >= PICKTO)
 TotalNightsQuery->FieldValues["TotalNights"] = (PICKTO - ARRIVAL);

Thanks Guys & Gals,

Phil.

Phil,

<< What's the most efficient way in SQL to get the number of days of a
reservation that fall between a date range.
For example a record stores the arrival and departure dates:

ARRIVE        DEPART
-----------------------------
2007-01-01   2007-01-15

Now I want to calculate the number of days that fall into in my reports
date range, say 2006-12-25 to 2007-01-10

Yeah OK I know it's 10 days but I'm trying to work out the most efficient
way and of course the other scenarios where the reservation covers the whole
report range, falls inside the report range and where the arrival falls in
the report range but the departure date is after. >>

Just subtract the dates to get the number of days to use in the
calculations.  The rest is just a matter of ANDing and ORing the conditions
together to get the desired result in terms of overlapping periods of days.

--
Tim Young
Elevate Software
www.elevatesoft.com

<< Just subtract the dates to get the number of days to use in the
calculations.  The rest is just a matter of ANDing and ORing the conditions
together to get the desired result in terms of overlapping periods of days.
>>

Thanks Tim,

It's the syntax I'm having trouble with, IF ELSE THEN to get the desired result of days in
a field. I have tried everything to get this working.
I apologise again for the lack of knowledge here,

Cheers,

<<
It's the syntax I'm having trouble with, IF ELSE THEN to get the desired result of days in
a field. I have tried everything to get this working.
I apologise again for the lack of knowledge here,
>>

After some more head scratching I figured it out and this seems to work perfecty and like
lightening (0.094 sec) for a whole month.

Thanks again for the help

SELECT UPPER(suburb) AS ReportField,
'' AS Description,
SUM(
IF (CAST(ar_time AS DATE) >= CAST('2007-04-01' AS DATE) AND CAST(dep_time AS DATE) <=
CAST('2007-04-30' AS DATE)
THEN CAST(dep_time AS DATE) - CAST(ar_time AS DATE)

ELSE IF (CAST(ar_time AS DATE) <= CAST('2007-04-01' AS DATE) AND CAST(dep_time AS DATE) >=
CAST('2007-04-30' AS DATE)
THEN CAST('2007-04-30' AS DATE) - CAST('2007-04-01' AS DATE)

ELSE IF (CAST(ar_time AS DATE) <= CAST('2007-04-01' AS DATE) AND CAST(dep_time AS DATE) <=
CAST('2007-04-30' AS DATE)
THEN CAST(dep_time AS DATE) - CAST('2007-04-01' AS DATE)

ELSE IF (CAST(ar_time AS DATE) >= CAST('2007-04-01' AS DATE) AND CAST(dep_time AS DATE) >=
CAST('2007-04-30' AS DATE)
THEN CAST('2007-04-30' AS DATE) - CAST(ar_time AS DATE)
ELSE 0))))) AS totalnights,

SUM(journal_history.trans_total) / SUM(CAST(history.dep_time AS DATE) -
CAST(history.ar_time AS DATE)) AS total_acc_rev,
SUM(journal_history.trans_total) AS TransTotal

FROM history

JOIN journal_history ON history.res_num = journal_history.res_num
JOIN department ON journal_history.trans_department = department.department_code

WHERE CAST(journal_history.trans_date AS DATE) BETWEEN '2007-04-01' AND '2007-04-30'

AND journal_history.trans_total > 0
AND department.accom = True

GROUP BY ReportField ORDER BY ReportField

Phil,

<< After some more head scratching I figured it out and this seems to work
perfecty and like lightening (0.094 sec) for a whole month. >>

Sorry for not being more complete with my last answer.  I wasn't quite sure
what you were trying to do and thought that you were just putting the
conditions in the WHERE clause.  I can see now that you were trying to do
this in the SELECT list, which is a little more dicey with IF().  You might
want to consider using CASE instead since it is a little easier to read.

--
Tim Young
Elevate Software
www.elevatesoft.com

Please don't apologise Tim, you where more than helpful and yes the using the CASE makes
for easier reading:

SELECT UPPER(suburb) AS ReportField,
'' AS Description,

SUM(
CASE (true)
WHEN CAST(ar_time AS DATE) >= CAST('2007-04-01' AS DATE) AND CAST(dep_time AS DATE) <=
CAST('2007-04-30' AS DATE)
THEN CAST(dep_time AS DATE) - CAST(ar_time AS DATE)

WHEN CAST(ar_time AS DATE) <= CAST('2007-04-01' AS DATE) AND CAST(dep_time AS DATE) >=
CAST('2007-04-30' AS DATE)
THEN CAST('2007-04-30' AS DATE) - CAST('2007-04-01' AS DATE)

WHEN CAST(ar_time AS DATE) <= CAST('2007-04-01' AS DATE) AND CAST(dep_time AS DATE) <=
CAST('2007-04-30' AS DATE)
THEN CAST(dep_time AS DATE) - CAST('2007-04-01' AS DATE)

WHEN CAST(ar_time AS DATE) >= CAST('2007-04-01' AS DATE) AND CAST(dep_time AS DATE) >=
CAST('2007-04-30' AS DATE)
THEN CAST('2007-04-30' AS DATE) - CAST(ar_time AS DATE)
END
) AS TotalNights,

SUM(journal_history.trans_total) / SUM(CAST(history.dep_time AS DATE) -
CAST(history.ar_time AS DATE)) AS total_acc_rev,
SUM(journal_history.trans_total) AS TransTotal

FROM history

INNER JOIN journal_history ON history.res_num = journal_history.res_num
INNER JOIN department ON journal_history.trans_department = department.department_code

WHERE CAST(journal_history.trans_date AS DATE) BETWEEN '2007-04-01' AND '2007-04-30'

AND journal_history.trans_total > 0
AND department.accom = True

GROUP BY ReportField ORDER BY ReportField

Hi Guys,

This has one has come back to haunt me. I thought I had it licked.

For some reason this query doesn't return the correct number of
TotalNights, it seems some are close and others are 3 or 4 times the
value of what tey could possibly be.

I have been puzzling this for over a day now and if anyone has some
input as to why it's not giving the correct TotalNights I would
appreciate it.

Thanks,

Phil.

--- SQL ---

SELECT UPPER(suburb) AS ReportField,
'' AS Description,

SUM(
CASE (true)
WHEN CAST(ar_time AS DATE) >= CAST('2007-04-01' AS DATE) AND
CAST(dep_time AS DATE) <= CAST('2007-04-30' AS DATE)
THEN CAST(dep_time AS DATE) - CAST(ar_time AS DATE)

WHEN CAST(ar_time AS DATE) <= CAST('2007-04-01' AS DATE) AND
CAST(dep_time AS DATE) >= CAST('2007-04-30' AS DATE)
THEN CAST('2007-04-30' AS DATE) - CAST('2007-04-01' AS DATE)

WHEN CAST(ar_time AS DATE) <= CAST('2007-04-01' AS DATE) AND
CAST(dep_time AS DATE) <= CAST('2007-04-30' AS DATE)
THEN CAST(dep_time AS DATE) - CAST('2007-04-01' AS DATE)

WHEN CAST(ar_time AS DATE) >= CAST('2007-04-01' AS DATE) AND
CAST(dep_time AS DATE) >= CAST('2007-04-30' AS DATE)
THEN CAST('2007-04-30' AS DATE) - CAST(ar_time AS DATE)
END
) AS TotalNights,

SUM(journal_history.trans_total) / SUM(CAST(history.dep_time AS DATE) -
CAST(history.ar_time AS DATE)) AS total_acc_rev,
SUM(journal_history.trans_total) AS TransTotal

FROM history

INNER JOIN journal_history ON history.res_num = journal_history.res_num
INNER JOIN department ON journal_history.trans_department =
department.department_code

WHERE CAST(journal_history.trans_date AS DATE) BETWEEN '2007-04-01' AND
'2007-04-30'

AND journal_history.trans_total > 0
AND department.accom = True

GROUP BY ReportField ORDER BY ReportField

Making progress...

The CODE that calculates the TotalNights works on it's own, so it must
be the JOINS messing with it. Maybe there's another approach I can take
by producing the TotalNights first and then doing a UNION of some kind
and updating the the TransTotal after.

Any suggestions would be appreciated,

Cheers,

Phil.

"Phil Read" <phil@vizualweb.com> wrote in message
news:73544552-7515-45E5-921F-24AEC25219A0@news.elevatesoft.com...
> Hi Guys,
>
> This has one has come back to haunt me. I thought I had it licked.
>
> For some reason this query doesn't return the correct number of
> TotalNights, it seems some are close and others are 3 or 4 times the value
> of what tey could possibly be.
>

Heresy. SQL always returns the "correct number". It's the question that is
worng.

You are going to have to rethink the query. One (or both) or your joined
tables are generating multiple hits on your history table. For example, if
you have

Table 1
id 1 number 10
table2
id2 1
id2 2
id2 1
and you select(sum) number from table1 join table2 on id = id2, you will get
20.

Could be something else, but I bet that's the problem. Run a quick query
with just some fields and the joins (no aggregates), and you will see which
ones are the duplicated value.

Robert

> I have been puzzling this for over a day now and if anyone has some input
> as to why it's not giving the correct TotalNights I would appreciate it.
>
> Thanks,
>
> Phil.
>
> --- SQL ---
>
> SELECT UPPER(suburb) AS ReportField,
> '' AS Description,
>
> SUM(
> CASE (true)
> WHEN CAST(ar_time AS DATE) >= CAST('2007-04-01' AS DATE) AND CAST(dep_time
> AS DATE) <= CAST('2007-04-30' AS DATE)
> THEN CAST(dep_time AS DATE) - CAST(ar_time AS DATE)
>
> WHEN CAST(ar_time AS DATE) <= CAST('2007-04-01' AS DATE) AND CAST(dep_time
> AS DATE) >= CAST('2007-04-30' AS DATE)
> THEN CAST('2007-04-30' AS DATE) - CAST('2007-04-01' AS DATE)
>
> WHEN CAST(ar_time AS DATE) <= CAST('2007-04-01' AS DATE) AND CAST(dep_time
> AS DATE) <= CAST('2007-04-30' AS DATE)
> THEN CAST(dep_time AS DATE) - CAST('2007-04-01' AS DATE)
>
> WHEN CAST(ar_time AS DATE) >= CAST('2007-04-01' AS DATE) AND CAST(dep_time
> AS DATE) >= CAST('2007-04-30' AS DATE)
> THEN CAST('2007-04-30' AS DATE) - CAST(ar_time AS DATE)
> END
> ) AS TotalNights,
>
> SUM(journal_history.trans_total) / SUM(CAST(history.dep_time AS DATE) -
> CAST(history.ar_time AS DATE)) AS total_acc_rev,
> SUM(journal_history.trans_total) AS TransTotal
>
> FROM history
>
> INNER JOIN journal_history ON history.res_num = journal_history.res_num
> INNER JOIN department ON journal_history.trans_department =
> department.department_code
>
> WHERE CAST(journal_history.trans_date AS DATE) BETWEEN '2007-04-01' AND
> '2007-04-30'
>
> AND journal_history.trans_total > 0
> AND department.accom = True
>
> GROUP BY ReportField ORDER BY ReportField

Phil,

<< For some reason this query doesn't return the correct number of
TotalNights, it seems some are close and others are 3 or 4 times the value
of what tey could possibly be.

I have been puzzling this for over a day now and if anyone has some input
as to why it's not giving the correct TotalNights I would appreciate it. >>

This is the issue that I was alluding to in another thread with respect to
correlated sub-queries.  DBISAM lacks this ability, so you have to
navigationally process the table and fire off the sub-query for each master
row to get the same result.

As Robert indicated, the issue is the join causing duplicate values to
appear in the SUM() totals, and using a correlated sub-query that does the
summing would solve this issue.

--
Tim Young
Elevate Software
www.elevatesoft.com