Calculated Query Fields Using DateDif Function

B

Bill Hamill

Greetings,

I'm having a *major* problem with some query logic and was
hoping somebody could help me 'think outside the box'.
Here's my pseudo-code:

Search Reservations Table within a user specified time
frame (Start Date & End Date ... parameters)

Output a generic label, Reservations.ArrivalDate, Sum
(Reservations.Units)

Discriminators

1. Reservations.MRKT = "I"
2. Reservations.ArrivalDate - Reservations.BookingDate <=
7 days

Real basic: I need the query to return all results from
the user-specified time frame, but the daily totals can
only be drawn from the past 7 days.

Now here where I'm screwing up: The calculations (Arrival
Date, Rooms Sold This Past Week) are exact, but ONLY for 7
Days! Looks as if my 'Where' Statement is pulling double
duty; restricting the sum calculation to just the previous
week AND RESTRICTING THE DATES RETURNED TO ONLY 7 DAYS!




INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
HAVING (((Reservations.ARRIVAL) Between [Start Date] And
[End Date]))
ORDER BY Reservations.ARRIVAL;
 
K

Ken Snell [MVP]

Not knowing what the data are nor which dates you're entering for start and
end, let me make a general observation. Your query is doing a second filter
through the HAVING clause. What this query does is select records that meet
the WHERE clause constraints, then it GROUP BY acts on those records, and
then it tests for the grouped records that meet the HAVING clause
constraint.

My guess is that this HAVING clause is what is causing you to see just the
limited set of records. Perhaps it should be changed to be part of the WHERE
clause:

INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((Reservations.ARRIVAL) Between [Start Date] And
[End Date])) AND
((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
ORDER BY Reservations.ARRIVAL;
 
B

Bill Hamill

The data relate to hotel reservations. Specifically,
rooms booked over the past 7 days, in order of arrival
date for whatever period my boss wants to look at
(generally 150 days out).

I'm shooting for the follwing results:

Generic Label (i.e. Market Segment)
Arrival Day (Date), ascending sort
Room Sold (How many rooms were booked for this arrival
date in the past 7 days; Arrival Date - Booking Date <=7)

-----Original Message-----
Not knowing what the data are nor which dates you're entering for start and
end, let me make a general observation. Your query is doing a second filter
through the HAVING clause. What this query does is select records that meet
the WHERE clause constraints, then it GROUP BY acts on those records, and
then it tests for the grouped records that meet the HAVING clause
constraint.

My guess is that this HAVING clause is what is causing you to see just the
limited set of records. Perhaps it should be changed to be part of the WHERE
clause:

INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((Reservations.ARRIVAL) Between [Start Date] And
[End Date])) AND
((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
ORDER BY Reservations.ARRIVAL;


--

Ken Snell
<MS ACCESS MVP>


Bill Hamill said:
Greetings,

I'm having a *major* problem with some query logic and was
hoping somebody could help me 'think outside the box'.
Here's my pseudo-code:

Search Reservations Table within a user specified time
frame (Start Date & End Date ... parameters)

Output a generic label, Reservations.ArrivalDate, Sum
(Reservations.Units)

Discriminators

1. Reservations.MRKT = "I"
2. Reservations.ArrivalDate - Reservations.BookingDate <=
7 days

Real basic: I need the query to return all results from
the user-specified time frame, but the daily totals can
only be drawn from the past 7 days.

Now here where I'm screwing up: The calculations (Arrival
Date, Rooms Sold This Past Week) are exact, but ONLY for 7
Days! Looks as if my 'Where' Statement is pulling double
duty; restricting the sum calculation to just the previous
week AND RESTRICTING THE DATES RETURNED TO ONLY 7 DAYS!




INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
HAVING (((Reservations.ARRIVAL) Between [Start Date] And
[End Date]))
ORDER BY Reservations.ARRIVAL;
Click to expand...


.
Click to expand...
 
K

Ken Snell [MVP]

I'm not clear about the logic flow here, sorry. It'd help if I could see
some actual data values to help me see what's working/not working.

Can you post a few example records and which ones you want to be selected
when you enter a specific start date and an end date?


--

Ken Snell
<MS ACCESS MVP>

Bill Hamill said:
The data relate to hotel reservations. Specifically,
rooms booked over the past 7 days, in order of arrival
date for whatever period my boss wants to look at
(generally 150 days out).

I'm shooting for the follwing results:

Generic Label (i.e. Market Segment)
Arrival Day (Date), ascending sort
Room Sold (How many rooms were booked for this arrival
date in the past 7 days; Arrival Date - Booking Date <=7)

-----Original Message-----
Not knowing what the data are nor which dates you're entering for start and
end, let me make a general observation. Your query is doing a second filter
through the HAVING clause. What this query does is select records that meet
the WHERE clause constraints, then it GROUP BY acts on those records, and
then it tests for the grouped records that meet the HAVING clause
constraint.

My guess is that this HAVING clause is what is causing you to see just the
limited set of records. Perhaps it should be changed to be part of the WHERE
clause:

INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((Reservations.ARRIVAL) Between [Start Date] And
[End Date])) AND
((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
ORDER BY Reservations.ARRIVAL;


--

Ken Snell
<MS ACCESS MVP>


Bill Hamill said:
Greetings,

I'm having a *major* problem with some query logic and was
hoping somebody could help me 'think outside the box'.
Here's my pseudo-code:

Search Reservations Table within a user specified time
frame (Start Date & End Date ... parameters)

Output a generic label, Reservations.ArrivalDate, Sum
(Reservations.Units)

Discriminators

1. Reservations.MRKT = "I"
2. Reservations.ArrivalDate - Reservations.BookingDate <=
7 days

Real basic: I need the query to return all results from
the user-specified time frame, but the daily totals can
only be drawn from the past 7 days.

Now here where I'm screwing up: The calculations (Arrival
Date, Rooms Sold This Past Week) are exact, but ONLY for 7
Days! Looks as if my 'Where' Statement is pulling double
duty; restricting the sum calculation to just the previous
week AND RESTRICTING THE DATES RETURNED TO ONLY 7 DAYS!




INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
HAVING (((Reservations.ARRIVAL) Between [Start Date] And
[End Date]))
ORDER BY Reservations.ARRIVAL;
Click to expand...


.
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Bill,

Ken has correctly identified how difficult
your question is to decipher without example
data, but on a *wild hunch*, you might try
the following for your Select portion of your
INSERT query:

SELECT
"Internet" AS [Sub-Market],
Reservations.ARRIVAL AS [Arrival Date],
Max(SELECT
Sum(R.UNITS)
FROM Reservations As R
WHERE
(R.ARRIVAL = Reservations.ARRIVAL)
AND
(DateDiff("d",R.[BOOKING], R.[ARRIVAL])<=7)
AND
(R.CANCEL Is Null)
AND
(R.MRKT="I")) AS UnitCnt
WHERE
Reservations.ARRIVAL Between [Start Date] And [End Date]
GROUP BY "Internet", Reservations.ARRIVAL
ORDER BY Reservations.ARRIVAL;

just a wild hunch and obviously untested...

Good luck,

Gary Walter

Bill Hamill said:
The data relate to hotel reservations. Specifically,
rooms booked over the past 7 days, in order of arrival
date for whatever period my boss wants to look at
(generally 150 days out).

I'm shooting for the follwing results:

Generic Label (i.e. Market Segment)
Arrival Day (Date), ascending sort
Room Sold (How many rooms were booked for this arrival
date in the past 7 days; Arrival Date - Booking Date <=7)

-----Original Message-----
Not knowing what the data are nor which dates you're entering for start and
end, let me make a general observation. Your query is doing a second filter
through the HAVING clause. What this query does is select records that meet
the WHERE clause constraints, then it GROUP BY acts on those records, and
then it tests for the grouped records that meet the HAVING clause
constraint.

My guess is that this HAVING clause is what is causing you to see just the
limited set of records. Perhaps it should be changed to be part of the WHERE
clause:

INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((Reservations.ARRIVAL) Between [Start Date] And
[End Date])) AND
((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
ORDER BY Reservations.ARRIVAL;


--

Ken Snell
<MS ACCESS MVP>


Bill Hamill said:
Greetings,

I'm having a *major* problem with some query logic and was
hoping somebody could help me 'think outside the box'.
Here's my pseudo-code:

Search Reservations Table within a user specified time
frame (Start Date & End Date ... parameters)

Output a generic label, Reservations.ArrivalDate, Sum
(Reservations.Units)

Discriminators

1. Reservations.MRKT = "I"
2. Reservations.ArrivalDate - Reservations.BookingDate <=
7 days

Real basic: I need the query to return all results from
the user-specified time frame, but the daily totals can
only be drawn from the past 7 days.

Now here where I'm screwing up: The calculations (Arrival
Date, Rooms Sold This Past Week) are exact, but ONLY for 7
Days! Looks as if my 'Where' Statement is pulling double
duty; restricting the sum calculation to just the previous
week AND RESTRICTING THE DATES RETURNED TO ONLY 7 DAYS!




INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
HAVING (((Reservations.ARRIVAL) Between [Start Date] And
[End Date]))
ORDER BY Reservations.ARRIVAL;
Click to expand...


.
Click to expand...
Click to expand...
 
G

Gary Walter

was the dearth of response due to me
forgetting FROM clause in main query?

SELECT
"Internet" AS [Sub-Market],
Reservations.ARRIVAL AS [Arrival Date],
Max(SELECT
Sum(R.UNITS)
FROM Reservations As R
WHERE
(R.ARRIVAL = Reservations.ARRIVAL)
AND
(DateDiff("d",R.[BOOKING], R.[ARRIVAL])<=7)
AND
(R.CANCEL Is Null)
AND
(R.MRKT="I")) AS UnitCnt
FROM Reservations
WHERE
Reservations.ARRIVAL Between [Start Date] And [End Date]
GROUP BY "Internet", Reservations.ARRIVAL
ORDER BY Reservations.ARRIVAL;

Gary Walter said:
Hi Bill,

Ken has correctly identified how difficult
your question is to decipher without example
data, but on a *wild hunch*, you might try
the following for your Select portion of your
INSERT query:

SELECT
"Internet" AS [Sub-Market],
Reservations.ARRIVAL AS [Arrival Date],
Max(SELECT
Sum(R.UNITS)
FROM Reservations As R
WHERE
(R.ARRIVAL = Reservations.ARRIVAL)
AND
(DateDiff("d",R.[BOOKING], R.[ARRIVAL])<=7)
AND
(R.CANCEL Is Null)
AND
(R.MRKT="I")) AS UnitCnt
WHERE
Reservations.ARRIVAL Between [Start Date] And [End Date]
GROUP BY "Internet", Reservations.ARRIVAL
ORDER BY Reservations.ARRIVAL;

just a wild hunch and obviously untested...

Good luck,

Gary Walter

Bill Hamill said:
The data relate to hotel reservations. Specifically,
rooms booked over the past 7 days, in order of arrival
date for whatever period my boss wants to look at
(generally 150 days out).

I'm shooting for the follwing results:

Generic Label (i.e. Market Segment)
Arrival Day (Date), ascending sort
Room Sold (How many rooms were booked for this arrival
date in the past 7 days; Arrival Date - Booking Date <=7)

-----Original Message-----
Not knowing what the data are nor which dates you're entering for start and
end, let me make a general observation. Your query is doing a second filter
through the HAVING clause. What this query does is select records that meet
the WHERE clause constraints, then it GROUP BY acts on those records, and
then it tests for the grouped records that meet the HAVING clause
constraint.

My guess is that this HAVING clause is what is causing you to see just the
limited set of records. Perhaps it should be changed to be part of the WHERE
clause:

INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((Reservations.ARRIVAL) Between [Start Date] And
[End Date])) AND
((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
ORDER BY Reservations.ARRIVAL;


--

Ken Snell
<MS ACCESS MVP>


Greetings,

I'm having a *major* problem with some query logic and was
hoping somebody could help me 'think outside the box'.
Here's my pseudo-code:

Search Reservations Table within a user specified time
frame (Start Date & End Date ... parameters)

Output a generic label, Reservations.ArrivalDate, Sum
(Reservations.Units)

Discriminators

1. Reservations.MRKT = "I"
2. Reservations.ArrivalDate - Reservations.BookingDate <=
7 days

Real basic: I need the query to return all results from
the user-specified time frame, but the daily totals can
only be drawn from the past 7 days.

Now here where I'm screwing up: The calculations (Arrival
Date, Rooms Sold This Past Week) are exact, but ONLY for 7
Days! Looks as if my 'Where' Statement is pulling double
duty; restricting the sum calculation to just the previous
week AND RESTRICTING THE DATES RETURNED TO ONLY 7 DAYS!




INSERT INTO RoomSaleCount ( [Sub-Market], [Arrival Date],
[Rooms Sold] )
SELECT "Internet" AS [Sub-Market], Reservations.ARRIVAL AS
[Arrival Date], Sum(Reservations.UNITS) AS [Rooms Sold]
FROM Reservations
WHERE (((DateDiff("d",[Reservations]![BOOKING],
[Reservations]![ARRIVAL]))<=7) AND ((Reservations.CANCEL)
Is Null) AND ((Reservations.MRKT)="I"))
GROUP BY "Internet", Reservations.ARRIVAL
HAVING (((Reservations.ARRIVAL) Between [Start Date] And
[End Date]))
ORDER BY Reservations.ARRIVAL;



.
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

or more probably:

SELECT
"Internet" AS [Sub-Market],
Reservations.ARRIVAL AS [Arrival Date],
Max(SELECT
Sum(R.UNITS)
FROM Reservations As R
WHERE
(R.ARRIVAL = Reservations.ARRIVAL)
AND
(DateDiff("d",R.[BOOKING], R.[ARRIVAL])<=7)
AND
(R.CANCEL Is Null)
AND
(R.MRKT="I")) AS UnitCnt
FROM Reservations
WHERE
(Reservations.MRKT = "I")
AND
(Reservations.CANCEL IS NULL)
AND
(Reservations.ARRIVAL Between [Start Date] And [End Date])
GROUP BY "Internet", Reservations.ARRIVAL
ORDER BY Reservations.ARRIVAL;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date Difference In Query 2
Query Trouble 1
Query filter using a moving date....HELP!! 3
AVG Function in a Query 5
Dates and Iif function in query criteria 4
How to query by date using individual columns for Months? 1
History of Xbox 360 defects - Dean Takahashi has the inside story(long) 2
another take on Xbox 360's genesis (originally 60 shader GPU - 16 core CPU) 3

Top