Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
Thanks
Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:

> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
sql

Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:
> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000
>> BOL:
>> USE pubs
>> GO
>> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
>> FROM titles
>> GO
>> GetDate() will allow you to compare to the time the query was run.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>>

Relative Dates

I need to create an SQL Statement that pulls data selected by a datetime
field. i want to be able to select dates and times relative to the time that
the query is run.
Can these date manipulations be done in the SQL statement?
ThanksHave a look at DateDiff in BOL. Here is an example from SQL Server 2000 BOL:
USE pubs
GO
SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
FROM titles
GO
GetDate() will allow you to compare to the time the query was run.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||i am not sure how i would use thet for waht i want, basically to start with
i
need to get all records that were created TODAY. then go on to records
created within the last n days
"Paul Ibison" wrote:

> Have a look at DateDiff in BOL. Here is an example from SQL Server 2000 BO
L:
> USE pubs
> GO
> SELECT DATEDIFF(day, pubdate, getdate()) AS no_of_days
> FROM titles
> GO
> GetDate() will allow you to compare to the time the query was run.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>|||Hi Mark,
something like this should do it:
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) = 0
SELECT cols FROM yourtable
where DATEDIFF(day, pubdate, getdate()) <= n
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
Expanding on Paul's example, if you want to consider date and time:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, GETDATE())
To consider date only:
DECLARE @.n int
SET @.n = 7
SELECT title
FROM titles
WHERE pubdate >= DATEADD(day, @.n * -1, DATEDIFF(day, 0, GETDATE())
Hope this helps.
Dan Guzman
SQL Server MVP
"Mark Shields" <MarkShields@.discussions.microsoft.com> wrote in message
news:2EE795F5-DA9D-4E51-A78B-57C929EDAE6B@.microsoft.com...[vbcol=seagreen]
>i am not sure how i would use thet for waht i want, basically to start with
>i
> need to get all records that were created TODAY. then go on to records
> created within the last n days
> "Paul Ibison" wrote:
>

Relative date 'Rolling 3 months' --help please

Hi,

I need to add special relative date categories in SSAS

that similar to the functionality offered by Cognos/Powerplay. With Cognos, you

can create relative time categories very easily-- like ‘Rolling 3 months’,

‘Prior Rolling 12 Months’ etc.

I created Time Dimension with SSAS BI Studio, added a new

named calculation ‘Rolling 3 Months’ to the Time Dimension. Below is

calculation code:

CREATE MEMBER

CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling

3]

AS

null,

VISIBLE = 1;

Scope(

{

[Measures].[ORDERS]

}

);

// Rolling 3 Months

(

[Time].[Rolling 3

Months].[Rolling 3],

[Time].[Month].[Month].Members

) =

Tail([Time].[Month].members ,3 );

End Scope;

I uesd Tail() function in SS Management studio, it got

the result I want. But it doesn’t work in SSAS BI Studio.

Am I missing something here? Or any suggestions would be

greatly appreciated.

Thanks.

Your syntax for the expression is wrong - you assign set where the numeric value is expected. I suggest that you use built-in Time Intelligence wizard - it supports expressions for Rolling 3 months. It would be something like

([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members) = Aggregate( [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember);

|||Thank

you very much for the help.

I realized something wrong here, but I just do

not know how to correct it. Thank you very much for point out. I tried Time

Dimension wizard, there are ‘month to date’ and ‘Three

Month Moving Average’ in Time Dimension wizard, but our project ask from Cognos Powerplay cube move to SSAS cube. in Powerplay,

‘Rolling 3 months’ will get each month order number for 3 months.

Should be like:

Month|

Orders

January, 2007|12345

December, 2006|14567

November, 2006|33562

Not 3 months Moving

Average. We expect get same

result like powerplay. Is there a way to get this result?

Thank you very much for the help, I really appreciated.

|||Please use the expression provided above.|||Hi Mosha,

Yes. I used the expression you provided above, but got "#VALUE!".
Rolling 3 months | Orders
-
Current Periods | 235263
Rolling 3 months | #VALUE!

What else I can do?

Thanks a lot.
|||Please provide the query that you sent, the exact MDX Script you have and the text of error message in order to find where is the problem.|||The code in BI studio script view:

First try:
CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS ([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
),
VISIBLE = 1 ;

Second try:

CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember,

VISIBLE = 1 ;

Scope(
{
[Measures].[ORDERS]
}
);

// Rolling 3 Months
([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);

End Scope;

Got same result in cube browser:

Rolling 3 Months | Orders
-
Current Periods | 123456
Rolling 3 | #VALUE!

no error message or say '#VALUE!' is an error message.

What's wrong?

|||

You still kept the portion with the error. You also need to decide what do you want to do at the levels above Month. I will put the code which keeps it NULL at such levels - up to you what to do there.

CREATE [Time].[Rolling 3 Months].[Rolling 3];

([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);

|||Thanks a lot Mosha.

I put the code you provided in to script view.
CREATE [Time].[Rolling 3 Months].[Rolling 3];

([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);

result:
[Rolling 3] row is missing.

Rolling 3 Months | Orders
--
Current Periods | 12345

Currently, we just want to get each month's order for last three months with 'Rolling 3 Months' this calculated member.
I am not sure your question for 'at the levels above Month', you mean Year level?
If I need Year level, how to add in?

am I missing some point?

Thanks a lot.
|||Yes, I meant on the Year level. It is not clear how you want to aggregate rolling 3 months to the Year. Anyway, you in order to see rolling sum on 3 month, please slice by some specific month, or add months to the axis.|||Thanks Mosha for the help.

I add specific month, but result some like before -no 'Rolling 3' row.

CREATE [Time].[Rolling 3 Months].[Rolling 3] ;

([Time].[Rolling 3 Months].[Rolling 3],[Time].[Month].[November 2006], Measures.[ORDERS]) = Aggregate(
[Time].[Month].[November 2006].Lag(2): [Time].[Month].[November 2006]
);

Something still wrong?

Thanks.|||The expression in the MDX script should stay the way it was before. It is your MDX query that needs to change to include slice by month.|||

As my understanding(may be wrong), MDX

script run in BI Studio, MDX query run in SS management Studio. In SS

management Studio, I used following MDX query:

SELECT Tail(

[Time].[Month].members,3 ) ON rows,

[Measures].[ORDERS] on columns

from [My cube]

Got

‘Rolling 3 Months’ data I want.

Month|

Orders

October

2006|12345

November

2006|34556

December

2006|23545

In BI Studio, if using filter by Month, it still able

to get ‘Rolling 3 Months’ result.

The

problem is using filter by Month, there are too many steps, our business user

will not accept that. They hope only drag ‘Rolling 3 Months’ from Time dimension drop to the cube browser then

can get above data (like Powerplay does). Is there a way to do that?

How to add MDX query in BI Studio?

Thanks for the help..

Relative date ''Rolling 3 months'' --help please

Hi,

I need to add special relative date categories in SSAS

that similar to the functionality offered by Cognos/Powerplay. With Cognos, you

can create relative time categories very easily-- like ‘Rolling 3 months’,

‘Prior Rolling 12 Months’ etc.

I created Time Dimension with SSAS BI Studio, added a new

named calculation ‘Rolling 3 Months’ to the Time Dimension. Below is

calculation code:

CREATE MEMBER

CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling

3]

AS

null,

VISIBLE = 1;

Scope(

{

[Measures].[ORDERS]

}

);

// Rolling 3 Months

(

[Time].[Rolling 3

Months].[Rolling 3],

[Time].[Month].[Month].Members

) =

Tail([Time].[Month].members ,3 );

End Scope;

I uesd Tail() function in SS Management studio, it got

the result I want. But it doesn’t work in SSAS BI Studio.

Am I missing something here? Or any suggestions would be

greatly appreciated.

Thanks.

Your syntax for the expression is wrong - you assign set where the numeric value is expected. I suggest that you use built-in Time Intelligence wizard - it supports expressions for Rolling 3 months. It would be something like

([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members) = Aggregate( [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember);

|||Thank

you very much for the help.

I realized something wrong here, but I just do

not know how to correct it. Thank you very much for point out. I tried Time

Dimension wizard, there are ‘month to date’ and ‘Three

Month Moving Average’ in Time Dimension wizard, but our project ask from Cognos Powerplay cube move to SSAS cube. in Powerplay,

‘Rolling 3 months’ will get each month order number for 3 months.

Should be like:

Month|

Orders

January, 2007|12345

December, 2006|14567

November, 2006|33562

Not 3 months Moving

Average. We expect get same

result like powerplay. Is there a way to get this result?

Thank you very much for the help, I really appreciated.

|||Please use the expression provided above.|||Hi Mosha,

Yes. I used the expression you provided above, but got "#VALUE!".
Rolling 3 months | Orders
-
Current Periods | 235263
Rolling 3 months | #VALUE!

What else I can do?

Thanks a lot.
|||Please provide the query that you sent, the exact MDX Script you have and the text of error message in order to find where is the problem.|||The code in BI studio script view:

First try:
CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS ([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
),
VISIBLE = 1 ;

Second try:

CREATE MEMBER CURRENTCUBE.[Time].[Rolling 3 Months].[Rolling 3]
AS [Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember,

VISIBLE = 1 ;

Scope(
{
[Measures].[ORDERS]
}
);

// Rolling 3 Months
([Time].[Rolling 3 Months].[Rolling 3],
[Time].[Month].[Month].Members)
=
Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);

End Scope;

Got same result in cube browser:

Rolling 3 Months | Orders
-
Current Periods | 123456
Rolling 3 | #VALUE!

no error message or say '#VALUE!' is an error message.

What's wrong?

|||

You still kept the portion with the error. You also need to decide what do you want to do at the levels above Month. I will put the code which keeps it NULL at such levels - up to you what to do there.

CREATE [Time].[Rolling 3 Months].[Rolling 3];

([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);

|||Thanks a lot Mosha.

I put the code you provided in to script view.
CREATE [Time].[Rolling 3 Months].[Rolling 3];

([Time].[Rolling 3 Months].[Rolling 3], [Time].[Month].[Month].Members, Measures.[ORDERS]) = Aggregate(
[Time].[Month].CurrentMember.Lag(2): [Time].[Month].CurrentMember
);

result:
[Rolling 3] row is missing.

Rolling 3 Months | Orders
--
Current Periods | 12345

Currently, we just want to get each month's order for last three months with 'Rolling 3 Months' this calculated member.
I am not sure your question for 'at the levels above Month', you mean Year level?
If I need Year level, how to add in?

am I missing some point?

Thanks a lot.
|||Yes, I meant on the Year level. It is not clear how you want to aggregate rolling 3 months to the Year. Anyway, you in order to see rolling sum on 3 month, please slice by some specific month, or add months to the axis.|||Thanks Mosha for the help.

I add specific month, but result some like before -no 'Rolling 3' row.

CREATE [Time].[Rolling 3 Months].[Rolling 3] ;

([Time].[Rolling 3 Months].[Rolling 3],[Time].[Month].[November 2006], Measures.[ORDERS]) = Aggregate(
[Time].[Month].[November 2006].Lag(2): [Time].[Month].[November 2006]
);

Something still wrong?

Thanks.|||The expression in the MDX script should stay the way it was before. It is your MDX query that needs to change to include slice by month.|||

As my understanding(may be wrong), MDX

script run in BI Studio, MDX query run in SS management Studio. In SS

management Studio, I used following MDX query:

SELECT Tail(

[Time].[Month].members,3 ) ON rows,

[Measures].[ORDERS] on columns

from [My cube]

Got

‘Rolling 3 Months’ data I want.

Month|

Orders

October

2006|12345

November

2006|34556

December

2006|23545

In BI Studio, if using filter by Month, it still able

to get ‘Rolling 3 Months’ result.

The

problem is using filter by Month, there are too many steps, our business user

will not accept that. They hope only drag ‘Rolling 3 Months’ from Time dimension drop to the cube browser then

can get above data (like Powerplay does). Is there a way to do that?

How to add MDX query in BI Studio?

Thanks for the help..sql

Relationships on MSDE

I am an Access user.
In Access when I create a relationship between 2 tables is use
Enforce Referential Integrity
Cascade Update Related Fields
Cascade Delete Related Records
If I change data in Primary Table is Update automaticaly in Foreign table.

I use a MSDE database, and I create 2 tabele
Table 1 - with a primary key (AUT_ID)
Table 2 (Foreign table) with 2 fields
Field :AUT_ID
Field: Field1
I use Access interfaces (adp Database) and I create a Diagram
Primary key table: Table_1; Field: AUT_ID
Foreign table: Table_2; Field: AUT_ID

PROBLEM: If I change data in Table_1 (field AUT_ID) data is not change in Table_2 and error occurs.[i]
PROBLEM: If I change data in Table_1 (field AUT_ID) data is not change in Table_2 and error occurs.

I see nothing that requests any sort of cascading-update.

Furthermore, Table_2 is clearly the master-table of the relationship and Table_1 the subordinate. Thus a change to Table_1 to introduce a key not in Table_2 would be disallowed, as you see.|||Is a version problem if I understud corectly
SQL vers.7 did not suport ON UPDATE NO ACTION / CASCADE
SQL vers.8 (suport ON UPDATE NO ACTION / CASCADE)
I installed vers.8 and everything is OK.

Thanks.

Relationships

Is it possible to have 2 different databases and create relationships inbetween the two? I am thinking about way down the road and the size of the databases and I believe that I need to split my data into multiple databases to keep from running into the size limitations.

Davids Learning

By "relationship", do you mean "foreign key"? If so, the answer is "No, you cannot create a foriegn key relationship across databases." What you can do though to implement the same functionality is to create a triggers on both tables that enforce the "relationship".

CREATE TRIGGER MyTrigger ON dbo.ChildTable AFTER INSERT, UPDATE

AS

IF EXISTS (

SELECT * FROM OtherDatabase.dbo.ParentTable p WHERE p.PrimaryKey NOT IN ( SELECT ChildFk FROM inserted )

)

BEGIN

RAISERROR ('You are attempting to insert/update like a heathen. Stop at once!', 16, 1)

ROLLBACK TRAN

END

GO

Relationship to table in another DB possible?

Is it possible to create a relationship to a table in another database,
either on the same server or a linked server?
- Dave
Hi
Foreign Keys are not possible across databases, you can create a check
constraint that calls a function (which may prove slow!) or enforce the
constraint in a trigger.
John
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>
|||Creating a foreign key across databases is not possible; however, you can
enforce cross-database relationships using triggers.
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>

Relationship to table in another DB possible?

Is it possible to create a relationship to a table in another database,
either on the same server or a linked server?
- DaveHi
Foreign Keys are not possible across databases, you can create a check
constraint that calls a function (which may prove slow!) or enforce the
constraint in a trigger.
John
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>|||Creating a foreign key across databases is not possible; however, you can
enforce cross-database relationships using triggers.
"David Slinn" <dslinn@.accesscomm.ca> wrote in message
news:eSZJ5e%23oEHA.1588@.TK2MSFTNGP09.phx.gbl...
> Is it possible to create a relationship to a table in another database,
> either on the same server or a linked server?
> - Dave
>

Relationship in Access, what about SQL Server 2000?

Hello,

In Microsoft Access, there is a feature to create relationships between tables. When creating these relations, you have the option to:

1. Establish the relation type. For example, 1 to many (primary key to foreign key)

2. Enforce referential integrity

3. Allow cascading updates

4. Allow cascading deletions

5. View these relations in a nice diagram

Does SQL Server 2000 contain this functionality? If so, where is this found in the Enterprise Manager? If not, what is the alternative to this very useful feature found in Access?

Thanks You For Any Help!

In Enterprise Manager select your Db, select Diagrams (Create One if neccessary - use auto) and then use drag and drop with the key fields to create the relationships. You will get a nice popup with various options such as cascading etc.|||

You can also use the design view or sql command to do this.

If you want to use the design view to add relationship, do the followings

Open EM|||

HIdefyant_2004

Sql server gives to us many features and there are many ways to create relationship between two tables. Here is the common way to create

1. Open EM

2. Right_click on Diagrams

3. Select New Database Diagram...

4. Next

5. Select the tables you want to create relationship

6. Next step...like Access

Cheers!

Relationship between two tables

Hi,
We can create more than one
relationship between two tables. Could you tell me a situation where we nee
d two relationships between two tables?
ThanksSay you have a customers table and a table that represents a transaction bet
ween two customers holding two columns with a customer id. The referencing t
able would need to have two foreign keys pointing on the customerid
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message news:OM$bER0VFHA
.1404@.TK2MSFTNGP09.phx.gbl...
Hi,
We can create more than one
relationship between two tables. Could you tell me a situation where we nee
d two relationships between two tables?
Thanks|||Hi
You can have that kind of relationship but it violates 2nd and 3rd Normal
Forms:
For Eg:
Table_Student
Name Subject-ID Room-Number
Table_Class Room
Subject-ID Subject-Desc Room-Number
was this your question?
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"§Chrissi§" wrote:

>|||> You can have that kind of relationship but it violates 2nd and 3rd Normal
Not necessarily. Suppose we have the following
Create Table Employees
(
Id Int Primary Key
, LastName VarChar(25)
, FirstName VarChar(25)
)
Create Table ManagerStaff
(
Id Int Primary Key
, ManagerId Int References Employee(Id)
, SubordinateId Int References Employee(Id)
, StartDate DateTime
, EndDate DateTime
)
I have two relationships to the Employees table yes this is still normalized
to
third normal form.
Thomas

relationship between tables

I need to create a relationship between 3 tables,say x,y,z. y and z being th
e
parent tables to x. there's an id field in all the 3 tables. i need to creat
e
a relationship between these 3 tables such that the id field in x is related
to either y or z. in other words, sometimes the id field in table y should
act as the primary key constraint and some other times the id field in table
z should act as the primary key constraint.
Is it possible? if so,how?
Thanks!"HP" <HP@.discussions.microsoft.com> wrote in message
news:C58E1008-C4C2-47A3-B6CE-396BFE08AE27@.microsoft.com...
>I need to create a relationship between 3 tables,say x,y,z. y and z being
>the
> parent tables to x. there's an id field in all the 3 tables. i need to
> create
> a relationship between these 3 tables such that the id field in x is
> related
> to either y or z. in other words, sometimes the id field in table y should
> act as the primary key constraint and some other times the id field in
> table
> z should act as the primary key constraint.
> Is it possible? if so,how?
> Thanks!
Gakkk!
While you could probably figure out a way to do this, why in the world do
you want to break normalization rules like this.
Bad design with tend to lead to poor performance and a host of
maintainability issues. Relational design and normalization is based on
proven mathematical formulas. Violate the formulas and you bring integrity
issues in to the system.
Rick Sawtell
MCT, MCSD, MCDBA|||Seems like you are missing a table. Create a fourth table to act as the
parent of all the other three.
David Portas
SQL Server MVP
--|||No it is not. A foreign key references one other table. Any other method
for referencing other than DRI is a kluge.
You should have y,z, x1 and x2, where y is the parent of x1 and z is the
parent of x2.
This is not redundant data, by the way. The x's are separate entities,
each describing similar "child" data of separate "parent" entities.
HP wrote:

>I need to create a relationship between 3 tables,say x,y,z. y and z being t
he
>parent tables to x. there's an id field in all the 3 tables. i need to crea
te
>a relationship between these 3 tables such that the id field in x is relate
d
>to either y or z. in other words, sometimes the id field in table y should
>act as the primary key constraint and some other times the id field in tabl
e
>z should act as the primary key constraint.
>Is it possible? if so,how?
>Thanks!
>

Relationship across databases

Hi,
Is there anyway I can create a relationship across different databases or
even show it on a diagram?
Thanks
Ed> Is there anyway I can create a relationship across different databases or
> even show it on a diagram?
No. The RI across diff dbs can be implemented, in sql server, using triggers
.
AMB
"Ed" wrote:

> Hi,
> Is there anyway I can create a relationship across different databases o
r
> even show it on a diagram?
> Thanks
> Ed

Relationship - Integrity Check - Burden on SQL

Dear Experts
While doing a Package i need to create a lot of relations for integrity
check. Am putting a burden on SQL Server for doing the hard work!
Question
Somewhere i read that it is better to do the integrity check from the front
end itself so that perfomances are not affected with many relationships in
force. What is the real style? or the right way to do? I am using MSDE 7.0
with VB
Thanks for your help
Manish Sawjiani
Three Cheers to Technet for the Help!
hi manish
Performing Integrity check from front-end is not a good practice. You will
be increasing
database hits by doing so and there by incereasing the network traffic.
This will definetely brings down the performance of your application.
Database Management Systems are designed to hold take database related loads.
If you perform integrity check in the front end then the integrity is
restricted.
i.e the check is done when you perform operations from frontend only, if
some one tries to
add data in the backend, the check is not performed and you will end up
having invalid data.
so dont wory and use Integrity check at backend itself
hope this answers your question. Is there anything that you would like to know
thanks and regards
Chandra
"Manish Sawjiani" wrote:

> Dear Experts
> While doing a Package i need to create a lot of relations for integrity
> check. Am putting a burden on SQL Server for doing the hard work!
> Question
> Somewhere i read that it is better to do the integrity check from the front
> end itself so that perfomances are not affected with many relationships in
> force. What is the real style? or the right way to do? I am using MSDE 7.0
> with VB
> Thanks for your help
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Relationship - Integrity Check - Burden on SQL

Dear Experts
While doing a Package i need to create a lot of relations for integrity
check. Am putting a burden on SQL Server for doing the hard work!
Question
Somewhere i read that it is better to do the integrity check from the front
end itself so that perfomances are not affected with many relationships in
force. What is the real style? or the right way to do? I am using MSDE 7.0
with VB
Thanks for your help
Manish Sawjiani
--
Three Cheers to Technet for the Help!hi manish
Performing Integrity check from front-end is not a good practice. You will
be increasing
database hits by doing so and there by incereasing the network traffic.
This will definetely brings down the performance of your application.
Database Management Systems are designed to hold take database related loads.
If you perform integrity check in the front end then the integrity is
restricted.
i.e the check is done when you perform operations from frontend only, if
some one tries to
add data in the backend, the check is not performed and you will end up
having invalid data.
so dont wory and use Integrity check at backend itself
hope this answers your question. Is there anything that you would like to know
thanks and regards
Chandra
"Manish Sawjiani" wrote:
> Dear Experts
> While doing a Package i need to create a lot of relations for integrity
> check. Am putting a burden on SQL Server for doing the hard work!
> Question
> Somewhere i read that it is better to do the integrity check from the front
> end itself so that perfomances are not affected with many relationships in
> force. What is the real style? or the right way to do? I am using MSDE 7.0
> with VB
> Thanks for your help
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!

Relationship - Integrity Check - Burden on SQL

Dear Experts
While doing a Package i need to create a lot of relations for integrity
check. Am putting a burden on SQL Server for doing the hard work!
Question
Somewhere i read that it is better to do the integrity check from the front
end itself so that perfomances are not affected with many relationships in
force. What is the real style? or the right way to do? I am using MSDE 7.0
with VB
Thanks for your help
Manish Sawjiani
Three Cheers to technet for the Help!hi manish
Performing Integrity check from front-end is not a good practice. You will
be increasing
database hits by doing so and there by incereasing the network traffic.
This will definetely brings down the performance of your application.
Database Management Systems are designed to hold take database related loads
.
If you perform integrity check in the front end then the integrity is
restricted.
i.e the check is done when you perform operations from frontend only, if
some one tries to
add data in the backend, the check is not performed and you will end up
having invalid data.
so dont wory and use Integrity check at backend itself
hope this answers your question. Is there anything that you would like to kn
ow
thanks and regards
Chandra
"Manish Sawjiani" wrote:

> Dear Experts
> While doing a Package i need to create a lot of relations for integrity
> check. Am putting a burden on SQL Server for doing the hard work!
> Question
> Somewhere i read that it is better to do the integrity check from the fron
t
> end itself so that perfomances are not affected with many relationships in
> force. What is the real style? or the right way to do? I am using MSDE 7.0
> with VB
> Thanks for your help
> Manish Sawjiani
> --
> Three Cheers to technet for the Help!

Relations between tables - contraints diagram

Hi all,
I have a big problem. I have many tables with constraints, with foreign keys. I need to create a ordered list of tables, on the top must be the basic table what has no parents, then the second level tables (those depends on the first level) the the names of third level etc.

for example:
Table A[id]
Table B[id, idc]
Table C[id, ida]
Table D[id, ida]
Table E[id, idc]

I tried it by using information_scheme but I was unsuccesfull.

The result should be:
A
C
D
B
E

Thank you,
Tom.There isn't always a relational solution to this problem, because users sometimes create non-linear (aka graphical) relations. I know... Bad user! Naughty user! No donut!

I worked up an iterative solution, but I don't have it handy. If no one else posts a solution (you still have a copy of that one Rudy?), I'll dig it up and post it, but it might be a day or three.

-PatP|||thank Pat,
I apreciate your help, I am also working now on a iterative solution, but it does not work. So if you will have a time I will be very glad to see your solution and help, three days or five, does not matter.
Thanks again.

Tom.

Relational database

Hi

I'm using VB.NET,ADO.NET in ASP.NET .

Microsoft .Net framework 1.0

Windows 2000

Visual Studio IDE

and SQLServer.

I have to create to tables as

Table1 contains ID,Name

Table2 contains ID,Marks,Foreign Key ie Primary key of the table Table1.

Give me an information how to create these two tables in SQLServer (I know how to create a table but i don't know how to create a table which includes Foreign Key.)

Then using Dataset i want to display the records as Name,Marks which are stored in two tables.

I have studied that in ADO Join query and record set object is used but it gives a problems and it is not good when we want to transfer the data between two applications or pages but dataset solves all those problems.

Give me an information about it.

Kindly help me

Thanks in advance

Regards.

To create table with Foreign key constraint using T-SQL:

create table tbl_PK (ID int primary key,name varchar(30))
go
create table tbl_FK (ID int foreign key REFERENCES tbl_PK(ID),
Marks varchar(50))

To get the joined result:

select p.name,f.marks from tbl_PK p join tbl_FK f
on p.ID=f.ID

|||

Hi

What you have given is also a join query.but i have studied that no need of join query in case of dataset but it is required for record set.

Thanks

|||To do that, please take a look a the following short movie:

http://msdn.microsoft.com/vstudio/express/media/en/AbsoluteBeginner/vb/08VB.wvx

This is tutorial movie about VWD 2005 and SQL server EE, I believe you can also apply it to VS 2005 because the similarity.

Basically what you do is you create database diagram with right clickon 'Database Diagram' under the Database explorer of your databasefilename. Then add new diagram. After that you can add all your tables,then drag the primary key to create the foreign key.

hope this will help.

Relating records with date ranges to Server Time dimension

I have a table whose records have a date range defined by a start_date and end_date. I would like to create a server time dimension so that I can relate each record in the table to a date in the time dimension when it is 'active' (i.e. the dimension date falls between the start_date and end_date inclusive). I am lost on how to accomplish this and any help is appreciated.

DW

DW, I would consider adding a column called "is_active" on the fact table. The SQL query would then include this column in the where clause. I would not limit the time dimension to just the active date range.
Vincent

Relathionship for two databases

Is it possible to create a relationship between two tables in different
databases or between a table and a view which is based on a table in another
database?
If this is not possible, is it possible to setup RI between two databases
using the triggers?
Thanks.No there no such mechanisam to do this, what you could do is to work with
Views and design them to update the databases on change, another way would
be to trigger the changes / the integrity directly on the tables.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Vik" <viktorum@.==hotmail.com==> schrieb im Newsbeitrag
news:%23s6W54DRFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Is it possible to create a relationship between two tables in different
> databases or between a table and a view which is based on a table in
> another database?
> If this is not possible, is it possible to setup RI between two databases
> using the triggers?
> Thanks.
>
>|||Yes, you have to force the RI using triggers.
AMB
"Vik" wrote:

> Is it possible to create a relationship between two tables in different
> databases or between a table and a view which is based on a table in anoth
er
> database?
> If this is not possible, is it possible to setup RI between two databases
> using the triggers?
> Thanks.
>
>