Query process time

I have a weird issue that I just can't figure out. I have a query that runs on tables that are all located in db x. If I run the query under a different db (like use database y) then it runs in like 11 seconds. If I run the query under the db that all of the tables reside under (use database x) then it takes almost 4 minutes.

Why would it take so much longer to run under the db where all of the data/tables exists? Both dbs are on the same SQL 2005 server.

I just don't get it?

Any ideas?

S

We can't possibly give you a meaningful response without having more information. What is the query, what is the nature of the data, what is different between the two databases, etc.

Perhaps, using your description about, database X has data in it, and database Y does not?

|||If you think that execution plan will be same then why SQl statement take different time on different server. But have you checked the CPU Cost and I/O cost.

Query Performance diff

Hi @.all,

I designed a query in report designer, data tab. This query runs 24 secs, when I execute it from the data tab in report designer.

Publishing the report and running it from the report server. The query needs 40x the time as in report designer.

Looking at the Execution Log, the report needs 800 secs for TimeDataRetrieval.

Why is it so much slower to run the query from report server? The report designer has also to retrieve the data, and runs so much faster.

Thanks for any hints

T

The data tab will only retrieve data, so the report design may be the cause for bad performance. How much time does it take to preview the report in your report designer?

Shyam

|||

I'll try that,

but isn't this time which gets reported in ExecutionLog in Cols TimeProcessing and TimeRendering?

Query Performance (tearing what's left of my hair out)

I really am on my last legs with this. I am trying to improve performance on
the following query. The query runs in 2hrs. When I remove the first two
references to a13.a13.Parent_County_desc and a13.County_desc the query
returns the same amount of rows in 1 minute!!! The only index on the
POt_lu_rd_post_code a13 table is as follows: clustered, unique located on
PRIMARY [Po_rd_postcode_id], [County_id]. The execution plan shows
a
distinct sort using 100% cpu when you include this select criteria and a nic
e
hash aggregate when we don't. The volume of data is quite large and we have
to use this table instead of a lookup due to a known bug. What can I do to
improve performance on this query. Please help.
select distinct coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id,
pa3.Ra_gender_id) Ra_gender_id, a12.Ra_gender_desc
Ra_gender_desc, coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id)
Pr_cover_id,
a11.Pr_cover_desc Pr_cover_desc,
coalesce(pa1.Parent_County_id, pa2.Parent_County_id, pa3.Parent_County_id)
Parent_County_id,
a13.Parent_County_desc Parent_County_desc,
coalesce(pa1.County_id, pa2.County_id, pa3.County_id) County_id,
a13.County_desc County_desc,
coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) Area_Id,
a13.Area_Desc Area_Desc,
coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code,
pa3.Po_rd_rating_code) Po_rd_rating_code,
coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) Week_id,
a14.week_desc week_desc,
pa1.WJXBFS1 WJXBFS1,
pa2.WJXBFS1 WJXBFS2,
pa3.WJXBFS1 WJXBFS3
from marc_ZZT5J02027GMD000 pa1(nolock)
full outer join marc_ZZT5J02027GMD001 pa2(nolock)
on (pa1.Area_Id = pa2.Area_Id and
pa1.County_id = pa2.County_id and
pa1.Parent_County_id = pa2.Parent_County_id and
pa1.Po_rd_rating_code = pa2.Po_rd_rating_code and
pa1.Pr_cover_id = pa2.Pr_cover_id and
pa1.Ra_gender_id = pa2.Ra_gender_id and
pa1.Week_id = pa2.Week_id)
full outer join marc_ZZT5J02027GMD002 pa3(nolock)
on (coalesce(pa1.Area_Id, pa2.Area_Id) = pa3.Area_Id and
coalesce(pa1.County_id, pa2.County_id) = pa3.County_id and
coalesce(pa1.Parent_County_id, pa2.Parent_County_id) = pa3.Parent_County_id
and
coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code) =
pa3.Po_rd_rating_code and
coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id) = pa3.Pr_cover_id and
coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id) = pa3.Ra_gender_id and
coalesce(pa1.Week_id, pa2.Week_id) = pa3.Week_id)
join PRt_lu_Cover a11
on (coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id) =
a11.Pr_cover_id)
join RAv_lu_Gender a12
on (coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id, pa3.Ra_gender_id) =
a12.Ra_gender_id)
join POt_lu_rd_post_code a13
on (coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) = a13.Area_Id)
join TIt_lu_day a14
on (coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) = a14.Week_id)marcmc
Don't you really need 'WHERE' condition , am I right?
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:122E9FF3-5A9A-4729-B652-EA1B6956D751@.microsoft.com...
> I really am on my last legs with this. I am trying to improve performance
on
> the following query. The query runs in 2hrs. When I remove the first two
> references to a13.a13.Parent_County_desc and a13.County_desc the query
> returns the same amount of rows in 1 minute!!! The only index on the
> POt_lu_rd_post_code a13 table is as follows: clustered, unique located on
> PRIMARY [Po_rd_postcode_id], [County_id]. The execution plan show
s a
> distinct sort using 100% cpu when you include this select criteria and a
nice
> hash aggregate when we don't. The volume of data is quite large and we
have
> to use this table instead of a lookup due to a known bug. What can I do to
> improve performance on this query. Please help.
>
> select distinct coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id,
> pa3.Ra_gender_id) Ra_gender_id, a12.Ra_gender_desc
> Ra_gender_desc, coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id,
pa3.Pr_cover_id)
> Pr_cover_id,
> a11.Pr_cover_desc Pr_cover_desc,
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id, pa3.Parent_County_id)
> Parent_County_id,
> a13.Parent_County_desc Parent_County_desc,
> coalesce(pa1.County_id, pa2.County_id, pa3.County_id) County_id,
> a13.County_desc County_desc,
> coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) Area_Id,
> a13.Area_Desc Area_Desc,
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code,
> pa3.Po_rd_rating_code) Po_rd_rating_code,
> coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) Week_id,
> a14.week_desc week_desc,
> pa1.WJXBFS1 WJXBFS1,
> pa2.WJXBFS1 WJXBFS2,
> pa3.WJXBFS1 WJXBFS3
> from marc_ZZT5J02027GMD000 pa1(nolock)
> full outer join marc_ZZT5J02027GMD001 pa2(nolock)
> on (pa1.Area_Id = pa2.Area_Id and
> pa1.County_id = pa2.County_id and
> pa1.Parent_County_id = pa2.Parent_County_id and
> pa1.Po_rd_rating_code = pa2.Po_rd_rating_code and
> pa1.Pr_cover_id = pa2.Pr_cover_id and
> pa1.Ra_gender_id = pa2.Ra_gender_id and
> pa1.Week_id = pa2.Week_id)
> full outer join marc_ZZT5J02027GMD002 pa3(nolock)
> on (coalesce(pa1.Area_Id, pa2.Area_Id) = pa3.Area_Id and
> coalesce(pa1.County_id, pa2.County_id) = pa3.County_id and
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id) =
pa3.Parent_County_id
> and
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code) =
> pa3.Po_rd_rating_code and
> coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id) = pa3.Pr_cover_id and
> coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id) = pa3.Ra_gender_id and
> coalesce(pa1.Week_id, pa2.Week_id) = pa3.Week_id)
> join PRt_lu_Cover a11
> on (coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id) =
> a11.Pr_cover_id)
> join RAv_lu_Gender a12
> on (coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id, pa3.Ra_gender_id) =
> a12.Ra_gender_id)
> join POt_lu_rd_post_code a13
> on (coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) = a13.Area_Id)
> join TIt_lu_day a14
> on (coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) = a14.Week_id)
>
>|||Honestly I would say yes very much so but this query was generated by a
reporting application so unfortunately I am stuck with it!
"Uri Dimant" wrote:

> marcmc
> Don't you really need 'WHERE' condition , am I right?
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:122E9FF3-5A9A-4729-B652-EA1B6956D751@.microsoft.com...
> on
> nice
> have
> pa3.Pr_cover_id)
> pa3.Parent_County_id
>
>|||ps: the problem is massive amounts of data where the county descriptions are
involved and the distinct sort is grouping them.
"marcmc" wrote:

> I really am on my last legs with this. I am trying to improve performance
on
> the following query. The query runs in 2hrs. When I remove the first two
> references to a13.a13.Parent_County_desc and a13.County_desc the query
> returns the same amount of rows in 1 minute!!! The only index on the
> POt_lu_rd_post_code a13 table is as follows: clustered, unique located on
> PRIMARY [Po_rd_postcode_id], [County_id]. The execution plan show
s a
> distinct sort using 100% cpu when you include this select criteria and a n
ice
> hash aggregate when we don't. The volume of data is quite large and we hav
e
> to use this table instead of a lookup due to a known bug. What can I do to
> improve performance on this query. Please help.
>
> select distinct coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id,
> pa3.Ra_gender_id) Ra_gender_id, a12.Ra_gender_desc
> Ra_gender_desc, coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id
)
> Pr_cover_id,
> a11.Pr_cover_desc Pr_cover_desc,
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id, pa3.Parent_County_id)
> Parent_County_id,
> a13.Parent_County_desc Parent_County_desc,
> coalesce(pa1.County_id, pa2.County_id, pa3.County_id) County_id,
> a13.County_desc County_desc,
> coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) Area_Id,
> a13.Area_Desc Area_Desc,
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code,
> pa3.Po_rd_rating_code) Po_rd_rating_code,
> coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) Week_id,
> a14.week_desc week_desc,
> pa1.WJXBFS1 WJXBFS1,
> pa2.WJXBFS1 WJXBFS2,
> pa3.WJXBFS1 WJXBFS3
> from marc_ZZT5J02027GMD000 pa1(nolock)
> full outer join marc_ZZT5J02027GMD001 pa2(nolock)
> on (pa1.Area_Id = pa2.Area_Id and
> pa1.County_id = pa2.County_id and
> pa1.Parent_County_id = pa2.Parent_County_id and
> pa1.Po_rd_rating_code = pa2.Po_rd_rating_code and
> pa1.Pr_cover_id = pa2.Pr_cover_id and
> pa1.Ra_gender_id = pa2.Ra_gender_id and
> pa1.Week_id = pa2.Week_id)
> full outer join marc_ZZT5J02027GMD002 pa3(nolock)
> on (coalesce(pa1.Area_Id, pa2.Area_Id) = pa3.Area_Id and
> coalesce(pa1.County_id, pa2.County_id) = pa3.County_id and
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id) = pa3.Parent_County_
id
> and
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code) =
> pa3.Po_rd_rating_code and
> coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id) = pa3.Pr_cover_id and
> coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id) = pa3.Ra_gender_id and
> coalesce(pa1.Week_id, pa2.Week_id) = pa3.Week_id)
> join PRt_lu_Cover a11
> on (coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id) =
> a11.Pr_cover_id)
> join RAv_lu_Gender a12
> on (coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id, pa3.Ra_gender_id) =
> a12.Ra_gender_id)
> join POt_lu_rd_post_code a13
> on (coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) = a13.Area_Id)
> join TIt_lu_day a14
> on (coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) = a14.Week_id)
>
>|||When you say you must use this table instead of a lookup, can you elaborate?
Is it possible that you can use this without DISTINCT initially and
apply DISTINCT
elsewhere in your process where it will not require as much work? Also,
what is
the known bug you mention? Maybe someone knows it and has a workaround.
Steve Kass
Drew University
marcmc wrote:

>I really am on my last legs with this. I am trying to improve performance o
n
>the following query. The query runs in 2hrs. When I remove the first two
>references to a13.a13.Parent_County_desc and a13.County_desc the query
>returns the same amount of rows in 1 minute!!! The only index on the
>POt_lu_rd_post_code a13 table is as follows: clustered, unique located on
>PRIMARY [Po_rd_postcode_id], [County_id]. The execution plan shows
a
>distinct sort using 100% cpu when you include this select criteria and a ni
ce
>hash aggregate when we don't. The volume of data is quite large and we have
>to use this table instead of a lookup due to a known bug. What can I do to
>improve performance on this query. Please help.
>
>select distinct coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id,
>pa3.Ra_gender_id) Ra_gender_id, a12.Ra_gender_desc
>Ra_gender_desc, coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id)
>Pr_cover_id,
>a11.Pr_cover_desc Pr_cover_desc,
>coalesce(pa1.Parent_County_id, pa2.Parent_County_id, pa3.Parent_County_id)
>Parent_County_id,
>a13.Parent_County_desc Parent_County_desc,
>coalesce(pa1.County_id, pa2.County_id, pa3.County_id) County_id,
>a13.County_desc County_desc,
>coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) Area_Id,
>a13.Area_Desc Area_Desc,
>coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code,
>pa3.Po_rd_rating_code) Po_rd_rating_code,
>coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) Week_id,
> a14.week_desc week_desc,
> pa1.WJXBFS1 WJXBFS1,
> pa2.WJXBFS1 WJXBFS2,
> pa3.WJXBFS1 WJXBFS3
>from marc_ZZT5J02027GMD000 pa1(nolock)
> full outer join marc_ZZT5J02027GMD001 pa2(nolock)
> on (pa1.Area_Id = pa2.Area_Id and
> pa1.County_id = pa2.County_id and
> pa1.Parent_County_id = pa2.Parent_County_id and
> pa1.Po_rd_rating_code = pa2.Po_rd_rating_code and
> pa1.Pr_cover_id = pa2.Pr_cover_id and
> pa1.Ra_gender_id = pa2.Ra_gender_id and
> pa1.Week_id = pa2.Week_id)
> full outer join marc_ZZT5J02027GMD002 pa3(nolock)
> on (coalesce(pa1.Area_Id, pa2.Area_Id) = pa3.Area_Id and
> coalesce(pa1.County_id, pa2.County_id) = pa3.County_id and
> coalesce(pa1.Parent_County_id, pa2.Parent_County_id) = pa3.Parent_County_i
d
>and
> coalesce(pa1.Po_rd_rating_code, pa2.Po_rd_rating_code) =
>pa3.Po_rd_rating_code and
> coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id) = pa3.Pr_cover_id and
> coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id) = pa3.Ra_gender_id and
> coalesce(pa1.Week_id, pa2.Week_id) = pa3.Week_id)
> join PRt_lu_Cover a11
> on (coalesce(pa1.Pr_cover_id, pa2.Pr_cover_id, pa3.Pr_cover_id) =
>a11.Pr_cover_id)
> join RAv_lu_Gender a12
> on (coalesce(pa1.Ra_gender_id, pa2.Ra_gender_id, pa3.Ra_gender_id) =
>a12.Ra_gender_id)
> join POt_lu_rd_post_code a13
> on (coalesce(pa1.Area_Id, pa2.Area_Id, pa3.Area_Id) = a13.Area_Id)
> join TIt_lu_day a14
> on (coalesce(pa1.Week_id, pa2.Week_id, pa3.Week_id) = a14.Week_id)
>
>
>|||thanks all. i have processed the change request and am going for the bug fix
on the dimension itself.
"Steve Kass" wrote:

> When you say you must use this table instead of a lookup, can you elaborat
e?
> Is it possible that you can use this without DISTINCT initially and
> apply DISTINCT
> elsewhere in your process where it will not require as much work? Also,
> what is
> the known bug you mention? Maybe someone knows it and has a workaround.
> Steve Kass
> Drew University
> marcmc wrote:
>
>

Query parameter drop-down not available once report is cached

Hi, I have a date parameter that is populated from a query to give me a
month drop-down on my report. When a user selects the month and runs
the report, it passes the parameter to a query to return a dataset.
This is all straight forward. Since the report runs quite slowly, I
wanted to cache the report witha dafault month, but allow the users to
then re-run the report with other months. I found that once I cached
the report on a shared schedule, the month drop-down is no longer
available for me to select a new query parameter to rerun the report.
Another filter parameter is available, so I am assuming it it because
it is a query parameter.
Is there a way to cache the reoprt, but still allow users to reselect
the query parameter and rerun the report?You can make the month parameter a filter parameter, bring back a all the
months data and then apply the month filter to the data regions in the
report..
Or if you are using Enterprise Edition you could preload the cache using a
data-driven subscription, see "Preloading the Cache"
here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rswork/htm/rms_processing_v1_2p89.asp
--
Douglas McDowell
douglas@.nospam.solidqualitylearning.com
"MDXQuery" <imgroup1@.hotmail.com> wrote in message
news:1109694129.517130.311640@.g14g2000cwa.googlegroups.com...
> Hi, I have a date parameter that is populated from a query to give me a
> month drop-down on my report. When a user selects the month and runs
> the report, it passes the parameter to a query to return a dataset.
> This is all straight forward. Since the report runs quite slowly, I
> wanted to cache the report witha dafault month, but allow the users to
> then re-run the report with other months. I found that once I cached
> the report on a shared schedule, the month drop-down is no longer
> available for me to select a new query parameter to rerun the report.
> Another filter parameter is available, so I am assuming it it because
> it is a query parameter.
> Is there a way to cache the reoprt, but still allow users to reselect
> the query parameter and rerun the report?
>

query optimisation question

I have a quick question regarding a query I'm working on. I was wondering why the first one runs much quicker than the second as I can't understand it myself.

Query 1:

SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1

Query 2:

SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp
WHERE pi.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1

The only difference is that in query 2 I have included a where clause in the subquery. The field jjobno is an indexed field so surely that by specifying an exact jjobno in an index field this would be quicker than specifying nothing? Could someone explain this to me? I'm using oracle version 7.3. Thanks in advance.You have (accidentally I presume) correlated the subquery to the main query in the second version by referring to "pi.jjobno". Alias "pi" is defined in the main query. Perhaps you mean to do this:

SELECT pi.jjobno,
pi.jpi06,
pi.jq01,
pi.jq02,
pi.jq03,
pi.jq04,
pi.jq05,
pi.jq06,
pi.jq07,
pi.jq08
FROM jpost_insp pi
WHERE pi.jinspected_date IS NOT NULL
AND (pi.jjobno, pi.jraised) IN (SELECT /*+ INDEX(jpost_insp I1JPOST_INSP)*/ jjobno, MAX(jraised)
FROM jpost_insp pi2
WHERE pi2.jjobno = :p_job_no
GROUP BY jjobno)
AND pi.jjobno = :p_job_no
AND ROWNUM = 1|||Of course, the hint needs changing now!|||Good spot I missed that completely. Why would I have to change the hint?

thanks,|||Well, either change the hint to use alias pi2, or change the table alias from pi2 to jpost_insp. They have to be the same in both places!

query on day's transactions

Let me preface this by saying that i'm not a programmer.
I'm trying to setup a job that runs a query every night at 7pm that will
pull all transactions with the date reported that equals the system date.
What identifier would I use so that it pulled transactions based on the the
system date? My query is below, but as you see it has a yesterday's date.
select [CaseLog].[DateReported], [CaseLog].[CaseNbr]
from [CaseLog]
where [CaseLog].[DateReported]='2005-09-26 00:00:00'
order by [CaseLog].[DateReported], [CaseLog].[CaseNbr]
Thanks in advance
On Mon, 26 Sep 2005 13:45:03 -0700, dhamric wrote:

>Let me preface this by saying that i'm not a programmer.
>I'm trying to setup a job that runs a query every night at 7pm that will
>pull all transactions with the date reported that equals the system date.
>What identifier would I use so that it pulled transactions based on the the
>system date? My query is below, but as you see it has a yesterday's date.
>select [CaseLog].[DateReported], [CaseLog].[CaseNbr]
>from [CaseLog]
>where [CaseLog].[DateReported]='2005-09-26 00:00:00'
>order by [CaseLog].[DateReported], [CaseLog].[CaseNbr]
>Thanks in advance
Hi dhamric,
You could use
SELECT CaseLog.DateReported, CaseLog.CaseNbr
FROM CaseLog
WHERE CaseLog.DateReported = DATEADD(day,
DATEDIFF(day, 0, getdate()), 0)
ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
But if the DateReported column can be stored with a time portion other
than midnight, you should use this instead:
SELECT CaseLog.DateReported, CaseLog.CaseNbr
FROM CaseLog
WHERE CaseLog.DateReported >= DATEADD(day,
DATEDIFF(day, 0, getdate()), 0)
AND CaseLog.DateReported < DATEADD(day,
DATEDIFF(day, 0, getdate()), 1)
ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Very helpful. Thank you!
"Hugo Kornelis" wrote:

> On Mon, 26 Sep 2005 13:45:03 -0700, dhamric wrote:
>
> Hi dhamric,
> You could use
> SELECT CaseLog.DateReported, CaseLog.CaseNbr
> FROM CaseLog
> WHERE CaseLog.DateReported = DATEADD(day,
> DATEDIFF(day, 0, getdate()), 0)
> ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
> But if the DateReported column can be stored with a time portion other
> than midnight, you should use this instead:
> SELECT CaseLog.DateReported, CaseLog.CaseNbr
> FROM CaseLog
> WHERE CaseLog.DateReported >= DATEADD(day,
> DATEDIFF(day, 0, getdate()), 0)
> AND CaseLog.DateReported < DATEADD(day,
> DATEDIFF(day, 0, getdate()), 1)
> ORDER BY CaseLog.DateReported, CaseLog.CaseNbr
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>