Returning Data Including Nulls
-
Someone may have a solution for this. I am trying to create a query (abbreviated database script below) which will allow me to extract the name of a list of Workplaces (WEPs) showing the committee member's name, the Workplace Name and Address and the Area name. However, I also need to be able to return the workplace data in the same report where there is NO corresponding member assigned.
As a work-around I have been able to obtain a solution on two passes the first which provides all the workplace details where there is a corresponding member name, and the second pass to extract workplaces where there is no member name assigned to the position. I have also developed a solution which populates an intermediate 'batchLabels' table containing each workplace id and either null or the members me_id for the position. I then use this both an outer join to obtain all the workplaces. Anyway I would appreciate if any one with more SQL experience than I can come up with a solution.
I have tried a number of variations of the script below with varying amounts of success.
For example the query
SELECT W.wep_id,me_id
FROM dbo.WEPS AS W
left JOIN (
SELECT cm_wep_id,
MAX(CASE WHEN cm_cpos_id = 2 THEN cm_me_id ELSE NULL END) AS me_id
FROM committeemembers
GROUP BY cm_wep_id
) AS CM ON CM.cm_wep_id = W.wep_id
Will give me all the required records, however I am unable to add in the other required tables correctly.
This query
SELECT W.wep_id,myRetId, me_id,M.me_surname,wep_name
FROM dbo.WEPS AS W
right Join members M on W.wep_id=M.me_wep_id
left JOIN (
SELECT cm_wep_id,
MAX(CASE WHEN cm_cpos_id = 2 THEN cm_me_id ELSE NULL END) AS myRetId
FROM committeemembers
GROUP BY cm_wep_id
) AS CM ON CM.cm_wep_id = W.wep_id
provides too much data including names where the position cm_cpos is not 2
Required Report Result if I want list of workplaces for 'Representatives'
Peter Brennan
Position Representative
Area B
ABC Company
123 High Street
Dublin
(Blank Name if no name assigned) (No Rep Name, but workplace should still print
Position Representative
Area A
ZIPPO Company
23 Low Street
Cork
, etc, etc.
Abbreviated Database structure...
use MyDataBase
go
IF OBJECT_ID('dbo.Members', 'U') IS NOT NULL
DROP TABLE dbo.Members
GO
IF OBJECT_ID('dbo.CommitteeMembers', 'U') IS NOT NULL
DROP TABLE dbo.CommitteeMembers
GO
IF OBJECT_ID('dbo.CommitteePositions', 'U') IS NOT NULL
DROP TABLE dbo.CommitteePositions
GO
IF OBJECT_ID('dbo.Areas', 'U') IS NOT NULL
DROP TABLE dbo.Areas
GO
IF OBJECT_ID('dbo.WEPs', 'U') IS NOT NULL
DROP TABLE dbo.WEPS
GO
CREATE TABLE CommitteePositions (
cpos_Id int IDENTITY(1,1) NOT NULL,
cpos_name varchar (60) NOT NULL,
cpos_active bit not null default 1
)
insert into CommitteePositions(cpos_name) Values('Quality Inspector')
insert into CommitteePositions(cpos_name) Values('Representative')
insert into CommitteePositions(cpos_name) Values('Safety Officer')
CREATE TABLE Members (
me_id int IDENTITY(654321,1) NOT NULL,
me_surname varchar (30) NOT NULL,
me_forename varchar (20) NOT NULL,
me_type int NOT NULL default 1, -- 0 part time, 1 fulltime
me_wep_id int NOT NULL,
me_active bit not null default 1
)
insert into Members (me_surname,me_forename,me_wep_id, me_type ) values('Brennan','Mary',1001,1) -- 654321
insert into Members (me_surname,me_forename,me_wep_id, me_type ) values('Brown','John',1003,1) -- 654322
insert into Members (me_surname,me_forename,me_wep_id, me_type ) values('Deloitte','Joan',1003,1) -- 654323
insert into Members (me_surname,me_forename,me_wep_id, me_type ) values('Brennan','Helen',1004,0) -- 654324
insert into Members (me_surname,me_forename,me_wep_id, me_type ) values('Brown','Francis',1001,0) -- 654325
insert into Members (me_surname,me_forename,me_wep_id, me_type ) values('Smith','Andre',1005,1) -- 654326
insert into Members (me_surname,me_forename,me_wep_id, me_type ) values('Cooper','Jeanne',1002,0) -- 654327
insert into Members (me_surname,me_forename,me_wep_id, me_type ) values('Deloitte','John',1005,1) -- 654328
CREATE TABLE CommitteeMembers (
cm_id int IDENTITY(1,1) NOT NULL,
cm_me_id int NOT NULL,
cm_cpos_id int NOT NULL,
cm_wep_id int NULL,
)
insert into committeemembers (cm_me_id,cm_cpos_id,cm_wep_id) values(654321,2,1001)
insert into committeemembers (cm_me_id,cm_cpos_id,cm_wep_id) values(654322,2,1003)
insert into committeemembers (cm_me_id,cm_cpos_id,cm_wep_id) values(654323,3,1003)
insert into committeemembers (cm_me_id,cm_cpos_id,cm_wep_id) values(654324,3,1004)
insert into committeemembers (cm_me_id,cm_cpos_id,cm_wep_id) values(654328,3,1005)
CREATE TABLE Areas (
ar_Id int IDENTITY(100,1) NOT NULL,
ar_name varchar (60) NOT NULL,
ar_active bit NULL
)
insert into areas(ar_name) values('Ireland')
insert into areas(ar_name) values('United Kingdom')
insert into areas(ar_name) values('France')
insert into areas(ar_name) values('Germany')
CREATE TABLE WEPs (
wep_id int IDENTITY(1000,1) NOT NULL,
wep_name varchar (60) NOT NULL,
wep_add1 varchar (60) NULL,
wep_add2 varchar (60) NULL,
wep_ar_id int NOT NULL,
wep_active bit NULL
)
insert into WEPS (wep_name,wep_add1,wep_add2,wep_ar_id) values('ABC COmpany','123 High Street','Cork',100) -- 1000
insert into WEPS (wep_name,wep_add1,wep_add2,wep_ar_id) values('Zippo COmpany','27 Low Street','London',101) -- 1001
insert into WEPS (wep_name,wep_add1,wep_add2,wep_ar_id) values('XYZ Wine Company','123 Route du Vin','Paris',102) -- 1002
insert into WEPS (wep_name,wep_add1,wep_add2,wep_ar_id) values('123 Wine Company','123 Route du Vin','Paris',102) -- 1003
insert into WEPS (wep_name,wep_add1,wep_add2,wep_ar_id) values('White Milk Company','Green Grass Avenue','Berlin',103) --1004
insert into WEPS (wep_name,wep_add1,wep_add2,wep_ar_id) values('Blue Cheese Company','Chedderland Street','Galway',100) --1005
insert into WEPS (wep_name,wep_add1,wep_add2,wep_ar_id) values('Dark Chocolate Express','7 Rue du Petit Bercy','Biaritz',102) --1006
insert into WEPS (wep_name,wep_add1,wep_add2,wep_ar_id) values('Dairy Produce Ltd','Somewhere Street','Cork',100) -- 1007
insert into WEPS (wep_name,wep_add1,wep_add2,wep_ar_id) values('ABC COmpany','SomePlace Place','Athenry',100) -- 1008
Thanks in advance,
Morke
-
GOOD JOB posting the required tables and sample data. It could have even better if you had given a "clear" pictoral representation of your expected result (though i see that you have given a good expected result)
Is this what you were after?
T-SQL Code:
DECLARE @Postion VARCHAR(60) = 'Representative'SELECT ForeName = CASE WHEN ColName = 'Pos' THEN ISNULL(me_forename , 'Unassigned')
WHEN ColName = 'wep_name' THEN ''
WHEN ColName = 'wep_add1' THEN ''
WHEN ColName = 'wep_add2' THEN ''
WHEN ColName = 'ar_name' THEN ''
END
,SurName = CASE WHEN ColName = 'Pos' THEN ISNULL(me_surname , 'Unassigned')
WHEN ColName = 'wep_name' THEN ''
WHEN ColName = 'wep_add1' THEN ''
WHEN ColName = 'wep_add2' THEN ''
WHEN ColName = 'ar_name' THEN ''
END
,ColVals
FROM
(
SELECT Pos = @Postion
,MB.me_forename
,MB.me_surname
,WP.wep_name
,WP.wep_add1
,WP.wep_add2
,AR.ar_name
FROM dbo.CommitteePositions AS CP
INNER JOIN dbo.CommitteeMembers AS CMB
ON CP.cpos_Id = CMB.cm_cpos_id
AND CP.cpos_name = @Postion
INNER JOIN dbo.Members AS MB
ON MB.me_id = CMB.cm_me_id
RIGHT JOIN dbo.WEPs AS WP
ON WP.wep_id = MB.me_wep_id
LEFT JOIN dbo.Areas AS AR
ON AR.ar_Id = WP.wep_ar_id
) Pvt_Src
UNPIVOT
(
ColVals FOR ColName IN ( [Pos]
,[wep_name]
,[wep_add1]
,[wep_add2]
,[ar_name]
)
) Pvt_Handle
Snapshot of the Result:
-
Thanks a lot. Worked a dream. . .
. . . and without any tweaking 🙂
Morke
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply