creating data for a histogram.

[jsfromynr]

Re: creating data for a histogram.

Hi Erland,

Thanks Again for your time .

Explaination is good. So may I consider that the UDF will always be
little slower because the Query Optimizer can never arrange it for
optimization. but using function make query more manageable

Please correct me if my assumption is wrong.

I have yet another question (query ) .
I have two tables
One empmast which store emp current designation
Other promotion table which store the promotions of an employee during
his service.It stores the information of employee designation promotion
date.

Empmast(empid int primary key,desigid int references desigmast
,............)
PromotionDtls(e mpid int references Empmast,promota tedTo int references
desigmast, promotedFrom int references Desigmast,DateO fPromotion
smalldatetime)

EmpMast
empid desigid (current designation of employee)
1 3 ............... ............... .
2 1 ............... .........

PromotionDtls
empid promotedTo PromotedFrom effectiveDate
1 2 1 1-jan-2003
1 3 2 2-dec-2003
.............


Now I wish to use the designation Id in a query
such that if the employee data exists in Promotion Table the promotedTo
should be picked according to Effectivedate
otherwise the Empmast designation
e.g If I say desigId of employee having empid 1 on date 2-jun-2003 then
it should be desigId 2
I did this using isnull but I wish to find a better method.

select isnull( ( select top 1 promotedTo from promotionDtls where
empid=1 and effectivedate<' anygivendate' order by effectivedate desc )
, (select desigid from empmast where empid=1) )

It did give the result but looking for better method to solve this.

With regards
Jatinder Singh

[Erland Sommarskog]

Re: creating data for a histogram.

jsfromynr (jatinder.singh @clovertechnolo gies.com) writes:[color=blue]
> I have yet another question (query ) .[/color]

Sorry for not coming back to you earlier, but I had limited time for
some days to read the posts in the newsgroups, so I deferred the
difficult stuff until later.

A general advice is that it's better to post a new problem to a new
thread. Then other people might be more keen to answer it.
[color=blue]
> I have two tables
> One empmast which store emp current designation
> Other promotion table which store the promotions of an employee during
> his service.It stores the information of employee designation promotion
> date.
> ...[/color]

I've now looked at the problem again, but I still could not really
understand what you are looking for. Since I don't like guessing, I
answer with the standard suggestion that you include:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

The first two makes it simple to copy and paste into Query Analyzer,
and the last makes it possible to actually produce a tested query, and
also helps to clarify what you are looking for.

It's not only that I'm lazy - neither do I like guessing.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp