help on comparing and modifying a resultset or table

HI everybody need help badly on the following

have to recordsets with the following with the following SQL

SELECT place, count(minus)as countminus
order by place
group by place

THE RESULT WOULD BE

PLACE countminus

ABC 10
DEF 50
HIJ 5
KLM 2
MNO 0

MY OTHER QUERY IS
SELECT plus, count(plus) as countplus
GROUP BY plus
ORDER BY plus

THE RESULT WOULD BE:

PLUS COUNTPLUS

ABC 10
DEF 20

my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS...

with this condition..

I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF...

THE RESULT WOULD BE THIS....

PLACE countminus COUNTPLUS

ABC 10 10
DEF 50 20
HIJ 5 0
KLM 2 0
MNO 0 0

I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYSPLEASE STOP THINKING ABOUT LOOPS!!!! SQL DOESN'T WORK THAT WAY!!!!
select m.place
, m.countminus
, coalesce(p.countplus,0) as countplus
from (
select place
, count(minus) as countminus
from sometable1
group by place
) as m
left outer
join (
select plus
, count(plus) as countplus
from sometable2
group by plus
) as p
on p.plus = m.place|||thanks so much it solved my problem sorry for the loop frase i mentioned...

again thanks

Help modifying MDX - Allow for Ownership Change in Team System

I am asking for your help solving a problem. I am very new to MDX and OLAP in general.

Here is my statement of the problem: Changing ownership of Bugs in Team System results in incorrect reporting of Completed Work.

I have crafted the following MDX , which works fine if Ownership of a Bug or Work Item is not changed.

=================================================================================

WITH

MEMBER [Measures].[Completed Work On Period Start] AS

(

(STRTOMember(@.prmStartDate)),

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed Work On Period End] AS

(

(STRTOMember(@.prmEndDate)),

[Measures].[Microsoft_VSTS_Scheduling_CompletedWork]

)

MEMBER [Measures].[Completed_Work] AS

(

[Measures].[Completed Work On Period End] - [Measures].[Completed Work On Period Start]

)

SELECT NON EMPTY

(

FILTER

(

NONEMPTYCROSSJOIN

(

[Assigned To].[Person].[Person],

[Work Item].[System_Id].[System_Id],

[Work Item].[System_Title].[System_Title],

[Work Item].[System_WorkItemType].[System_WorkItemType],

[Measures].[Current Work Item Count],

4

),

[Measures].[Completed_Work] > 0

)

)

ON ROWS,

NON EMPTY

{

[Measures].[Work Item Url],

[Measures].[Completed_Work]

}

ON COLUMNS

FROM

[Team System]

=================================================================================

The problem arises when anyone changes ownership of a bug (or work item).

I have used [Changed By].[Person].[Person] and [Assigned To].[Person].[Person] as the first element in the NONEMPTYCROSSJOIN, and neither return the proper result.

BTW: I know that NONEMPTYCROSSJOIN is depreciated, but I am unsure how to replace it.

Thanks in advance.

Hi Doug:

At first glance your MDX look sound. No obvious reason why changing Work Item ownership results in the wrong answer. Which leads me to think the issue is in the dimensional model, or fact table. Not in the MDX of your query. As an aside, you asked how to emulate the NONEMPTYCROSSJOIN with a different statement. I created an example below with two equivalent statements. One uses the NONEMPTYCROSSJOIN, and the other uses a FILTER() with a CROSSJOIN to give the same results. The samples work on the Adventure Works database.

To help track down the root of your changing ownership problem it will help to see the sample fact table and query result before the problem is created and after the problem is created.

Hope this helps - PGoldy

SELECT

{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS

,NONEMPTYCROSSJOIN([Customer].[Customer Geography].[France].Children

,

{[Date].[Calendar].[Calendar Year].&[2004]}, [Measures].[Internet Sales Amount], 2)

ON ROWS

FROM [Adventure Works]

WHERE (

[Product].[Product Categories].[Category].&[3]

)

////

SELECT

{[Measures].[Internet Sales Amount], [Measures].[Internet Tax Amount]} ON COLUMNS

,FILTER(CROSSJOIN([Customer].[Customer Geography].[France].Children

,

{[Date].[Calendar].[Calendar Year].&[2004]}), [Measures].[Internet Sales Amount] <> 0)

ON ROWS

FROM [Adventure Works]

WHERE (

[Product].[Product Categories].[Category].&[3]

)