Alias or Group SSAS Dimension at Query time.

In an MDX Query i am trying to alias (or group ) the returned dimension as shown below but i am getting the wrong result.I believe the issue is in the case statement logic.

Is there a way to alias (or group dynamically) dimension without creating a named column in DSV?

Any help will be appreciated.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

SET [GroupedRatings] AS

CASE

WHEN [Curve Family].[SP Rating].&[AA-] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AA+] THEN [Curve Family].[SP Rating].&[AA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[AAA+] THEN [Curve Family].[SP Rating].&[AAA]

WHEN [Curve Family].[SP Rating].&[BB-] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BB+] THEN [Curve Family].[SP Rating].&[BB]

WHEN [Curve Family].[SP Rating].&[BBB+] THEN [Curve Family].[SP Rating].&[BBB]

ELSE NULL

END

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

This is where the similarities between MDX and SQL can be confusing. What you really want to do is to create some calculated members to do your grouping and then create a set of these members.

eg.

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

MEMBER [Curve Family].[SP Rating].&[AA] AS Aggregate({[Curve Family].[SP Rating].&[AA-],[Curve Family].[SP Rating].&[AA+]})

MEMBER [Curve Family].[SP Rating].&[AAA] AS Aggregate({[Curve Family].[SP Rating].&[AAA-],[Curve Family].[SP Rating].&[AAA+]}

MEMBER [Curve Family].[SP Rating].&[BB] AS Aggregate({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]})

