Calling SQL Gurus (query help needed!)

Hey guys, iam having major problems with this query:

"average percent increase in price of work between initial sale price and last sale price"?

if any one can find a solution it would be GREATLY appreciated.

CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[LocationID] [smallint] NOT NULL,
[VendorID] [smallint] NOT NULL,
[WorkID] [int] NOT NULL,
[Sale_Status] [char](8)
[Previous_Sale_Price] [money] NULL,
[Sale_Price] [money] NULL,
[Commission] [float] NULL,
[Date_Up_For_Sales] [datetime] NULL,
[Date_Withdrawn_From_Sale] [datetime] NULL,
[Sale_Date] [datetime] NULL,
CONSTRAINT [pk_Sales] PRIMARY KEY CLUSTERED

CREATE TABLE [dbo].[Work](
[WorkID] [int] IDENTITY(1,1) NOT NULL,
[Work_TypeID] [tinyint] NOT NULL,
[Work_Title] [varchar](40) NOT NULL,
[Creation_Date] [datetime] NOT NULL,
CONSTRAINT [pk_Work] PRIMARY KEY CLUSTEREDnot enough info, even for a homework assignment

surely the assignment gives a hint about what constitutes the initial and the final sale price? how do these relate to the columns Sale_Price and Previous_Sale_Price? what is Sale_Status and how does it apply?|||I always find it amusing that these students think an "SQL Guru" is required to help them cheat on their simple homework assignments.|||items are put up for sale, these items can be sold many times, if an item is sold or put up for sale it is marked accordingly in the sale status for eg "for sale" "sold" etc...

i need to no the average increase in price for an item between its original sale date and its last sale date.

So i assume the query will go something like this: filter by status to find all the items that have been involved in a sale, "sold" items, and then find first date it was put up for sale "Min" date_up_for_sale: then find its last sale date "max" sale_date: the average Previous_Sale_Price and Sale_Price|||Use a common table expression to find the first price for an item, a second CTE for the last price, then calculate the average over the difference of both prices. Instead of CTEs, you can use subselects. No big deal...|||if you want the average price.. then presumably you want to look at the AVERAGE of all sales, how you calculate that is up to you.. do you want a simple AVG or a weighted one.. where you give weight to number of items sold at that price
say you had 2 sales one for 10 items at 15.25, one at 14.00, one at 14.25
a simple average would be (15.25+14.00+14.25)/3= 14.50, whereas the weighted average would be (15.25*10 + 14 + 14.25)/12 =15.06

BTW have you investigated what functions are available as part of SQL?
I wonder if SQL has an AVG or even AVERAGE function that may help?|||SQL has an AVG() aggregate function (aka column function), of course.

Calculations in Mdx Query

How to show the vlaues of increase in salary for few departments along with other departments.

Ex

we have A,B,C,D departments having total salary of 1000 each

A 1000

B 1000

c 1000

d 1000

Total 4000

If there was an increment of 2% for A and C deptment

Then I want to see the results as

Old_sal NewSal

A 1000 1020

B 1000 1000

C 1000 1020

D 1000 1000

Total 4000 4040

Please help to write the mdx query to populate the above results.

I am using Pivot table as client

Thanks In advance

The simplest technique is using a calculated member which detects the current department member and increases the Old_Sal by a percentage to give the NewSal value. The drawback to this technique is "hardcoding" the detection of the department in the calculated member making the calculation somewhat inflexible. However, without knowing a lot more detail about your model this is the best suggestion I have. Here's the MDX for the calculated member [NewSal]

WITH MEMBER Measures.[NewSal] AS
'
IIF([Department].[Department].CurrentMember.Name = "A" OR [Department].[Department].CurrentMember.Name = "C",
Measures.[Old_Sal] * 1.2,
Measures.[Old_Sal])
'

SELECT {Measures.[Old_Sal], Measures.[NewSal]} ON COLUMNS
,{[Department].[Department].[<your level>].Members} ON ROWS
FROM [your cube]

Hope this helps.

PGoldy

|||

Hello Paul. I have thought about the same problem. Will this work in a MDX-script?

Create Member CurrentCube.[Measures].[NewSal] AS "NA";
SCOPE ([Department].Members) =
( CASE
When [Department].CurrentMember IS [Department].[ DepA]
OR [Department].CurrentMember IS [Department].[ DepB]
THEN [Salary]*1.2
ELSE [Salary]
END);

This is with the assumption that you have correct attribute relations defined.

Regards

Thomas Ivarsson

|||

This should instead be formulated as:

Create Member CurrentCube.[Measures].[NewSal] AS [Measures].[Salary];
SCOPE ({[Department].[DepA],[Department].[DepB]}, [Measures].[NewSal]);
This = 1.2 * [Measures].[Salary];
END SCOPE;

|||

Thanks for the responses to every body.

Using these I can change the values but as I am using pivot table in the aspx page. The total are not calculated. They show the same old total.

How to get the new total also..

Thanks

|||

Hi Anil:

Using the CreateMember... as Michael, and Thomas suggested (Thanks Michael and Thomas) is a change to the cube definition which resides on the server. When the new calculated member resides on the server you can reference is from your pivot table and see [Measures].[NewSal] as an additional measure.

Hope this helps - PaulG

|||

Thank you Micheal, Thomas and paul for giving me valuble responses

I understood what you are saying.

I am still having problems with executing these.

I am new to analysis services and pivot table concepts.

I am using asp.net 2003 and sql server 2000

I wish to do the above logic at a click of a button.

Could you guys pleas help in to provide steps How to proceed for this scenario.

in the below code

cmd.execute is giving error saying syntax error.

Please Help!!!

Dim m_XML As String

Dim strOLAPConn As String = ConfigurationSettings.AppSettings("OLAPConnectionString")

Dim objPT As PivotTableClass = New PivotTableClass

Dim objPTView As PivotView

Dim fldFCElement, fldCenter, fldProcess, fldHierarchy As PivotField

Dim fSetFCElement, fsetOrgTitle, fSetProcess, fsetOrgAttr, fsetCenter, fsetHierarchy As PivotFieldSet

Try

Dim cnn As New ADODB.Connection

Dim cmd As New ADODB.Command

Dim rs As New ADOMD.Cellset

cnn.ConnectionString = strOLAPConn

cnn.Open()

cmd.ActiveConnection = cnn

Cmd.CommandText = "Create Member Demo.[Measures].[NewSal] AS '[Measures].[BY];SCOPE ({[Center].[NASA Headquarters]}, [Measures].[NewSal]);This = 1.2 * [Measures].[BY];END SCOPE;'"

cmd.Execute()

objPT.ConnectionString = cnn.ConnectionString

objPT.Toolbar.Enabled = True

objPT.DataMember = "Demo"

objPT.AutoFit = True

objPTView = objPT.ActiveView

fSetProcess = objPTView.FieldSets("Process")

objPT.ActiveView.ColumnAxis.InsertFieldSet(fSetProcess)

...

...

.

|||

Ahhh... Yes. The command you are trying to send will not work. First of all, what I suggested was syntax for Analysis Services 2005. Apparently, you are connecting to an Analysis Services 2000 instance, so that is one reason why it does not work... Even if you were connecting to AS2005, it still would not work, since SCOPE statements need to be defined on the server.

Now, if what you need to do is create the calculated member with DDL, your CommandText should be something like:

CREATE MEMBER Demo.[Measures].[NewSal] AS 'IIF([Department].[Department].CurrentMember IS [Department].[Department]. OR [Department].[Department].CurrentMember IS [Department].[Department]., Measures.[Old_Sal] * 1.2, Measures.[Old_Sal])'

This will, of course, not return any data, why you could use the ExecuteNonQuery() method on your Command object for clarity - at least you can do this with ADOMD.NET, not quite sure about ADOMD.