Get back calculated column from insert

I have the following in my Stored Procedure, where I am just adding a record
that will set the ApplicantID to the largest number + 1 or 1000 if null.
INSERT INTO ftsolutions.dbo.Applicant
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
Select
@.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
How do I find out what the ApplicantID is? This is similar to what an
Identity column would do, but I need to do it manually.
Thanks,
TomYour table should have a natural key defined in addition to the
surrogate ApplicantID. Use the natural key to retrieve the last ID. For
example, if this is for an online application you might make the email
address a key, therefore:
SET @.last_id =
(SELECT applicantid
FROM dbo.Applicant
WHERE email = @.email)
If you don't declare a natural key then you can't guarantee uniqueness
and integrity and therefore you can't reliably retrieve the Applicantid
after insert.
David Portas
SQL Server MVP
--|||Why do you need to "do it manually"? What do you think is going to happen
if two transactions hit this at exactly the same time?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:OvU13sfFFHA.624@.TK2MSFTNGP15.phx.gbl...
> I have the following in my Stored Procedure, where I am just adding a
record
> that will set the ApplicantID to the largest number + 1 or 1000 if null.
> INSERT INTO ftsolutions.dbo.Applicant
>
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
> Select
> @.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
> @.LastName,@.Email,getdate()
> from ftsolutions.dbo.Applicant
> where ClientID = @.ClientID
> How do I find out what the ApplicantID is? This is similar to what an
> Identity column would do, but I need to do it manually.
> Thanks,
> Tom
>|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ef63V1fFFHA.392@.TK2MSFTNGP09.phx.gbl...
> Why do you need to "do it manually"? What do you think is going to happen
> if two transactions hit this at exactly the same time?
Because there are cases where I will need to add another ApplicantID in the
insert statement.
The create is:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
GO
The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
What would happen here is I could add a record that has no ApplicantID
asssociated with it and the insert would have take the max and add 1 and the
Applicant Unique would be 1. If there is already an ApplicantID out there,
I would have the same ApplicantID with ApplicantUnique =
max(ApplicantUnique)+1. Since the ApplicantID would need to be in this
Insert statement, it can't be an Identity.
Actually, we originally designed this to only allow one ApplicantID per
person without the PositionID, but now we need to allow multiple PositionIDs
per ApplicationID's, so we changed ApplicantID to be a normal bigint.
Tom

>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:OvU13sfFFHA.624@.TK2MSFTNGP15.phx.gbl...
> record
> (ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,
Da
> tePosted)
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108760606.023053.73570@.l41g2000cwc.googlegroups.com...
> Your table should have a natural key defined in addition to the
> surrogate ApplicantID. Use the natural key to retrieve the last ID. For
> example, if this is for an online application you might make the email
> address a key, therefore:
> SET @.last_id =
> (SELECT applicantid
> FROM dbo.Applicant
> WHERE email = @.email)
> If you don't declare a natural key then you can't guarantee uniqueness
> and integrity and therefore you can't reliably retrieve the Applicantid
> after insert.
>
Actually, I do have key.
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
GO
The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
The insert statement that gets the ApplicantID is:
****************************************
************************************
***************
if not exists (Select ApplicantID from ftsolutions.dbo.Applicant where
ClientID = @.ClientID and
LastName = @.LastName and FirstName = @.FirstName and Email = @.Email)
INSERT INTO ftsolutions.dbo.Applicant
(ClientID,ApplicantID,PositionID,Applica
ntUnique,FirstName,LastName,Email,Da
tePosted)
Select
@.ClientID,COALESCE(max(ApplicantID),1000
)+1,@.PositionID,1,@.FirstName,
@.LastName,@.Email,getdate()
from ftsolutions.dbo.Applicant
where ClientID = @.ClientID
****************************************
************************************
**************
If it does exist, then I would use the same ApplicantID and increase the
ApplicantUnique by one
I used to have ApplicantID as an identity (as I mentioned in my other post)
and after the insert I would do a:
Select SCOPE_IDENTITY() as ApplicantID
to pass the ApplicantID back.
I could do the same thing by doing another select (inside a transaction to
make sure no one else increases it before I get it back) and then pass it
back, but I was hoping there was a way to get it from the insert/select
statement.
Thanks,
Tom

> --
> David Portas
> SQL Server MVP
> --
>|||> The primary key is ClientID,ApplicantID,PositionID,Applican
tUnique.
An artificial key should not be required to make a candidate key. You
haven't even got First Normal Form. Normalize your schema (apparently a
subset of these columns belongs in another table) and then your problem
will be solved.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108769600.895342.127230@.l41g2000cwc.googlegroups.com...
> An artificial key should not be required to make a candidate key. You
> haven't even got First Normal Form. Normalize your schema (apparently a
> subset of these columns belongs in another table) and then your problem
> will be solved.
I assume you mean something like:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ApplicantDetail] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
The problem is that this is just a temporary table which may not go any
farther. It may generate another record in another table which would be the
ClientID, ApplicantID,PositionID and ApplicantUnique.
I'm not sure what you mean by an Artificial key. The key is the Client
Identification, the Applicant Identification, and the Position
Identification. Are you saying I should generate another key?
Tom
> --
> David Portas
> SQL Server MVP
> --
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108769600.895342.127230@.l41g2000cwc.googlegroups.com...
> An artificial key should not be required to make a candidate key. You
> haven't even got First Normal Form. Normalize your schema (apparently a
> subset of these columns belongs in another table) and then your problem
> will be solved.
I assume you mean something like:
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[ApplicantDetail] (
[ClientID] [varchar] (20) NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[ApplicantUnique] [int] NOT NULL ,
[ResumeUnique] [int] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) NULL ,
[LastName] [varchar] (30) NULL ,
[Email] [varchar] (45) NULL
) ON [PRIMARY]
The problem is that this is just a temporary table which may not go any
farther. It may generate another record in another table which would be the
ClientID, ApplicantID,PositionID and ApplicantUnique.
I'm not sure what you mean by an Artificial key. The key is the Client
Identification, the Applicant Identification, and the Position
Identification. Are you saying I should generate another key?
I have a Client Table that is the ClientID only. I also have a Postions
table which is ClientID and PositionID (could have the same PositionID for
different Clients). The we have the Applicant table, where we have multiple
Positions for each Client and each Applicant could have more than one
instance of the same position (which is why I need the ApplicantUnique).
Tom
> --
> David Portas
> SQL Server MVP
> --
>|||An artificial key is an arbitrary value, such as an incrementing count,
that is not a real attribute of the entity you are modelling. By
definition you are carrying redundant data if you don't have a Natural
key - ie. some subset of the attributes (real attributes) that is
unique.
It's far from clear to me what all these columns are but you seem to
have a high level of non-key dependencies in this table. Are you
familiar with the concept of Normalization? Normalization is a basic
design step in which you eliminate duplicated information by
decomposing data into appropriate tables based on functional
dependencies. If this process is performed correctly then it won't be
possible to end up with a structure such as you have. For example I
would not expect to see data about Applicants (actual names and email
addresses of people) in the same table as data about Applications
(positions applied for and who applied for them). Here's a wild guess,
not perfect, but all the tables have at least one natural key:
CREATE TABLE Applicants (applicant_id INTEGER PRIMARY KEY,
email_address VARCHAR(45) NOT NULL UNIQUE, firstname VARCHAR(30) NOT
NULL, lastname VARCHAR(30) NOT NULL)
CREATE TABLE Clients (client_id INTEGER PRIMARY KEY, client_name
VARCHAR(50) NOT NULL UNIQUE)
CREATE TABLE Positions (client_id INTEGER REFERENCES Clients
(client_id), client_job_code VARCHAR(20) NOT NULL, PRIMARY KEY
(client_id, client_job_code))
CREATE TABLE Applications (applicant_id INTEGER NOT NULL REFERENCES
Applicants (applicant_id), client_id INTEGER NOT NULL, client_job_code
VARCHAR(20) NOT NULL, FOREIGN KEY (client_id, client_job_code)
REFERENCES Positions (client_id, client_job_code), application_date
DATETIME NOT NULL, PRIMARY KEY (applicant_id, client_id,
client_job_code, application_date))
Regarding PositionUnique. I don't see any other information associated
with multiple instances of a Position for an Applicant. If there isn't
any such information then you don't actually need to add a new row. For
completeness I've added Application_Date into the key.

