Re: Calculated Query Fields Using DateDif Function
From: Gary Walter (garylwpleasenospam_at_wamego.net)
Date: 09/24/04
- Next message: haggr: "adding null values"
- Previous message: Laura: "Return value of prev record if null in query"
- In reply to: Gary Walter: "Re: Calculated Query Fields Using DateDif Function"
- Next in thread: Gary Walter: "Re: Calculated Query Fields Using DateDif Function"
- Reply: Gary Walter: "Re: Calculated Query Fields Using DateDif Function"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 24 Sep 2004 10:53:20 -0500
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" <garylwpleasenospam@wamego.net> wrote in message
news:eglgOQLoEHA.3876@TK2MSFTNGP15.phx.gbl...
> 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" wrote:
> > 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" <whamill@boardwalklv.com> wrote in message
> > >news:35f701c4a040$d80c6aa0$a501280a@phx.gbl...
> > >> 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;
> > >>
> > >
> > >
> > >.
> > >
>
>
- Next message: haggr: "adding null values"
- Previous message: Laura: "Return value of prev record if null in query"
- In reply to: Gary Walter: "Re: Calculated Query Fields Using DateDif Function"
- Next in thread: Gary Walter: "Re: Calculated Query Fields Using DateDif Function"
- Reply: Gary Walter: "Re: Calculated Query Fields Using DateDif Function"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