MEMBER [Curve Family].[SP Rating].&[BBB] AS Aggregate({[Curve Family].[SP Rating].&[BBB+]})

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].&[AA]
,[Curve Family].[SP Rating].&[AAA]
,[Curve Family].[SP Rating].&[BB]
,[Curve Family].[SP Rating].&[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

The case statement won't create new members dynamically, which it looks like you're trying to do. You could declare each member explicitly, like:

WITH MEMBER [Measures].[Long] AS

IIF(

[Measures].[Risk Value]<0,

[Measures].[Risk Value],

null)

Member [Curve Family].[SP Rating].[AA] as

Sum({[Curve Family].[SP Rating].&[AA-], [Curve Family].[SP Rating].&[AA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[AAA] as

Sum({[Curve Family].[SP Rating].&[AAA-], [Curve Family].[SP Rating].&[AAA+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BB] as

Sum({[Curve Family].[SP Rating].&[BB-], [Curve Family].[SP Rating].&[BB+]}),

SOLVE_ORDER = 10

Member [Curve Family].[SP Rating].[BBB] as

Sum({[Curve Family].[SP Rating].&[BBB-], [Curve Family].[SP Rating].&[BBB+]}),

SOLVE_ORDER = 10

SET [GroupedRatings] AS

{[Curve Family].[SP Rating].[AA], [Curve Family].[SP Rating].[AAA],

[Curve Family].[SP Rating].[BB], [Curve Family].[SP Rating].[BBB]}

SELECT { [Measures].[Long]} ON COLUMNS,

{ ([GroupedRatings]*[Book].[Desk].[Desk].Members) } --cross join grouped rating and desk members

ON ROWS

FROM [DM]

|||

Thanks Darren for pointing me in the right direction.I changed the code to the sample below to make it work properly.

WITH

MEMBER [Curve Family].[SP Rating].[AA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[AAA] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[AAA-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[AAA+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BB] AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BB+],([Measures].[Risk Value])<0)})

MEMBER [Curve Family].[SP Rating].[BBB]AS

Aggregate({FILTER([Curve Family].[SP Rating].&[BBB-],([Measures].[Risk Value])<0),FILTER([Curve Family].[SP Rating].&[BBB+],([Measures].[Risk Value])<0)})

SET [GroupedRatings] AS

{

[Curve Family].[SP Rating].[AA]

,[Curve Family].[SP Rating].[AAA]

,[Curve Family].[SP Rating].[BB]

,[Curve Family].[SP Rating].[BBB]

}

SELECT

NON EMPTY { [Measures].[Risk Value]} ON COLUMNS,

NON EMPTY {([GroupedRatings]*[Vdim Book].[Desk].[Desk].Members)} ON ROWS

FROM

[DM]

Aggregator Transform bug or feature

Hi,

I have two input columns (both DT_I4) in a column collection to a Aggregator transform. Now I am doing a group by to one and Count to another column.

To my surprise the output's column datatype is changed for Count Transform (DT_UI8) and I have to put extra Data Conversion Transfrom to get my DT_I4 datatype back.

Is this a bug or feature.

Dharmbir

Dharmbir wrote:

Hi,

I have two input columns (both DT_I4) in a column collection to a Aggregator transform. Now I am doing a group by to one and Count to another column.

To my surprise the output's column datatype is changed for Count Transform (DT_UI8) and I have to put extra Data Conversion Transfrom to get my DT_I4 datatype back.

Is this a bug or feature.

Dharmbir

Feature. The aggregated columns are "new" to the dataflow, and a count can't be negative so they use the unsigned-integer datatype. I know, I hate it too, but that's the way it goes.

Aggregation table

Hi all

i need to create aggregation table from 2 tables group by date, any one have any idea how to create it by using SSIS

thanks & regards

Use a merge join or union all to bring your two tables together and then use the aggregate component.

Aggregating groups of rows using SQL

Dear Group,
I have a SQL coding problem, which I hope that you can help me with!
I have the following SQL view, which returns a Data Table, STOCK_TRADE, with
the following data..
CUSTOMER STOCK_CODE QUANTITY PRICE DATE
J. BLOGGS MSFT 1000 535.50 10/05/2006
J. BLOGGS MSFT 2000 536.75 11/05/2006
J. BLOGGS GOOG 500 400.00 10/05/2006
J. BLOGGS GOOG 100 300.00 12/05/2006
H. ENFIELD MSFT 300 536.75 11/05/2006
My query is, I wish to aggregate rows which are for the same customer and
stock code taking a SUM of the QUANTITY and an AVERAGE of the PRICE and the
FIRST/TOP/EARLIEST of the DATE, so that the table looks like this:
CUSTOMER STOCK_CODE QUANTITY PRICE DATE
J. BLOGGS MSFT 3000 536.13 10/05/2006
J. BLOGGS GOOG 600 350.00 10/05/2006
H. ENFIELD MSFT 300 536.75 11/05/2006
Ultimately, I am aiming to order by STOCK_CODE in a Crystal Report to GROUP
BY the STOCK_CODE and then just one line per customer for each stock that
they have.
Can anyone suggest the SQL - preferably without using cursors - to perform a
SELECT on the STOCK_TRADE table as shown in the top example to return the
aggregate Data Set in the bottom table?
I am using Visual Studio 2005 (.NET) and SQL Server 2005, although generic
SQL would be my preference to solve the programming task.
Many thanks in advance for your time and help.SELECT CUSTOMER,
STOCK_CODE,
SUM(QUANTITY),
AVG(PRICE),
MIN(DATE)
FROM STOCK_TRADE
GROUP BY CUSTOMER,STOCK_CODE|||<markc600@.hotmail.com> wrote in message
news:1147021432.247292.102430@.e56g2000cwe.googlegroups.com...
> SELECT CUSTOMER,
> STOCK_CODE,
> SUM(QUANTITY),
> AVG(PRICE),
> MIN(DATE)
> FROM STOCK_TRADE
> GROUP BY CUSTOMER,STOCK_CODE
Many thanks for that - it seems to be exactly what I'm looking for.
Apologies for the double posting too; I've cancelled the second message and
forgot that I posted this one.
Thanks again.|||SELECT [CUSTOMER], [STOCK_CODE], SUM(QUANTITY) as [QUANTITY], AVG(PRICE) as
[PRICE], MIN(DATE) as [DATE] FROM WHATEVERYOURVIEWIS GROUP BY [CUSTOMER],
[STOCK_CODE]
Although this is the wrong place to post this!
Cheers,
Greg Young
MVP - C#
"Liddle Feesh" <none> wrote in message
news:445e273b$0$9257$ed2619ec@.ptn-nntp-reader01.plus.net...
> Dear Group,
> I have a SQL coding problem, which I hope that you can help me with!
> I have the following SQL view, which returns a Data Table, STOCK_TRADE,
> with the following data..
> CUSTOMER STOCK_CODE QUANTITY PRICE DATE
> J. BLOGGS MSFT 1000 535.50 10/05/2006
> J. BLOGGS MSFT 2000 536.75 11/05/2006
> J. BLOGGS GOOG 500 400.00 10/05/2006
> J. BLOGGS GOOG 100 300.00 12/05/2006
> H. ENFIELD MSFT 300 536.75 11/05/2006
> My query is, I wish to aggregate rows which are for the same customer and
> stock code taking a SUM of the QUANTITY and an AVERAGE of the PRICE and
> the FIRST/TOP/EARLIEST of the DATE, so that the table looks like this:
> CUSTOMER STOCK_CODE QUANTITY PRICE DATE
> J. BLOGGS MSFT 3000 536.13 10/05/2006
> J. BLOGGS GOOG 600 350.00 10/05/2006
> H. ENFIELD MSFT 300 536.75 11/05/2006
> Ultimately, I am aiming to order by STOCK_CODE in a Crystal Report to
> GROUP BY the STOCK_CODE and then just one line per customer for each stock
> that they have.
> Can anyone suggest the SQL - preferably without using cursors - to perform
> a SELECT on the STOCK_TRADE table as shown in the top example to return
> the aggregate Data Set in the bottom table?
> I am using Visual Studio 2005 (.NET) and SQL Server 2005, although generic
> SQL would be my preference to solve the programming task.
> Many thanks in advance for your time and help.
>|||hmm this showed up with no replies when I first saw it :-?
"Greg Young" <DruckDruckGoose@.hotmail.com> wrote in message
news:udINwWocGHA.3388@.TK2MSFTNGP05.phx.gbl...
> SELECT [CUSTOMER], [STOCK_CODE], SUM(QUANTITY) as [QUANTITY], AVG(PRICE)
> as [PRICE], MIN(DATE) as [DATE] FROM WHATEVERYOURVIEWIS GROUP BY
> [CUSTOMER], [STOCK_CODE]
> Although this is the wrong place to post this!
> Cheers,
> Greg Young
> MVP - C#
> "Liddle Feesh" <none> wrote in message
> news:445e273b$0$9257$ed2619ec@.ptn-nntp-reader01.plus.net...
>|||Just one point...
Are you sure you want a straight average of stock price, rather than a
weighted average?
i.e. if 999 shares sell for $1000 each, and 1 share sells for $2, should
the average be $501 or should it be $999.002?
You may want something like this...
SELECT CUSTOMER,
STOCK_CODE,
SUM(QUANTITY) as TotalQuantity,
SUM(PRICE*quantity)/SUM(QUANTITY) as AvgPrice,
MIN(DATE) as FirstDate
FROM STOCK_TRADE
GROUP BY CUSTOMER,STOCK_CODE
"Liddle Feesh" <none> wrote in message
news:445e29c0$0$9265$ed2619ec@.ptn-nntp-reader01.plus.net...
> <markc600@.hotmail.com> wrote in message
> news:1147021432.247292.102430@.e56g2000cwe.googlegroups.com...
> Many thanks for that - it seems to be exactly what I'm looking for.
> Apologies for the double posting too; I've cancelled the second message
and
> forgot that I posted this one.
> Thanks again.
>

Aggregate Total Acreage and Group By for Mail Merge

I was helped on an earlier question to complete my mail merge with the following code:
select YourTable.*
from YourTable
inner join --DistinctNames
(select Max(PrimaryKey) as PrimaryKey
from YourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey
Basically this code queries my mailing list and ensures that i do not send mutiple letters to one person at the same address who might be in the batabase more than once. However, the reason they are in there more than once is that they might own additional properties. Anyway, I have a column that includes their acreage for each property in each record and I would like to add those up for each person during my query. Thought anyone? Thanks!select YourTable.*, DistinctNames.total_acreage
from YourTable
inner join --DistinctNames
(select Max(PrimaryKey) as PrimaryKey, sum(acreage) total_acreage
from YourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey|||Thanks, the query seems to run withour error, however, the "total acreage" field is simply populated with one of their acreage values, not their total. It looks like the sumation is occurring after the new table is created, which would provide an incorrect result. Any other thoughts?|||Nevermind, it worked flawlessly. Thanks a lot!

aggregate function in the argument to another aggregate function

Hello all,
I am new to SQL Reporting services and I am having trouble performing a
group sum. I Currently have a group within another group the embedded
group (group2) is performing a group sum
(=3DCount(Fields!FacilityTemp1.V=ACalue/Fields!Temp2.Value) and this works
fine but now I would like to use the outside group to perform a sum of
all values within group2 however SQL reporting Services does not allow
aggregate function in the argument to another aggregate function. Does
anyone no how I can simply get around this. Expression that does not
work:
=3DSUM(Count(Fields!FacilityTemp=AC1.Value/Fields!Temp2.Value)
Thanks=20
ChrisSince this question is specific to SQL Server Reporting Services, you
should post it in the microsoft.public.sqlserver.reportingsvcs group.
Razvan

Aggregate - Sum with group by

Hi,

I'm trying to use the aggregate transformation to sum my orders table unit price and quantity with a grouping of state but i can't see how to add the sub grouping. My order table has the following fields of interest Unit Price (Money), Quantity (Integer) and State (Varchar)

ID Unit Price Quantity State 1 $2.19 500 AZ 2 $29.99 33 WA 3 $1000.00 1 WA 4 $1.20 7 WA

When i run the aggregate i want the output to be grouped by state

Total Price Quantity Sold State $2.19 500 AZ $1031.19 41 WA

Hope the values are correct

Martin

Add the aggregate to your data flow, and connect an input. Open the properties and check the Unit Price, Quantity, and State on the available input columns. In the grid at the bottom, make sure the State has the Operation set to "Group By", and Operation to "Sum" for the Unit Price and Quantity columns. That should do it.|||In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?|||

Martin Perkins wrote:

In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?

That's strange. What's the data type of the state column? Have you installed service pack 1 at least?|||

The State property is Nvarchar(max) Just installing service pack 2 for SQL Server 2005

|||

It was the length of the state column. If i convert the column from Nvarchar(max) to Nvarchar(20) i can then group by.

Thanks for help

Martin

|||

SSIS treats NVARCHAR(MAX) as BLOB type, and does not support grouping by blobs. You need to convert it to fixed-lenght type, like NVARCHAR(2).

You can do it either

1) at the source - NVARCHAR(MAX) is very inefficient method of storing 2-character data, if you can change the source, I suggest doing it

2) case to fixed-lenght type in the SQL query

3) use SSIS to create another column of fixed lenght (I'm not sure if type convertion transform does it, if not then try derived column transform)

Hope this help,

Michael.

Agent job owner/visibility

Windows 2000, SQL Server 2000 SP 4, Windows Authentication. We have several
developers, all in a Windows domain group, and when one developer creates an
Agent job, that job is owned by that one developer and is not visible by the
other developers. Is it possible to have a job be owned by a login that
corresponds to a Windows domain group? The Owner dropdown in EM seems to
indicate it's not possible, because no groups (domain or local) are listed.
We are not in the sqladmin group, nor in the local Windows Administrators
group.
Thanks
Vern RabeVern
Has this Windows Domain Group a login to SQL Server?
What are permissions to the login?
"Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
news:DA097453-1559-45B5-BB2C-E45A3180DA3F@.microsoft.com...
> Windows 2000, SQL Server 2000 SP 4, Windows Authentication. We have
> several
> developers, all in a Windows domain group, and when one developer creates
> an
> Agent job, that job is owned by that one developer and is not visible by
> the
> other developers. Is it possible to have a job be owned by a login that
> corresponds to a Windows domain group? The Owner dropdown in EM seems to
> indicate it's not possible, because no groups (domain or local) are
> listed.
> We are not in the sqladmin group, nor in the local Windows Administrators
> group.
> Thanks
> Vern Rabe|||Uri:
Yes, we have a login to SQL Server for that Windows Domain Group. I don't
believe the login is in any fixed server roles.
I may have separately found the solution, however. I have requested the DBAs
to add the login to the TargetServersRole in the msdb database. Haven't
tested yet (or even gotten their approval to do it), so we'll see.
Thanks
Vern
"Uri Dimant" wrote:

> Vern
> Has this Windows Domain Group a login to SQL Server?
> What are permissions to the login?
>
> "Vern Rabe" <VernRabe@.discussions.microsoft.com> wrote in message
> news:DA097453-1559-45B5-BB2C-E45A3180DA3F@.microsoft.com...
>
>

Age Old Question about GROUP BY clause (i think) - Probably easy answer

How does one get the primary key of the row that is joined in via a
group by aggregate clause when the aggregate is not performed on the
primary key?

For example,

Person table
(
PersonID int,
FirstName varchar(50)
LastName varchar(50)
)

Visit table
(
VisitID int,
PersonID int,
VisitDate datetime
)

These are simplified versions of my tables. I'm trying to create a
view that gets the first time each person Visited:

selectp.PersonID,
min(v.VisitDate)
fromVisit v
joinPerson p on p.PersonID = v.PersonID
group byp.PersonID

The problem is that I would like to return the VisitID in the
resultset, but when I do it expands the query since I have to also put
it in the group by clause.

What are the different ways to achieve this?
Subqueries?
Only return the date and then join off of date on the outside?

Neither of these seem too entising...

Thanks in advance for any help.

-DaveHow about:

select person.*, visit.*
from person left join visit
on person.personid = visit.visitid
and visit.visitdate =
(select min(visitdate) from visit where personid = person.personid)

FN

malcolm wrote:
> How does one get the primary key of the row that is joined in via a
> group by aggregate clause when the aggregate is not performed on the
> primary key?
> For example,
> Person table
> (
> PersonID int,
> FirstName varchar(50)
> LastName varchar(50)
> )
>
> Visit table
> (
> VisitID int,
> PersonID int,
> VisitDate datetime
> )
> These are simplified versions of my tables. I'm trying to create a
> view that gets the first time each person Visited:
> selectp.PersonID,
> min(v.VisitDate)
> fromVisit v
> joinPerson p on p.PersonID = v.PersonID
> group byp.PersonID
> The problem is that I would like to return the VisitID in the
> resultset, but when I do it expands the query since I have to also put
> it in the group by clause.
> What are the different ways to achieve this?
> Subqueries?
> Only return the date and then join off of date on the outside?
> Neither of these seem too entising...
> Thanks in advance for any help.
> -Dave|||Oops, make that third line:

on person.personid = visit.personid

But I'm sure you got the gist.

FN

fn wrote:

> How about:
> select person.*, visit.*
> from person left join visit
> on person.personid = visit.visitid
> and visit.visitdate =
> (select min(visitdate) from visit where personid = person.personid)
> FN
> malcolm wrote:
>> How does one get the primary key of the row that is joined in via a
>> group by aggregate clause when the aggregate is not performed on the
>> primary key?
>>
>> For example,
>>
>> Person table
>> (
>> PersonID int,
>> FirstName varchar(50)
>> LastName varchar(50)
>> )
>>
>>
>> Visit table
>> (
>> VisitID int,
>> PersonID int,
>> VisitDate datetime
>> )
>>
>> These are simplified versions of my tables. I'm trying to create a
>> view that gets the first time each person Visited:
>>
>> select p.PersonID,
>> min(v.VisitDate)
>> from Visit v
>> join Person p on p.PersonID = v.PersonID
>> group by p.PersonID
>>
>> The problem is that I would like to return the VisitID in the
>> resultset, but when I do it expands the query since I have to also put
>> it in the group by clause.
>>
>> What are the different ways to achieve this? Subqueries? Only return
>> the date and then join off of date on the outside?
>>
>> Neither of these seem too entising...
>>
>> Thanks in advance for any help.
>>
>> -Dave|||"malcolm" <chakachimp@.yahoo.com> wrote in message
news:4fe7c9e8.0406241624.18ca60ed@.posting.google.c om...
> How does one get the primary key of the row that is joined in via a
> group by aggregate clause when the aggregate is not performed on the
> primary key?
> For example,
> Person table
> (
> PersonID int,
> FirstName varchar(50)
> LastName varchar(50)
> )
>
> Visit table
> (
> VisitID int,
> PersonID int,
> VisitDate datetime
> )
> These are simplified versions of my tables. I'm trying to create a
> view that gets the first time each person Visited:
> select p.PersonID,
> min(v.VisitDate)
> from Visit v
> join Person p on p.PersonID = v.PersonID
> group by p.PersonID
> The problem is that I would like to return the VisitID in the
> resultset, but when I do it expands the query since I have to also put
> it in the group by clause.
> What are the different ways to achieve this?
> Subqueries?
> Only return the date and then join off of date on the outside?
> Neither of these seem too entising...
> Thanks in advance for any help.
> -Dave

SELECT P.PersonID, V1.VisitID, V1.VisitDate
FROM Persons AS P
LEFT OUTER JOIN
Visits AS V1
ON P.PersonID = V1.PersonID
LEFT OUTER JOIN
Visits AS V2
ON P.PersonID = V2.PersonID AND
V2.VisitDate < V1.VisitDate
WHERE V2.VisitDate IS NULL

--
JAG|||Please post DDL in the future. What you did post had no keys, too
many NULLs, the wrong datatypes (ever meet anyone with a fifty letter
first name? Only if they were named for a full Bible verse) and
singular table names. Is this what you meant?

CREATE TABLE Persons
(person_id INTEGER NOT NULL PRIMARY KEY, --assumption
first_name VARCHAR(15) NOT NULL, --USPS size name
last_name VARCHAR(15) NOT NULL); --USPS size name

Now I have to make assumptions about not having visits from unknown
people in my DRI.

CREATE TABLE Visits
(visit_nbr INTEGER NOT NULL PRIMARY KEY, --assumption
person_id INTEGER NOT NULL -- DRI assumption
REFERENCES Persons (person_id),
visit_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL -- default
assumption
);

>> I'm trying to create a view that gets the first time each person
visited .. The problem is that I would like to return the visit_id in
the resultset, <<

CREATE VIEW FirstVisits (person_id, visit_nbr, visit_date)
AS
SELECT V1.person_id, V1.visit_nbr, V1.visit_date
FROM Visits AS V1
WHERE V1.visit_date
= (SELECT MIN(v2.visit_date)
FROM Visits AS V2
WHERE V1.person_id = V2.person_id);

Yeah, yeah, I know it was quicky posting, but get in the habit of
doing it right all the time. Most DML problems come from bad DDL.|||You're right I did quickly slop together the question. What I posted
doesn't even come close to my example so I simply took too many
shortcuts in my post; I was simply trying to get to the point of my
question. Consider it DDL-UML ;)

The reason I didn't post working DDL is because I assumed someone
would have the answer off the top of thier head. Thanks for the
detailed response though.

-dave

jcelko212@.earthlink.net (--CELKO--) wrote in message news:<18c7b3c2.0406251250.13026daa@.posting.google.com>...
> Please post DDL in the future. What you did post had no keys, too
> many NULLs, the wrong datatypes (ever meet anyone with a fifty letter
> first name? Only if they were named for a full Bible verse) and
> singular table names. Is this what you meant?
> CREATE TABLE Persons
> (person_id INTEGER NOT NULL PRIMARY KEY, --assumption
> first_name VARCHAR(15) NOT NULL, --USPS size name
> last_name VARCHAR(15) NOT NULL); --USPS size name
> Now I have to make assumptions about not having visits from unknown
> people in my DRI.
> CREATE TABLE Visits
> (visit_nbr INTEGER NOT NULL PRIMARY KEY, --assumption
> person_id INTEGER NOT NULL -- DRI assumption
> REFERENCES Persons (person_id),
> visit_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL -- default
> assumption
> );
> >> I'm trying to create a view that gets the first time each person
> visited .. The problem is that I would like to return the visit_id in
> the resultset, <<
> CREATE VIEW FirstVisits (person_id, visit_nbr, visit_date)
> AS
> SELECT V1.person_id, V1.visit_nbr, V1.visit_date
> FROM Visits AS V1
> WHERE V1.visit_date
> = (SELECT MIN(v2.visit_date)
> FROM Visits AS V2
> WHERE V1.person_id = V2.person_id);
> Yeah, yeah, I know it was quicky posting, but get in the habit of
> doing it right all the time. Most DML problems come from bad DDL.|||Don't use a group by clause, use a subquery instead.

SELECT p.personID,v.VisitDate
FROM Person p,Visit v
WHERE p.PersonID = v.PersonID
and p.VisitDate = (SELECT MIN(v2.VisitDate)
FROM Visit v2
WHERE v2.PersonID = p.PersonID)

Age Average

I have to calculate the average age in a group.
What is the best way to calculate the average age where I have the
individual's birth date?
Thanks in advance!SELECT AVG(DATEDIFF(DAY,birth_date,CURRENT_TIME
STAMP))/365.25
FROM YourTable
GROUP BY ...
David Portas
SQL Server MVP
--|||I would use
select datediff(mm, yourDOB, getdate())/12.0
Perayu
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:F3FCEC3B-D90D-42C5-B42D-179206660285@.microsoft.com...
>I have to calculate the average age in a group.
> What is the best way to calculate the average age where I have the
> individual's birth date?
> Thanks in advance!|||wnfisba wrote:
> I have to calculate the average age in a group.
> What is the best way to calculate the average age where I have the
> individual's birth date?
> Thanks in advance!
More information please...
Post your table DDL and more detailed specs of what you're trying to
accomplish. SQL has an AVG() function to calculate averages based on a
set of data.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||As an alternative to getting the average age, you could get the average
birthdate. That could be more useful in some circumstances.
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:F3FCEC3B-D90D-42C5-B42D-179206660285@.microsoft.com...
>I have to calculate the average age in a group.
> What is the best way to calculate the average age where I have the
> individual's birth date?
> Thanks in advance!|||Just be sure it's birthdate, not birth(month/day). I can see the headline:
"Average American born in early July, study shows." ;)
Steve Kass
Drew University
Paul Pedersen wrote:

>As an alternative to getting the average age, you could get the average
>birthdate. That could be more useful in some circumstances.
>
>"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
>news:F3FCEC3B-D90D-42C5-B42D-179206660285@.microsoft.com...
>
>
>|||Ha ha! So I'm average after all.
"Steve Kass" <skass@.drew.edu> wrote in message
news:OcEysRNsFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Just be sure it's birthdate, not birth(month/day). I can see the
> headline:
> "Average American born in early July, study shows." ;)
> Steve Kass
> Drew University
> Paul Pedersen wrote:
>

after system restore the agent does not start

Hi group,
we have a server that had a server restore (near mirror, the system admin had to work out a few kinks). After the restore, the sql server agent does not start. When trying start it in service, it appears to be trying to start, but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The service did not return an error. This could be an internal error or an internal service error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: 17050, initerrlog: Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process cannot access the file because it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Quentin
The log mentioned, is the normal name for the SQL Server error log... It looks like SQL is trying to open it a second time, (as if you have 2 instances of SQL Server trying to write to the same log file)... THis can be changed in SEM startup properties...
However it might be possible that your SQL Agent is trying to open the log ( in SEM right click SQL Agent and go to properties to discover the log name it is using..)
All in all it looks to me that the error you are showing is not related to the SQL Agent not starting...Ensure the agent is using an NT login that is a SQL Server administrator.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's community of SQL Server professionals.
www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message news:OwDGnstQEHA.1960@.TK2MSFTNGP10.phx.gbl...
Hi group,
we have a server that had a server restore (near mirror, the system admin had to work out a few kinks). After the restore, the sql server agent does not start. When trying start it in service, it appears to be trying to start, but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The service did not return an error. This could be an internal error or an internal service error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: 17050, initerrlog: Could not open error log file 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process cannot access the file because it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Quentin
|||>>The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer.
sounds like the sqlagent thinks it's running on a different computer.
does this restored server have a different name than the original
server?
[vbcol=seagreen]
system error = 32(The process cannot access the file because it is being
used by another process.).
this, as the other poster pointed out, sounds like something is using
that log file. that log file is for sqlserver. you should specify a
different log file for the agent, generally should be something like
e:\mssql\log\sqlagent.out
Quentin Ran wrote:

> Hi group, we have a server that had a server restore (near mirror, the
> system admin had to work out a few kinks). After the restore, the sql
> server agent does not start. When trying start it in service, it
> appears to be trying to start, but at the end the MMC gives a
> message Could not start the SQLSERVERAGENT service on local computer.
> The service did not return an error. This could be an internal error
> or an internal service error. Window event log shows the following
> error: The description for Event ID ( 17055 ) in Source ( MSSQLSERVER
> ) cannot be found. The local computer may not have the necessary
> registry information or message DLL files to display messages from a
> remote computer. The following information is part of the event:
> 17050, initerrlog: Could not open error log file
> 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> cannot access the file because it is being used by another
> process.). Can someone offer the fix? Thanks. The following is the
> configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> 3) Quentin
|||Thanks for both responses.
The restored server has exactly the same name as before. SQL Server Agent
is running off a server admin account.
SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. The
server service log seems to work at least half way -- when the server
service starts, it records what it was doing like opening the databases. In
fact it even recorded a restore of a database. The fact that it did not
record any failure of launching the server agent may be due to it never was
asked to do so -- Windows may not know to direct such event there. The
agent log actually also contains some entries after the starting of the
server service, but not those ones that agent service start failed.
Any additional comments?
Quentin
"ch" <ch@.dontemailme.com> wrote in message
news:40B4980C.B82ABDFE@.dontemailme.com...
> message DLL files to display messages from a remote computer.
> sounds like the sqlagent thinks it's running on a different computer.
> does this restored server have a different name than the original
> server?
>
> system error = 32(The process cannot access the file because it is being
> used by another process.).
> this, as the other poster pointed out, sounds like something is using
> that log file. that log file is for sqlserver. you should specify a
> different log file for the agent, generally should be something like
> e:\mssql\log\sqlagent.out
>
>
> Quentin Ran wrote:
>
|||maybe there are some registry settings incorrect due to the restore of the
server.
try this.
create an nt account that is local admin on the server.
using enterprise manager, change the sqlserver and sqlagent services to use that
account.
restart server. see if agent starts properly.
now go back using enterprise manager and reset your sqlserver and sqlagent
accounts to whatever account(s) they were using before.
restart server. see if agent starts properly.
if that doesn't work, see if you can get them to run properly using the system
account for the two services.
be sure to use enterprise manager to make changes to the accounts the services
use.
Quentin Ran wrote:
[vbcol=seagreen]
> Thanks for both responses.
> The restored server has exactly the same name as before. SQL Server Agent
> is running off a server admin account.
> SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
> Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. The
> server service log seems to work at least half way -- when the server
> service starts, it records what it was doing like opening the databases. In
> fact it even recorded a restore of a database. The fact that it did not
> record any failure of launching the server agent may be due to it never was
> asked to do so -- Windows may not know to direct such event there. The
> agent log actually also contains some entries after the starting of the
> server service, but not those ones that agent service start failed.
> Any additional comments?
> Quentin
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4980C.B82ABDFE@.dontemailme.com...
|||Neither a local admin account nor the system account would start them.
There must be wrong registry settings, and we don't know which ones and the
correct values.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4BCFB.1AF8E97B@.dontemailme.com...
> maybe there are some registry settings incorrect due to the restore of the
> server.
> try this.
> create an nt account that is local admin on the server.
> using enterprise manager, change the sqlserver and sqlagent services to
use that
> account.
> restart server. see if agent starts properly.
> now go back using enterprise manager and reset your sqlserver and sqlagent
> accounts to whatever account(s) they were using before.
> restart server. see if agent starts properly.
> if that doesn't work, see if you can get them to run properly using the
system
> account for the two services.
> be sure to use enterprise manager to make changes to the accounts the
services[vbcol=seagreen]
> use.
>
> Quentin Ran wrote:
Agent[vbcol=seagreen]
SQL[vbcol=seagreen]
The[vbcol=seagreen]
In[vbcol=seagreen]
was[vbcol=seagreen]
or[vbcol=seagreen]
being[vbcol=seagreen]
the[vbcol=seagreen]
sql[vbcol=seagreen]
computer.[vbcol=seagreen]
error[vbcol=seagreen]
MSSQLSERVER
>
|||check this article
http://support.microsoft.com/default...b;en-us;283811
Quentin Ran wrote:
[vbcol=seagreen]
> Neither a local admin account nor the system account would start them.
> There must be wrong registry settings, and we don't know which ones and the
> correct values.
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4BCFB.1AF8E97B@.dontemailme.com...
> use that
> system
> services
> Agent
> SQL
> The
> In
> was
> or
> being
> the
> sql
> computer.
> error
> MSSQLSERVER
|||Thanks.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4D8F9.5C7C10D7@.dontemailme.com...[vbcol=seagreen]
> check this article
> http://support.microsoft.com/default...b;en-us;283811
>
> Quentin Ran wrote:
the[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
sqlagent[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
E:\MSSQL\LOG\SQLAGENT.OUT.[vbcol=seagreen]
server[vbcol=seagreen]
databases.[vbcol=seagreen]
not[vbcol=seagreen]
never[vbcol=seagreen]
The[vbcol=seagreen]
the[vbcol=seagreen]
information[vbcol=seagreen]
computer.[vbcol=seagreen]
using[vbcol=seagreen]
specify a[vbcol=seagreen]
like[vbcol=seagreen]
mirror,[vbcol=seagreen]
the[vbcol=seagreen]
it[vbcol=seagreen]
following[vbcol=seagreen]
from a[vbcol=seagreen]
the
>

after system restore the agent does not start

Hi group,
we have a server that had a server restore (near mirror, the system admin ha
d to work out a few kinks). After the restore, the sql server agent does no
t start. When trying start it in service, it appears to be trying to start,
but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The service d
id not return an error. This could be an internal error or an internal serv
ice error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be f
ound. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. The following
information is part of the event: 17050, initerrlog: Could not open error lo
g file 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process cann
ot access the file because it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
QuentinThe log mentioned, is the normal name for the SQL Server error log... It lo
oks like SQL is trying to open it a second time, (as if you have 2 instances
of SQL Server trying to write to the same log file)... THis can be changed
in SEM startup properties...
However it might be possible that your SQL Agent is trying to open the log (
in SEM right click SQL Agent and go to properties to discover the log name
it is using..)
All in all it looks to me that the error you are showing is not related to t
he SQL Agent not starting...Ensure the agent is using an NT login that is a
SQL Server administrator.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's communi
ty of SQL Server professionals.
www.sqlpass.org
"Quentin Ran" <ab@.who.com> wrote in message news:OwDGnstQEHA.1960@.TK2MSFTNGP
10.phx.gbl...
Hi group,
we have a server that had a server restore (near mirror, the system admin ha
d to work out a few kinks). After the restore, the sql server agent does no
t start. When trying start it in service, it appears to be trying to start,
but at the end the MMC gives a message
Could not start the SQLSERVERAGENT service on local computer. The service d
id not return an error. This could be an internal error or an internal serv
ice error.
Window event log shows the following error:
The description for Event ID ( 17055 ) in Source ( MSSQLSERVER ) cannot be f
ound. The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer. The following
information is part of the event: 17050, initerrlog: Could not open error lo
g file 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process cann
ot access the file because it is being used by another process.).
Can someone offer the fix? Thanks.
The following is the configuration:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 3)
Quentin|||>>The local computer may not have the necessary registry information or
message DLL files to display messages from a remote computer.
sounds like the sqlagent thinks it's running on a different computer.
does this restored server have a different name than the original
server?

system error = 32(The process cannot access the file because it is being
used by another process.).
this, as the other poster pointed out, sounds like something is using
that log file. that log file is for sqlserver. you should specify a
different log file for the agent, generally should be something like
e:\mssql\log\sqlagent.out
Quentin Ran wrote:
[vbcol=seagreen]
> Hi group, we have a server that had a server restore (near mirror, the
> system admin had to work out a few kinks). After the restore, the sql
> server agent does not start. When trying start it in service, it
> appears to be trying to start, but at the end the MMC gives a
> message Could not start the SQLSERVERAGENT service on local computer.
> The service did not return an error. This could be an internal error
> or an internal service error. Window event log shows the following
> error: The description for Event ID ( 17055 ) in Source ( MSSQLSERVER
> ) cannot be found. The local computer may not have the necessary
> registry information or message DLL files to display messages from a
> remote computer. The following information is part of the event:
> 17050, initerrlog: Could not open error log file
> 'e:\MSSQL\log\ERRORLOG'. Operating system error = 32(The process
> cannot access the file because it is being used by another
> process.). Can someone offer the fix? Thanks. The following is the
> configuration: Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack
> 3) Quentin|||Thanks for both responses.
The restored server has exactly the same name as before. SQL Server Agent
is running off a server admin account.
SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. The
server service log seems to work at least half way -- when the server
service starts, it records what it was doing like opening the databases. In
fact it even recorded a restore of a database. The fact that it did not
record any failure of launching the server agent may be due to it never was
asked to do so -- Windows may not know to direct such event there. The
agent log actually also contains some entries after the starting of the
server service, but not those ones that agent service start failed.
Any additional comments?
Quentin
"ch" <ch@.dontemailme.com> wrote in message
news:40B4980C.B82ABDFE@.dontemailme.com...
> message DLL files to display messages from a remote computer.
> sounds like the sqlagent thinks it's running on a different computer.
> does this restored server have a different name than the original
> server?
>
> system error = 32(The process cannot access the file because it is being
> used by another process.).
> this, as the other poster pointed out, sounds like something is using
> that log file. that log file is for sqlserver. you should specify a
> different log file for the agent, generally should be something like
> e:\mssql\log\sqlagent.out
>
>
> Quentin Ran wrote:
>
>|||maybe there are some registry settings incorrect due to the restore of the
server.
try this.
create an nt account that is local admin on the server.
using enterprise manager, change the sqlserver and sqlagent services to use
that
account.
restart server. see if agent starts properly.
now go back using enterprise manager and reset your sqlserver and sqlagent
accounts to whatever account(s) they were using before.
restart server. see if agent starts properly.
if that doesn't work, see if you can get them to run properly using the syst
em
account for the two services.
be sure to use enterprise manager to make changes to the accounts the servic
es
use.
Quentin Ran wrote:
[vbcol=seagreen]
> Thanks for both responses.
> The restored server has exactly the same name as before. SQL Server Agent
> is running off a server admin account.
> SQL Server Service uses the error log file e:\MSSQL\log\ERRORLOG, and SQL
> Server Agent Service uses the error log file E:\MSSQL\LOG\SQLAGENT.OUT. T
he
> server service log seems to work at least half way -- when the server
> service starts, it records what it was doing like opening the databases.
In
> fact it even recorded a restore of a database. The fact that it did not
> record any failure of launching the server agent may be due to it never wa
s
> asked to do so -- Windows may not know to direct such event there. The
> agent log actually also contains some entries after the starting of the
> server service, but not those ones that agent service start failed.
> Any additional comments?
> Quentin
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4980C.B82ABDFE@.dontemailme.com...|||Neither a local admin account nor the system account would start them.
There must be wrong registry settings, and we don't know which ones and the
correct values.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4BCFB.1AF8E97B@.dontemailme.com...
> maybe there are some registry settings incorrect due to the restore of the
> server.
> try this.
> create an nt account that is local admin on the server.
> using enterprise manager, change the sqlserver and sqlagent services to
use that
> account.
> restart server. see if agent starts properly.
> now go back using enterprise manager and reset your sqlserver and sqlagent
> accounts to whatever account(s) they were using before.
> restart server. see if agent starts properly.
> if that doesn't work, see if you can get them to run properly using the
system
> account for the two services.
> be sure to use enterprise manager to make changes to the accounts the
services
> use.
>
> Quentin Ran wrote:
>
Agent[vbcol=seagreen]
SQL[vbcol=seagreen]
The[vbcol=seagreen]
In[vbcol=seagreen]
was[vbcol=seagreen]
or[vbcol=seagreen]
being[vbcol=seagreen]
the[vbcol=seagreen]
sql[vbcol=seagreen]
computer.[vbcol=seagreen]
error[vbcol=seagreen]
MSSQLSERVER[vbcol=seagreen]
>|||check this article
http://support.microsoft.com/defaul...kb;en-us;283811
Quentin Ran wrote:
[vbcol=seagreen]
> Neither a local admin account nor the system account would start them.
> There must be wrong registry settings, and we don't know which ones and th
e
> correct values.
> "ch" <ch@.dontemailme.com> wrote in message
> news:40B4BCFB.1AF8E97B@.dontemailme.com...
> use that
> system
> services
> Agent
> SQL
> The
> In
> was
> or
> being
> the
> sql
> computer.
> error
> MSSQLSERVER|||Thanks.
"ch" <ch@.dontemailme.com> wrote in message
news:40B4D8F9.5C7C10D7@.dontemailme.com...
> check this article
> http://support.microsoft.com/defaul...kb;en-us;283811
>
> Quentin Ran wrote:
>
the[vbcol=seagreen]
the[vbcol=seagreen]
to[vbcol=seagreen]
sqlagent[vbcol=seagreen]
the[vbcol=seagreen]
and[vbcol=seagreen]
E:\MSSQL\LOG\SQLAGENT.OUT.[vbcol=seagreen]
server[vbcol=seagreen]
databases.[vbcol=seagreen]
not[vbcol=seagreen]
never[vbcol=seagreen]
The[vbcol=seagreen]
the[vbcol=seagreen]
information[vbcol=seagreen]
computer.[vbcol=seagreen]
using[vbcol=seagreen]
specify a[vbcol=seagreen]
like[vbcol=seagreen]
mirror,[vbcol=seagreen]
the[vbcol=seagreen]
it[vbcol=seagreen]
following[vbcol=seagreen]
from a[vbcol=seagreen]
the[vbcol=seagreen]
>