Subquery assistance? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery assistance?

Author

Topic

Rovastar
Starting Member

38 Posts

Posted - 2007-11-14 : 12:13:01

Greeting folks.

Not been on the forums for a bit as moved away from SQL Server in my last contract. More of an web admin that a query writer and need a little assistance if possible.

I have 2 tables Deal and Organistation UNit I want deal money stuff from one date(s) in the deal table and the name from the OrgUnit table.

This I got to work no problem.

Select OrganisationUnit.Name, SUM(TotalContribution) AS TOTAL from Deal JOIN OrganisationUnit
ON SubmittingOrgUnitID = OrganisationUnit.OrganisationUnitId
WHERE TradingDate >= '01 Nov 2007'
GROUP BY OrganisationUnit.Name
ORDER BY SUM(TotalContribution) DESC

However some of the names are "null" as orgunit I find out broken down into 4 more sections per OrgUnitID called OrgLevelID.

For where 1=Group, 2=Company, 3=Office, 4=Contact

Now I only need say the company so I need somewhere in the query

OrganisationUnit.OrganisationLevelID = 2

But I am stuggling to put it in a query.

(I tried adding
"and OrganisationUnit.OrganisationLevelID = 2"
and also addding
"and (SELECT OrganisationUnit.Name FROM OrganisationUnit WHERE OrganisationUnit.OrganisationLevelID = '2')"
But they didn't work.)

Example of sample dat in OrgUnit table if you need it.

OrgUnitID..OrgLevelID....Name

1.............1........StockBrokers Group
1.............2........XY StockBrokers
1.............3........London Office
1.............4........Fred Smiths Desk
2.............1........ABC Holdings Group
2.............2........ABC Holdings
2.............3........NULL
2.............4........NULL
3.............1........StockBrokers Group
3.............2........QQR Stocks
3.............3........New York Office
3.............4........NULL

Can anyone help.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 12:31:10

Maybe I am missing something, but if the results only need to be for orglevel2 you would just add

WHERE TradingDate >= '01 Nov 2007' and OrglevelID = 2

Poor planning on your part does not constitute an emergency on my part.

Rovastar
Starting Member

38 Posts

Posted - 2007-11-14 : 13:23:22

Yeah that is what I thought and tried that but it didn't seem to give me the results back I was after hence my confusion. I think there were less total names brought back.

Select OrganisationUnit.Name, SUM(TotalContribution) AS TOTAL from Deal JOIN OrganisationUnit
ON SubmittingOrgUnitID = OrganisationUnit.OrganisationUnitId
WHERE TradingDate >= '01 Nov 2007'
AND OrganisationUnit.OrganisationLevelID = 2
GROUP BY OrganisationUnit.Name
ORDER BY SUM(TotalContribution) DESC

I will look at it again at work in the morning. That should work shouldn't it.....I am not going mad......

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-14 : 13:28:20

Find the name which appear whithout filter =2 and which dissapear whith filter =2
Then make few selects manually to understand, what is wrong

Rovastar
Starting Member

38 Posts

Posted - 2007-11-15 : 04:47:43

ok I got my explaination completely wrong.

The OrgUnit table like all data it seems is obvioulsy more complex.

OrgUnitID..ParentOrgID OrgLevelID ... Name
271........NULL...............1...........DDT Group --group
272........271................2...........DDT Holdings --company
273........272................3...........London Office --Office
278........273................4...........Ian smith accounts--Contact
279........273................4...........<NULL> --Contact
280........273................4...........Fred Taylor --contact
281........273................4...........<NULL> --contact

So they all have parents IDs of the one above.

So if orgunit level is 1 then there will be no parent as it is top of the chain.
And I want orgunit level = 2 for the company name.

There can be many level 2s for a level 1s and many level 3s for a level 2, etc/
There are only levels 1 to 4 of nesting.

There is also a child counter I think as well if that is useful.

Now I am miffed I know there is some subqueries here ut I do not am struggling to get my head around it.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-11-15 : 08:11:35

You may need to look at recursion. Something like:

;WITH Temp (OrganisationUnitId, TopID)
AS
(
    SELECT OrganisationUnitId, OrganisationUnitId
    FROM OrganisationUnit
    WHERE [Name] IS NOT NULL

    UNION ALL

    SELECT U.OrganisationUnitId, T.TopID
    FROM Temp T
        JOIN OrganisationUnit U
            ON T.OrganisationUnitId = U.ParentOrgID
)
SELECT *
FROM OrganisationUnit U
    JOIN (
            SELECT T.TopID, SUM(D.TotalContribution) AS Total
            FROM Temp T
                JOIN Deal D
                    ON T.OrganisationUnitId = D.SubmittingOrgUnitID
            WHERE D.TradingDate >= '20071101'
            GROUP BY T.TopID
        ) V
        ON U.OrganisationUnitId = V.TopID

Rovastar
Starting Member

38 Posts

Posted - 2007-11-19 : 09:46:52

Finaly did it. Thanks for the help. In the end I roped ina friend witrh more SQL knowledge than me and we properly did it a long way but it works and returns all the info I want. Here is the query if anyone is interested.

Select TotalOrgUnit.d AS 'Group (level 1)', TotalOrgUnit.c AS 'Company (level 2)', TotalOrgUnit.b AS 'Office (level 3)', count(*) AS 'Total Deals' from Deal JOIN

(select type, sequencelevel, originalID,a,b,c,d

from

(

(

select

'Contact' as type,

'4' as sequencelevel,

originalId,

--REPLACE(CONCAT(if(c1.a is null,'unknown',c1.a),' in the ', c1.b, ', of the ', c1.c,' company from ', t.name),' ',' ') as fullName,

c1.a as a,

c1.b as b,

c1.c as c,

t.name as d

from

OrganisationUnit AS t

inner join (

select

originalId,

c2.a as a,

c2.b as b,

t.name as c,

t.ParentOrganisationUnitId

from

OrganisationUnit AS t

inner join (

select

originalId,

c3.a as a,

t.name as b,

t.ParentOrganisationUnitId

from

OrganisationUnit as t

inner join (

select

OrganisationUnitId as originalId,

name as a,

ParentOrganisationUnitId

from

OrganisationUnit

where

OrganisationLevelId = 4

) as c3 on t.OrganisationUnitId = c3.ParentOrganisationUnitId

) as c2 on t.OrganisationUnitId = c2.ParentOrganisationUnitId

) as c1 on t.OrganisationUnitId = c1.ParentOrganisationUnitId

)

union

(

select

'Office' as type,

'3' as sequencelevel,

originalId,

--REPLACE(CONCAT(c1.a,', of the ', c1.b, ' company from ', t.name),' ',' ') as fullName,

null as a,

c1.a as b,

c1.b as c,

t.name as d

from

OrganisationUnit AS t

inner join (

select

originalId,

c2.a as a,

t.name as b,

t.ParentOrganisationUnitId

from

OrganisationUnit as t

inner join (

select

OrganisationUnitId as originalId,

name as a,

ParentOrganisationUnitId

from

OrganisationUnit

where

OrganisationLevelId = 3

) as c2 on t.OrganisationUnitId = c2.ParentOrganisationUnitId

) as c1 on t.OrganisationUnitId = c1.ParentOrganisationUnitId

)

) as u1

union

(

select

'Company' as type,

'2' as sequencelevel,

OriginalID,

--REPLACE(CONCAT(c1.a,', of the ', c1.b, ' company from ', t.name),' ',' ') as fullName,

null as a,

null as b,

c1.a as c,

t.name as d

--t.ParentOrgId

from

OrganisationUnit as t

inner join

(

select

--originalId,

OrganisationUnitId as originalId,

name as a,

ParentOrganisationUnitId,

OrganisationUnitId

from

OrganisationUnit

where

OrganisationLevelId = 2

) as c1 on t.OrganisationUnitId = c1.ParentOrganisationUnitId

)

union

(

Select

'Group' as type,

'1' as sequencelevel,

OrganisationUnitId as OriginalID,

null as a,

null as b,

null as c,

name as d

from

OrganisationUnit

where OrganisationLevelId = 1

)

)

As TotalOrgUnit

ON SubmittingOrgUnitID = TotalOrgUnit.originalId

WHERE TradingDate >= '01 Nov 2007'

GROUP BY TotalOrgUnit.d, TotalOrgUnit.c, TotalOrgUnit.b

ORDER BY COUNT(*) DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources