Need help with SELECT statement.

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)

Need help with SELECT statement.

Author

Topic

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-04-19 : 14:26:24

Hi,

I need some help with the select statement given the business rules and desired result below.
Thank you so much for your help in advance. Please see the business rules below. SQL 2005.

IF OBJECT_ID('Tempdb.dbo.#Office', 'u') IS NOT NULL
DROP TABLE #Office

CREATE TABLE #Office
(
CompanyId INT NULL,
OfficeId INT NULL,
CorpOffice BIT NULL,
DBAName VARCHAR(15) NULL
)
go

INSERT INTO #Office VALUES (1323, 1302, 0, 'Test1')
INSERT INTO #Office VALUES (1323, 1303, 0, 'Test2')
INSERT INTO #Office VALUES (1323, 1306, 0, 'Test3')

INSERT INTO #Office VALUES (1324, 1304, 1, 'Test3')
INSERT INTO #Office VALUES (1324, 1305, 0, 'Test4')

INSERT INTO #Office VALUES (1325, 1307, 0, 'Test5')
INSERT INTO #Office VALUES (1328, 1309, 1, 'Test6')
GO

SELECT *
FROM #Office;
go

CompanyId OfficeId CorpOffice DBAName
----------- ----------- ---------- ---------------
1323 1302 0 Test1
1323 1303 0 Test2
1323 1306 0 Test3

1324 1304 1 Test3
1324 1305 0 Test4

1325 1307 0 Test5

1328 1309 1 Test6

/*
-- Business Rules: 1. Only one CompanyId and CorpOffice = 0 Return
2. Multiple CompanyId and CorpOffice = 1 in one of them Return
3. Multiple CompanyId and CorpOffice = 0 Don't return.
*/

-- desired results:
CompanyId OfficeId CorpOffice DBAName
----------- ----------- ---------- ---------------
1324 1304 1 Test3
1324 1305 0 Test4

1325 1307 0 Test5

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-04-19 : 14:45:19

SELECT *
FROM
(
select CompanyID,[Offices] = COUNT(Distinct o.officeid),[CorpOffice] = sum(convert(tinyint,CorpOffice))
from #office o
group by o.companyid
having (COUNT(Distinct o.officeid) > 1 and sum(convert(tinyint,CorpOffice)) > 0)
or (COUNT(Distinct o.officeid) = 1 and sum(convert(tinyint,CorpOffice)) = 0)
) t1
INNER JOIN #office t2 ON
t1.companyid = t2.companyid

Jim

Everyday I learn something that somebody else already knew

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-04-19 : 16:12:08

Jim,

When I ran your query, the result return only but I need record CompanyId = 1325 return as well. Again, Thanks.

CompanyID Offices CorpOffice CompanyId OfficeId CorpOffice DBAName
----------- ----------- ----------- ----------- ----------- ---------- ---------------
1324 2 1 1324 1304 1 Test3
1324 2 1 1324 1305 0 Test4

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-04-19 : 23:54:23

Jim,

My mistake, your query work like a charm. Thanks very much.

quote:
Originally posted by jimf

SELECT *
FROM
(
select CompanyID,[Offices] = COUNT(Distinct o.officeid),[CorpOffice] = sum(convert(tinyint,CorpOffice))
from #office o
group by o.companyid
having (COUNT(Distinct o.officeid) > 1 and sum(convert(tinyint,CorpOffice)) > 0)
or (COUNT(Distinct o.officeid) = 1 and sum(convert(tinyint,CorpOffice)) = 0)
) t1
INNER JOIN #office t2 ON
t1.companyid = t2.companyid

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 00:56:01

[code]SELECT CompanyId, OfficeId, CorpOffice, DBAName
FROM
(SELECT COUNT(CASE WHEN CorpOffice=1 THEN 1 ELSE NULL END) OVER (PARTITION BY CompanyId) AS 1Occur,COUNT(OfficeId) OVER (PARTITION BY CompanyId) AS Occur,CompanyId, OfficeId, CorpOffice, DBAName
FROM YourTable
)t
WHERE 1Occur > 0
OR Occur=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NguyenL71
Posting Yak Master

228 Posts

Posted - 2010-04-21 : 10:40:24

Thank you visakhm

L

quote:
Originally posted by visakh16

SELECT CompanyId, OfficeId, CorpOffice, DBAName
FROM
(SELECT COUNT(CASE WHEN CorpOffice=1 THEN 1 ELSE NULL END) OVER (PARTITION BY CompanyId) AS 1Occur,COUNT(OfficeId) OVER (PARTITION BY CompanyId) AS Occur,CompanyId, OfficeId, CorpOffice, DBAName
FROM YourTable
)t
WHERE 1Occur > 0
OR Occur=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-21 : 23:40:14

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources