nested query hell

I

Ian

i'm trying to use a nested query (previous post under counting problem but no
solution found). Here's what I've done so far:

SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre,
Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus, dbo_Appt.ApptTypeID,
dbo_Appt.OfficeID
FROM dbo_Appt
GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;

With this I'm getting a count of apptID for each distinct date. What I want
though is a count with different where clauses for each distinct apptdate

For grouping 
 a count of apptID with an apptdate between 0-30days prior
to the distinct [apptdate] and an apptstatus of 3 or 4

For grouping [Post] a count of apptID with an apptdate greater than the
distinct [apptdate] and an apptstatus of 0.

I have no idea how to create these nested queries and how to create a the
temporary value of distinct date to compare.  All help is appreciated.  thx.
Ian
 
I

Ian

update -- the following sql gives me the distinct date and has the subquery I
think i need but all the counts are 0. How do I carry the 1st column result
(distinct date) into the WHERE clause of the subquery?

SELECT DISTINCT ApptDate AS DISTDATE,
(SELECT Count(ApptID) FROM dbo_appt WHERE apptdate Between distdate-30 And
distdate) AS Pre
FROM dbo_Appt;
 
G

Gary Walter

Hi Ian,

I wonder if it would be to your advantange
to use a passthrough and use SQL Server
syntax....

Nevertheless, try

SELECT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

good luck,

gary


Ian said:
update -- the following sql gives me the distinct date and has the
subquery I
think i need but all the counts are 0. How do I carry the 1st column
result
(distinct date) into the WHERE clause of the subquery?

SELECT DISTINCT ApptDate AS DISTDATE,
(SELECT Count(ApptID) FROM dbo_appt WHERE apptdate Between distdate-30
And
distdate) AS Pre
FROM dbo_Appt;


Ian said:
i'm trying to use a nested query (previous post under counting problem
but no
solution found). Here's what I've done so far:

SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre,
Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus, dbo_Appt.ApptTypeID,
dbo_Appt.OfficeID
FROM dbo_Appt
GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;

With this I'm getting a count of apptID for each distinct date. What I
want
though is a count with different where clauses for each distinct apptdate

For grouping 
 a count of apptID with an apptdate between 0-30days
prior
to the distinct [apptdate] and an apptstatus of 3 or 4

For grouping [Post] a count of apptID with an apptdate greater than the
distinct [apptdate] and an apptstatus of 0.

I have no idea how to create these nested queries and how to create a the
temporary value of distinct date to compare.  All help is appreciated.
thx.
Ian
Click to expand...
Click to expand...
 
G

Gary Walter

sorry..either have to wrap subquery in irrelevant
aggregate (say MAX) if use GROUP BY

SELECT
D.ApptDate AS DISTDATE,
MAX(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

or, just use your DISTINCT

SELECT DISTINCT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D;


I wonder if it would be to your advantange
to use a passthrough and use SQL Server
syntax....

Nevertheless, try

SELECT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

good luck,

gary


Ian said:
update -- the following sql gives me the distinct date and has the
subquery I
think i need but all the counts are 0. How do I carry the 1st column
result
(distinct date) into the WHERE clause of the subquery?

SELECT DISTINCT ApptDate AS DISTDATE,
(SELECT Count(ApptID) FROM dbo_appt WHERE apptdate Between distdate-30
And
distdate) AS Pre
FROM dbo_Appt;


Ian said:
i'm trying to use a nested query (previous post under counting problem
but no
solution found). Here's what I've done so far:

SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre,
Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus,
dbo_Appt.ApptTypeID,
dbo_Appt.OfficeID
FROM dbo_Appt
GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;

With this I'm getting a count of apptID for each distinct date. What I
want
though is a count with different where clauses for each distinct
apptdate

For grouping 
 a count of apptID with an apptdate between 0-30days
prior
to the distinct [apptdate] and an apptstatus of 3 or 4

For grouping [Post] a count of apptID with an apptdate greater than the
distinct [apptdate] and an apptstatus of 0.

I have no idea how to create these nested queries and how to create a
the
temporary value of distinct date to compare.  All help is appreciated.
thx.
Ian
Click to expand...
Click to expand...
Click to expand...
 
I

Ian

I put the following statment in and set it to work and left it for 1.5hours
and it said it was about 1/3 through. Any ideas what I've so wrong to bog it
down?

SELECT DISTINCT D.ApptDate AS DISTDATE,
(SELECT Count(Q.ApptID)
FROM dbo_appt as Q
WHERE Q.apptdate>=D.ApptDate-30
And Q.apptDate < D.apptDate +1) AS Pre
FROM dbo_Appt As D;

Gary Walter said:
sorry..either have to wrap subquery in irrelevant
aggregate (say MAX) if use GROUP BY

SELECT
D.ApptDate AS DISTDATE,
MAX(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

or, just use your DISTINCT

SELECT DISTINCT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D;


I wonder if it would be to your advantange
to use a passthrough and use SQL Server
syntax....

Nevertheless, try

SELECT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

good luck,

gary


Ian said:
update -- the following sql gives me the distinct date and has the
subquery I
think i need but all the counts are 0. How do I carry the 1st column
result
(distinct date) into the WHERE clause of the subquery?

SELECT DISTINCT ApptDate AS DISTDATE,
(SELECT Count(ApptID) FROM dbo_appt WHERE apptdate Between distdate-30
And
distdate) AS Pre
FROM dbo_Appt;


:

i'm trying to use a nested query (previous post under counting problem
but no
solution found). Here's what I've done so far:

SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS Pre,
Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus,
dbo_Appt.ApptTypeID,
dbo_Appt.OfficeID
FROM dbo_Appt
GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;

With this I'm getting a count of apptID for each distinct date. What I
want
though is a count with different where clauses for each distinct
apptdate

For grouping 
 a count of apptID with an apptdate between 0-30days
prior
to the distinct [apptdate] and an apptstatus of 3 or 4

For grouping [Post] a count of apptID with an apptdate greater than the
distinct [apptdate] and an apptstatus of 0.

I have no idea how to create these nested queries and how to create a
the
temporary value of distinct date to compare.  All help is appreciated.
thx.
Ian
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Ian,

Open up Query Designer (SQL SERVER)
and see what performance you get
(change "dbo_appt" to "dbo.Appt")

it may be you will get okay performance
as a pass-through...

it is really asking alot if your table is huge..

it's like opening a recordset to distinct dates,
then moving through each record getting the
date, rebuilding and running the subquery
based on that records date; made only worse
if no index on ApptDate which Access can use
and because of the Access-to-SQL interface...


Ian said:
I put the following statment in and set it to work and left it for 1.5hours
and it said it was about 1/3 through. Any ideas what I've so wrong to bog
it
down?

SELECT DISTINCT D.ApptDate AS DISTDATE,
(SELECT Count(Q.ApptID)
FROM dbo_appt as Q
WHERE Q.apptdate>=D.ApptDate-30
And Q.apptDate < D.apptDate +1) AS Pre
FROM dbo_Appt As D;

Gary Walter said:
sorry..either have to wrap subquery in irrelevant
aggregate (say MAX) if use GROUP BY

SELECT
D.ApptDate AS DISTDATE,
MAX(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

or, just use your DISTINCT

SELECT DISTINCT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D;


I wonder if it would be to your advantange
to use a passthrough and use SQL Server
syntax....

Nevertheless, try

SELECT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

good luck,

gary


:
update -- the following sql gives me the distinct date and has the
subquery I
think i need but all the counts are 0. How do I carry the 1st column
result
(distinct date) into the WHERE clause of the subquery?

SELECT DISTINCT ApptDate AS DISTDATE,
(SELECT Count(ApptID) FROM dbo_appt WHERE apptdate Between
distdate-30
And
distdate) AS Pre
FROM dbo_Appt;


:

i'm trying to use a nested query (previous post under counting
problem
but no
solution found). Here's what I've done so far:

SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS
Pre,
Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus,
dbo_Appt.ApptTypeID,
dbo_Appt.OfficeID
FROM dbo_Appt
GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;

With this I'm getting a count of apptID for each distinct date. What
I
want
though is a count with different where clauses for each distinct
apptdate

For grouping 
 a count of apptID with an apptdate between
0-30days
prior
to the distinct [apptdate] and an apptstatus of 3 or 4

For grouping [Post] a count of apptID with an apptdate greater than
the
distinct [apptdate] and an apptstatus of 0.

I have no idea how to create these nested queries and how to create a
the
temporary value of distinct date to compare.  All help is
appreciated.
thx.
Ian
Click to expand...
Click to expand...
Click to expand...
 
G

Gary Walter

Hi Ian,

You may have maybe moved on, but...

All my programs connect to SQL Server
via mdb's. If that is your case also, maybe
you would be interested in how I think I
would probably attack this problem (untested).

I would bring the ApptDate and Count for each
date into a "scratch" table (say "tblDateCounts")
into Access. This should be a fairly quick exercise.

Start with a make-table query that just counts ApptID's
for each ApptDate.

SELECT
D.ApptDate As DistDate,
Count(D.ApptID) AS DayCnt
INTO
tblDateCounts
FROM
dbo_Appt AS D
GROUP BY
D.ApptDate
ORDER BY
D.ApptDate;

the above should happen nearly instantly
if ApptDate is indexed...

if so, change it to an append query and
save it (say as "qryapptblDateCounts")

Next, open tblDateCounts in Design Mode
and add a Long field (say "Prev30DaysCnt"),
default 0, plus make DistDate the primary key.

Now (with all data in Access) an update query
to sum previous 30 days of DayCnt's should not
be such a "time-downer," probably even if you
simply resort to the slow domain function DSUM().
It just isn't that many records...say you had 10 years
of data for every date --> 10*365 records (ignoring
leap years).

UPDATE tblDateCounts AS T
SET T.Prev30DaysCnt =
DSUM("DayCnt", "tblDateCounts",
"[DistDate]>=#" & T.DistDate - 30 & "#
AND
[DistDate]<#" & T.DistDate + 1 & "#");

So...

you start a process where you need these values..

first, clear tblDateCounts
DELETE * FROM tblDateCounts

then run saved append query
(or, to make even quicker, determine a range
of dates you really only need and use these in
WHERE clause of your append query)

then run the update query.

Now all calculations are done...
every place you need them
you "pluck them from the tree"
instead of "planting a tree, waiting
for it to grow, and then bear fruit."

Please respond back if I have not
been clear about something.

good luck,

gary


Ian said:
I put the following statment in and set it to work and left it for 1.5hours
and it said it was about 1/3 through. Any ideas what I've so wrong to bog
it
down?

SELECT DISTINCT D.ApptDate AS DISTDATE,
(SELECT Count(Q.ApptID)
FROM dbo_appt as Q
WHERE Q.apptdate>=D.ApptDate-30
And Q.apptDate < D.apptDate +1) AS Pre
FROM dbo_Appt As D;

Gary Walter said:
sorry..either have to wrap subquery in irrelevant
aggregate (say MAX) if use GROUP BY

SELECT
D.ApptDate AS DISTDATE,
MAX(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

or, just use your DISTINCT

SELECT DISTINCT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D;


I wonder if it would be to your advantange
to use a passthrough and use SQL Server
syntax....

Nevertheless, try

SELECT
D.ApptDate AS DISTDATE,
(SELECT
Count(Q.ApptID)
FROM
dbo_appt AS Q
WHERE
Q.apptdate>= D.ApptDate - 30
AND
Q.ApptDate < D.ApptDate + 1 ) AS Pre
FROM dbo_Appt AS D
GROUP BY
D.ApptDate;

good luck,

gary


:
update -- the following sql gives me the distinct date and has the
subquery I
think i need but all the counts are 0. How do I carry the 1st column
result
(distinct date) into the WHERE clause of the subquery?

SELECT DISTINCT ApptDate AS DISTDATE,
(SELECT Count(ApptID) FROM dbo_appt WHERE apptdate Between
distdate-30
And
distdate) AS Pre
FROM dbo_Appt;


:

i'm trying to use a nested query (previous post under counting
problem
but no
solution found). Here's what I've done so far:

SELECT DISTINCT dbo_Appt.ApptDate AS TMP, Count(dbo_Appt.ApptID) AS
Pre,
Count(dbo_Appt.ApptID) AS Post, dbo_Appt.ApptStatus,
dbo_Appt.ApptTypeID,
dbo_Appt.OfficeID
FROM dbo_Appt
GROUP BY dbo_Appt.ApptDate, dbo_Appt.ApptTypeID, dbo_Appt.OfficeID;

With this I'm getting a count of apptID for each distinct date. What
I
want
though is a count with different where clauses for each distinct
apptdate

For grouping 
 a count of apptID with an apptdate between
0-30days
prior
to the distinct [apptdate] and an apptstatus of 3 or 4

For grouping [Post] a count of apptID with an apptdate greater than
the
distinct [apptdate] and an apptstatus of 0.

I have no idea how to create these nested queries and how to create a
the
temporary value of distinct date to compare.  All help is
appreciated.
thx.
Ian
Click to expand...
Click to expand...
Click to expand...
 

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

counting problem 4
query dates that show previous appointment AND the next appointmen 6
Delete Type Query 2
Counting distinct? 2
multiple grouped counts in one query 8
Replacing COUNT(DISTINCT column) in an Access 03 query 0
Error 3070 and a CrossTab Query 4
count unique - yet more info 4

Top