Counter settings: create

Showing posts with label create. Show all posts

Thursday, March 29, 2012

Count Records in a Group

Hello All.
I'm running SSRS 2005 on top of a sql2000 database.
I need to create a report that counts the number of invoices salesreps
process per month.
The fields I have selected are;
SalesRep ID Invoice No Invoice Date
1 5467 2/22/2006 3:06:17 PM
5 4526 2/22/2006 3:29:56 PM
8 6589 6/14/2005 4:20:26 PM
5 8569 2/22/2006 3:29:56 PM
5 2563 6/10/2007 8:29:56 AM
5 1523 2/22/2006 3:29:56 PM
8 9876 8/23/2006 5:29:56 PM
1 7563 4/23/2006 1:29:56 PM
What i want to do is group by Salesrep ID, then show the total number of
invoices that rep did per month;
SalesRep ID Month TotalInvoices Processed
1 Jan 4
Feb 2
Mar 5
5 Jan 5
Feb 6
Mar 3
8 Jan 2
Feb 10
Mar 20 ... and so on.
I'm not sure exactly how to do this. Also how do I convert the date format
into just showing the month, not every minute of every day?
Any help is much appreciated.
Thanks.You want to do this with the sql statement. Since you didn't show your SQL I
had to make up names.
select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
invoices_count from yourtable a
group by a.salesrepid, datepart(month, a.invoicedate)
order by a.salesrepid, month
Note this gives you month by number which is what you need in order to order
it properly, if you want by month name then add that in and still order by
the month number to keep in in the proper order.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> Hello All.
> I'm running SSRS 2005 on top of a sql2000 database.
> I need to create a report that counts the number of invoices salesreps
> process per month.
> The fields I have selected are;
> SalesRep ID Invoice No Invoice Date
>
> 1 5467 2/22/2006 3:06:17 PM
> 5 4526 2/22/2006 3:29:56 PM
> 8 6589 6/14/2005 4:20:26 PM
> 5 8569 2/22/2006 3:29:56 PM
> 5 2563 6/10/2007 8:29:56 AM
> 5 1523 2/22/2006 3:29:56 PM
> 8 9876 8/23/2006 5:29:56 PM
> 1 7563 4/23/2006 1:29:56 PM
> What i want to do is group by Salesrep ID, then show the total number of
> invoices that rep did per month;
> SalesRep ID Month TotalInvoices Processed
> 1 Jan 4
> Feb 2
> Mar 5
> 5 Jan 5
> Feb 6
> Mar 3
> 8 Jan 2
> Feb 10
> Mar 20 ... and so on.
>
> I'm not sure exactly how to do this. Also how do I convert the date format
> into just showing the month, not every minute of every day?
> Any help is much appreciated.
> Thanks.
>|||Thank you soo much Bruce.
Here is the statement;
SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
dbo.invoice_hdr.order_date
FROM dbo.contacts INNER JOIN
dbo.invoice_hdr ON dbo.contacts.id =dbo.invoice_hdr.salesrep_id
"Bruce L-C [MVP]" wrote:
> You want to do this with the sql statement. Since you didn't show your SQL I
> had to make up names.
> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> invoices_count from yourtable a
> group by a.salesrepid, datepart(month, a.invoicedate)
> order by a.salesrepid, month
> Note this gives you month by number which is what you need in order to order
> it properly, if you want by month name then add that in and still order by
> the month number to keep in in the proper order.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> > Hello All.
> >
> > I'm running SSRS 2005 on top of a sql2000 database.
> >
> > I need to create a report that counts the number of invoices salesreps
> > process per month.
> >
> > The fields I have selected are;
> >
> > SalesRep ID Invoice No Invoice Date
> >
> >
> > 1 5467 2/22/2006 3:06:17 PM
> > 5 4526 2/22/2006 3:29:56 PM
> > 8 6589 6/14/2005 4:20:26 PM
> > 5 8569 2/22/2006 3:29:56 PM
> > 5 2563 6/10/2007 8:29:56 AM
> > 5 1523 2/22/2006 3:29:56 PM
> > 8 9876 8/23/2006 5:29:56 PM
> > 1 7563 4/23/2006 1:29:56 PM
> >
> > What i want to do is group by Salesrep ID, then show the total number of
> > invoices that rep did per month;
> >
> > SalesRep ID Month TotalInvoices Processed
> > 1 Jan 4
> > Feb 2
> > Mar 5
> >
> > 5 Jan 5
> > Feb 6
> > Mar 3
> >
> > 8 Jan 2
> > Feb 10
> > Mar 20 ... and so on.
> >
> >
> > I'm not sure exactly how to do this. Also how do I convert the date format
> > into just showing the month, not every minute of every day?
> >
> > Any help is much appreciated.
> > Thanks.
> >
>
>|||Bruce that worked perfectly!!
Thanks again!
"Damon Johnson" wrote:
> Thank you soo much Bruce.
> Here is the statement;
> SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> dbo.invoice_hdr.order_date
> FROM dbo.contacts INNER JOIN
> dbo.invoice_hdr ON dbo.contacts.id => dbo.invoice_hdr.salesrep_id
> "Bruce L-C [MVP]" wrote:
> > You want to do this with the sql statement. Since you didn't show your SQL I
> > had to make up names.
> >
> > select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> > invoices_count from yourtable a
> > group by a.salesrepid, datepart(month, a.invoicedate)
> > order by a.salesrepid, month
> >
> > Note this gives you month by number which is what you need in order to order
> > it properly, if you want by month name then add that in and still order by
> > the month number to keep in in the proper order.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> > news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> > > Hello All.
> > >
> > > I'm running SSRS 2005 on top of a sql2000 database.
> > >
> > > I need to create a report that counts the number of invoices salesreps
> > > process per month.
> > >
> > > The fields I have selected are;
> > >
> > > SalesRep ID Invoice No Invoice Date
> > >
> > >
> > > 1 5467 2/22/2006 3:06:17 PM
> > > 5 4526 2/22/2006 3:29:56 PM
> > > 8 6589 6/14/2005 4:20:26 PM
> > > 5 8569 2/22/2006 3:29:56 PM
> > > 5 2563 6/10/2007 8:29:56 AM
> > > 5 1523 2/22/2006 3:29:56 PM
> > > 8 9876 8/23/2006 5:29:56 PM
> > > 1 7563 4/23/2006 1:29:56 PM
> > >
> > > What i want to do is group by Salesrep ID, then show the total number of
> > > invoices that rep did per month;
> > >
> > > SalesRep ID Month TotalInvoices Processed
> > > 1 Jan 4
> > > Feb 2
> > > Mar 5
> > >
> > > 5 Jan 5
> > > Feb 6
> > > Mar 3
> > >
> > > 8 Jan 2
> > > Feb 10
> > > Mar 20 ... and so on.
> > >
> > >
> > > I'm not sure exactly how to do this. Also how do I convert the date format
> > > into just showing the month, not every minute of every day?
> > >
> > > Any help is much appreciated.
> > > Thanks.
> > >
> >
> >
> >|||First, you do not need TOP 100 percent. That means you want all the records
which is what you get without the TOP syntax.
SELECT a.salesrep_id, datepart(month,b.order_date) as Month,
datename(month,b.order_date) as Month_Name,count(*) as invoices_count
FROM dbo.contacts a INNER JOIN dbo.invoice_hdr b ON a.id =b.salesrep_id
where b.order_date >= @.STARTDATE and b.order_date < @.ENDDATE
group by a.salesrep_id, datepart(month,b.order_date)
order by a.salesrep_id, month
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
news:8C3A09EC-3B59-4263-BD46-82880D31C84B@.microsoft.com...
> Thank you soo much Bruce.
> Here is the statement;
> SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> dbo.invoice_hdr.order_date
> FROM dbo.contacts INNER JOIN
> dbo.invoice_hdr ON dbo.contacts.id => dbo.invoice_hdr.salesrep_id
> "Bruce L-C [MVP]" wrote:
>> You want to do this with the sql statement. Since you didn't show your
>> SQL I
>> had to make up names.
>> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
>> invoices_count from yourtable a
>> group by a.salesrepid, datepart(month, a.invoicedate)
>> order by a.salesrepid, month
>> Note this gives you month by number which is what you need in order to
>> order
>> it properly, if you want by month name then add that in and still order
>> by
>> the month number to keep in in the proper order.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
>> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
>> > Hello All.
>> >
>> > I'm running SSRS 2005 on top of a sql2000 database.
>> >
>> > I need to create a report that counts the number of invoices salesreps
>> > process per month.
>> >
>> > The fields I have selected are;
>> >
>> > SalesRep ID Invoice No Invoice Date
>> >
>> >
>> > 1 5467 2/22/2006 3:06:17 PM
>> > 5 4526 2/22/2006 3:29:56 PM
>> > 8 6589 6/14/2005 4:20:26 PM
>> > 5 8569 2/22/2006 3:29:56 PM
>> > 5 2563 6/10/2007 8:29:56 AM
>> > 5 1523 2/22/2006 3:29:56 PM
>> > 8 9876 8/23/2006 5:29:56 PM
>> > 1 7563 4/23/2006 1:29:56 PM
>> >
>> > What i want to do is group by Salesrep ID, then show the total number
>> > of
>> > invoices that rep did per month;
>> >
>> > SalesRep ID Month TotalInvoices Processed
>> > 1 Jan 4
>> > Feb 2
>> > Mar 5
>> >
>> > 5 Jan 5
>> > Feb 6
>> > Mar 3
>> >
>> > 8 Jan 2
>> > Feb 10
>> > Mar 20 ... and so on.
>> >
>> >
>> > I'm not sure exactly how to do this. Also how do I convert the date
>> > format
>> > into just showing the month, not every minute of every day?
>> >
>> > Any help is much appreciated.
>> > Thanks.
>> >
>>|||OK Bruce I think this is my last request.
the DATENAME(month, invoice_date) returns the month only.
How do i get it to return the month and year. I will be setting this up as a
parameter query;
Between @.StartDate and @.EndDate
and want the user to put in May 07 and June 07
thanks again.
"Bruce L-C [MVP]" wrote:
> First, you do not need TOP 100 percent. That means you want all the records
> which is what you get without the TOP syntax.
> SELECT a.salesrep_id, datepart(month,b.order_date) as Month,
> datename(month,b.order_date) as Month_Name,count(*) as invoices_count
> FROM dbo.contacts a INNER JOIN dbo.invoice_hdr b ON a.id => b.salesrep_id
> where b.order_date >= @.STARTDATE and b.order_date < @.ENDDATE
> group by a.salesrep_id, datepart(month,b.order_date)
> order by a.salesrep_id, month
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> news:8C3A09EC-3B59-4263-BD46-82880D31C84B@.microsoft.com...
> > Thank you soo much Bruce.
> > Here is the statement;
> >
> > SELECT TOP 100 PERCENT dbo.invoice_hdr.salesrep_id,
> > dbo.invoice_hdr.order_date
> > FROM dbo.contacts INNER JOIN
> > dbo.invoice_hdr ON dbo.contacts.id => > dbo.invoice_hdr.salesrep_id
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You want to do this with the sql statement. Since you didn't show your
> >> SQL I
> >> had to make up names.
> >>
> >> select a.salesrepid, datepart(month, a.invoicedate) as month, count(*) as
> >> invoices_count from yourtable a
> >> group by a.salesrepid, datepart(month, a.invoicedate)
> >> order by a.salesrepid, month
> >>
> >> Note this gives you month by number which is what you need in order to
> >> order
> >> it properly, if you want by month name then add that in and still order
> >> by
> >> the month number to keep in in the proper order.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >> "Damon Johnson" <DamonJohnson@.discussions.microsoft.com> wrote in message
> >> news:410E3C42-D07E-4388-97A7-E6278586890F@.microsoft.com...
> >> > Hello All.
> >> >
> >> > I'm running SSRS 2005 on top of a sql2000 database.
> >> >
> >> > I need to create a report that counts the number of invoices salesreps
> >> > process per month.
> >> >
> >> > The fields I have selected are;
> >> >
> >> > SalesRep ID Invoice No Invoice Date
> >> >
> >> >
> >> > 1 5467 2/22/2006 3:06:17 PM
> >> > 5 4526 2/22/2006 3:29:56 PM
> >> > 8 6589 6/14/2005 4:20:26 PM
> >> > 5 8569 2/22/2006 3:29:56 PM
> >> > 5 2563 6/10/2007 8:29:56 AM
> >> > 5 1523 2/22/2006 3:29:56 PM
> >> > 8 9876 8/23/2006 5:29:56 PM
> >> > 1 7563 4/23/2006 1:29:56 PM
> >> >
> >> > What i want to do is group by Salesrep ID, then show the total number
> >> > of
> >> > invoices that rep did per month;
> >> >
> >> > SalesRep ID Month TotalInvoices Processed
> >> > 1 Jan 4
> >> > Feb 2
> >> > Mar 5
> >> >
> >> > 5 Jan 5
> >> > Feb 6
> >> > Mar 3
> >> >
> >> > 8 Jan 2
> >> > Feb 10
> >> > Mar 20 ... and so on.
> >> >
> >> >
> >> > I'm not sure exactly how to do this. Also how do I convert the date
> >> > format
> >> > into just showing the month, not every minute of every day?
> >> >
> >> > Any help is much appreciated.
> >> > Thanks.
> >> >
> >>
> >>
> >>
>
>

Count Records Help

Hello I have the following table below. I need to create a query that will list conference, avg. attendance, avg. winning percentage for the current year grouped by conference. For winning percentage I'm assuming I would need to count occurrnces of self score > opp_score then divide that by counting the number of dates entries within that year? ..Dont have a clue on how to express this query - Thanks for any help or suggestions

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));Although I dont have the exact answer are you trying to do the following

CONF ATT SELF_SCORE OPP_SCORE
---- ---- ---- ----
A 5000 3 8
A 9500 2 1
A 6700 5 2
B 12000 9 6
B 8000 2 2
B 16000 3 7

select conf, count(*) / (select count(*) from table)
from table
where self_score > opp_score
group by conf;

This query doesn't work because you need to group the divided query into the same group as the outer query. I would also like to know how this is done.|||Greetings,

I now have the answer :),

conf = conference

select s1.conf, avg_att As "Avg. Att", (wins / total) * 100 As "% Won"
from
(select conf, count(*) As wins
from help
where self_score > opp_score
group by conf) s1,
(select conf, avg(att) As avg_att, count(*) As total
from help
group by conf) s2
where s1.conf = s2.conf;

The average attendance is for the entire conference group regardless of whether or not they won. If you only want average attendance for the games they played then select the avg(att) in the top from-query as opposed to the second.

The sample table I used for this is shown below,

SQL> select * from help;

CONF ATT SELF_SCORE OPP_SCORE
---- ---- ---- ----
A 5000 3 8
A 9500 2 1
A 6700 5 0
B 12000 4 6
B 8000 2 0
B 16000 3 7

And the results generated from the answer query

CONF Avg. Att % Won
---- ---- ----
A 7066.66667 66.6666667
B 12000 33.3333333

Cheers.|||- Thanks for your reply, It appears I left some info out, Ive been trying to adapt what you replied with but still no dice.

I am including the tables and data below. There are some null values as I am only inputing sample data that is needed in the reports.

CREATE TABLE SCHOOL
(school VARCHAR2(30),
conference VARCHAR2(25),
stadium_size NUMBER(6),
ticket_price NUMBER(4,2),
in_state_players NUMBER(2),
outstate_players NUMBER(2),
scholarships NUMBER(2),
graduate NUMBER(20));

INSERT INTO school VALUES
('Indiana Univ.', 'Big Ten', 53000, null, null, null, null, null);
INSERT INTO school VALUES
('Ohio State Univ.', 'Big Ten', 104000, null, null, null, null, null);
INSERT INTO school VALUES
('Penn State Univ.', 'Independent', 80000, null, null, null, null, null);
INSERT INTO school VALUES
('Univ. of Pittsburgh', 'Independent', 51000, null, null, null, null, null);
INSERT INTO school VALUES
('Pondunk Univ.', 'Independent', 44000, null, null, null, null, 35);
INSERT INTO school VALUES
('Violator Univ.', 'Independent', 39000, null, null, null, null, 47);

CREATE TABLE SCHOOL_INCIDENTS
(school VARCHAR2(30),
idate DATE,
incident_code NUMBER(5));

INSERT INTO school_incidents VALUES
('Indiana Univ.',null, 17983);
INSERT INTO school_incidents VALUES
('Ohio State Univ',null, 12891);
INSERT INTO school_incidents VALUES
('Penn State Univ.',null, 17250);

CREATE TABLE HOMEGAME
(school VARCHAR2(30),
hdate DATE,
opponent VARCHAR2(30),
attendance NUMBER(6),
self_score NUMBER(3),
opp_score NUMBER(3),
self_injuries NUMBER(3),
opp_injuries NUMBER(3));

INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 43000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 42000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 41000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 40000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 39000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 38000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 37000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Indiana Univ.', null, null, 36000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 51000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 50000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 49000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 48000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 47000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 46000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 45000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 44000, 7, 0, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 43000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 42000, 0, 7, null, null);
INSERT INTO homegame VALUES
('Penn State Univ.', null, null, 41000, 0, 7, null, null);

Below is the basic query I have been working from. I will need to add a column inbetween conference and attendance for "WINNNING PERCENTAGE" which would be calcuated for self_score & opp_score in homegame
then average the attendance column and count the recruiting incidents per conference. The final output should be 2 rows listing the calculated data.

SELECT ALL SCHOOL.CONFERENCE, HOMEGAME.ATTENDANCE "AVG ATTENDANCE", SCHOOL_INCIDENTS.INCIDENT_CODE "RECRUITING INCIDENTS"
FROM SCHOOL, SCHOOL_INCIDENTS, HOMEGAME
WHERE ((SCHOOL.SCHOOL = HOMEGAME.SCHOOL)
AND (HOMEGAME.SCHOOL = SCHOOL_INCIDENTS.SCHOOL));

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
-------- ----- -------
Big Ten 46000 17983
Big Ten 45000 17983
Big Ten 44000 17983
Big Ten 43000 17983
Big Ten 42000 17983
Big Ten 41000 17983
Big Ten 40000 17983
Big Ten 39000 17983
Big Ten 38000 17983
Big Ten 37000 17983
Big Ten 36000 17983

CONFERENCE AVG ATTENDANCE RECRUITING INCIDENTS
-------- ----- -------
Independent 51000 17250
Independent 50000 17250
Independent 49000 17250
Independent 48000 17250
Independent 47000 17250
Independent 46000 17250
Independent 45000 17250
Independent 44000 17250
Independent 43000 17250
Independent 42000 17250
Independent 41000 17250

Count Query Question

I have a table that I am trying to do a query on.

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?

Count of matching result sets

I'm okay with simple select statements, but this one is kicking my behind...
I'm trying to create an overview of our data holdings for various
categories. It needs to be dynamic as the types of data and the accompanying
criteria will be dynamic.
The data holdings table, which I'll call "DH", is related via a one-to-many
to an "Xref_DH_Topic" table, which is related via a many-to-one to a "Topics"
table.
"DH" table:
| dhid | Name |
| 1 | Fred's Ocean Carbon Data |
| 2 | Joe's Tide Data |
| 3 | Pete's Surface Flux Data |
| 4 | Lou's Tide & Surface Flux Data |
"Xref_DH_Topic" table:
| xid | dhid | topicid |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 4 | 1 |
| 9 | 4 | 3 |
| 10 | 4 | 4 |
| 11 | 4 | 5 |
"Topics" table:
| topicid | TopicTitle |
| 1 | Ocean |
| 2 | Carbon |
| 3 | Tide |
| 4 | Surface |
| 5 | Flux |
So far so good. Now I want to have a "DH Matrix" table which defines the
criteria for inclusion in a particular row, which will be modified as more
data holdings and topics are added. It is linked to an "Xref_Matrix_Topic"
table which links to the same "Topics" table as above.
"DH Matrix" table:
| dhmid | Category |
| 1 | All Ocean Data |
| 2 | Tide Data Holdings |
| 3 | Carbon Data Holdings |
| 4 | Surface Flux Data Holdings |
"Xref_Matrix_Topic" table:
| xmtid | dhmid | topicid |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 4 | 5 |
The desired end-result is a view that will display each "category" in the
"DH Matrix" table and count up the number of rows in the "DH" table that
match the criteria set up in the "Xref_Matrix_Topic" table.
Matrix of Data Holdings:
| Category | # of Hits in DH |
| All Ocean Data | 4 |
| Tide Data Holdings | 2 |
| Carbon Data Holdings | 1 |
| Surface Flux Holdings | 2 |
Any help would be greatly appreciated.
TIA.
What about some DDL scripts and data for us ?

Count of matching result sets

I'm okay with simple select statements, but this one is kicking my behind...
I'm trying to create an overview of our data holdings for various
categories. It needs to be dynamic as the types of data and the accompanyin
g
criteria will be dynamic.
The data holdings table, which I'll call "DH", is related via a one-to-many
to an "Xref_DH_Topic" table, which is related via a many-to-one to a "Topics
"
table.
"DH" table:
| dhid | Name |
| 1 | Fred's Ocean Carbon Data |
| 2 | Joe's Tide Data |
| 3 | Pete's Surface Flux Data |
| 4 | Lou's Tide & Surface Flux Data |
"Xref_DH_Topic" table:
| xid | dhid | topicid |
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
| 7 | 3 | 5 |
| 8 | 4 | 1 |
| 9 | 4 | 3 |
| 10 | 4 | 4 |
| 11 | 4 | 5 |
"Topics" table:
| topicid | TopicTitle |
| 1 | Ocean |
| 2 | Carbon |
| 3 | Tide |
| 4 | Surface |
| 5 | Flux |
So far so good. Now I want to have a "DH Matrix" table which defines the
criteria for inclusion in a particular row, which will be modified as more
data holdings and topics are added. It is linked to an "Xref_Matrix_Topic"
table which links to the same "Topics" table as above.
"DH Matrix" table:
| dhmid | Category |
| 1 | All Ocean Data |
| 2 | Tide Data Holdings |
| 3 | Carbon Data Holdings |
| 4 | Surface Flux Data Holdings |
"Xref_Matrix_Topic" table:
| xmtid | dhmid | topicid |
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 3 |
| 4 | 3 | 1 |
| 5 | 3 | 2 |
| 6 | 4 | 1 |
| 7 | 4 | 4 |
| 8 | 4 | 5 |
The desired end-result is a view that will display each "category" in the
"DH Matrix" table and count up the number of rows in the "DH" table that
match the criteria set up in the "Xref_Matrix_Topic" table.
Matrix of Data Holdings:
| Category | # of Hits in DH |
| All Ocean Data | 4 |
| Tide Data Holdings | 2 |
| Carbon Data Holdings | 1 |
| Surface Flux Holdings | 2 |
Any help would be greatly appreciated.
TIA.What about some DDL scripts and data for us ?

Tuesday, March 27, 2012

count measure question

Hi,

I have table with column which is containing two values ("Y" and "N") and I want to create measure which will count Y values and second measure which will count N value. How can I create it?

Thank you

Pavel

Is the Y/N column part of a fact or dimension table?

Probably the easiest solution would be to add two INT fields to your fact table named IsYes and IsNo. Set the values to 0 or 1 appropriately and use them as measures with a SUM aggregation function. It's simple but easy.

Sunday, March 25, 2012

Count for each word in a field

Is it possible to create a SELECT query that will give me the count of all the words in a single field?

SELECT field1, count(field1) FROM table1 GROUP BY field1

won't do it because field1 has multiple words and I want them broken out and counted individually.

Data example:

Field1
-------
dog
dog ate my homework
cat
dog and cat
tail
dog tail
...

I want to get a count of every occurance of each word (e.g. "dog") whether in the field by itself or with other words.

Hope this makes sense.

AlNo, you would require a function that takes a text string as input, and counts the number of occurences of another string in it so that you could write:

select field1, word_count(field1,'dog')
from table1;

Depending on your DBMS's capabilities, such a function may already exist or you may be able to write one for yourself. In Oracle for example, you could certainly write one yourself and maybe one already exists in the Oracle Text tool (I don't know).|||Tony,

Thanks for the response. I forgot to mention the DB engine I'm using: MySQL.

I'm not sure I explained it well. Per your function example you're passing the word "dog" to the function. I need it to count all of the words in each field. Using my original example, the results would look like this (with an ORDER BY added):

field1 qty
-- --
dog 4
cat 3
tail 2
and 1
ate 1
my 1

It probably still requires a function to accomplish this.

Al|||Oh, I see - that's rather different. What you need is first to split out all the words into one per row like this:

word
--
dog
dog
dog
dog
cat
cat
cat
tail
tail
and
ate
my

Then of course it is easy to group and count the words. But how to split them up? If you could be sure there were no more than N words in any sentence then you could use a brute-force approach with a user-defined function like this:

select get_word(field1,1) as word from table1
union all
select get_word(field1,2) as word from table1
union all
...
union all
select get_word(field1,N) as word from table1

However, that probably isn't what you need. I don't know MySQL at all, but in Oracle you could achieve this (without the "N" words limit) by writing a function that returns a collection (like an array), and then selecting from the results of the function - a fairly complex operation.

It may be that this can't be done in MySQL using just a select statement - you may have to write a program that populates a temporary table with all the words, and then select from that.|||I'm thinking I'll create a new table that will house the individual words and after writing the "phrase" to field1, I'll parse the words and write them to the new table for future counting.

Thanks for your feedback!
Al

COUNT Expression Values

Hello,

I am trying to create totals of the different values of a certain expression in the Report Footer. Currently I have the expression in a group which gives me a running subtotal of the 4 different values of the expression. Now I need 4 running Totals of the 4 different value subtotals. I tried placing some code in the Report Properties but I had a hard time trying to code visual basic within the editor.

Any info is helpful.

I answered my own question. I didn't need any coding. It was basically creating new fields and applying the calculations to the new created fields.

COUNT Expression Values

Hello,

Any info is helpful.

I answered my own question. I didn't need any coding. It was basically creating new fields and applying the calculations to the new created fields.sql

Count consecutive numbers

I'm trying extract a count of consecutive numbers, or "unbroken" years in
this case, at any particular given time.

For example (simplified):

CREATE TABLE #Customers
(
CustNo INT,
YearNo INT,
IsCust CHAR(1)
)

INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')
INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')

SELECT * FROM #Customers

CustNo YearNo IsCust
---- ---- --
999 2006 Y
999 2005 Y
999 2004 Y
999 2003 N
999 2002 N
999 2001 Y
999 2000 Y

In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
2, etc. Ideally I'd feed it a single year to lookup

I'm resisting the urge to create cursor here -- anyone have any hints?

...Chris.ChrisD wrote:

> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001
> for 2, etc. Ideally I'd feed it a single year to lookup

This works in Postgres, you'll have to change the "limit 1" to mssql TOP 1
syntax. Also note the hardcoded year on line 4, replace that with a
parameter.

Ironically, this only works if you specify the year. Without the year you
get spurious rows.

select a.yearno,b.yearno,(a.yearno - b.yearno) + 1 as "years"
from customers a join customers b on a.custno = b.custno
where a.yearno > b.yearno
AND a.yearno = 2006
AND a.isCust = 'Y' and b.isCust = 'Y'
and not exists
(
select yearno
FROM customers x
WHERE x.custno = a.custno
AND x.yearno between b.yearno AND a.yearno
AND x.isCust = 'N'
)
order by b.yearno
limit 1

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@.(Sec)ure(Dat)a(.com)|||"ChrisD" <spambucket@.hotmail.com> wrote in message news:<Yfn2e.840846$Xk.593396@.pd7tw3no>...
> I'm trying extract a count of consecutive numbers, or "unbroken" years in
> this case, at any particular given time.
> For example (simplified):
> CREATE TABLE #Customers
> (
> CustNo INT,
> YearNo INT,
> IsCust CHAR(1)
> )
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')
> SELECT * FROM #Customers
8<-- Obvious result omitted
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> 2, etc. Ideally I'd feed it a single year to lookup
> I'm resisting the urge to create cursor here -- anyone have any hints?
> ...Chris.

The computation you want to perform is a subtraction.
There are some caveats concernig your data.

Just two hints ...|||how about this:

select top 1 max(a.yearno)+1 as from_ ,b.yearno as to_ , b.yearno -(
max(a.yearno)+1) as consecutive_time from #customers a join #Customers
b on a.custno = b.custno and
a.iscust='N' and b.iscust='Y' and a.yearno < b.yearno
group by b.yearno
order by consecutive_time desc

i.e. get the max diff between an 'N' and the 'Y' after it|||create view cust as
select custno, yearno, isCust from Customers
union
select custno, min(yearno) - 1, 'N'
from Customers group by custno
go

select custno,yearno, iscust,
case iscust
when 'N' THEN 0
ELSE 1+(select count(*)
from cust a where a.custno = b.custno and
a.yearno < b.yearno and
(a.yearno >
(select max(yearno) from cust c where iscust = 'N' and yearno <
b.yearno and custno = b.custno))
) end as active_for
from cust b
where yearno >= (select min(yearno) from customers x where x.custno =
b.custno )
order by custno, yearno|||"ChrisD" <spambucket@.hotmail.com> wrote in message news:Yfn2e.840846$Xk.593396@.pd7tw3no...
> I'm trying extract a count of consecutive numbers, or "unbroken" years in
> this case, at any particular given time.
> For example (simplified):
> CREATE TABLE #Customers
> (
> CustNo INT,
> YearNo INT,
> IsCust CHAR(1)
> )
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')
> INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')
> SELECT * FROM #Customers
> CustNo YearNo IsCust
> ---- ---- --
> 999 2006 Y
> 999 2005 Y
> 999 2004 Y
> 999 2003 N
> 999 2002 N
> 999 2001 Y
> 999 2000 Y
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> 2, etc. Ideally I'd feed it a single year to lookup
> I'm resisting the urge to create cursor here -- anyone have any hints?
> ...Chris.

SELECT C.CustNo AS CustNo,
C.YearNo AS YearNo,
C.YearNo - MAX(FY.YearNo) + 1 AS YearTally
FROM #Customers AS C
INNER JOIN
(SELECT C1.CustNo, C1.YearNo
FROM #Customers AS C1
LEFT OUTER JOIN
#Customers AS C2
ON C1.CustNo = C2.CustNo AND
C2.YearNo = C1.YearNo - 1 AND
C2.IsCust = 'Y'
WHERE C1.IsCust = 'Y' AND C2.CustNo IS NULL) AS FY -- 1st year
ON FY.CustNo = C.CustNo AND
C.IsCust = 'Y' AND
FY.YearNo <= C.YearNo
GROUP BY C.CustNo, C.YearNo
ORDER BY CustNo, YearNo

--
JAG|||"ChrisD" <spambucket@.hotmail.com> wrote in message news:<Yfn2e.840846$Xk.593396@.pd7tw3no>...
> I'm trying extract a count of consecutive numbers, or "unbroken" years in
> this case, at any particular given time.
> For example (simplified):
> CREATE TABLE #Customers
> (
> CustNo INT,
> YearNo INT,
> IsCust CHAR(1)
> )
8<----Big snip
> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> 2, etc. Ideally I'd feed it a single year to lookup
> I'm resisting the urge to create cursor here -- anyone have any hints?
> ...Chris.

As I said in my previous posting:
The computation you want to perform is a subtraction.
There are some caveats concernig your data.

