SQL tool for MSDE: query

Showing posts with label query. Show all posts

Tuesday, March 27, 2012

Aliasing columns for a DMX subquery

I require the column of a nested table (KOL s) as part of the output of my DMX query, which needs to be written out to a relational table. Hence, I flatten the <select_list> of the SELECT DMX query as below:

SELECT FLATTENED

([Speciality].[SPECIALITY ID]) as [Speciality_Id],

(0) as [Bool_NameInAuthors],

(0) as [Bool_EmailInAbstract],

(0) as [Bool_AffiliationInAbstract],

(SELECT ([KOL ID]) as [Id], ([FIRST NAME]) as [FirstName], ([MIDDLE NAME]) as [MiddleName], ([LAST NAME]) as [LastName], ([AFFILIATION]) as [Affiliation], ([EMAILADDRESS]) as [EmailAddress] FROM [Speciality].[KO Ls]),

(SELECT ([Speciality Term DESCRIPTION]) as [Term] FROM [Speciality].[SPECIALITYTERMS]) AS Spec

From

[Speciality]

PREDICTION JOIN

OPENQUERY([ETL Profiler DB],

'SELECT

[SPECIALITY_ID]

FROM

[dbo].[KOLs]

') AS t

[Speciality].[SPECIALITY ID] = t.[SPECIALITY_ID]

However, this causes the subquery columns (ID, FirstName, ...) to be aliased as Expression.ID, Expression.FirstName...

How do I alias these flattened columns properly?

I tried to alias the subquery to a derived table (as follows), but it just replaces the Expression word by the derived table alias (KOL in this case). So, does not solve my problem.

You can enclose the entire query in another SELECT where you alias the nested table columns -

SELECT [KOL.Id] as KOL_Id, [KOL.FirstName] as KOL_FirstName, ....

FROM

(SELECT FLATTENED ....) AS TT

sql

Aliases & Columns Name w/ Spaces

Formatting question. The query below is failing on the columns with spaces in the name. I've tried brackets and single quotes with no luck. How should this be formatted?

SELECT x.trkuniq, s.mstuniq, t.meetuniq,
c.coursec AS Course_Code,
c.descript AS Course_Name,
[q.cactus #] AS Cactus_#
s.sectionn AS Section,
RTRIM(f.lastname) + ', ' + RTRIM(f.firstname) AS Teacher, f.funiq,
t.termc AS Term_Code, zd.cycldayc AS Day,
zp.periodc AS Period, zp.periodn,
p.schoolc AS School
FROM mstmeet t INNER JOIN
mstsched s ON t.mstuniq = s.mstuniq INNER JOIN
trkcrs x ON s.trkcrsuniq = x.trkcrsuniq INNER JOIN
course c ON x.crsuniq = c.crsuniq INNER JOIN
track p ON p.trkuniq = x.trkuniq INNER JOIN
facdemo f ON s.funiq = f.funiq INNER JOIN
courses q ON c.coursec = [q.course number] INNER JOIN
trkper zp ON t.periodn = zp.periodn AND
x.trkuniq = zp.trkuniq INNER JOIN
trkcycle zd ON t.cycldayn = zd.cycldayn AND
x.trkuniq = zd.trkuniq

Kinny:

Try changing your reference from [q.cactus #] to q.[cactus #]

Dave

|||

The brackets are use to encapsulate a name of a table, schema, etc, not the entire messed up name :)

You can even include dots in the name:

create table [dbo].[bad.idea]
(
[bad.idea.id] int
)

select *
from [bad.idea]

And yes, very very bad idea.

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].&[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]

Alias on Update query

How can I put an alias on the table in an Update query
Update T64PE as Person
Where Person.ID = 5
(PS This works with Sybase)OK
Found this !

Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||Originally posted by Karolyn
OK
Found this !

Update T64PE
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5

Even more :

Update Person
Set NAME='CHIRAC'
From T64PE as Person
Where Person.ID = 5|||noted !
(thks)sql

Alias of a Linked Server

I am using linked server with an IP address then want to user this server in query but using IP address gives error and I want to define an Alias for that linked server to use in query, Please help me.

Thanks in advance

Muhammad Hanif

Have you tried with the hostname?|||

You can create an alias using the Client Network Utility (on SQL 2000) or Configuration Manager (on SQL 2005). You can create whatever alias name and enter the IP address for the server.

-Sue

Alias in Query

For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchoo
lName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony RobinsonHi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchool
Name
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes matter
s worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:u%23cQP0
f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||...it's comparing it against a USERID with a value of 1:
AND WHERE U.UserID = CONVERT(varchar(15), 1)
think of it as
AND WHERE U.UserID = CONVERT(varchar(15), @.USERID)
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eEBDbNg7
FHA.3804@.TK2MSFTNGP14.phx.gbl...
You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinsonsql

Monday, March 19, 2012

agrument tuncated in execution plan .. how do I get the whole argu

Hello,
I tried analysing a query using the execution plan provided by the query
analyser. But when I move the mouse pointer onto some queryitems it seems
that the argument used by the query part is tuncated. Even using SET
SHOWPLAN_TEXT or SET SHOWPLAN_ALL doesn't help to view the whole argument.
How can I do that ?
Thanks
Lars
In QA by default a column can show text up to 255 characters. I think you
need to increase the this to a higher number.
Go to tools->Options->Results->"Maximum Characters Per row" change the
value to a higher no.
Thanks
Vikas

Aggregete() function doesn't work for SQL query results! - Part 2

I am writing my own MDX query inside Reporting Services, bypassing the graphic MDX generator.

Both MDX query and SQL query return identical results and both have detail level values and high level values.

During the flattenning process, null value from MDX is still null, but null value from SQL is translated into blank.

Thus only the MDX result works for Aggregate() function, not the SQL result. Because Aggregate() function is triggered by null value, not blank.

Someone suggested to use MDX stored procedure to do custom calculations. Thus my report is calling a MDX query, not a SQL query. I have tried this approach already, but MDX is not as flexible as SQL. For example, in SQL, I can easily join, pivot, order by, group by, filter...None of these is easy in MDX.

If I know how to make reporting services treat null as null or to make Aggregate() function be triggered by a blank value, then my problem is solved.

Does Aggregate() funciton only works with MDX, not SQL?

Thanks,

Bo Dong

bo_dong@.yahoo.com

The Aggregate() function only works in combination with a data extension that implements the IDataReaderExtension interface (see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_clr_dataproc_9lit.asp).

The "Analysis Services" data extension available in RS 2005 implements this interface and internally detects aggregation rows based on nulls in hierarchies and exposes that information through the IDataReaderExtension interface.

Note: The "SQL Server" data extension available in RS 2005 does not implement this interface.

You can implement your own custom data extension (http://msdn2.microsoft.com/en-us/library/ms154655.aspx) and then implement the IDataReaderExtension interface - based on that you have full control in your custom data extension of how exactly aggregate rows from your relational query are marked.

-- Robert

Aggregete() function doesn't work for SQL query results!

Reporting Services translates null value into blank on SQL query results, but not on MDX query results. But Aggregate() functions is triggered only by null value.

So Aggregate() function only works for MDX query results, not for SQL query results.

MDX example:

select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube

SQL example:

SELECT * FROM OPENQUERY(Linked_Cube, '
select {[Measures].[Sales]} on columns,

{[Account].[Hierarchy].Members} on rows

FROM Cube')

Now you build a report with a table, then add a grouping and use "=Aggregate(Fields!Sales.Value)" for the group level cell. If you bind MDX query to this table, then aggregates show up correctly. But if you bind SQL query to this table, there are no aggregates at all.

I need to use SQL query to drive my reports, because MDX query results need to be merged with results from other calculations.

How can I make Aggregate() function to work for SQL query results?

Thanks,

Bo Dong

bo_dong@.yahoo.com

How can I make it to work for SQL as well?

Please read my answer on your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=668524&SiteID=1

-- Robert

Sunday, March 11, 2012

Aggregation of Calculated members of form: MEASURE op ATTRIBUTE VALUE

hi there. i do not understand how to use an attribute in a calculation. In the query below, I'm trying to say:

for all members of the date.calendar hierarchy, show me the sum of (sales amount fact * dealer price attribute for the product sold)

with

member [measures].[fact times attribute]

as cdbl([Measures].[Sales Amount]) * cdbl([Product].[Dealer Price].CurrentMember.memberValue)

select [measures].[fact times attribute] oncolumns,

[Date].[Calendar].allmembersonrows

from [Adventure Works]

where [Geography].[City].&[Beaverton]&[OR]

this gives a type mismatch error complaining that "All Products" cannot be cast to double. But I don't understand how to force the calculation to occur at the lowest level and for aggregation to be done subsequently.

Product is currently at the "All Product" member. What you are actually wanting to do is get the price for each product and multiply that by the sales of that product and then aggregate (i'll assume sum) those values. That query looks like the one below. (Actually, the one below is kinduva shortcut to the solution. Instead of going to the individual product, I just went to the product price. If two products have the same price, the query kinda says that we treat them the same. Nit-picky, I know.)

Good luck.

Code Snippet

withmember [measures].[fact times attribute] as

SUM([Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount])

select [measures].[fact times attribute] oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||Dear Bryan,

Thanks for your pointers. I actually work in hong kong so were ~12 hours ahead of you. I'm checking this from home and so can't test it. but i'm reviewing it now, because I'd love to get this right for work tomorrow.

Looking at your solution, one thing I find very conceptually troubling:

>>I don't see a multiplication sign anywhere!!!!<<

So at the very point where I would imagine attribute and fact really connect, the code is silent! Also when you refer to a shortcut and being nitpicky, that is all just going straight over my head - my knowledge of mdx (rather like my knowledge of cantonese just isn't evolved enough to understand the subtleties here. I can just about order lunch with a lot of gesticulation but that's it.

i will of course kick the tires on this tomorrow when I get into work. But [embarrassingly] I've been trying to figure out what the missing link is here for almost 3 days without success! Would you mind just leaving a working "best practice" solution for me? I feel like I sound like a lazy person - I'm not, I just don't grock this yet and its driving me nuts.

Yours,

John G.
|||

Sorry about that. I was pushing to get the code assembled and totally dropped that one critical part of the code. Try this one. I use the CurrentMember of the Dealer Price to get to the value.

Code Snippet

withmember [measures].[fact times attribute] as

SUM(

[Product].[Dealer Price].[Dealer Price].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select [measures].[fact times attribute] on columns,

NON EMPTY [Date].[Calendar].allmembers on rows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

;

|||

Jo,

Try to use named calculations in the datasourceview of your factTable... it would be a better performance, because you only run the formulas once when the cube is processing.

Regards!

|||

ok it works fine - THANKS!

I also tried this which returns the same results - is it logically identical?

with

member [measures].[fact times attribute]

asSUM([Product].[Product].[Product].Members,

[Measures].[Sales Amount]*[Product].[Dealer Price].CurrentMember.MemberValue

)

select {[measures].[fact times attribute]} oncolumns,

NONEMPTY [Date].[Calendar].allmembersonrows

from [Adventure Works]

where ([Geography].[City].&[Beaverton]&[OR])

i'm not sure if you'd have the patience of a saint to wade through what follows, but I'm trying to understand the execution of the query. Would you mind critiquing the below, or if it's easier for you just describe the above query in pseudo code for dummies

For my general understanding when you refer to [Product].[Dealer Price].CurrentMember.MemberValue,

what is the context of the CurrentMember? Is it:

1. The current member in the context of traversing the set of members of the product hierarchy as defined by the set argument passed to the SUM function.

or 2. the currentmember in some wider context of the query (actually I'm not sure if that makes sense, so I'll go with answer 1).

Assuming 1. above, then I would interpret the query as follows:

1. slice by beaverton

2. iterate through all members of the date hierarchy.

3. for each sales fact within the intersection of beaverton and the current date, calculate [fact times attribute] as follows:

a) for each member of the product hierarchy ...

i) ... extract the tuple set defined by the intersection of that product within the current region [for many product members this will be a null set]

ii) for that set of tuples, sum up (sales amount * dealer price).

iii) keep running total of that sum, and the final total will be your result of the current date member.

|||

thanks - this did occur to me, since as i understand it calculated members (even cube scoped ones) are calculated at runtime not at processing time, correct?

however, in my real calculation I'm using another calculated member which doesn't exist in the underlying table so this makes it tricky.

Would you say that in general, one should strive to do as much data cleaning / preparation / precalculation as possible outside the cube, and then just leave the cube to prepare aggregations and pure analysis calculations that are hard to do outside of mdx?

|||Yeah joGo, I'm with you! If you have other calculated members that you cannot replace for named calculation, so you are right...|||

Your code is logically similar and should produce the same result. You may want to test for performance differences, but the only way I can imagine a performance difference would exist would be under a very particular situation I suspect does not exist in the cube. (In other words, they probably have the same performance so don't sweat it.)

Regarding the CURRENTMEMBER question, you have to keep in mind context at all times. In the SUM function, you generate a set. That set definition is in the context of the cube as a whole. If I want to limit that context based on my slicer (WHERE clause), I can use the EXISTING keyword in the set definition.

So, now I have a set. Then, for each member in that set, I will return and/or calculate a value. That value is determined in the context of the member from the set I am currently working with. At this point, how that set came to be is unknown to me. The set has been determined and I'm just working through it blindly. I think this is what you're saying in the section at the bottom of your email.

Sorry I am being slow to respond today. The MSDN forum email seems to be jammed up a bit and I'm not getting alerts like I should.

Thanks,
Bryan

Aggregation Issue

I have a cube that I designed aggregation with 12% performance in MOLAP storage mode. However, when I ran query it read from partition not from aggregation.

How can I change so that the query read from aggregation?

Thanks in advance,
A. Imamuddin

Hi Ashari

1. Ensure that you have designed hierarchies on your dimensions even though they seem to be unnecessary. I found that aggregations are created when these exist

2. I find that creating aggregations manually by editing the XMLA for the measure group works better. You do this by scripting the measure group in the SQL Server Man Studio and add/edit your aggregations.

Let me know if this helps?

Thanks

John

|||Hi John,

Thanks for your replay. I would like to inform you, I do not applied point 1 because I have already had hierarchies. I have applied point 2, but after processing the partition, the query still read from partition. FYI, I also design aggregation using Usage Based Optimization Wizard.

Thanks,
A. Imamuddin|||

It's likely that, even after usage-based optimisation, you still haven't build any aggregations useful for your query. Rather than change your query, to make sure you're building the right aggregations take a look at:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

HTH,

Chris

Aggregation Issue

I have a cube that I designed aggregation with 12% performance in MOLAP storage mode. However, when I ran query it read from partition not from aggregation.

How can I change so that the query read from aggregation?

Thanks in advance,
A. Imamuddin

Hi Ashari

1. Ensure that you have designed hierarchies on your dimensions even though they seem to be unnecessary. I found that aggregations are created when these exist

Let me know if this helps?

Thanks

John

|||Hi John,

Thanks,
A. Imamuddin|||

HTH,

Chris

Aggregation functon

Hi,
I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
And now I need
SELECT * FROM Table WHERE
row is equal to row which was used to calculate output of result from
SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
It suffices me this
SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
but it is not legal.
Thank for your suggestionsI think the easiest way to do this would be :
SELECT TOP 1 SQRT(X1*X2+Y1*Y2), *
FROM Table
ORDER BY SQRT(X1*X2+Y1*Y2) DESC
This will return you the first line only where SQRT() is the biggest. Mind
that if you want to have ALL lines where SQRT reaches it's maximum, then
you'll want this :
SELECT SQRT(X1*X2+Y1*Y2), *
FROM Table
WHERE SQRT(X1*X2+Y1*Y2) = (SELECT MAX(SQRT(X1*X2+Y1*Y2))
FROM Table)
Good luck
Roby
"B.J." wrote:

> Hi,
> I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> And now I need
> SELECT * FROM Table WHERE
> row is equal to row which was used to calculate output of result from
> SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> It suffices me this
> SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> but it is not legal.
> Thank for your suggestions|||something like this:
select *
from table
where pk = (select top 1 pk from table order by SQRT(X1*X2+Y1*Y2) desc)
where 'pk' is the table's primary key.
dean
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:67E9D409-A13A-4982-A7EA-74A946A42542@.microsoft.com...
> Hi,
> I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> And now I need
> SELECT * FROM Table WHERE
> row is equal to row which was used to calculate output of result from
> SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> It suffices me this
> SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> but it is not legal.
> Thank for your suggestions|||SELECT x1, x2, y1, y2, SQRT(x1*x2 + y1*y2)
FROM YourTable
WHERE x1*x2 + y1*y2 =
(SELECT MAX(x1*x2 + y1*y2)
FROM YourTable)
The SQRT() function is redundant in the subquery so I've left it out
here to save a few cycles.
David Portas
SQL Server MVP
--|||Another way to get all rows with the maximum X1*X2+Y1*Y2 is
SELECT TOP 1 WITH TIES SQRT(X1*X2+Y1*Y2), *
FROM T
ORDER BY SQRT(X1*X2+Y1*Y2) DESC
Steve Kass
Drew University
deroby wrote:
>I think the easiest way to do this would be :
>SELECT TOP 1 SQRT(X1*X2+Y1*Y2), *
> FROM Table
>ORDER BY SQRT(X1*X2+Y1*Y2) DESC
>This will return you the first line only where SQRT() is the biggest. Mind
>that if you want to have ALL lines where SQRT reaches it's maximum, then
>you'll want this :
>SELECT SQRT(X1*X2+Y1*Y2), *
> FROM Table
> WHERE SQRT(X1*X2+Y1*Y2) = (SELECT MAX(SQRT(X1*X2+Y1*Y2))
> FROM Table)
>Good luck
>Roby
>
>"B.J." wrote:
>
>

Aggregates and subqueries

In the pubs database I need to find all the books whose total sales (qty)
exceed the average total sales
I came up with this query...
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is there a better way to write this?
Hi David
Why are you JOINING with the titles table? There is nothing in that table
you are using. Sales has a title_id. You would only have to JOIN to titles
if you wanted the book title.
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David F" <davef@.nksj.ru> wrote in message
news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
> In the pubs database I need to find all the books whose total sales (qty)
> exceed the average total sales
> I came up with this query...
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> Is there a better way to write this?
>
|||Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>
|||Not quite. Check your GROUP BY:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David F" <davef@.nksj.ru> wrote in message news:OzHtRagGEHA.3816@.TK2MSFTNGP12.phx.gbl...
Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>

Aggregates and subqueries

In the pubs database I need to find all the books whose total sales (qty)
exceed the average total sales
I came up with this query...
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is there a better way to write this?Hi David
Why are you JOINING with the titles table? There is nothing in that table
you are using. Sales has a title_id. You would only have to JOIN to titles
if you wanted the book title.
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David F" <davef@.nksj.ru> wrote in message
news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
> In the pubs database I need to find all the books whose total sales (qty)
> exceed the average total sales
> I came up with this query...
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> Is there a better way to write this?
>|||Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>|||Not quite. Check your GROUP BY:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David F" <davef@.nksj.ru> wrote in message news:OzHtRagGEHA.3816@.TK2MSFTNGP1
2.phx.gbl...
Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>

Thursday, March 8, 2012

Aggregate values based on the value of another field

I'm not having much fun trying to aggregate values based on the value of
another field
My query is returning the following...
DATE, VOLUME, PRODUCT
1/1/07, 22, OIL
1/1/07, 0, WATER
1/2/07, 8, OIL
1/2/07, 12, WATER
I want to sum all the VOLUME values where the product = 'OIL' and another to
sum where the product = 'GAS'. Is this type of conditional aggregation
possible. If so how?Correction: I want I want to sum all the VOLUME values where the product ='OIL' and another to sum where the product = 'WATER'.
"smithcjb" wrote:
> I'm not having much fun trying to aggregate values based on the value of
> another field
> My query is returning the following...
> DATE, VOLUME, PRODUCT
> 1/1/07, 22, OIL
> 1/1/07, 0, WATER
> 1/2/07, 8, OIL
> 1/2/07, 12, WATER
> I want to sum all the VOLUME values where the product = 'OIL' and another to
> sum where the product = 'GAS'. Is this type of conditional aggregation
> possible. If so how?|||Hey Smithcjb,
If you are trying to do this in your SQL statement, then drop the "date"
(you can leave it in if your are filtering by it, just make sure you define
it as part of the WHERE clause only). Write it like this
SELECT PRODUCT, SUM(VOLUME)
FROM MyTable
WHERE Date Between Date1 and Date2
GROUP BY PRODUCT
If your trying to do this in a table cell or matrix you could use a
conditinal that says
=SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
then again for water
=SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
and so on...
Michael C
"smithcjb" wrote:
> I'm not having much fun trying to aggregate values based on the value of
> another field
> My query is returning the following...
> DATE, VOLUME, PRODUCT
> 1/1/07, 22, OIL
> 1/1/07, 0, WATER
> 1/2/07, 8, OIL
> 1/2/07, 12, WATER
> I want to sum all the VOLUME values where the product = 'OIL' and another to
> sum where the product = 'GAS'. Is this type of conditional aggregation
> possible. If so how?|||For performance reasons I wanted to do this in the report and not in the SQL.
I've tried grouping by the following in the report with no success...
=SUM(IIf(Fields!PRODUCT.Value="OIL",Fields!LIQUID_VOL.Value,0))
"Michael C" wrote:
> Hey Smithcjb,
> If you are trying to do this in your SQL statement, then drop the "date"
> (you can leave it in if your are filtering by it, just make sure you define
> it as part of the WHERE clause only). Write it like this
> SELECT PRODUCT, SUM(VOLUME)
> FROM MyTable
> WHERE Date Between Date1 and Date2
> GROUP BY PRODUCT
> If your trying to do this in a table cell or matrix you could use a
> conditinal that says
> =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> then again for water
> =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> and so on...
> Michael C
> "smithcjb" wrote:
> > I'm not having much fun trying to aggregate values based on the value of
> > another field
> >
> > My query is returning the following...
> > DATE, VOLUME, PRODUCT
> >
> > 1/1/07, 22, OIL
> > 1/1/07, 0, WATER
> > 1/2/07, 8, OIL
> > 1/2/07, 12, WATER
> >
> > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > sum where the product = 'GAS'. Is this type of conditional aggregation
> > possible. If so how?|||Just to reiterate. I want to perform this in the report, not the query. I
tried the conditional expressions you provided but the report just returns
"Error" in the field
"Michael C" wrote:
> Hey Smithcjb,
> If you are trying to do this in your SQL statement, then drop the "date"
> (you can leave it in if your are filtering by it, just make sure you define
> it as part of the WHERE clause only). Write it like this
> SELECT PRODUCT, SUM(VOLUME)
> FROM MyTable
> WHERE Date Between Date1 and Date2
> GROUP BY PRODUCT
> If your trying to do this in a table cell or matrix you could use a
> conditinal that says
> =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> then again for water
> =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> and so on...
> Michael C
> "smithcjb" wrote:
> > I'm not having much fun trying to aggregate values based on the value of
> > another field
> >
> > My query is returning the following...
> > DATE, VOLUME, PRODUCT
> >
> > 1/1/07, 22, OIL
> > 1/1/07, 0, WATER
> > 1/2/07, 8, OIL
> > 1/2/07, 12, WATER
> >
> > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > sum where the product = 'GAS'. Is this type of conditional aggregation
> > possible. If so how?|||Is this aggregate happening in a table? Is it happening in a Group footer?
What is the grouping? What error are you getting? By all accounts this
should work.
Michael C.
"smithcjb" wrote:
> For performance reasons I wanted to do this in the report and not in the SQL.
> I've tried grouping by the following in the report with no success...
> =SUM(IIf(Fields!PRODUCT.Value="OIL",Fields!LIQUID_VOL.Value,0))
> "Michael C" wrote:
> > Hey Smithcjb,
> > If you are trying to do this in your SQL statement, then drop the "date"
> > (you can leave it in if your are filtering by it, just make sure you define
> > it as part of the WHERE clause only). Write it like this
> >
> > SELECT PRODUCT, SUM(VOLUME)
> > FROM MyTable
> > WHERE Date Between Date1 and Date2
> > GROUP BY PRODUCT
> >
> > If your trying to do this in a table cell or matrix you could use a
> > conditinal that says
> >
> > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > then again for water
> > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> >
> > and so on...
> >
> > Michael C
> >
> > "smithcjb" wrote:
> >
> > > I'm not having much fun trying to aggregate values based on the value of
> > > another field
> > >
> > > My query is returning the following...
> > > DATE, VOLUME, PRODUCT
> > >
> > > 1/1/07, 22, OIL
> > > 1/1/07, 0, WATER
> > > 1/2/07, 8, OIL
> > > 1/2/07, 12, WATER
> > >
> > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > possible. If so how?|||Tables (also called matrix) exist INSIDE reports and have nothing to do with
Datasets. I fully understand what your saying, so no need to "reiterate"
anything.
Also, the comment "for performance reasons i want to do this in the report".
Are you saying you want the report to run slower? Performance wise , as far
as I've read, would suggest you do this in the SQL. But hey...its up to you.
Michael C.
"smithcjb" wrote:
> Just to reiterate. I want to perform this in the report, not the query. I
> tried the conditional expressions you provided but the report just returns
> "Error" in the field
> "Michael C" wrote:
> > Hey Smithcjb,
> > If you are trying to do this in your SQL statement, then drop the "date"
> > (you can leave it in if your are filtering by it, just make sure you define
> > it as part of the WHERE clause only). Write it like this
> >
> > SELECT PRODUCT, SUM(VOLUME)
> > FROM MyTable
> > WHERE Date Between Date1 and Date2
> > GROUP BY PRODUCT
> >
> > If your trying to do this in a table cell or matrix you could use a
> > conditinal that says
> >
> > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > then again for water
> > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> >
> > and so on...
> >
> > Michael C
> >
> > "smithcjb" wrote:
> >
> > > I'm not having much fun trying to aggregate values based on the value of
> > > another field
> > >
> > > My query is returning the following...
> > > DATE, VOLUME, PRODUCT
> > >
> > > 1/1/07, 22, OIL
> > > 1/1/07, 0, WATER
> > > 1/2/07, 8, OIL
> > > 1/2/07, 12, WATER
> > >
> > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > possible. If so how?|||Michael,
I appreciate your comments. This is my full query. I'm returning 3 rows per
date (1 per product). I've tried to use inner joins on the view I'm pulling
from but performance is woeful for large date ranges. I'm trying to sum the
production for the specified date range for each product - easy for GAS since
it appears in it's own column! Any further help greatly appreciated. I don't
know why the expressions you provided won;t workm - they seem logical to me!
Kind regards,
Colin
SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
INNER JOIN
dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
'1/1/04' and '1/1/07') AND (c.ITEM_ID IN
(SELECT COMPLETION_ITEM_ID
FROM dbo.REP_ORG_COMPLETION
WHERE (FIELD_ITEM_ID = @.FieldItemId)))
GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
p.LIQUID_VOL,p.PRODUCT
"Michael C" wrote:
>
> Tables (also called matrix) exist INSIDE reports and have nothing to do with
> Datasets. I fully understand what your saying, so no need to "reiterate"
> anything.
>
> Also, the comment "for performance reasons i want to do this in the report".
> Are you saying you want the report to run slower? Performance wise , as far
> as I've read, would suggest you do this in the SQL. But hey...its up to you.
> Michael C.
> "smithcjb" wrote:
> > Just to reiterate. I want to perform this in the report, not the query. I
> > tried the conditional expressions you provided but the report just returns
> > "Error" in the field
> >
> > "Michael C" wrote:
> >
> > > Hey Smithcjb,
> > > If you are trying to do this in your SQL statement, then drop the "date"
> > > (you can leave it in if your are filtering by it, just make sure you define
> > > it as part of the WHERE clause only). Write it like this
> > >
> > > SELECT PRODUCT, SUM(VOLUME)
> > > FROM MyTable
> > > WHERE Date Between Date1 and Date2
> > > GROUP BY PRODUCT
> > >
> > > If your trying to do this in a table cell or matrix you could use a
> > > conditinal that says
> > >
> > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > then again for water
> > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > >
> > > and so on...
> > >
> > > Michael C
> > >
> > > "smithcjb" wrote:
> > >
> > > > I'm not having much fun trying to aggregate values based on the value of
> > > > another field
> > > >
> > > > My query is returning the following...
> > > > DATE, VOLUME, PRODUCT
> > > >
> > > > 1/1/07, 22, OIL
> > > > 1/1/07, 0, WATER
> > > > 1/2/07, 8, OIL
> > > > 1/2/07, 12, WATER
> > > >
> > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > possible. If so how?|||Okay, so now I do understand your want to do this in report. I'm a little
stumped at why the IIF won't work (Unless the report crosses pages?).
I would suggest trying a CASE statement in your SQL to give both OIL and
WATER their own column at least to benchmark what the added overhead is.
CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
If it is minimal overhead to do this then voila, you now have columns for
both oil and water (unless of course other products like NGL's and
Condensates are being included in your products list in which case you'll
need to expand the case statements).
Michael C.
"smithcjb" wrote:
> Michael,
> I appreciate your comments. This is my full query. I'm returning 3 rows per
> date (1 per product). I've tried to use inner joins on the view I'm pulling
> from but performance is woeful for large date ranges. I'm trying to sum the
> production for the specified date range for each product - easy for GAS since
> it appears in it's own column! Any further help greatly appreciated. I don't
> know why the expressions you provided won;t workm - they seem logical to me!
> Kind regards,
> Colin
> SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> INNER JOIN
> dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> (SELECT COMPLETION_ITEM_ID
> FROM dbo.REP_ORG_COMPLETION
> WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> p.LIQUID_VOL,p.PRODUCT
> "Michael C" wrote:
> >
> >
> > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > Datasets. I fully understand what your saying, so no need to "reiterate"
> > anything.
> >
> >
> > Also, the comment "for performance reasons i want to do this in the report".
> > Are you saying you want the report to run slower? Performance wise , as far
> > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> >
> > Michael C.
> >
> > "smithcjb" wrote:
> >
> > > Just to reiterate. I want to perform this in the report, not the query. I
> > > tried the conditional expressions you provided but the report just returns
> > > "Error" in the field
> > >
> > > "Michael C" wrote:
> > >
> > > > Hey Smithcjb,
> > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > it as part of the WHERE clause only). Write it like this
> > > >
> > > > SELECT PRODUCT, SUM(VOLUME)
> > > > FROM MyTable
> > > > WHERE Date Between Date1 and Date2
> > > > GROUP BY PRODUCT
> > > >
> > > > If your trying to do this in a table cell or matrix you could use a
> > > > conditinal that says
> > > >
> > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > then again for water
> > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > >
> > > > and so on...
> > > >
> > > > Michael C
> > > >
> > > > "smithcjb" wrote:
> > > >
> > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > another field
> > > > >
> > > > > My query is returning the following...
> > > > > DATE, VOLUME, PRODUCT
> > > > >
> > > > > 1/1/07, 22, OIL
> > > > > 1/1/07, 0, WATER
> > > > > 1/2/07, 8, OIL
> > > > > 1/2/07, 12, WATER
> > > > >
> > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > possible. If so how?|||Well,I hope i'm not being too much of a pain, but you could also use custom
code ( I realize my last answer is exactly what you DIDN"T want to do).
you can create a function that fires on each detail setting static variables
over the group, then a second function that returns the answers at the end of
the group. this is actually quite easy to accomplish too.
Sorry I can't be of more help Colin.
Michael C.
"Michael C" wrote:
> Okay, so now I do understand your want to do this in report. I'm a little
> stumped at why the IIF won't work (Unless the report crosses pages?).
> I would suggest trying a CASE statement in your SQL to give both OIL and
> WATER their own column at least to benchmark what the added overhead is.
> CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
> CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
> If it is minimal overhead to do this then voila, you now have columns for
> both oil and water (unless of course other products like NGL's and
> Condensates are being included in your products list in which case you'll
> need to expand the case statements).
>
> Michael C.
>
>
>
> "smithcjb" wrote:
> > Michael,
> >
> > I appreciate your comments. This is my full query. I'm returning 3 rows per
> > date (1 per product). I've tried to use inner joins on the view I'm pulling
> > from but performance is woeful for large date ranges. I'm trying to sum the
> > production for the specified date range for each product - easy for GAS since
> > it appears in it's own column! Any further help greatly appreciated. I don't
> > know why the expressions you provided won;t workm - they seem logical to me!
> >
> > Kind regards,
> > Colin
> >
> > SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> > p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> > FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> > dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> > INNER JOIN
> > dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> > WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> > '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> > (SELECT COMPLETION_ITEM_ID
> > FROM dbo.REP_ORG_COMPLETION
> > WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> > GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> > p.LIQUID_VOL,p.PRODUCT
> >
> > "Michael C" wrote:
> >
> > >
> > >
> > > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > > Datasets. I fully understand what your saying, so no need to "reiterate"
> > > anything.
> > >
> > >
> > > Also, the comment "for performance reasons i want to do this in the report".
> > > Are you saying you want the report to run slower? Performance wise , as far
> > > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> > >
> > > Michael C.
> > >
> > > "smithcjb" wrote:
> > >
> > > > Just to reiterate. I want to perform this in the report, not the query. I
> > > > tried the conditional expressions you provided but the report just returns
> > > > "Error" in the field
> > > >
> > > > "Michael C" wrote:
> > > >
> > > > > Hey Smithcjb,
> > > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > > it as part of the WHERE clause only). Write it like this
> > > > >
> > > > > SELECT PRODUCT, SUM(VOLUME)
> > > > > FROM MyTable
> > > > > WHERE Date Between Date1 and Date2
> > > > > GROUP BY PRODUCT
> > > > >
> > > > > If your trying to do this in a table cell or matrix you could use a
> > > > > conditinal that says
> > > > >
> > > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > > then again for water
> > > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > > >
> > > > > and so on...
> > > > >
> > > > > Michael C
> > > > >
> > > > > "smithcjb" wrote:
> > > > >
> > > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > > another field
> > > > > >
> > > > > > My query is returning the following...
> > > > > > DATE, VOLUME, PRODUCT
> > > > > >
> > > > > > 1/1/07, 22, OIL
> > > > > > 1/1/07, 0, WATER
> > > > > > 1/2/07, 8, OIL
> > > > > > 1/2/07, 12, WATER
> > > > > >
> > > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > > possible. If so how?|||Michael,
As it happens your SQL has been of great value. I've rebuilt the view and
the CASE statements (which are new to me) - and seem to be doing the trick.
Many thanks,
Colin
"Michael C" wrote:
> Well,I hope i'm not being too much of a pain, but you could also use custom
> code ( I realize my last answer is exactly what you DIDN"T want to do).
> you can create a function that fires on each detail setting static variables
> over the group, then a second function that returns the answers at the end of
> the group. this is actually quite easy to accomplish too.
> Sorry I can't be of more help Colin.
> Michael C.
> "Michael C" wrote:
> >
> > Okay, so now I do understand your want to do this in report. I'm a little
> > stumped at why the IIF won't work (Unless the report crosses pages?).
> >
> > I would suggest trying a CASE statement in your SQL to give both OIL and
> > WATER their own column at least to benchmark what the added overhead is.
> >
> > CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
> > CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
> >
> > If it is minimal overhead to do this then voila, you now have columns for
> > both oil and water (unless of course other products like NGL's and
> > Condensates are being included in your products list in which case you'll
> > need to expand the case statements).
> >
> >
> > Michael C.
> >
> >
> >
> >
> >
> >
> > "smithcjb" wrote:
> >
> > > Michael,
> > >
> > > I appreciate your comments. This is my full query. I'm returning 3 rows per
> > > date (1 per product). I've tried to use inner joins on the view I'm pulling
> > > from but performance is woeful for large date ranges. I'm trying to sum the
> > > production for the specified date range for each product - easy for GAS since
> > > it appears in it's own column! Any further help greatly appreciated. I don't
> > > know why the expressions you provided won;t workm - they seem logical to me!
> > >
> > > Kind regards,
> > > Colin
> > >
> > > SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> > > p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> > > FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> > > dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> > > INNER JOIN
> > > dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> > > WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> > > '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> > > (SELECT COMPLETION_ITEM_ID
> > > FROM dbo.REP_ORG_COMPLETION
> > > WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> > > GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> > > p.LIQUID_VOL,p.PRODUCT
> > >
> > > "Michael C" wrote:
> > >
> > > >
> > > >
> > > > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > > > Datasets. I fully understand what your saying, so no need to "reiterate"
> > > > anything.
> > > >
> > > >
> > > > Also, the comment "for performance reasons i want to do this in the report".
> > > > Are you saying you want the report to run slower? Performance wise , as far
> > > > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> > > >
> > > > Michael C.
> > > >
> > > > "smithcjb" wrote:
> > > >
> > > > > Just to reiterate. I want to perform this in the report, not the query. I
> > > > > tried the conditional expressions you provided but the report just returns
> > > > > "Error" in the field
> > > > >
> > > > > "Michael C" wrote:
> > > > >
> > > > > > Hey Smithcjb,
> > > > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > > > it as part of the WHERE clause only). Write it like this
> > > > > >
> > > > > > SELECT PRODUCT, SUM(VOLUME)
> > > > > > FROM MyTable
> > > > > > WHERE Date Between Date1 and Date2
> > > > > > GROUP BY PRODUCT
> > > > > >
> > > > > > If your trying to do this in a table cell or matrix you could use a
> > > > > > conditinal that says
> > > > > >
> > > > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > > > then again for water
> > > > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > > > >
> > > > > > and so on...
> > > > > >
> > > > > > Michael C
> > > > > >
> > > > > > "smithcjb" wrote:
> > > > > >
> > > > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > > > another field
> > > > > > >
> > > > > > > My query is returning the following...
> > > > > > > DATE, VOLUME, PRODUCT
> > > > > > >
> > > > > > > 1/1/07, 22, OIL
> > > > > > > 1/1/07, 0, WATER
> > > > > > > 1/2/07, 8, OIL
> > > > > > > 1/2/07, 12, WATER
> > > > > > >
> > > > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > > > possible. If so how?|||Colin,
My pleasure, Im glad I could be of assistance.
Michael
"smithcjb" wrote:
> Michael,
> As it happens your SQL has been of great value. I've rebuilt the view and
> the CASE statements (which are new to me) - and seem to be doing the trick.
> Many thanks,
> Colin
> "Michael C" wrote:
> > Well,I hope i'm not being too much of a pain, but you could also use custom
> > code ( I realize my last answer is exactly what you DIDN"T want to do).
> >
> > you can create a function that fires on each detail setting static variables
> > over the group, then a second function that returns the answers at the end of
> > the group. this is actually quite easy to accomplish too.
> >
> > Sorry I can't be of more help Colin.
> >
> > Michael C.
> >
> > "Michael C" wrote:
> >
> > >
> > > Okay, so now I do understand your want to do this in report. I'm a little
> > > stumped at why the IIF won't work (Unless the report crosses pages?).
> > >
> > > I would suggest trying a CASE statement in your SQL to give both OIL and
> > > WATER their own column at least to benchmark what the added overhead is.
> > >
> > > CASE WHEN p.PRODUCT = 'OIL' THEN p.LIQUID_VOL ELSE 0 END as OIL_VOL,
> > > CASE WHEN p.PRODUCT = 'WATER' THEN p.LIQUID_VOL ELSE 0 END as WATER_VOL,
> > >
> > > If it is minimal overhead to do this then voila, you now have columns for
> > > both oil and water (unless of course other products like NGL's and
> > > Condensates are being included in your products list in which case you'll
> > > need to expand the case statements).
> > >
> > >
> > > Michael C.
> > >
> > >
> > >
> > >
> > >
> > >
> > > "smithcjb" wrote:
> > >
> > > > Michael,
> > > >
> > > > I appreciate your comments. This is my full query. I'm returning 3 rows per
> > > > date (1 per product). I've tried to use inner joins on the view I'm pulling
> > > > from but performance is woeful for large date ranges. I'm trying to sum the
> > > > production for the specified date range for each product - easy for GAS since
> > > > it appears in it's own column! Any further help greatly appreciated. I don't
> > > > know why the expressions you provided won;t workm - they seem logical to me!
> > > >
> > > > Kind regards,
> > > > Colin
> > > >
> > > > SELECT c.ITEM_NAME AS COMPLETION_NAME, c.ITEM_ID AS COMPLETION_ITEM_ID,
> > > > p.START_DATETIME, p.GAS_VOL, p.LIQUID_VOL,p.PRODUCT
> > > > FROM dbo.VI_COMPLETION_en_US AS c INNER JOIN
> > > > dbo.ITEM_LINK AS il ON c.ITEM_ID = il.FROM_ITEM_ID
> > > > INNER JOIN
> > > > dbo.VT_ACT_DAY_en_US AS p ON c.ITEM_ID = p.ITEM_ID
> > > > WHERE (il.LINK_TYPE = 'NET_MEMBER') AND (p.START_DATETIME between
> > > > '1/1/04' and '1/1/07') AND (c.ITEM_ID IN
> > > > (SELECT COMPLETION_ITEM_ID
> > > > FROM dbo.REP_ORG_COMPLETION
> > > > WHERE (FIELD_ITEM_ID = @.FieldItemId)))
> > > > GROUP BY c.ITEM_NAME, c.ITEM_ID, p.START_DATETIME, p.GAS_VOL,
> > > > p.LIQUID_VOL,p.PRODUCT
> > > >
> > > > "Michael C" wrote:
> > > >
> > > > >
> > > > >
> > > > > Tables (also called matrix) exist INSIDE reports and have nothing to do with
> > > > > Datasets. I fully understand what your saying, so no need to "reiterate"
> > > > > anything.
> > > > >
> > > > >
> > > > > Also, the comment "for performance reasons i want to do this in the report".
> > > > > Are you saying you want the report to run slower? Performance wise , as far
> > > > > as I've read, would suggest you do this in the SQL. But hey...its up to you.
> > > > >
> > > > > Michael C.
> > > > >
> > > > > "smithcjb" wrote:
> > > > >
> > > > > > Just to reiterate. I want to perform this in the report, not the query. I
> > > > > > tried the conditional expressions you provided but the report just returns
> > > > > > "Error" in the field
> > > > > >
> > > > > > "Michael C" wrote:
> > > > > >
> > > > > > > Hey Smithcjb,
> > > > > > > If you are trying to do this in your SQL statement, then drop the "date"
> > > > > > > (you can leave it in if your are filtering by it, just make sure you define
> > > > > > > it as part of the WHERE clause only). Write it like this
> > > > > > >
> > > > > > > SELECT PRODUCT, SUM(VOLUME)
> > > > > > > FROM MyTable
> > > > > > > WHERE Date Between Date1 and Date2
> > > > > > > GROUP BY PRODUCT
> > > > > > >
> > > > > > > If your trying to do this in a table cell or matrix you could use a
> > > > > > > conditinal that says
> > > > > > >
> > > > > > > =SUM(iif(Fields!product.value = "OIL", Fields!volume.value, 0))
> > > > > > > then again for water
> > > > > > > =SUM(iif(Fields!product.value = "WATER", Fields!volume.value, 0))
> > > > > > >
> > > > > > > and so on...
> > > > > > >
> > > > > > > Michael C
> > > > > > >
> > > > > > > "smithcjb" wrote:
> > > > > > >
> > > > > > > > I'm not having much fun trying to aggregate values based on the value of
> > > > > > > > another field
> > > > > > > >
> > > > > > > > My query is returning the following...
> > > > > > > > DATE, VOLUME, PRODUCT
> > > > > > > >
> > > > > > > > 1/1/07, 22, OIL
> > > > > > > > 1/1/07, 0, WATER
> > > > > > > > 1/2/07, 8, OIL
> > > > > > > > 1/2/07, 12, WATER
> > > > > > > >
> > > > > > > > I want to sum all the VOLUME values where the product = 'OIL' and another to
> > > > > > > > sum where the product = 'GAS'. Is this type of conditional aggregation
> > > > > > > > possible. If so how?

Aggregate sum query, need help

I have two tables tb1 with item and qtyOnHand and a second table tb2 with item and qtyOrdered I am trying without success to make this happen;
select sum (onHand-Ordered) from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered
I kind of gathered it would work based on thishttp://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspx
I have also tried this;
select tb1.item from (select sum (qtyOnHand) from tb1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from tb2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from tb1 where tb1.item = RD35
Any ides, there are multiple rows of each item in each table tb1 is inventory with several different locations and tb2 is an orders table.

I'm not sure exactly what values you are looking for but it looks like you want the new quantity on hand after the order for a particular item. Try this:
SELECT
tb1.Item,
IsNull(tb1.qtyOnHand - SUM(qtyOrdered), 0) AS qtyNewOnHand
FROM tb1
LEFT JOIN tb2 ON tb1.item = tb2.item
WHERE tb1.item = RD35
GROUP BY tb1.item, tb1.qtyOnHand
I assumed that tb1 has 1 record per item but that tb2 may have multiple records (orders).
HTH.

|||

Thanks for the reply. There a multiple records in table 1 for a single item, it is an inventory by location table where bin numbers are the primary key. There are multiple records in table 2 as well. Which is why unions and joins are biting me with some multplication. In this particular case RD35 has 3 records in table 1 with a sum of 237 and 6 records in table 2 with a sum of 97.
select table1.item from (select sum (qtyOnHand) from table1 where item = RD35 group by item) as onHand, (select sum (qtyOrdered) from table2 where item = RD35 group by item) as Ordered, sum (onHand-Ordered) as available from table1 where table1.item = RD35
This ends up giving me; onHand 237, Ordered 97 and vailable 402. available should be 140.

|||

Try this:

SELECT
tbOnHand.item,
OnHand,
IsNull(Ordered, 0),
OnHand - IsNull(Ordered, 0) AS Available
FROM
(
SELECT
item,
SUM(qtyOnHand) as OnHand
FROM tb1
WHERE item = RD35
GROUP BY item
) AS tbOnHand
LEFT JOIN
(
SELECT
item,
SUM(qtyOrdered) as Ordered
FROM tb2
WHERE item = RD35
GROUP BY item
) AS tbOrdered ON tbOnHand.item = tbOrdered.item

Aggregate Strings While Aggregating Data

I'm using the following query to determine the TotTons produced daily. A
sample data set is included.
On days that two grades are produced, I'd like the Grade name to be a
combination of the two grade names separated by a \. For example the desire
d
Grade name for 08-01-05 would be H\V. At this point I'm not concerned about
the order in which the letters appear in the new string.
Is this possible?
Thanks in advance,
Raul
SELECT
MAX(Datestamp1) AS DateStamp,
SUM(Tons) AS TotTons,
MAX(Grade) AS Grade 'just for example
FROM
(
SELECT
DateStamp as Datestamp1,
NumBatches,
Grade,
Tons
FROM
DailyTonsByGrade
) inrqry
GROUP BY Datestamp1
ORDER BY DateStamp ASC;
CREATE TABLE DailyTonsByGrade (
DateStamp smalldatetime,
NumBatches int,
Grade Varchar(20),
Tons real)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('06-01-05', 48, 'V', 403.2)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('07-01-05', 62, 'V', 520.8)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('08-01-05', 12, 'H', 112.8)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('08-01-05', 31, 'V', 285.6)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('09-01-05', 44, 'H', 413.6)
INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
('10-01-05', 60, 'H', 564.0)
Or
DateStamp NumBatches Grade Tons
06-01-05 48 V 403.2
07-01-05 62 V 520.8
08-01-05 12 H 112.8
08-01-05 34 V 285.6
09-01-05 44 H 413.6
10-01-05 60 H 564would you ever have more than 2 grades on the same day ?
Message posted via http://www.webservertalk.com|||
In order to do that you need code that processes the multiple records in one
day's group, and outputs a concatenation of the Grade NAmes...
It can be done, but it involves row-processing (using a cursor, or a temp
table or table Variable) probably in a Stored Proc, or User efined FUnction.
One solution(using Latter)
would be
Create Functiondbo.FuelGrades(@.Dt DateTime)
Returns VarChar(500)
As
Begin
Declare @.Out VarChar(500) Set @.Out = ''
Declare @.Grade VarChar(50) Set @.Grade = ''
While Exists
(Select * From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade)
Select @.Out = @.Out + Min(Grade) + '/',
@.Grade = Min(Grade)
From DailyTonsByGrade
Where DateStamp = @.Dt
-- --
If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
Return @.Out
End
Then, in your query, just refer to this UDF...
Select Max(Datestamp) DateStamp,
Sum(Tons) TotTons,
dbo.FuelGrades(Datestamp) Grade
From DailyTonsByGrade
Group By Datestamp
Order By Max(Datestamp);
This will work, but performance will be poor...
"Raul" wrote:

> I'm using the following query to determine the TotTons produced daily. A
> sample data set is included.
> On days that two grades are produced, I'd like the Grade name to be a
> combination of the two grade names separated by a \. For example the desi
red
> Grade name for 08-01-05 would be H\V. At this point I'm not concerned abo
ut
> the order in which the letters appear in the new string.
> Is this possible?
> Thanks in advance,
> Raul
> SELECT
> MAX(Datestamp1) AS DateStamp,
> SUM(Tons) AS TotTons,
> MAX(Grade) AS Grade 'just for example
> FROM
> (
> SELECT
> DateStamp as Datestamp1,
> NumBatches,
> Grade,
> Tons
> FROM
> DailyTonsByGrade
> ) inrqry
> GROUP BY Datestamp1
> ORDER BY DateStamp ASC;
> CREATE TABLE DailyTonsByGrade (
> DateStamp smalldatetime,
> NumBatches int,
> Grade Varchar(20),
> Tons real)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('06-01-05', 48, 'V', 403.2)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('07-01-05', 62, 'V', 520.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 12, 'H', 112.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 31, 'V', 285.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('09-01-05', 44, 'H', 413.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('10-01-05', 60, 'H', 564.0)
> Or
> DateStamp NumBatches Grade Tons
> 06-01-05 48 V 403.2
> 07-01-05 62 V 520.8
> 08-01-05 12 H 112.8
> 08-01-05 34 V 285.6
> 09-01-05 44 H 413.6
> 10-01-05 60 H 564
>|||opps, I left out something...
THe UDF should be
Create Functiondbo.FuelGrades(@.Dt DateTime)
Returns VarChar(500)
As
Begin
Declare @.Out VarChar(500) Set @.Out = ''
Declare @.Grade VarChar(50) Set @.Grade = ''
While Exists
(Select * From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade)
Select @.Out = @.Out + Min(Grade) + '/',
@.Grade = Min(Grade)
From DailyTonsByGrade
Where DateStamp = @.Dt
And Grade > @.Grade -- This is line I left Out
-- --
If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
Return @.Out
End
"Raul" wrote:

> I'm using the following query to determine the TotTons produced daily. A
> sample data set is included.
> On days that two grades are produced, I'd like the Grade name to be a
> combination of the two grade names separated by a \. For example the desi
red
> Grade name for 08-01-05 would be H\V. At this point I'm not concerned abo
ut
> the order in which the letters appear in the new string.
> Is this possible?
> Thanks in advance,
> Raul
> SELECT
> MAX(Datestamp1) AS DateStamp,
> SUM(Tons) AS TotTons,
> MAX(Grade) AS Grade 'just for example
> FROM
> (
> SELECT
> DateStamp as Datestamp1,
> NumBatches,
> Grade,
> Tons
> FROM
> DailyTonsByGrade
> ) inrqry
> GROUP BY Datestamp1
> ORDER BY DateStamp ASC;
> CREATE TABLE DailyTonsByGrade (
> DateStamp smalldatetime,
> NumBatches int,
> Grade Varchar(20),
> Tons real)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('06-01-05', 48, 'V', 403.2)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('07-01-05', 62, 'V', 520.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 12, 'H', 112.8)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('08-01-05', 31, 'V', 285.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('09-01-05', 44, 'H', 413.6)
> INSERT INTO DailyTonsByGrade (DateStamp, NumBatches, Grade, Tons) VALUES
> ('10-01-05', 60, 'H', 564.0)
> Or
> DateStamp NumBatches Grade Tons
> 06-01-05 48 V 403.2
> 07-01-05 62 V 520.8
> 08-01-05 12 H 112.8
> 08-01-05 34 V 285.6
> 09-01-05 44 H 413.6
> 10-01-05 60 H 564
>|||if you are NEVER going to exceed 2 grade on any one day then this should
work
SELECT
MAX(Datestamp) AS DateStamp,
SUM(Tons) AS TotTons,
case
when MAX(Grade) <> Min(grade) then MAX(Grade)+ ''+ Min(grade)
else min(grade)
end AS Grade --just for example
FROM
DailyTonsByGrade
GROUP BY Datestamp
ORDER BY DateStamp ASC
Message posted via http://www.webservertalk.com|||It is unlikely that we will produce more than two grades in one day.
This is a pretty clever solution.
Thanks a bunch,
Raul
"baie dronk via webservertalk.com" wrote:

> if you are NEVER going to exceed 2 grade on any one day then this should
> work
> SELECT
> MAX(Datestamp) AS DateStamp,
> SUM(Tons) AS TotTons,
> case
> when MAX(Grade) <> Min(grade) then MAX(Grade)+ ''+ Min(grade)
> else min(grade)
> end AS Grade --just for example
> FROM
> DailyTonsByGrade
> GROUP BY Datestamp
> ORDER BY DateStamp ASC
> --
> Message posted via http://www.webservertalk.com
>|||I'll try this solution also.
Thank you,
Raul
"CBretana" wrote:
> opps, I left out something...
> THe UDF should be
> Create Functiondbo.FuelGrades(@.Dt DateTime)
> Returns VarChar(500)
> As
> Begin
> Declare @.Out VarChar(500) Set @.Out = ''
> Declare @.Grade VarChar(50) Set @.Grade = ''
> While Exists
> (Select * From DailyTonsByGrade
> Where DateStamp = @.Dt
> And Grade > @.Grade)
> Select @.Out = @.Out + Min(Grade) + '/',
> @.Grade = Min(Grade)
> From DailyTonsByGrade
> Where DateStamp = @.Dt
> And Grade > @.Grade -- This is line I left Out
> -- --
> If Len(@.Out) > 0 Set @.Out = Left(@.Out, Len(@.Out) - 1)
> Return @.Out
> End
>
> "Raul" wrote:
>

Tuesday, March 27, 2012

Aliasing columns for a DMX subquery

Aliases & Columns Name w/ Spaces

Alias or Group SSAS Dimension at Query time.

Alias on Update query

Alias of a Linked Server

Alias in Query

Monday, March 19, 2012

agrument tuncated in execution plan .. how do I get the whole argu

Aggregete() function doesn't work for SQL query results! - Part 2

Aggregete() function doesn't work for SQL query results!

Sunday, March 11, 2012

Aggregation of Calculated members of form: MEASURE op ATTRIBUTE VALUE

Aggregation Issue

Aggregation Issue

Aggregation functon

Aggregates and subqueries

Aggregates and subqueries

Thursday, March 8, 2012

Aggregate values based on the value of another field

Aggregate sum query, need help

Aggregate Strings While Aggregating Data

SQL tool for MSDE

Blog Archive

About Me