Help needed making a tidy join query

(Simplfied for question asking purposes)
I have a help desk system with support categories and assignable
employees. I would like to run a query that will pull back a list of
all categories along with a count of unassigned tickets. So my tables
are:
tblSupportCategories
CategoryID int
CategoryName varchar(50)
tblSupportTickets
TicketID int
CategoryID int
SupportContact varchar(15) (default is null)
if I say
select tsc.categoryid,count(*)
from tblsupportcategories tsc
left outer join tblsupporttickets tst on tsc.categoryid =
tst.categoryid and tst.supportcontact is null
what I get is a list of all the support categories along with a non
zero number whereas what I want is something that looks like
Website 0
Office 2
E-Mail 1
Warranty 3
I suspect that an elegant query can be written to query this back in
one statement but I am at a loss as to how to do it. If anyone could
help, I certainly appreciate it.
TIAAssuming you really are using SQL Server (layout smells like Access) you cou
ld
do the following:
Select TSC.CategoryId, Count([TST.TicketId])
From tblSupportCategories As TSC
Left Join tblSupportTickets As TST
On TSC.CategoryId = TST.CategoryId
And TST.SupportContact Is Null
I'm assuming that TicketId is the PK of the tickets table (you didn't provid
e
DDL so I can't tell for sure).
Note that the criteria is in the Join clause not the Where clause. In this w
ay,
the tickets list will be filtered *before* it is joined to the categories ta
ble.
Also, by using Count([TST.TicketId]), I'm counting all ticket values where
support contact was null.
Thomas
"Matthew Speed" <mspeed@.mspeed.net> wrote in message
news:50tl511ophieh5k8v1i1haiv7ls6ee4gi7@.
4ax.com...
> (Simplfied for question asking purposes)
> I have a help desk system with support categories and assignable
> employees. I would like to run a query that will pull back a list of
> all categories along with a count of unassigned tickets. So my tables
> are:
> tblSupportCategories
> CategoryID int
> CategoryName varchar(50)
> tblSupportTickets
> TicketID int
> CategoryID int
> SupportContact varchar(15) (default is null)
> if I say
> select tsc.categoryid,count(*)
> from tblsupportcategories tsc
> left outer join tblsupporttickets tst on tsc.categoryid =
> tst.categoryid and tst.supportcontact is null
>
> what I get is a list of all the support categories along with a non
> zero number whereas what I want is something that looks like
> Website 0
> Office 2
> E-Mail 1
> Warranty 3
> I suspect that an elegant query can be written to query this back in
> one statement but I am at a loss as to how to do it. If anyone could
> help, I certainly appreciate it.
> TIA
>|||Matthew Speed wrote:
> (Simplfied for question asking purposes)
> I have a help desk system with support categories and assignable
> employees. I would like to run a query that will pull back a list of
> all categories along with a count of unassigned tickets. So my tables
> are:
> tblSupportCategories
> CategoryID int
> CategoryName varchar(50)
> tblSupportTickets
> TicketID int
> CategoryID int
> SupportContact varchar(15) (default is null)
> if I say
> select tsc.categoryid,count(*)
> from tblsupportcategories tsc
> left outer join tblsupporttickets tst on tsc.categoryid =
> tst.categoryid and tst.supportcontact is null
>
> what I get is a list of all the support categories along with a non
> zero number whereas what I want is something that looks like
You do? That's not even a legal query ... you left out the Group By clause

> Website 0
> Office 2
> E-Mail 1
> Warranty 3
>
Sample data would have been helpful ... However, this should get you what
you want:
select 1 categoryid, 'Website' categoryname into #categories
union all
select 2,'Office'
union all
select 3,'Email'
union all
select 4,'Warranty'
select 1 ticketid,1 categoryid,'test1' supportcontact into #tickets
union all
select 2,2,'test'
union all
select 3,2,null
union all
select 4,3,null
union all
select 5,4,null
union all
select 6,4,null
union all
select 7,4,null
union all
select 8,2,null
select tsc.categoryid,coalesce(ticketcount,0)
unassignedtickets
from #categories tsc
left outer join (
select categoryid,count(*) ticketcount from
#tickets
where supportcontact is null
group by categoryid
) tst on tsc.categoryid =
tst.categoryid
drop table #categories
drop table #tickets
Bob Barrows
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||On Mon, 11 Apr 2005 18:53:22 -0400, "Bob Barrows [MVP]"
<reb01501@.NOyahoo.SPAMcom> wrote:

>Matthew Speed wrote:
>You do? That's not even a legal query ... you left out the Group By clause
>
That is not my actual query. I was trying to include a simplified
version of my real query and left out the group by clause in my
rewrite.|||
>Assuming you really are using SQL Server (layout smells like Access)
I have a SQL 7.0 MCDBA that I am currently upgrading to SQL 2K, thank
you very much. I haven't used Access to query anything since Access
97.

>Select TSC.CategoryId, Count([TST.TicketId])
>From tblSupportCategories As TSC
> Left Join tblSupportTickets As TST
> On TSC.CategoryId = TST.CategoryId
> And TST.SupportContact Is Null
>Note that the criteria is in the Join clause not the Where clause. In this
way,
>the tickets list will be filtered *before* it is joined to the categories table.[/c
olor]
I had this part.
>Also, by using Count([TST.TicketId]), I'm counting all ticket values where
>support contact was null.
>
This is what got me. I was trying to count(*). My original thinking
was that by doing a left outer join with the null clause in the join I
would get back a zero unless rows were matched on the right side of
the join. Unfortunately, the outer also got me a one count on every
left side row that didn't match on the right. Obviously that was
wrong but your solution worked great.
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]

)