Chris wanted hints, not complete solutions.
The solutions offered are likely to fail (I didn't test this)
if there is an 'active' year without any 'inactive' predecessor.|||"Theo Peterbroers" <peterbroers@.floron.leidenuniv.nl> wrote in message
news:39bb2c10.0503300659.231f1c7c@.posting.google.c om...
> "ChrisD" <spambucket@.hotmail.com> wrote in message news:<Yfn2e.840846$Xk.593396@.pd7tw3no>...
> > I'm trying extract a count of consecutive numbers, or "unbroken" years in
> > this case, at any particular given time.
> > For example (simplified):
> > CREATE TABLE #Customers
> > (
> > CustNo INT,
> > YearNo INT,
> > IsCust CHAR(1)
> > )
> 8<----Big snip
> > In 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for
> > 2, etc. Ideally I'd feed it a single year to lookup
> > I'm resisting the urge to create cursor here -- anyone have any hints?
> > ...Chris.
> As I said in my previous posting:
> The computation you want to perform is a subtraction.
> There are some caveats concernig your data.
> Chris wanted hints, not complete solutions.

I didn't take that as his literal intention. If it was, a quick glance
will reveal a solution, but probably not lead to comprehension,
and he can choose to ignore it.

> The solutions offered are likely to fail (I didn't test this)
> if there is an 'active' year without any 'inactive' predecessor.

His sample data includes an active year without an inactive
predecessor. As Chris was helpful enough to include DDL
and sample data, I assume all respondents who offered
complete solutions availed themselves of it. As far as I
can tell, my solution solves the problem.

--
JAG|||Yet another version, with a little-used predicate!

SELECT X.cust_nbr, MIN(X.start_date) AS start_date, X.end_date
FROM (SELECT C1.cust_nbr, C1.cust_year, MAX(C2.cust_year)
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_nbr = C2.cust_nbr
AND C1.cust_year <= C2.cust_year
AND 'Y' = ALL (SELECT cust_flag
FROM Customers AS C3
WHERE C3.cust_nbr = C2.cust_nbr
AND C3.cust_year BETWEEN C1.cust_year AND
C2.cust_year)
GROUP BY C1.cust_nbr, C1.cust_year)
AS X(cust_nbr, start_year, end_year)
GROUP BY X.cust_nbr, X.end_date;|||Opps! fix my typos:

SELECT X.cust_nbr, MIN(X.start_year) AS start_date, X.end_year
FROM (SELECT C1.cust_nbr, C1.cust_year, MAX(C2.cust_year)
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_nbr = C2.cust_nbr
AND C1.cust_year <= C2.cust_year
AND 'Y' = ALL (SELECT cust_flag
FROM Customers AS C3
WHERE C3.cust_nbr = C2.cust_nbr
AND C3.cust_year BETWEEN C1.cust_year AND
C2.cust_year)
GROUP BY C1.cust_nbr, C1.cust_year)
AS X(cust_nbr, start_year, end_year)
GROUP BY X.cust_nbr, X.end_year;|||ChrisD wrote:

> In 2006 CustNo 999 would have been active for 3 years, 2004 for 1,
> 2001 for 2, etc. Ideally I'd feed it a single year to lookup

Thanks all for the nudges!

I was able to make this work using a combination of John's and Kenneth's
samples. Joe's works too.

In practice I will always have a previous year -- but I suppose it's a
always a good idea to check.

...Chris.

Count by month in summarized data

create table calls
(
customer char(10),
call_date datetime,
called_number char(10),
call_ammount char(10)
)
insert calls values('Customer1','1/1/2005','555-1234','1.00')
insert calls values('Customer2','1/2/2005','555-1231','0.50')
insert calls values('Customer3','1/1/2005','555-1232','5.00')
insert calls values('Customer4','2/1/2005','555-1233','1.00')
insert calls values('Customer1','2/2/2005','555-1234','1.00')
insert calls values('Customer1','2/3/2005','555-1235','1.00')
insert calls values('Customer2','2/4/2005','555-1236','3.00')
insert calls values('Customer4','2/1/2005','555-1237','2.00')
insert calls values('Customer5','3/1/2005','555-1238','2.00')
I need to get a list of customers calling for > $2.50 a month.
select MONTH(call_date) as Months
, customer
,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
,count( distinct customer ) as 'No. of cust.'
from calls with(nolock)
where call_date >= '1/1/2005'
group by MONTH(call_date), customer
--with rollup
HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
order by Months
, 'SUM Ammount $' desc
(without the counts) will get what I need .
Months Customer SUM $ No. of cust.
1 Customer3 5.00 1
2 Customer2 3.00 1
2 Customer4 3.00 1
3 Customer5 3.00 1
Turns out , I 'd also need to get monthly counts (ideally with $
totals) of these customers .
This is where I get stuck.
When I add a count to the query I have the result as shown above but
what I'd need is
Months SUM $ No. of cust.
1 5.00 1
2 6.00 2
3 3.00 1
or totals like with Rollup but Rollup doesn't work with distinct count.
BTW yes, this is simplified, and I know this is bad (no) design but
there is nothing I can do about that at the moment.
TIA
ZTry using your query as a derived table.
select
months,
sum([SUM $]) as c1,
sum([No. of cust.]) as c2
from
(
select MONTH(call_date) as Months
, customer
,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
,count( distinct customer ) as 'No. of cust.'
from calls with(nolock)
where call_date >= '1/1/2005'
group by MONTH(call_date), customer
HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
)
group by
months
AMB
"zoranlee@.hotmail.com" wrote:

> create table calls
> (
> customer char(10),
> call_date datetime,
> called_number char(10),
> call_ammount char(10)
> )
> insert calls values('Customer1','1/1/2005','555-1234','1.00')
> insert calls values('Customer2','1/2/2005','555-1231','0.50')
> insert calls values('Customer3','1/1/2005','555-1232','5.00')
> insert calls values('Customer4','2/1/2005','555-1233','1.00')
> insert calls values('Customer1','2/2/2005','555-1234','1.00')
> insert calls values('Customer1','2/3/2005','555-1235','1.00')
> insert calls values('Customer2','2/4/2005','555-1236','3.00')
> insert calls values('Customer4','2/1/2005','555-1237','2.00')
> insert calls values('Customer5','3/1/2005','555-1238','2.00')
> I need to get a list of customers calling for > $2.50 a month.
> select MONTH(call_date) as Months
> , customer
> ,sum (cast(call_ammount as decimal(3,2))) as 'SUM $'
> ,count( distinct customer ) as 'No. of cust.'
> from calls with(nolock)
> where call_date >= '1/1/2005'
> group by MONTH(call_date), customer
> --with rollup
> HAVING sum(cast(call_ammount as decimal(3,2))) > 2.5
> order by Months
> , 'SUM Ammount $' desc
> (without the counts) will get what I need .
> Months Customer SUM $ No. of cust.
> 1 Customer3 5.00 1
> 2 Customer2 3.00 1
> 2 Customer4 3.00 1
> 3 Customer5 3.00 1
> Turns out , I 'd also need to get monthly counts (ideally with $
> totals) of these customers .
> This is where I get stuck.
> When I add a count to the query I have the result as shown above but
> what I'd need is
> Months SUM $ No. of cust.
> 1 5.00 1
> 2 6.00 2
> 3 3.00 1
> or totals like with Rollup but Rollup doesn't work with distinct count.
> BTW yes, this is simplified, and I know this is bad (no) design but
> there is nothing I can do about that at the moment.
> TIA
> Z
>

Thursday, March 22, 2012

Couldn't use and aggregate funtion on an another aggregate function

