creating data for a histogram.

[chudson007]

I have a table, TableA with amongst other fields, a field for Qty.
Qty can range from 0 to 100.
How do I count the number of rows with a qty between 1 and 10, 11 and
20, 21 and 30, and so on using one SQL statement?

Regards,
Ciarán

[Erland Sommarskog]

jsfromynr (ja************ @clovertechnolo gies.com) writes:

Can we similarly replace CUBE operator in SQL Server by using simple
queries that will run on any RDBMS?

I don't use CUBE very often, so I may miss some fine detail. But the
two queries below returns the same result:

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id WITH CUBE
ORDER BY type, pub_id

SELECT type, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY type, pub_id
UNION
SELECT type, NULL, SUM(price), SUM(advance)
FROM titles
GROUP BY type
UNION
SELECT NULL, pub_id, SUM(price), SUM(advance)
FROM titles
GROUP BY pub_id
UNION
SELECT NULL, NULL, SUM(price), SUM(advance)
FROM titles
ORDER BY type, pub_id

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[jsfromynr]

Hi Erland
I am bit confused by the output produced by the Query Analyzer and
finding it bit difficult to decide which one of the following query is
faster.

In query one I am using Correlated subquery Approach and it consumes
78% of batch time when run with 2nd query but time of mere 20
micrseconds
In query two I am using functions (these functions berely takes
PolicyNumber and Endrosment No to give output and does the same query )
Approach and it consumes 22% of batch time when run with 1st query but
time of 400 micrseconds
Query 1:-
select RowId,PolicyNum ber,EndoNumber, status,SF,case when SubQ is Null
then 'No' else 'Yes' end as Lock,RCount from
(
SELECT RowId,PolicyNum ber,EndoNumber, status,SF,
(select status from InProcessData05 2005MstM WHERE status in ('pen') and
IP.PolicyNumber =PolicyNumber and IP.EndoNumber=E ndoNumber) as subQ,
(select count(*) from InProcessData05 2005MstM WHERE status in
('pen','cur') and IP.PolicyNumber =PolicyNumber and
IP.EndoNumber=E ndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData05 2005MstM IP
WHERE status in ('cur','pen')
) X
ORDER BY PolicyNumber

select getdate()
SELECT
RowId,PolicyNum ber,EndoNumber, status,SF,dbo.f nTryGetPolicyCo unt(PolicyNumbe r,EndoNumber)
as RCount,
dbo.fnTryGetPol icyLock(PolicyN umber,EndoNumbe r) as Lock
--(select status from InProcessData05 2005MstM WHERE status in ('pen')
and IP.PolicyNumber =PolicyNumber and IP.EndoNumber=E ndoNumber) as subQ,
--(select count(*) from InProcessData05 2005MstM WHERE status in
('pen','cur') and IP.PolicyNumber =PolicyNumber and
IP.EndoNumber=E ndoNumber) as RCount
--,case when SubQ is Null then 'No' else 'Yes' end as Lock
FROM InProcessData05 2005MstM
WHERE status in ('cur','pen')
order by PolicyNumber
select getdate()
create function fnTryGetPolicyC ount(@p varchar(16),@e varchar(3))
returns int
as
begin
return (select count(*) from InProcessData05 2005MstM WHERE status in
('pen','cur') and PolicyNumber=@p and EndoNumber=@e)
end

create function fnTryGetPolicyL ock(@p varchar(16),@e varchar(3))
returns varchar(3)
as
begin
declare @Lock varchar(3)
select @Lock=status from InProcessData05 2005MstM WHERE status in
('pen') and PolicyNumber=@p and EndoNumber=@E
if @Lock is null
set @Lock='No'
else
set @Lock='Yes'

return (@Lock)
--(select status from InProcessData05 2005MstM WHERE status in ('pen')
and PolicyNumber=@p and EndoNumber=@E)
end

----------------------------------------------------------
Jatinder

[Erland Sommarskog]

jsfromynr (ja************ @clovertechnolo gies.com) writes:

I am bit confused by the output produced by the Query Analyzer and
finding it bit difficult to decide which one of the following query is
faster.

In query one I am using Correlated subquery Approach and it consumes
78% of batch time when run with 2nd query but time of mere 20
micrseconds
In query two I am using functions (these functions berely takes
PolicyNumber and Endrosment No to give output and does the same query )
Approach and it consumes 22% of batch time when run with 1st query but
time of 400 micrseconds

The difference in estimate may be because the function is not considered.

Anyway, the one way to benchmark queries is this:

DECLARE @d datetime, @tookms int
SELECT @d = getdate()
-- run query here
SELECT @tookms = datediff(ms, @d, getdate())
PRINT 'This query took ' + ltrim(str(@took ms) + ' ms to run.'

You need to consider the effect of the cache. If the two queries operates
on the same data, the easiest may be to run the queries several times
and discard the first result. You can also run DBCC DROPCLEANBUFFER S to
clean the cache, but that affects the entire server.

Also, beware that datetime has a resolution of 3.33 ms. For the
measurement method above, I have never seen any value between 0 and
13 ms. I consider values below 50 ms to be too inaccurate to be
taken as a significant. 400 ms is certainly significant.

Note: above you talk "microsecon ds". I assume this is a typo for
"millisecon ds".

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[jsfromynr]

Hi Erland,

Thanks for your answer and time.
Sorry for the typo error.
I can be wrong in my assumption buut isn't it that the two queries are
working in simliar fashion. Both are taking a value(or two) passing it
to inner corelated query (funtion) and getting the result.
Waiting for your reply.
Jatinder

[Erland Sommarskog]

jsfromynr (ja************ @clovertechnolo gies.com) writes:

Thanks for your answer and time.
Sorry for the typo error.
I can be wrong in my assumption buut isn't it that the two queries are
working in simliar fashion. Both are taking a value(or two) passing it
to inner corelated query (funtion) and getting the result.

Just because two queries logically are the same, that does not mean that
performance is. There is quite some overhead with calls to saclar user-
defined functions. Also, when you stuff a subquery into a scalar function,
all the optimizer sees is a call, it does not see the contents of rhe
UDF, so it cannot take any shortcuts.

Table-valued functions are different. Particularly inline functions. Table-
valued inline functions are really just macros, and the query text is
pasted into the query, so the optimizer can rearrange as it likes.

As for the estimates you saw in Query Analyzer, they are just estimates, and
I would not pay too much attention on them.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[jsfromynr]

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]

jsfromynr (ja************ @clovertechnolo gies.com) writes:

I have yet another question (query ) .
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.
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.
...

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, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp