Another SQL Query!

Dear All,
I have two tables in my database, part of a tennis league results and
fixtures website I am putting together.
Results
id fixture_id home_team_rubbers away_team_rubbers
1 229 2 2
2 253 2 2
3 265 1 3
4 230 2 2
Fixtures Table
id home_team_id away_team_id match_date
229 20 26 2006-05-03
230 20 29 2006-05-31
231 20 45 2006-05-17
232 20 78 2006-06-28
233 20 79 2006-07-26
234 20 89 2006-07-12
235 26 20 2006-06-20
236 26 29 2006-07-25
237 26 45 2006-07-11
238 26 78 2006-05-16
239 26 79 2006-06-13
240 26 89 2006-05-30
253 78 20 2006-05-09
265 89 20 2006-05-23
What I want to do it list all the fixture (based on fixure id) that
don't have a result associated with the fixture through an SQL query.
The resulting output from the query that I desire is the following.
Query Output
fixture_id
231
232
233
234
235
236
237
238
239
240
Any ideas how I can do this?
Cheers,
SimonTry:
select
f.id
from
Fixtures f
where not exists
(
select
*
from
Results r
where
r.fixture_id = f.id
)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<simon.stockton@.baesystems.com> wrote in message
news:1149375877.441866.70510@.i40g2000cwc.googlegroups.com...
Dear All,
I have two tables in my database, part of a tennis league results and
fixtures website I am putting together.
Results
id fixture_id home_team_rubbers away_team_rubbers
1 229 2 2
2 253 2 2
3 265 1 3
4 230 2 2
Fixtures Table
id home_team_id away_team_id match_date
229 20 26 2006-05-03
230 20 29 2006-05-31
231 20 45 2006-05-17
232 20 78 2006-06-28
233 20 79 2006-07-26
234 20 89 2006-07-12
235 26 20 2006-06-20
236 26 29 2006-07-25
237 26 45 2006-07-11
238 26 78 2006-05-16
239 26 79 2006-06-13
240 26 89 2006-05-30
253 78 20 2006-05-09
265 89 20 2006-05-23
What I want to do it list all the fixture (based on fixure id) that
don't have a result associated with the fixture through an SQL query.
The resulting output from the query that I desire is the following.
Query Output
fixture_id
231
232
233
234
235
236
237
238
239
240
Any ideas how I can do this?
Cheers,
Simon|||Select f.id
From Fixtures f
Left Join Results r on f.id = r.fixture_id
Where r.id Is Null
Order By f.id
Tom
<simon.stockton@.baesystems.com> wrote in message
news:1149375877.441866.70510@.i40g2000cwc.googlegroups.com...
> Dear All,
> I have two tables in my database, part of a tennis league results and
> fixtures website I am putting together.
> Results
> id fixture_id home_team_rubbers away_team_rubbers
> 1 229 2 2
> 2 253 2 2
> 3 265 1 3
> 4 230 2 2
> Fixtures Table
> id home_team_id away_team_id match_date
> 229 20 26 2006-05-03
> 230 20 29 2006-05-31
> 231 20 45 2006-05-17
> 232 20 78 2006-06-28
> 233 20 79 2006-07-26
> 234 20 89 2006-07-12
> 235 26 20 2006-06-20
> 236 26 29 2006-07-25
> 237 26 45 2006-07-11
> 238 26 78 2006-05-16
> 239 26 79 2006-06-13
> 240 26 89 2006-05-30
> 253 78 20 2006-05-09
> 265 89 20 2006-05-23
> What I want to do it list all the fixture (based on fixure id) that
> don't have a result associated with the fixture through an SQL query.
> The resulting output from the query that I desire is the following.
> Query Output
> fixture_id
> 231
> 232
> 233
> 234
> 235
> 236
> 237
> 238
> 239
> 240
> Any ideas how I can do this?
> Cheers,
> Simon
>|||Thanks guys, much appreciated!

Announcing the Analysis Services Stored Procedure Project

A few months ago, a few community-spririted Analysis Services guys (including me) got together to create some example Analysis Services stored procedures. I'm happy to announce that beta 1 of our project is now available to download here:
http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures
The idea was to create a set of useful extensions to MDX to help solve common problems and at the same time provide some example source code to help people writing their own stored procedures. Please take a look and tell us what you think!

Very Nice!

How about a function that takes care of divide-by-zero and returns NULL if the denominator is 0? Ie. ReturnDivide(division). That would clean up a lot of iif mdx.

|||

We discussed this exact problem, but Mosha explained that using a sproc in this way would do more harm than good. Basically, the problem is that there's no way of marking a sproc as being deterministic (ie will always return the same result for the same cell) and so that means that if you use a sproc in a calculated member then the value returned by that calculation will never be cached. As a result, it's probably better to use IIF instead so that subsequent requests for the result returned by a calculation for any given cell in the cube will be returned from the cache.

To answer your other question about IIF, if <mdxstatement> is a calculated measure whose result can be cached, then no, it will only be executed once and the second time it's evaluated the value will be returned from the cache. So it's good idea to create a calculated measure to hold the value of <mdxstatement> even if you don't intend to display the result to the user and set its Visible property to False.

HTH,

Chris

|||

Just so I understand:
You are saying that the following MDX script will make the server calculate [measures].[summation] once in the scope iif statement:

Create Member [Measures].[Summation] AS

Aggregate({[DimMember1], [DimMember2]});

Scope ([DimMemberX]);

this = iif([Measures].[Summation] = 0, NULL, [SomeSet] / [Mesures].[Summation];

End Scope;

Whereas this statement will make it calculate it twice:

Scope ([DimMemberX]);

this = iif(aggregate({[Dimmember1], [Dimmember2]}) = 0, NULL, [Someset] / aggregate([DimMember1], [DimMember2]));

End Scope;

|||

Yes, that's what I understand.

Chris

Analysis Services and Proclarity

We have following environment setup: SQL Server 2005, SSAS 2005 and Proclarity working together to provide some business intelligence.

I have login problems with Proclarity Dashboar server.

What happens is after user logs in into the dashboard server and leaves the browser (IE6) idle for some period of time, the user gets logged out (due to session time out). Once logged out, the user can't log back in as themselves. Now if the user logins with some other userid and logs out properly, then he/she can log back in again as themselves.

Dashboard uses the windows userid/password for logins.

Anybodys help would be greatly appriciated.

Thanks

Giri

Looks like you are using basic authentication for the ProClarity Dashboard server? You have the option to use windows integrated security!

My advice is to contact http://www.proclarity.com/services/support.asp about this issue. You will need a valid account for this. As a customer you will also have access to information on two ProClarity communities. I have seen a lot of discussions and recommendations of how to set up windows integrated security(Active Directory) with ProClarity Dashboard Server 6.2

It is impossible to write all the details you will have to know here.

HTH

Thomas Ivarsson