Hi all,
I've got a quick question.
How would I count the number of records between two dates.
I started with something like this.
SELECT COUNT(*) AS COUNT, dtAdded
FROM tSurveyPerson
WHERE (dtAdded BETWEEN '2004-03-01' AND '2004-04-01')
GROUP BY dtAdded
but as you probably all know this ain't right. I would like to get just the number of records.
ThanksLeave off the GROUP BY.
-PatP|||got it...
SELECT COUNT(dtAdded) AS numRecords
FROM tSurveyPerson
WHERE (dtAdded BETWEEN '2004-03-01' AND '2004-04-01')
Thanks Patsql
Thursday, March 29, 2012
Count record from table incorrect
When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
http://www.aspfaq.com/
(Reverse address to reply.)|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,
>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
records.[vbcol=seagreen]
all[vbcol=seagreen]
>
>.
>|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...[vbcol=seagreen]
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
>
> successful execution of the
> records.
> all|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:
>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
http://www.aspfaq.com/
(Reverse address to reply.)|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,
>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
records.[vbcol=seagreen]
all[vbcol=seagreen]
>
>.
>|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...[vbcol=seagreen]
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
>
> successful execution of the
> records.
> all|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:
>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT
" se to 2M?
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
Count record from table incorrect
When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,
Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,
|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,
>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the[vbcol=seagreen]
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
records.[vbcol=seagreen]
all
>
>.
>
|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...[vbcol=seagreen]
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
> successful execution of the
> records.
> all
|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:
>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,
Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,
|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
http://www.aspfaq.com/
(Reverse address to reply.)
|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,
>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the[vbcol=seagreen]
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
records.[vbcol=seagreen]
all
>
>.
>
|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...[vbcol=seagreen]
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
> successful execution of the
> records.
> all
|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:
>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
Tea C.
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
|||You said that when you did
select *
into temptableA
from tableA
about 2M rows were affected.
What does select count(*) from temptableA return? Do you have "SET ROWCOUNT" se to 2M?
"Aaron [SQL Server MVP]" wrote:
> You keep saying that, but how do you know that "for sure"?
> Have you updated statistics recently?
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
Count record from table incorrect
When I count records from tableA, I got results as about
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,
>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
>> When I count records from tableA, I got results as about
>> 11M records . But the table actually has only 2M
records.
>> I did count(*), or count (ID), or count(distinct ID),
all
>> give me the same 12M.
>> But I'm sure the table has only 2M. Thanks,
>> It is SQL Server 2000. I did it in Query analyzer.
>>
>> The exact queries are:
>> select count(ID)
>> from tableA
>> select count(*)
>> from tableA
>> select count(Field1)
>> from tableA
>> select count(field2)
>> from tableA
>> select count(*)
>> from tableA
>> select distinct count(ID)
>> from tableA
>> comment: ID is the identity field.
>> I rebuilt/recreated the indexes.
>> They all showed as about 12M
>> select sum(1) from tableA
>> I got about 12M
>> I know the records in 2M for sure. Also when I did
>> select *
>> into temptableA
>> from tableA
>> about 2M rows affected.
>> What could be wrong?
>> Thanks,
>
>.
>|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
> >--Original Message--
> >Hi,
> >
> >Execute the below command :-
> >
> >sp_spaceused <table_name>,@.updateusage='true'
> >
> >THis will return the exact row count. After the
> successful execution of the
> >above command try executing the
> >
> >select count(*) from table_name
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >
> >"ISD_ERD" <lxwang@.pa1call.org> wrote in message
> >news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> >>
> >> When I count records from tableA, I got results as about
> >> 11M records . But the table actually has only 2M
> records.
> >> I did count(*), or count (ID), or count(distinct ID),
> all
> >> give me the same 12M.
> >> But I'm sure the table has only 2M. Thanks,
> >>
> >> It is SQL Server 2000. I did it in Query analyzer.
> >>
> >>
> >> The exact queries are:
> >>
> >> select count(ID)
> >> from tableA
> >>
> >> select count(*)
> >> from tableA
> >>
> >> select count(Field1)
> >> from tableA
> >>
> >> select count(field2)
> >> from tableA
> >>
> >> select count(*)
> >> from tableA
> >>
> >> select distinct count(ID)
> >> from tableA
> >>
> >> comment: ID is the identity field.
> >>
> >> I rebuilt/recreated the indexes.
> >>
> >> They all showed as about 12M
> >>
> >> select sum(1) from tableA
> >> I got about 12M
> >>
> >> I know the records in 2M for sure. Also when I did
> >> select *
> >> into temptableA
> >> from tableA
> >>
> >> about 2M rows affected.
> >>
> >> What could be wrong?
> >>
> >> Thanks,
> >
> >
> >.
> >|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:
>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>>Hari,
>>sp_spaceused returns me the right number as 2M,
>>but then I did "select count(*) from table_name"
>>That still gives me 11M.
>>Thanks,
>>
>>
>>--Original Message--
>>Hi,
>>Execute the below command :-
>>sp_spaceused <table_name>,@.updateusage='true'
>>THis will return the exact row count. After the
>>
>>successful execution of the
>>
>>above command try executing the
>>select count(*) from table_name
>>Thanks
>>Hari
>>MCDBA
>>
>>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
>>
>>When I count records from tableA, I got results as about
>>11M records . But the table actually has only 2M
>>
>>records.
>>
>>I did count(*), or count (ID), or count(distinct ID),
>>
>>all
>>
>>give me the same 12M.
>>But I'm sure the table has only 2M. Thanks,
>>It is SQL Server 2000. I did it in Query analyzer.
>>
>>The exact queries are:
>>select count(ID)
>>from tableA
>>select count(*)
>>from tableA
>>select count(Field1)
>>from tableA
>>select count(field2)
>>from tableA
>>select count(*)
>>from tableA
>>select distinct count(ID)
>>from tableA
>>comment: ID is the identity field.
>>I rebuilt/recreated the indexes.
>>They all showed as about 12M
>>select sum(1) from tableA
>> I got about 12M
>>I know the records in 2M for sure. Also when I did
>>select *
>>into temptableA
>>from tableA
>>about 2M rows affected.
>>What could be wrong?
>>Thanks,
>>
>>.
>>
>
>
11M records . But the table actually has only 2M records.
I did count(*), or count (ID), or count(distinct ID), all
give me the same 12M.
But I'm sure the table has only 2M. Thanks,
It is SQL Server 2000. I did it in Query analyzer.
The exact queries are:
select count(ID)
from tableA
select count(*)
from tableA
select count(Field1)
from tableA
select count(field2)
from tableA
select count(*)
from tableA
select distinct count(ID)
from tableA
comment: ID is the identity field.
I rebuilt/recreated the indexes.
They all showed as about 12M
select sum(1) from tableA
I got about 12M
I know the records in 2M for sure. Also when I did
select *
into temptableA
from tableA
about 2M rows affected.
What could be wrong?
Thanks,Hi,
Execute the below command :-
sp_spaceused <table_name>,@.updateusage='true'
THis will return the exact row count. After the successful execution of the
above command try executing the
select count(*) from table_name
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> When I count records from tableA, I got results as about
> 11M records . But the table actually has only 2M records.
> I did count(*), or count (ID), or count(distinct ID), all
> give me the same 12M.
> But I'm sure the table has only 2M. Thanks,
> It is SQL Server 2000. I did it in Query analyzer.
>
> The exact queries are:
> select count(ID)
> from tableA
> select count(*)
> from tableA
> select count(Field1)
> from tableA
> select count(field2)
> from tableA
> select count(*)
> from tableA
> select distinct count(ID)
> from tableA
> comment: ID is the identity field.
> I rebuilt/recreated the indexes.
> They all showed as about 12M
> select sum(1) from tableA
> I got about 12M
> I know the records in 2M for sure. Also when I did
> select *
> into temptableA
> from tableA
> about 2M rows affected.
> What could be wrong?
> Thanks,|||> But I'm sure the table has only 2M. Thanks,
> ...
> I know the records in 2M for sure.
You keep saying that, but how do you know that "for sure"?
Have you updated statistics recently?
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hari,
sp_spaceused returns me the right number as 2M,
but then I did "select count(*) from table_name"
That still gives me 11M.
Thanks,
>--Original Message--
>Hi,
>Execute the below command :-
>sp_spaceused <table_name>,@.updateusage='true'
>THis will return the exact row count. After the
successful execution of the
>above command try executing the
>select count(*) from table_name
>Thanks
>Hari
>MCDBA
>
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
>> When I count records from tableA, I got results as about
>> 11M records . But the table actually has only 2M
records.
>> I did count(*), or count (ID), or count(distinct ID),
all
>> give me the same 12M.
>> But I'm sure the table has only 2M. Thanks,
>> It is SQL Server 2000. I did it in Query analyzer.
>>
>> The exact queries are:
>> select count(ID)
>> from tableA
>> select count(*)
>> from tableA
>> select count(Field1)
>> from tableA
>> select count(field2)
>> from tableA
>> select count(*)
>> from tableA
>> select distinct count(ID)
>> from tableA
>> comment: ID is the identity field.
>> I rebuilt/recreated the indexes.
>> They all showed as about 12M
>> select sum(1) from tableA
>> I got about 12M
>> I know the records in 2M for sure. Also when I did
>> select *
>> into temptableA
>> from tableA
>> about 2M rows affected.
>> What could be wrong?
>> Thanks,
>
>.
>|||Hi,
Did you run @.updateusage='true' along with sp_spaceused. This will correct
the inconsistencies in sysindexes.
use <dbname>
go
sp_spaceused <table_name>,@.updateusage='true'
Thanks
Hari
MCDBA
"ISD_ERD" <lxwang@.pa1call.org> wrote in message
news:439501c47330$bd689a30$a601280a@.phx.gbl...
> Hari,
> sp_spaceused returns me the right number as 2M,
> but then I did "select count(*) from table_name"
> That still gives me 11M.
> Thanks,
>
> >--Original Message--
> >Hi,
> >
> >Execute the below command :-
> >
> >sp_spaceused <table_name>,@.updateusage='true'
> >
> >THis will return the exact row count. After the
> successful execution of the
> >above command try executing the
> >
> >select count(*) from table_name
> >
> >Thanks
> >Hari
> >MCDBA
> >
> >
> >
> >"ISD_ERD" <lxwang@.pa1call.org> wrote in message
> >news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
> >>
> >> When I count records from tableA, I got results as about
> >> 11M records . But the table actually has only 2M
> records.
> >> I did count(*), or count (ID), or count(distinct ID),
> all
> >> give me the same 12M.
> >> But I'm sure the table has only 2M. Thanks,
> >>
> >> It is SQL Server 2000. I did it in Query analyzer.
> >>
> >>
> >> The exact queries are:
> >>
> >> select count(ID)
> >> from tableA
> >>
> >> select count(*)
> >> from tableA
> >>
> >> select count(Field1)
> >> from tableA
> >>
> >> select count(field2)
> >> from tableA
> >>
> >> select count(*)
> >> from tableA
> >>
> >> select distinct count(ID)
> >> from tableA
> >>
> >> comment: ID is the identity field.
> >>
> >> I rebuilt/recreated the indexes.
> >>
> >> They all showed as about 12M
> >>
> >> select sum(1) from tableA
> >> I got about 12M
> >>
> >> I know the records in 2M for sure. Also when I did
> >> select *
> >> into temptableA
> >> from tableA
> >>
> >> about 2M rows affected.
> >>
> >> What could be wrong?
> >>
> >> Thanks,
> >
> >
> >.
> >|||Hari,
As far as I know, select count(*) from T doesn't use information in
sysindexes at all, but refers to the actual data. I can think of a few
explanations of what's going on. From possible to very speculative,
here they are:
1. There is an open transaction. If the transaction isolation level is
read uncommitted, could someone have inserted 9 million rows but not
committed? I don't know what sysindexes or sp_spaceused with
update-usage does in this case, but some combination of isolation level
and open transaction could be at work..
2. There are 11 million rows, and sysindexes is wrong (as it can be).
TableA has a unique constraint with ignore_dup_key set on it, and 9
million rows are being discarded by the insert.
3. The rowcount from the insert is wrong because of a trigger (I have
seen this when a distributed transaction is involved, but I think it's
been fixed).
4. The COUNT(*) query plan involves an indexed view, and something odd
is going on there. Or there is some other issue to do with a view.
5. There is a trigger on temptableA that affected the rowcount.
5. There are two tableA tables, with different owners, and something
funny is going on with ownership resolution.
I suspect count(*) is correct, and would try this:
select 1 as One
into #counter
from tableA
select sum(One) as ct from #counter
or
declare @.i int
set @.i = 0
select @.i = @.i + 1
from tableA
select @.i
or maybe
declare @.i int
set @.i = 0
select top 99.999999999 percent @.i = @.i + 1
from tableA
order by OrderID
select @.i
Steve Kass
Drew University
Hari Prasad wrote:
>Hi,
>Did you run @.updateusage='true' along with sp_spaceused. This will correct
>the inconsistencies in sysindexes.
>use <dbname>
>go
>sp_spaceused <table_name>,@.updateusage='true'
>Thanks
>Hari
>MCDBA
>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>news:439501c47330$bd689a30$a601280a@.phx.gbl...
>
>>Hari,
>>sp_spaceused returns me the right number as 2M,
>>but then I did "select count(*) from table_name"
>>That still gives me 11M.
>>Thanks,
>>
>>
>>--Original Message--
>>Hi,
>>Execute the below command :-
>>sp_spaceused <table_name>,@.updateusage='true'
>>THis will return the exact row count. After the
>>
>>successful execution of the
>>
>>above command try executing the
>>select count(*) from table_name
>>Thanks
>>Hari
>>MCDBA
>>
>>"ISD_ERD" <lxwang@.pa1call.org> wrote in message
>>news:43dd01c4732e$2a8e1160$a401280a@.phx.gbl...
>>
>>When I count records from tableA, I got results as about
>>11M records . But the table actually has only 2M
>>
>>records.
>>
>>I did count(*), or count (ID), or count(distinct ID),
>>
>>all
>>
>>give me the same 12M.
>>But I'm sure the table has only 2M. Thanks,
>>It is SQL Server 2000. I did it in Query analyzer.
>>
>>The exact queries are:
>>select count(ID)
>>from tableA
>>select count(*)
>>from tableA
>>select count(Field1)
>>from tableA
>>select count(field2)
>>from tableA
>>select count(*)
>>from tableA
>>select distinct count(ID)
>>from tableA
>>comment: ID is the identity field.
>>I rebuilt/recreated the indexes.
>>They all showed as about 12M
>>select sum(1) from tableA
>> I got about 12M
>>I know the records in 2M for sure. Also when I did
>>select *
>>into temptableA
>>from tableA
>>about 2M rows affected.
>>What could be wrong?
>>Thanks,
>>
>>.
>>
>
>
count question
have a table (A) that contains fields:
[OPENDATE] [datetime] NULL
[CLSDDATE] [datetime] NULL
[DUEDATE] [datetime] NULL
[PRIORITY] [varchar] (30
[USERID] [int] NULL
and table (B) that contains fields:
[USERID] [int] NOT NULL
[DEPT_NUM] [varchar] (30)
[DEPT] [varchar] (30)
-- the variables ae declared and set
select B.dept_num, count(*) from A inner join B on B.userid = A.userid where
A.opendate between @.startdate and @.enddate
and A.clsddate > A.duedate and B.dept = @.dept group by B.dept_num order by
B.dept_num
This gives me two columns, but I also need a third column with a count that
meets this criteria
where As.opendate between @.startdate and @.enddate
and B.dept = @.dept
Wanting result to be:
dept A 23 47
dept B 44 89
deptcC 28 17
etc...
thanks,> This gives me two columns, but I also need a third column with a count
> that
> meets this criteria
How about giving us proper DDL and some sample data that we can correlate to
the desired results, instead of a word problem?
http://www.aspfaq.com/5006|||Try not filtering in the where clause, and instead use a case expression to
calculate both columns.
select
B.dept_num,
sum(
case when A.opendate between @.startdate and @.enddate
and A.clsddate > A.duedate and B.dept = @.dept then 1 else 0 end
) as c1,
sum(
case when As.opendate between @.startdate and @.enddate
and B.dept = @.dept then 1 else 0 end
) as c2
from
A inner join B on B.userid = A.userid
group by
B.dept_num
order by
B.dept_num
go
AMB
"cheilig" wrote:
> have a table (A) that contains fields:
> [OPENDATE] [datetime] NULL
> [CLSDDATE] [datetime] NULL
> [DUEDATE] [datetime] NULL
> [PRIORITY] [varchar] (30
> [USERID] [int] NULL
> and table (B) that contains fields:
> [USERID] [int] NOT NULL
> [DEPT_NUM] [varchar] (30)
> [DEPT] [varchar] (30)
> -- the variables ae declared and set
> select B.dept_num, count(*) from A inner join B on B.userid = A.userid whe
re
> A.opendate between @.startdate and @.enddate
> and A.clsddate > A.duedate and B.dept = @.dept group by B.dept_num order by
> B.dept_num
> This gives me two columns, but I also need a third column with a count tha
t
> meets this criteria
> where As.opendate between @.startdate and @.enddate
> and B.dept = @.dept
> Wanting result to be:
> dept A 23 47
> dept B 44 89
> deptcC 28 17
> etc...
> thanks,|||you da man. thanks.
"Alejandro Mesa" wrote:
> Try not filtering in the where clause, and instead use a case expression t
o
> calculate both columns.
> select
> B.dept_num,
> sum(
> case when A.opendate between @.startdate and @.enddate
> and A.clsddate > A.duedate and B.dept = @.dept then 1 else 0 end
> ) as c1,
> sum(
> case when As.opendate between @.startdate and @.enddate
> and B.dept = @.dept then 1 else 0 end
> ) as c2
> from
> A inner join B on B.userid = A.userid
> group by
> B.dept_num
> order by
> B.dept_num
> go
>
> AMB
> "cheilig" wrote:
>|||seem to give enough info for the above responder to answer the question
"Aaron Bertrand [SQL Server MVP]" wrote:
> How about giving us proper DDL and some sample data that we can correlate
to
> the desired results, instead of a word problem?
> http://www.aspfaq.com/5006
>
>|||> seem to give enough info for the above responder to answer the question
Great, congratulations! Alejandro is more willing than the rest of us to
make guesses and potentially do a bunch of work for nothing. Do you think
http://www.aspfaq.com/5006 was written just so we can be bullies? Or do you
not comprehend the point of it all?
[OPENDATE] [datetime] NULL
[CLSDDATE] [datetime] NULL
[DUEDATE] [datetime] NULL
[PRIORITY] [varchar] (30
[USERID] [int] NULL
and table (B) that contains fields:
[USERID] [int] NOT NULL
[DEPT_NUM] [varchar] (30)
[DEPT] [varchar] (30)
-- the variables ae declared and set
select B.dept_num, count(*) from A inner join B on B.userid = A.userid where
A.opendate between @.startdate and @.enddate
and A.clsddate > A.duedate and B.dept = @.dept group by B.dept_num order by
B.dept_num
This gives me two columns, but I also need a third column with a count that
meets this criteria
where As.opendate between @.startdate and @.enddate
and B.dept = @.dept
Wanting result to be:
dept A 23 47
dept B 44 89
deptcC 28 17
etc...
thanks,> This gives me two columns, but I also need a third column with a count
> that
> meets this criteria
How about giving us proper DDL and some sample data that we can correlate to
the desired results, instead of a word problem?
http://www.aspfaq.com/5006|||Try not filtering in the where clause, and instead use a case expression to
calculate both columns.
select
B.dept_num,
sum(
case when A.opendate between @.startdate and @.enddate
and A.clsddate > A.duedate and B.dept = @.dept then 1 else 0 end
) as c1,
sum(
case when As.opendate between @.startdate and @.enddate
and B.dept = @.dept then 1 else 0 end
) as c2
from
A inner join B on B.userid = A.userid
group by
B.dept_num
order by
B.dept_num
go
AMB
"cheilig" wrote:
> have a table (A) that contains fields:
> [OPENDATE] [datetime] NULL
> [CLSDDATE] [datetime] NULL
> [DUEDATE] [datetime] NULL
> [PRIORITY] [varchar] (30
> [USERID] [int] NULL
> and table (B) that contains fields:
> [USERID] [int] NOT NULL
> [DEPT_NUM] [varchar] (30)
> [DEPT] [varchar] (30)
> -- the variables ae declared and set
> select B.dept_num, count(*) from A inner join B on B.userid = A.userid whe
re
> A.opendate between @.startdate and @.enddate
> and A.clsddate > A.duedate and B.dept = @.dept group by B.dept_num order by
> B.dept_num
> This gives me two columns, but I also need a third column with a count tha
t
> meets this criteria
> where As.opendate between @.startdate and @.enddate
> and B.dept = @.dept
> Wanting result to be:
> dept A 23 47
> dept B 44 89
> deptcC 28 17
> etc...
> thanks,|||you da man. thanks.
"Alejandro Mesa" wrote:
> Try not filtering in the where clause, and instead use a case expression t
o
> calculate both columns.
> select
> B.dept_num,
> sum(
> case when A.opendate between @.startdate and @.enddate
> and A.clsddate > A.duedate and B.dept = @.dept then 1 else 0 end
> ) as c1,
> sum(
> case when As.opendate between @.startdate and @.enddate
> and B.dept = @.dept then 1 else 0 end
> ) as c2
> from
> A inner join B on B.userid = A.userid
> group by
> B.dept_num
> order by
> B.dept_num
> go
>
> AMB
> "cheilig" wrote:
>|||seem to give enough info for the above responder to answer the question
"Aaron Bertrand [SQL Server MVP]" wrote:
> How about giving us proper DDL and some sample data that we can correlate
to
> the desired results, instead of a word problem?
> http://www.aspfaq.com/5006
>
>|||> seem to give enough info for the above responder to answer the question
Great, congratulations! Alejandro is more willing than the rest of us to
make guesses and potentially do a bunch of work for nothing. Do you think
http://www.aspfaq.com/5006 was written just so we can be bullies? Or do you
not comprehend the point of it all?
Count question
Hi DBA's -
Kindly help me figure out the following
My data looks like this -
Month Product Brand Revenue
-- --- --- ---
Jan A x 10
Jan A y 20
Jan B z 30
A report from the above data would be
Revenue for Jan = 60 and Product count = 2.
So I figure, I would need
Month Product Brand Revenue Flag
-- --- --- --- --
Jan A x 10 1
Jan A y 20 0 (since A is counted)
Jan B z 30 1
I want to count only the first occurence of the product in the Flag column.
Is there a way to do this.
- Vivekselect Month,
sum(Revenue),
Count(distinct Product)
from YourTable
group by Month|||select [month],
productcount=count(distinct product),
totalrevenue=sum(revenue)
from your_table
group by [month]|||hey, I just didn't click on Submit, because there other things in life, like phone calls!!!!|||Phone calls?
That's OLD TECHNOLOGY...
Your code was more complete, anyway. I confess I was being a little lazy...|||But did you notice the snippets are almost identical? This is earie...|||You are just saying that to be nice. Yours was much more colorful than mine as well. I simply lack your aesthetic sense of code.
Mine was pathetic. A shoddy hack of garbled syntax totally lacking in character or depth. In my haste to post, I neglected that which makes code enjoyable and pleasing to the senses.
I am truly ashamed.
Wait a minute... "Groupby"?
Hey! "Group by" is two words, not one! That won't even compile, much less execute!
Hmmph! Well. I guess I feel better now. :)|||Now who's a real hoot? :Dsql
Kindly help me figure out the following
My data looks like this -
Month Product Brand Revenue
-- --- --- ---
Jan A x 10
Jan A y 20
Jan B z 30
A report from the above data would be
Revenue for Jan = 60 and Product count = 2.
So I figure, I would need
Month Product Brand Revenue Flag
-- --- --- --- --
Jan A x 10 1
Jan A y 20 0 (since A is counted)
Jan B z 30 1
I want to count only the first occurence of the product in the Flag column.
Is there a way to do this.
- Vivekselect Month,
sum(Revenue),
Count(distinct Product)
from YourTable
group by Month|||select [month],
productcount=count(distinct product),
totalrevenue=sum(revenue)
from your_table
group by [month]|||hey, I just didn't click on Submit, because there other things in life, like phone calls!!!!|||Phone calls?
That's OLD TECHNOLOGY...
Your code was more complete, anyway. I confess I was being a little lazy...|||But did you notice the snippets are almost identical? This is earie...|||You are just saying that to be nice. Yours was much more colorful than mine as well. I simply lack your aesthetic sense of code.
Mine was pathetic. A shoddy hack of garbled syntax totally lacking in character or depth. In my haste to post, I neglected that which makes code enjoyable and pleasing to the senses.
I am truly ashamed.
Wait a minute... "Groupby"?
Hey! "Group by" is two words, not one! That won't even compile, much less execute!
Hmmph! Well. I guess I feel better now. :)|||Now who's a real hoot? :Dsql
Count Query Question
I have a table that I am trying to do a query on.
Join
(SelectWeekID,
Unit,
Count(Unit) UnitCount
From gpfcount2
Where WeekID Between WeekID - 4 and WeekID
Group By Unit,WeekID
) B
On A.Unit = B.Unit
Did you mean A.WeekID there at the end?
Table is named GPFCount2.
CREATE TABLE [GPFCount2] (
[WeekID] [int] NULL ,
[BeginDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Region] [int] NULL ,
[Unit] [int] NULL ,
[GPFCount] [int] NULL
) ON [PRIMARY]
For example:
30 ,'11/11/2006 15:00:00','11/18/2006 14:59:59', 8000 , 192 , 14
The above says that unit 92 had 14 GPFs during the week of 11/11/2006
3PM to 11/18/2006 2:59:59 PM. Unit 192 is part of region 8000. The
time period covered was week 30.
What I want to see is the number of times the unit has been in the top
25 list over the last 5 weeks. Unit 192 is in the top 25 list for
Weeks, 30, 29, 28, and 26.
So my result set for this unit should be:
30 ,'11/11/2006 15:00:00','11/18/2006 14:59:59', 8000 , 192 , 14, 4
The 4 being the number of times in the last 5 weeks that unit 192 was
in the top 25.
And then for Week 29, assuming unit 192 is in the top 25 for weeks
29,28 and 26 (and not 27 or 25), then it would be 3. And the results
from the query would be:
29 ,'11/04/2006 15:00:00','11/11/2006 14:59:59', 8000 , 192 , 14, 3
This is the query I was working with, but it's not working. I'm not
too sure how to make this work.
SelectA.weekid,
A.begindate,
A.EndDate,
A.region,
A.unit,
A.gpfcount,
B.UnitCount
Quote:
Originally Posted by
>From gpfcount2 A
Join
(SelectWeekID,
Unit,
Count(Unit) UnitCount
From gpfcount2
Where WeekID Between WeekID - 4 and WeekID
Group By Unit,WeekID
) B
On A.Unit = B.Unit
Thanks,
Jennifer
INSERTS FOR TABLE (There are inserts only for weeks 30 through 20 for
brevity's sake):
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 898 , 22
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 777 , 21
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 9000 , 846 , 21
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 907 , 20
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 9000 , 608 , 18
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 40 , 17
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 107 , 17
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 723 , 17
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 60 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 78 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 300 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 317 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 658 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 719 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 782 , 15
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 2 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 192 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 362 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 456 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 607 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 9000 , 609 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 7000 , 715 , 14
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 8000 , 182 , 13
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 712 , 13
insert into GPFCount2 select 30 ,'11/11/2006 15:00:00','11/18/2006
14:59:59', 4000 , 588 , 12
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 191 , 19
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 450 , 17
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 498 , 17
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 192 , 16
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 445 , 16
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 742 , 16
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 532 , 15
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 540 , 14
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 715 , 14
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 184 , 13
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 288 , 12
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 313 , 12
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 78 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 598 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 610 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 840 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 918 , 10
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 221 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 452 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 594 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 608 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 9000 , 706 , 9
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 4000 , 35 , 8
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 8000 , 112 , 8
insert into GPFCount2 select 29 ,'11/04/2006 15:00:00','11/11/2006
14:59:59', 7000 , 218 , 8
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 542 , 30
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 35 , 26
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 695 , 26
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 924 , 26
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 533 , 25
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 878 , 18
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 12 , 17
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 139 , 17
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 698 , 17
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 458 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 528 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 740 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 911 , 16
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 778 , 14
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 192 , 13
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 550 , 13
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 9000 , 738 , 13
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 2 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 9000 , 176 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 4000 , 450 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 571 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 7000 , 715 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 840 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 9000 , 875 , 12
insert into GPFCount2 select 28 ,'10/28/2006 15:00:00','11/04/2006
14:59:59', 8000 , 925 , 12
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 123 , 34
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 192 , 32
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 264 , 19
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 601 , 18
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 875 , 17
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 550 , 16
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 7000 , 761 , 15
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 141 , 14
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 3 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 745 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 750 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 816 , 11
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 190 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 506 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 533 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 899 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 903 , 10
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 175 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 300 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 8000 , 311 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 397 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 450 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 597 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 9000 , 743 , 9
insert into GPFCount2 select 27 ,'10/21/2006 15:00:00','10/28/2006
14:59:59', 4000 , 878 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 782 , 20
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 192 , 19
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 317 , 18
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 60 , 16
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 695 , 16
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 85 , 15
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 190 , 14
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 592 , 13
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 439 , 12
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 576 , 12
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 349 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 509 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 563 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 816 , 11
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 280 , 10
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 123 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 337 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 388 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 601 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 8000 , 698 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 7000 , 715 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 812 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 832 , 9
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 9000 , 368 , 8
insert into GPFCount2 select 26 ,'10/14/2006 15:00:00','10/21/2006
14:59:59', 4000 , 490 , 8
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 777 , 26
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 907 , 22
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 597 , 18
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 285 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 396 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 439 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 450 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 781 , 17
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 898 , 13
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 906 , 13
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 12 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 745 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 748 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 840 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 875 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 889 , 12
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 192 , 11
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 749 , 11
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 755 , 11
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 107 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 4000 , 443 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 9000 , 540 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 595 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 839 , 10
insert into GPFCount2 select 25 ,'10/07/2006 15:00:00','10/14/2006
14:59:59', 8000 , 190 , 9
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 907 , 29
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 12 , 25
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 695 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 777 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 778 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 788 , 17
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 439 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 566 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 723 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 7000 , 774 , 16
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 40 , 15
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 396 , 14
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 607 , 14
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 175 , 13
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 336 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 498 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 9000 , 781 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 829 , 12
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 9000 , 140 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 8000 , 311 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 9000 , 448 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 514 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 791 , 11
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 139 , 10
insert into GPFCount2 select 24 ,'09/30/2006 15:00:00','10/07/2006
14:59:59', 4000 , 551 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 788 , 33
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 723 , 24
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 192 , 18
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 397 , 15
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 166 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 498 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 695 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 898 , 13
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 264 , 12
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 601 , 12
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 694 , 12
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 396 , 11
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 708 , 11
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 733 , 11
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 439 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 527 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 550 , 10
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 190 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 7000 , 217 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 399 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 425 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 609 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 9000 , 728 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 8000 , 787 , 9
insert into GPFCount2 select 23 ,'09/23/2006 15:00:00','09/30/2006
14:59:59', 4000 , 131 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 604 , 28
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 223 , 18
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 723 , 18
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 724 , 17
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 598 , 15
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 3 , 14
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 550 , 13
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 619 , 13
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 397 , 12
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 540 , 12
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 601 , 12
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 490 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 498 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 658 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 782 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 823 , 11
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 334 , 10
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 7000 , 774 , 10
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 870 , 10
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 43 , 9
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 9000 , 549 , 9
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 192 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 4000 , 443 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 527 , 8
insert into GPFCount2 select 22 ,'09/16/2006 15:00:00','09/23/2006
14:59:59', 8000 , 566 , 8
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 407 , 21
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 7000 , 451 , 20
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 723 , 19
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 755 , 17
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 286 , 14
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 336 , 14
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 285 , 13
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 778 , 13
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 89 , 12
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 264 , 12
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 445 , 12
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 176 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 292 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 324 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 349 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 480 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 7000 , 715 , 11
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 201 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 396 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 469 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 578 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 9000 , 724 , 10
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 132 , 9
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 4000 , 262 , 9
insert into GPFCount2 select 21 ,'09/09/2006 15:00:00','09/16/2006
14:59:59', 8000 , 288 , 9
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 723 , 33
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 550 , 27
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 2 , 25
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 349 , 20
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 911 , 20
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 829 , 18
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 396 , 17
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 782 , 17
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 60 , 16
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 320 , 15
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 7000 , 587 , 15
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 788 , 15
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 796 , 14
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 81 , 13
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 285 , 13
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 501 , 13
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 292 , 12
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 9000 , 799 , 12
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 9000 , 430 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 450 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 790 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 898 , 11
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 8000 , 399 , 10
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 745 , 10
insert into GPFCount2 select 20 ,'09/02/2006 15:00:00','09/09/2006
14:59:59', 4000 , 750 , 10Count Query Question|||Count Query Question|||Roy Harvey wrote:
Quote:
Originally Posted by
AND B.WeekID BETWEEN A.WeekID - 4 and B.WeekID
Did you mean A.WeekID there at the end?
Subscribe to:
Posts (Atom)