Subquery example

Question

Post reply

Subquery example

kdnicholsster

Ten Centuries

Points: 1152
More actions
December 29, 2011 at 11:36 am

#248910

I have the following query:
SELECT Sum(SystemWorkItemType,System_WorkItemType DWI)
FROM DimPerson DP, DimWorkItem DWI
WHERE DWI.System_Title like '%Accept%'
GROUP BY DP.Name,
(SELECT DWI.System_Id, DWI.System_WorkItemType, DWI.System_Title, DP.Name, DWI.System_State, DI.IterationPath
from DimWorkItem DWI
inner join DimPerson DP
on DWI.System_AssignedTo__PersonSK = dp.PersonSK
inner join DimIteration DI
on DWI.IterationSK = DI.IterationSK
and System_Id in (
select TargetWorkItemID from FactWorkItemLinkHistory -- Links
where SourceWorkItemID in
(SELECT DWI.System_Id FROM DimWorkItem DWI, DimIteration DI -- All Work Items
where DWI.IterationSK = DI.IterationSK
--and DWI.System_Id in (7492, 7512, 7861, 10361,9259,7622,7654,7722,9340,10061,10071,10315,10430,10602,10629,9308)
and DWI.System_WorkItemType LIKE 'Bug'
--and DWI.System_RevisedDate > '1/1/9991'
and DI.IterationPath like '\xxxxx%'
and DI.IterationPath not like '\xxxxx\(_Bug Migration)%'
and System_State not in ('Done', 'Removed'))
)
and DWI.System_WorkItemType = 'Task'
and DWI.System_RevisedDate > '1/1/9991'
and DWI.System_Title like '%Accept%'
and DWI.System_State = 'In Progress'
and DWI.System_ChangedDate > '12/15/2011'
and DWI.System_ChangedDate < '1/12/2012'
)
I am trying to get a SUBQUERY total on Tasks where they are associated with names.
This is a section of code that does work, so I am trying to break it down in pieces.
Thanks for any and all help.
Kurt

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

kdnicholsster Ten Centuries Points: 1152 More actions · Answer 1

OK now I have this:

SELECT

DP.Name [DPName],

SUM(CASE WHEN DWI.System_Title LIKE '%Accept%'

THEN 1

ELSE 0

END) [Count]

FROM DimPerson DP

JOIN DimWorkItem DWI JOIN DimIteration

GROUP BY DP.Name, DWI.System_Id, DWI.System_WorkItemType, DWI.System_Title, DP.Name, DWI.System_State, DI.IterationPath,

(SELECT DWI.System_Id, DWI.System_WorkItemType, DWI.System_Title, DP.Name, DWI.System_State, DI.IterationPath

from DimWorkItem DWI

inner join DimPerson DP

on DWI.System_AssignedTo__PersonSK = dp.PersonSK

inner join DimIteration DI

on DWI.IterationSK = DI.IterationSK

and System_Id in (

select TargetWorkItemID from FactWorkItemLinkHistory -- Links

where SourceWorkItemID in

(SELECT DWI.System_Id FROM DimWorkItem DWI, DimIteration DI -- All Work Items

where DWI.IterationSK = DI.IterationSK

--and DWI.System_Id in (7492, 7512, 7861, 10361,9259,7622,7654,7722,9340,10061,10071,10315,10430,10602,10629,9308)

and DWI.System_WorkItemType LIKE 'Bug'

--and DWI.System_RevisedDate > '1/1/9991'

and DI.IterationPath like '\xxxx%'

and DI.IterationPath not like '\xxxxx\(_Bug Migration)%'

and System_State not in ('Done', 'Removed'))

)

and DWI.System_WorkItemType = 'Task'

and DWI.System_RevisedDate > '1/1/9991'

and DWI.System_Title like '%Accept%'

and DWI.System_State = 'In Progress'

and DWI.System_ChangedDate > '12/15/2011'

and DWI.System_ChangedDate < '1/12/2012'

)

I am getting an error on the GROUP BY

Any ideas?

Kurt

Lowell SSC Guru Points: 323518 More actions · Answer 2

you have this line

JOIN DimIteration

that is missing the join criteria..

ON sometable.column = DimIteration.column

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kdnicholsster Ten Centuries Points: 1152 More actions · Answer 3

Hello Lowell,

OK now I added the table and collumn

SELECT

DP.Name [DPName],

SUM(CASE WHEN DWI.System_Title LIKE '%Accept%'

THEN 1

ELSE 0

END) [Count]

FROM DimPerson DP, DimWorkItem DWI, DimIteration DI,

JOIN DWI.System_WorkItemType ON System_WorkItemType = 'Task',

GROUP BY DWI.System_Id, DWI.System_WorkItemType, DWI.System_Title, DP.Name, DWI.System_State, DI.IterationPath,

(SELECT DWI.System_Id, DWI.System_WorkItemType, DWI.System_Title, DP.Name, DWI.System_State, DI.IterationPath

I now get a red line under JOIN DWI.System_WorkItemType ON System_WorkItemType = 'Task',

And the GROUP

What did I do wrong here?

Kurt

David Moutray SSCertifiable Points: 5956 More actions · Answer 4

Using the old (and eventually to be deprecated) ANSI-92 join syntax does not help. You should get used to the new syntax.

Old Syntax :crying:

FROM Table1, Table2

WHERE Table1.ExampleID = Table2.ExampleID

New and Improved Syntax 🙂

FROM Table1

JOIN Table2

ON Table1.ExampleID = Table2.ExampleID

LutzM SSC Guru Points: 107049 More actions · Answer 5

... especially if the old syntax is lacking the WHERE clause in the given example.... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]