"Jeffrey K. Ericson" wrote:

> The following queries;
>
> select
> count(id),year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> from tblMasterRT
> where transactiondatetime>'5/1/2005'
> and transactiontype in ('a','r','l','o','j')
> and transactioncomplete=1 and flag3=1
> group by
> year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> order by count(id)
> desc,year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
>
>
>
> select
> count(id),year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> from tblMasterRT
> where transactiondatetime>'6/1/2005'
> and transactiontype in ('a','r','l','o','j')
> and transactioncomplete=1 and flag3=1
> group by
> year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> order by count(id)
> desc,year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
>
>
> behave differently.   The second returns 2 times the actual number of rows
> for certain days(generally towards then end of the record set).  I updated
> stats and have been told the problem existed prior to the last reindex job
> that ran successfully.
>
> Any ideas?
>
>

"Alejandro Mesa" wrote:

> Can you recreate all the table's indexes?
>
>
> AMB
>
> "Jeffrey K. Ericson" wrote:
>
> > The following queries;
> >
> > select
> > count(id),year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> > from tblMasterRT
> > where transactiondatetime>'5/1/2005'
> > and transactiontype in ('a','r','l','o','j')
> > and transactioncomplete=1 and flag3=1
> > group by
> > year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> > order by count(id)
> > desc,year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> >
> >
> >
> > select
> > count(id),year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> > from tblMasterRT
> > where transactiondatetime>'6/1/2005'
> > and transactiontype in ('a','r','l','o','j')
> > and transactioncomplete=1 and flag3=1
> > group by
> > year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> > order by count(id)
> > desc,year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> >
> >
> > behave differently.   The second returns 2 times the actual number of rows
> > for certain days(generally towards then end of the record set).  I updated
> > stats and have been told the problem existed prior to the last reindex job
> > that ran successfully.
> >
> > Any ideas?
> >
> >

> The following queries;
>
> select
> count(id),year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> from tblMasterRT
> where transactiondatetime>'5/1/2005'
> and transactiontype in ('a','r','l','o','j')
> and transactioncomplete=1 and flag3=1
> group by
> year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> order by count(id)
> desc,year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
>
>
>
> select
> count(id),year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> from tblMasterRT
> where transactiondatetime>'6/1/2005'
> and transactiontype in ('a','r','l','o','j')
> and transactioncomplete=1 and flag3=1
> group by
> year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> order by count(id)
> desc,year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
>
>
> behave differently.   The second returns 2 times the actual number of
> rows for certain days(generally towards then end of the record set).
> I updated stats and have been told the problem existed prior to the
> last reindex job that ran successfully.
>
> Any ideas?

"David Gugick" wrote:

> Jeffrey K. Ericson wrote:
> > The following queries;
> >
> > select
> > count(id),year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> > from tblMasterRT
> > where transactiondatetime>'5/1/2005'
> > and transactiontype in ('a','r','l','o','j')
> > and transactioncomplete=1 and flag3=1
> > group by
> > year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> > order by count(id)
> > desc,year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> >
> >
> >
> > select
> > count(id),year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> > from tblMasterRT
> > where transactiondatetime>'6/1/2005'
> > and transactiontype in ('a','r','l','o','j')
> > and transactioncomplete=1 and flag3=1
> > group by
> > year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> > order by count(id)
> > desc,year(transactiondatetime),month(transactiondatetime),day(transactiondatetime)
> >
> >
> > behave differently.   The second returns 2 times the actual number of
> > rows for certain days(generally towards then end of the record set).
> > I updated stats and have been told the problem existed prior to the
> > last reindex job that ran successfully.
> >
> > Any ideas?
>
> Add a MAXDOP 1 to the query. Parallelism can sometimes throw off counts.
> http://support.microsoft.com/kb/822746
> Supposedly fixed in SP4.
>
>
>
>
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
>