afternoon?? you luck bast@rd... it's 10H30 am here .

HTH.

Nice avatar btw.. did you make it yourself?

HTH.

I had my fun imagining some of you guys scratching your head on this one like I did.

Still it's nice to see how 2 different set based approach can have...

SELECT COUNT(Distinct O1.OrderID), COUNT(O2.ItemID)

FROM dbo.Orders O1

--inner join flushs orders with no item

INNER JOIN dbo.OrderItems O2 ON O1.OrderID = O2.OrderID

where order_filled_date IS NULL

--not sure, but you might need this

--group...

try count(CompetencyCode_ID) instead of count(*).

I thaught of that one too.. but he said he didn't want to have orders with no items (how can that be, that's another questions

BTW, the function is meant to only generate the items list. It cannot be modified to return the quantity (even if it could count 'em easily). That's why...

Select count(distinct OrderID) as Orders, count(*) as Items from dbo.Order_item

(assuming that both columns are non-nullable)

Try this modified select :

Select O.Name, O.id, count(*) as Total, dbo.ListTableColumns(O.id) from dbo.SysObjects O inner join dbo.SysColumns C on O.id = C.id group by O.ID, O.Name

Can you post the code?

NP, you just have a better chance at an expert picking up your request if you put it there.

New thread started HERE

That's a nice idea, but if I remember correctly, his boss wants to see all the lines, and all the differences in all the columns (and leave the good ones...

Maybe there's an option to goto to next page (after completion) at the bottom section of that grouping (still referring to my access background).

well the straight solution, me thinks, is the create a linked server and go with insert select where not exists.