I suppose you could define a cursor over "select distinct Type from table1", use this to build a string with the SQL statement you are after, and then dynamically execute this...

Could you post the definition of FloatToStr as well?

Alternatively, use the following:

select

user_id,

opdate,

sum(case when Type = 'HIV' then 1 else 0 end) as HIV_Count,

sum(case when Type = 'HBs' then 1 else 0 end) as HBS_Count

from table1

group by user_id, opdate

I am still not sure I understand you correctly, but as long as the outer transaction can be rolled back, I don't think you can avoid having a lock on...

Surely, there are many opinions on column names, and you shouldn't redesign a whole system based on what I think But I would...

Try

Select * from orders o left join [order details] od

on o.orderid = od.orderid

where od.orderid is null

Peet, thanks for providing a table definition and sample data that I can copy and paste into my query analyzer And thanks for...

I suppose the two tables have the same definition. You could of course execute "select * from ? order by ?", save results to files and make a file comparison.

You...

Thanks, VladRUS.ca... I am surprised that I am falling that much behind  I thought David's solution was slow...

The negative amount only came up because I wanted to say that your query might produce a different output. As I said, this may not be the case, I don't...

You might be able to achieve what you want if you use save points. The code below may not be nice, but it can be used to begin, commit and...

Scott, Mark has stated that he wants 'OK' back. Furthermore, he has stated quite clearly what the condition on past due amounts should be. You may of course start a discussion of...

I suppose you are thinking of something like

CREATE TABLE #Numbers( Number varchar(100), sort varchar(1000) null, dot1 int null, dot2 int null)

INSERT INTO #Numbers (Number) VALUES( '1')

INSERT INTO #Numbers (Number)...

You certainly have some points there and your query is definitely simpler

I am not sure your query meets condition 2 of the original...

Very nice, I'm impressed - I will store this trick for later use