> The problem is that this is just a temporary table which may not go
any
> farther.
Then you surely won't need another artifical key at all. The only use
for the INSERT you originally asked for is if new rows of data are
being inserted. If you need more help then I expect you can get better
advice if you tell us what your actual *objective* is rather than
propose half a solution to a problem you haven't really explained. The
following article describes the best way to post your problem for the
group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108778173.839320.171900@.z14g2000cwz.googlegroups.com...
> An artificial key is an arbitrary value, such as an incrementing count,
> that is not a real attribute of the entity you are modelling. By
> definition you are carrying redundant data if you don't have a Natural
> key - ie. some subset of the attributes (real attributes) that is
> unique.
> It's far from clear to me what all these columns are but you seem to
> have a high level of non-key dependencies in this table. Are you
> familiar with the concept of Normalization? Normalization is a basic
> design step in which you eliminate duplicated information by
> decomposing data into appropriate tables based on functional
> dependencies.
I understand Normalization and do a certain amount of it but not completely
(to the consternation of many).

>If this process is performed correctly then it won't be
> possible to end up with a structure such as you have.
That's true.

>For example I
> would not expect to see data about Applicants (actual names and email
> addresses of people) in the same table as data about Applications
> (positions applied for and who applied for them).
Not true here.
I would agree with you in other cases, however. I might have a persons data
in a separate table than the application table. But in our case, the person
may want to have different basic information for each position. He will
typically gear the information to the position. We need to keep a snapshot
of each application of each person. So in our case, it is just easier to
keep the personal information along with the application data. We do have a
separate table for each position (whose key is the PostitionID).

>Here's a wild guess,
> not perfect, but all the tables have at least one natural key:
> CREATE TABLE Applicants (applicant_id INTEGER PRIMARY KEY,
> email_address VARCHAR(45) NOT NULL UNIQUE, firstname VARCHAR(30) NOT
> NULL, lastname VARCHAR(30) NOT NULL)
> CREATE TABLE Clients (client_id INTEGER PRIMARY KEY, client_name
> VARCHAR(50) NOT NULL UNIQUE)
> CREATE TABLE Positions (client_id INTEGER REFERENCES Clients
> (client_id), client_job_code VARCHAR(20) NOT NULL, PRIMARY KEY
> (client_id, client_job_code))
> CREATE TABLE Applications (applicant_id INTEGER NOT NULL REFERENCES
> Applicants (applicant_id), client_id INTEGER NOT NULL, client_job_code
> VARCHAR(20) NOT NULL, FOREIGN KEY (client_id, client_job_code)
> REFERENCES Positions (client_id, client_job_code), application_date
> DATETIME NOT NULL, PRIMARY KEY (applicant_id, client_id,
> client_job_code, application_date))
Your table is pretty close to mine. If I were to separate my tables the you
have, I would also need to have the ClientID in the Applicants table also -
as we need to keep our applicants for our clients separate from each other.
I did make some changes, after looking at your last post about artificial
keys. I realized that I do have another table where I keep summary data
(JobApplicant) for each application and gave that a specific ID by making it
an identity field. There is one JobApplicant record for each application
filed. So I replace the ApplicantUnique with JobApplicant as that would
uniquify the record. The JobApplicant key "artificial key" would be the
JobID which is an identity field.
Also, a person can apply to multiple positions (so for the same ApplicantID
there could be multiple PositionIDs). Also, a person could apply to the
same position more than once. This was why I had the ApplicantUnique
before. But since there is only one JobApplicant record for each Applicant
and each position applied for - I can replace it with that.
Here is what I came up with and how it is currently on my system (there are
more fields in the tables, but these are the pertinent data).
CREATE TABLE [dbo].[Client] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Name] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Division] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ContactName] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
CREATE TABLE [dbo].[Position] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[PositionID] [int] IDENTITY (1, 1) NOT NULL ,
[JobTitle] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatePosted] [datetime] NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[Position] WITH NOCHECK ADD
CONSTRAINT [PK_Position] PRIMARY KEY CLUSTERED
(
[ClientID],
[PositionID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
ALTER TABLE [dbo].[Position] ADD
CONSTRAINT [FK_Position_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
)
CREATE TABLE [dbo].[Applicant] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] NOT NULL ,
[DatePosted] [datetime] NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Applicant] WITH NOCHECK ADD
CONSTRAINT [DF_Applicant_PositionID] DEFAULT (0) FOR [PositionID],
CONSTRAINT [DF_Applicant_ApplicantUnique] DEFAULT (0) FOR [JobID],
CONSTRAINT [DF_Applicant_ResumeUnique] DEFAULT (0) FOR [ResumeUnique],
CONSTRAINT [PK_Applicant] PRIMARY KEY CLUSTERED
(
[ClientID],
[ApplicantID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[JobApplicant] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Resume] [datetime] NULL ,
[Application] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[JobApplicant] WITH NOCHECK ADD
CONSTRAINT [DF_jobApplicant_Interviews] DEFAULT (0) FOR [Interviews],
CONSTRAINT [PK_jobApplicant] PRIMARY KEY CLUSTERED
(
[ClientID],
[ApplicantID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE TABLE [dbo].[ApplicantPosition] (
[ClientID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ApplicantID] [bigint] NOT NULL ,
[PositionID] [int] NOT NULL ,
[JobID] [bigint] NOT NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MiddleName] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (45) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateApplied] [datetime] NULL ,
[DateUpdated] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ApplicantPosition] WITH NOCHECK ADD
CONSTRAINT [DF_ApplicantPosition_JobID] DEFAULT (0) FOR [JobID],
CONSTRAINT [PK_ApplicantPosition] PRIMARY KEY CLUSTERED
(
[ApplicantID],
[ClientID],
[PositionID],
[JobID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
Tom

> Regarding PositionUnique. I don't see any other information associated
> with multiple instances of a Position for an Applicant. If there isn't
> any such information then you don't actually need to add a new row. For
> completeness I've added Application_Date into the key.
>
> any
> Then you surely won't need another artifical key at all. The only use
> for the INSERT you originally asked for is if new rows of data are
> being inserted. If you need more help then I expect you can get better
> advice if you tell us what your actual *objective* is rather than
> propose half a solution to a problem you haven't really explained. The
> following article describes the best way to post your problem for the
> group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>sql

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSa
l.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't
know how?
With this value i want to fill the calculated member.
Any help is appreciated.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25])
, if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ****************************************
******************************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

get a value from a measure

I want to create a new calculated member in a virtul cube.
I have a dimension Time with 3 levels Year Month and Day and a Measure ValSal.
I want to get a value from the measure ValSal on day 2004/05/25, but i don't know how?
With this value i want to fill the calculated member.
Any help is appreciated.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Try ([Measures].[ValSal],[Time].&[2005].&[5].&[25]), if your dimension
[Time] is Year-month-day
wrote in message news:epEgjy4UEHA.212@.TK2MSFTNGP12.phx.gbl...
> I want to create a new calculated member in a virtul cube.
> I have a dimension Time with 3 levels Year Month and Day and a Measure
ValSal.
> I want to get a value from the measure ValSal on day 2004/05/25, but i
don't know how?
> With this value i want to fill the calculated member.
>
> Any help is appreciated.
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...
sql

Generic calculated measure

Hi

I have to create a calculated measure which is applicable for 16 measures.

How can i specify it in a generic manner?

for eg:

create member currentcube.ytd

as

sum(ytd(),[measures].[sales units];

here my scenario requires the calculation of YTD for many other measures also other than sales units

so can i have something instead of [measures].[units], so that it will be generic.

Thanks in advance.

The standard SSAS Time Intelligence enhancements provide this type of capability:

http://msdn2.microsoft.com/en-us/library/ms175440.aspx

>>

SQL Server 2005 Books Online

Defining Time Intelligence Calculations using the Business Intelligence Wizard

The time intelligence enhancement is a cube enhancement that adds time calculations (or time views) to a selected hierarchy. This enhancement supports the following categories of calculations:

Period to date.

Period over period growth.

Moving averages.|||

You can also create is as a calculated member in a seperate dimension called ValueType or something.

I can't give you tested precise answer because i'm installing SP2 at hte moment, but for your example it would look something like

create calculated member CurrentCube.ValueType.Ytd as

sum(ytd();

It will display the Ytd of whatever measure is in context. If it should only apply to certain measures you could scope on those.

Regards GJ

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury822.34602709630
Standard433.34602709630
Custom397.34602709630
Modified285.34602709630
Other222.34602709630
More...676.34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury822.10032954961
Standard433.05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.com
Hi,
The Query can be re-written as:
=============================
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via droptable.com" wrote:

> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury822.34602709630
> Standard433.34602709630
> Custom397.34602709630
> Modified285.34602709630
> Other222.34602709630
> More...676.34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury822.10032954961
> Standard433.05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
>
|||Hi Chandra,
Thanks for the assist - that took care of it.
Message posted via http://www.droptable.com
|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via droptable.com" wrote:

> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.droptable.com
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
--
Message posted via http://www.sqlmonster.comHi,
The Query can be re-written as:
=============================ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via SQLMonster.com" wrote:
> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for each
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFunction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunction
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
--
Message posted via http://www.sqlmonster.com|||Good to know that the solution addressed your needs. Really appreciate if you
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via SQLMonster.com" wrote:
> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.sqlmonster.com
>

Generate 'calculated values' for rows within Stored Procedure

I'm having a problem with generating a 'calculated' percentage value as
noted in the sample output below:
#######
Luxury 822 .34602709630
Standard 433 .34602709630
Custom 397 .34602709630
Modified 285 .34602709630
Other 222 .34602709630
More... 676 .34602709630
#######
The 'VP_VClass' column (left) correctly shows each classification, and
provides the correct count for each. However the percentage for each
classification is shown 'incorrectly' as it should be a percentage for each
instead of for the 'sum' of the classifications count, like so:
#######
Luxury 822 .10032954961
Standard 433 .05284999389
#######
The SP is as follows, where the function [dbo.udf_Current_InventoryFunct
ion
()] provides the 'total' count (which in the examples above is 8,193).
#######
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass,
(SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)) /
(SELECT *
FROM dbo.udf_Current_InventoryFunction
()) AS Expr1
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
#######
Any thoughts or suggestions would be appreciated. Thanks.
Message posted via http://www.droptable.comHi,
The Query can be re-written as:
=============================
ALTER PROCEDURE dbo.usp_VClass_Breakdown
AS
SELECT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass, COUNT(VP_VClass) /
udf_Current_InventoryFunction() PercentOfVP
FROM dbo.vw_VClass_BreakdownView
WHERE (Active = 1) AND (Current = 1)
GROUP BY VP_VClass
ORDER BY COUNT(VP_VClass) DESC
================
I believe this addressed your question. If there are any more problem please
revert back
thanks and regards
Chandra
"The Gekkster via droptable.com" wrote:

> I'm having a problem with generating a 'calculated' percentage value as
> noted in the sample output below:
> #######
> Luxury 822 .34602709630
> Standard 433 .34602709630
> Custom 397 .34602709630
> Modified 285 .34602709630
> Other 222 .34602709630
> More... 676 .34602709630
> #######
> The 'VP_VClass' column (left) correctly shows each classification, and
> provides the correct count for each. However the percentage for each
> classification is shown 'incorrectly' as it should be a percentage for eac
h
> instead of for the 'sum' of the classifications count, like so:
> #######
> Luxury 822 .10032954961
> Standard 433 .05284999389
> #######
> The SP is as follows, where the function [dbo.udf_Current_InventoryFun
ction
> ()] provides the 'total' count (which in the examples above is 8,193).
> #######
> ALTER PROCEDURE dbo.usp_VClass_Breakdown
> AS
> SELECT TOP 100 PERCENT VP_VClass, COUNT(VP_VClass) AS CountOfVP_VClass
,
> (SELECT CAST(COUNT(VP_VClass) AS NUMERIC)
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)) /
> (SELECT *
> FROM dbo.udf_Current_InventoryFunctio
n
> ()) AS Expr1
> FROM dbo.vw_VClass_BreakdownView
> WHERE (Active = 1) AND (Current = 1)
> GROUP BY VP_VClass
> ORDER BY COUNT(VP_VClass) DESC
> #######
> Any thoughts or suggestions would be appreciated. Thanks.
> --
> Message posted via http://www.droptable.com
>|||Hi Chandra,
Thanks for the assist - that took care of it.
Message posted via http://www.droptable.com|||Good to know that the solution addressed your needs. Really appreciate if yo
u
can rate the Post.
This can be done by answering "Was this post helpful to you?"
"The Gekkster via droptable.com" wrote:

> Hi Chandra,
> Thanks for the assist - that took care of it.
> --
> Message posted via http://www.droptable.com
>