I have a column that has an expression with a runningvalue in it, a sum of "PO Count" for each month. I need to create another column that aggregates the SUM of "PO Count %". I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.

Below is a message:
The value expression for the textbox 'textbox4' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

I am using stored procedure and whnever I try to add a calculated field, Visual studio itself is getting hanged and asked me to close.

I am using Ms-SQL server 2005 and Vs 2005 Team System Enterprise Architect editions.

Any help?

Thanks in indavnce

Visualcpp

Aggregates of aggregates are not supported

Couldn't make Filter Option of Report to work

I am trying to figure out how to create report using dynamic parameter and filter from dropdown. I saw a webcast that allow to drag and drop the filter functionality in the dataset and generate report. I have the SQL Server Beta 2 and VS 2005 Beta installed in my machine.
But I didn't see any option in data design that allow me to do the filtering. I can add report parameter but was unable to bind the parameter value to filter expression. I am really confuse and any help will be appreciated.

Are you using a relational database as your data source or Analysis Services? Only Analysis Services has the drag and drop filter functionality. If you are using relational, you have to add the query parameters and then the designer will autogenerate report parameters.

Take a look at some of the AdventureWorks sample reports and walkthroughs it should help.

|||Thank you Brian for your reply. It cleared lots of thing for me. Yeah I was try to get the filter from relational database.

I saw a demo of AdventureWorks sample in one MSDN event. I thought I can do the same with all the database.

I hadn't work with Analysis service before. I tried to get the AdventureWorks sample in my computer and I am SQL Server Beta 2(full including analysis and reporting service) and VS 2005 Beta 2. But I couldn't figure out how to get the sample working. I couldn't get the analysis service to get the AdventureWorks database hooked up. May be I have find more about analysis service. I am building a reporting project and may be it is a better solution..

But currently my problem is creating dynamic report from parameter selection. I tried the MSDN solution but I have more complex query. I am kind of lost using Dataset filters property. I have a paramter declared using Report Paramter. In my sql I have group by clause. I want to filter the record depending on the parameter selection at run time. Therefore, I was trying to use filters tab from dataset. But I don't know how to set the expression and value. I know I have to get the value from form paramter and the expression should be the field name but it is not working.

But thanks for showing me the right direction.|||Just install the relational database samples for AdventureWorks. There are several examples of dynamic parameters.

For any filter with a dynamic value list, you need two queries. The first brings back the list of values. The second uses a single value and has a parameter that is bound to a report parameter populated from the first query.

There is a tutorial in the Books Online called "Using a Dynamic Query in a Report" that has an example of this.

There are also several really good books on Reporting Services that might help. There is a list at http://www.microsoft.com/sql/reporting/techinfo/books.mspx.

Tuesday, March 20, 2012

Could not setup Web Service Identity --> Error Code 80070003

Hello!
I′m trying to setup a Report Server on my SQL 2005 server. In Reporting Services Configuration Manager I get an error when i try to create the Web Sevice Identity

ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 80070003
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)

The default website contains an office sharepoint 2007 beta solution if that could cause the error..

Would be real grateful for some hints...

Regards

Erik B

Have you found a solution to this? I'm getting the same error after moving the Identities from one Website to another.|||

8007003 is ERROR_PATH_NOT_FOUND. Are you on Win2K3? I suspect this has something to do with ASP.NET setup. Either the WMI provider could not open the ASP.NET registry keys, or it could not find the machine.config file. Make sure you have run aspnet_regiis -i -enable.

If it doesn't work, I would start using regmon and filemon to looks for suspicious errors that could lead to 8007003. Unfortunately at this point the error code is too general to provide any clue.

|||

Hi... I reinstalled SQL 2005 and then it worked fine.. However I think it had to do something with the Encryption Keys... Gwired if I were you I′d delete those from the Reporting Services Configuration Manager, create new ones and then setup a Web Service Identity.

Have a nice day

/Erk

|||

I ran into the same problem, and discovered the issue had to do with the fact that I installed SQL Server 2005, SP1, SP2 and THEN installed Reporting Services. Because I had installed SP2 before Reporting Services, SP2 was not applied to Reporting Services.

The only other thing of note that happened was that when I tried to run the User Provisioning Tool after installing SP2 I got a 'object not set to an instance of an object error'. I didn't really think anything about it at the time, but I think it was only because I figured I'd find the problem down the road. I guess you can say that I did.

Could not setup Web Service Identity --> Error Code 80070003

Hello!
I′m trying to setup a Report Server on my SQL 2005 server. In Reporting Services Configuration Manager I get an error when i try to create the Web Sevice Identity

The default website contains an office sharepoint 2007 beta solution if that could cause the error..

Would be real grateful for some hints...

Regards

Erik B

Have you found a solution to this? I'm getting the same error after moving the Identities from one Website to another.|||

If it doesn't work, I would start using regmon and filemon to looks for suspicious errors that could lead to 8007003. Unfortunately at this point the error code is too general to provide any clue.

|||

Have a nice day

/Erk

|||

Sunday, March 11, 2012

could not load type Smo.Agent.JobBaseCollection

I have an app that uses SMO to create/schedule jobs on Sql2005 which was working just fine thank you 2 days ago. Yesterday I installed SP2 on my workstation. Today the same code fails with the following error (when running on my PC where it worked before):

An exception of type 'System.TypeLoadException' occurred in Atlas.Quality.Admin.UI.DLL but was not handled in user code

Additional information: Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'.

The code in question is a class I built with this method:

public Job CreateNewJob(string jobName, string pkgName)

{}

which returns a reference to an object of type Microsoft.SqlServer.Management.Smo.Agent.Job

This code works with 'Microsoft.SqlServer.Smo' - product version 9.00.2047.00

This code fails with 'Microsoft.SqlServer.Smo' - product version 9.00.3042.00

Another developer that has not installed SP2 can run the application on his workstation - and it successfully creates jobs on the target SQL Server 2005/SP1.

Any help would be appreciated.

Also, this error occurs when viewing SQL Agent Job Activity Monitor and deleting a job:

TITLE: Microsoft SQL Server Management Studio

Could not load type 'Microsoft.SqlServer.Management.Smo.Agent.JobBaseCollection' from assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. (SqlManagerUI)

BUTTONS:

|||I'm seeing the same thing here, and I definitely have isolated it to the installation of SP2. No clues on a remedy yet.|||

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

|||

TobyKraft wrote:

I found that I had installed sql express and then later installed SSMS from sql enterprise edition. The SP2 for express appears to not update all the workstation components. So I suspected some version mismatch between the SMO DLLs and the client code that was still the old version.

For other reasons, I decided to reinstall both sql server and SSMS. I uninstalled them both, installed SQL server - std edition and the SSMS that comes with it. Then installed SP1 (to match our production environment) and do not see this problem now.

I will be installing SP2 soon though to check it out and will update this thread if I see a problem again.

Thanks.

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

Code Snippet

TITLE: Microsoft SQL Server

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2050.00&EvtSrc=Microsoft.SqlServer.Management.UI.WizardFrameworkErrorSR&EvtID=UncaughtException&LinkId=20476

ADDITIONAL INFORMATION:

BUTTONS:

I'm in the process of installing SQL Server SP2 to see if that addresses the problem.

Jay

|||

jay_harlow wrote:

It definately appears related to SQL Express SP2 with SQL SP1 on the same machine. My Microsoft Update history shows that SQL Express SP2 was installed on March 30, SQL Server update for SP1 was installed April 3. SQL Server SP2 is not installed. I get the following error when attempting to enable Full-Text Search on a new table.

As a follow up: Installing SQL Server SP2 resolved the issue.

Jay

|||Hi,

I'm also having the same problem. My SQL Server 2005 has SP2 installed. I started coding my program after SP2 was installed.

After creating the job, I will immediately start the job in the same block of code. Though there were no errors encountered, the job did not start. To make it run properly, I would open the job and the job step in Management Studio, and try to run it there. It will do the job.

When I try to alter the job in the code, it will encounter the problem discussed here.

Thanks! Any help will be appreciated.

Regards,
Lloyd|||

Lloyd,

Make sure you don't have different versions/svcpacks between the client tools and the server. That was my problem.

Toby

|||I am getting same error on trying to add fulltext catalog to any table on my database.What is the relation between FullText Catalog and Jobs?|||

I encounter the same issue. I installed SQL Server 2005 SP2 and started getting this message on when running the code below.

Code Snippet

private Job GetJobByName(string jobname)

{

try

{

ServerConnection cnn = new ServerConnection("localhost");

Server svr = new Server(cnn);

JobServer agent = svr.JobServer;

return agent.Jobs[jobname];

// etc

The resolution above states that the issue is solved when the client and server both have the same SQL Svr service packs installed, but this is running from my local machine connecting to my local machine. Perhaps I have misunderstood...?

Any other suggestions?

|||

Hi,

This problem with creating jobs using SMO seems to have appeared with SP2, but I don't know whether it is a "fix" for some flaw in the RTM or a new bug. What happens after SP2 is that the job created has no target servers. MSDN documentation suggests the target is the local server if you use the no-arg Server constructor, and that's what happens with the RTM. You can see this problem if you just open Management Studio and look at the created job's Target server page...multiple servers is selected and greyed out, and "Target local server" is NOT checked. You can fix this in Management Studio by just checking "Target local Server", but of course that isn't very useful if you're creating the jobs with SMO.

After a lot of experimentation, I found a work around. After you create the job (i.e., after calling Job.Create()), call Job.ApplyToTargetServer("(local)"). The passed string must be "(local)", including the parenthesis; not localhost, not the machine name. Doing this results in the "Target local server" being checked in Management Studio, and the job will run.

My suspicion is that in RTM, having an empty target server collection somehow defaulted to the local machine, but now it must be specified explicitly.

Dave

|||Looks like there must be a few reasons that this error occurs... we were still getting this error on jobs created via the SP2 Management Studio, but using SMO to retrieve the job in a DLL that was compiled against the pre-SP2 SQL Server DLLs.

After installing SP2 on the development machine, the error was not reproducible when debugging, so we just re-built and re-deployed the DLL.... no more Type Load problems.

HTH
Aranda

|||

The problem for this and the other errors mentioned here are becuase you still have mismatching Client and Server versions of SQL Server SP2. The version of the SP2 .NET SMO dlls are no longer in sync with the underlying SQL Server system stored procedures. Why is this? Becuase SQL Server SP2 was installed on a Windows XP environment that is missing MSDTC (Microsoft Distributed Transaction Coordinator) and the Notification Services and Client Tools portion of the upgrade fails.

Cool, I'll just start MSDTC and reinstall you say? Not quite. For whatever reason, many Windows XP machines no longer have MSDTC loaded in services (haven't figured out why yet). So the first step is to get MSDTC back, then reinstall SQL Server SP2. Here is the complete steps to solving this error:

You will need to reinstall SQL Server SP2, but first do a few tasks:

1) Make sure that MSDTC (Microsoft Distributed Transaction Coordinator) service is installed. Go to Control Panel->Administrative Tools->Services and look for Distributed Transaction Coordinator.
2) If it's installed, make sure the service is running by clicking on the Start button, then skip to step 4.
3) If MSDTC is not installed, you must manually reinstall it. To do this you must carefully - very carefully - follow these instructions: http://support.microsoft.com/default.aspx/kb/891801.
4) Now that we know MSDTC is installed and running, reinstall SQL Server 2005 SP2. You should only have to check Client Tools and Notification Services for this 2nd install.

5) Finished the install and you should be all set.

|||

I am not understanding something here. Thanks for the detailed explanation and fix GoodGuysWin, but I have some inconsistencies that seem to refute your findings:

1) The error occurs when our Win 2003 Server (with SQL Server 2005 SP2) tries to access locally stored jobs

2) The error occurs on WinXP SP2 (with SQL Server 2005 SP2) when code compiled against SQL 2005 SP1 tries to access locally stored jobs

3) The error is eliminated (without the suggested MSDTC fix) when the offending code is re-compiled against SQL 2005 SP2.

For the record, I can't see the MSTDC service on the WinXP SP2 box.

|||

Aranda,

Sounds like there are two sets of problems occuring. One is resolved by re-compiling SP1 jobs with SP2 as you have stated, but the SMO errors will appear elsewhere (for example creating a Full Text Index), if SP2 was installed without MSDTC. Hopefully my fix helps those folks.

Thursday, March 29, 2012

Count Records in a Group

Count Records Help

Count Query Question

Count of matching result sets

Count of matching result sets

Count of matching result sets

Tuesday, March 27, 2012

count measure question

Sunday, March 25, 2012

Count for each word in a field

COUNT Expression Values

COUNT Expression Values

Count consecutive numbers

Count by month in summarized data

Thursday, March 22, 2012

Couldn't use and aggregate funtion on an another aggregate function

Couldn't make Filter Option of Report to work

Tuesday, March 20, 2012

Could not setup Web Service Identity --> Error Code 80070003

Could not setup Web Service Identity --> Error Code 80070003

Sunday, March 11, 2012

could not load type Smo.Agent.JobBaseCollection

Counter settings

Blog Archive

About Me