June 14, 2010 at 11:58 am
Not sure if this should be done at query or design level for a report so will start here to see... Please let me know....
I am creating a report that shows the history of who has been added or removed from a case which is pretty easy... but I was asked to tweak it so management can run it by individual and return the history of everyone on that case.
EX of returning everything:
case #style Name create_user_idaction_sk action_dateBeginDateEndDate
111111Somebody, Joe A.Assistant, A. Legal BBB 1 9/1/09 8:48 9/1/09 8:489/1/09 8:48
Assistant, A. Legal BBB 3 9/1/09 8:489/1/09 8:489/1/09 8:48
Assistant, Another LegalAAA 1 10/1/09 8:4810/1/09 8:4810/1/09 8:48
222222SomebodyElse, Tom B.Assistant, Joe Legal CCC 1 1/1/10 10:481/1/10 10:481/1/10 10:48
333333Doe, Susan Assistant, A. Legal CCC 1 2/1/10 10:482/1/10 10:482/1/10 10:48
444444Doe, John Assistant, Joe Legal CCC 1 3/1/10 10:483/1/10 10:483/1/10 10:48
555555Nobody, Joe Assistant, A. Legal DDD 1 2/1/10 13:482/1/10 13:482/1/10 13:48
Assistant, A. Legal DDD 3 2/1/10 13:482/1/10 13:482/1/10 13:48
Assistant, Susan LegalEEE 1 5/1/10 13:155/1/10 13:155/1/10 13:15
Assistant, Susan LegalEEE 3 5/1/10 13:155/1/10 13:155/1/10 13:15
Assistant, Another LegalEEE 1 6/1/10 14:156/1/10 14:156/1/10 14:15
Note: action_sk is hardcoded - 1 = added and 2 = removed (I use SWITCH in design to label)
In the above sample, it returns everything but if I add a parameter to search for a name it only returns the name. EX with "Assistant, A. Legal":
case #style Name create_user_idaction_sk action_dateBeginDateEndDate
111111Somebody, Joe A.Assistant, A. Legal BBB 1 9/1/09 8:48 9/1/09 8:489/1/09 8:48
Assistant, A. Legal BBB 3 9/1/09 8:489/1/09 8:489/1/09 8:48
333333Doe, Susan Assistant, A. Legal CCC 1 2/1/10 10:482/1/10 10:482/1/10 10:48
555555Nobody, Joe Assistant, A. Legal DDD 1 2/1/10 13:482/1/10 13:482/1/10 13:48
Assistant, A. Legal DDD 3 2/1/10 13:482/1/10 13:482/1/10 13:48
What I need it to return is this:
case #style Name create_user_idaction_sk action_dateBeginDateEndDate
111111Somebody, Joe A.Assistant, A. Legal BBB 1 9/1/09 8:48 9/1/09 8:489/1/09 8:48
Assistant, A. Legal BBB 3 9/1/09 8:489/1/09 8:489/1/09 8:48
Assistant, Another LegalAAA 1 10/1/09 8:4810/1/09 8:4810/1/09 8:48
333333Doe, Susan Assistant, A. Legal CCC 1 2/1/10 10:482/1/10 10:482/1/10 10:48
555555Nobody, Joe Assistant, A. Legal DDD 1 2/1/10 13:482/1/10 13:482/1/10 13:48
Assistant, A. Legal DDD 3 2/1/10 13:482/1/10 13:482/1/10 13:48
Assistant, Susan LegalEEE 1 5/1/10 13:155/1/10 13:155/1/10 13:15
Assistant, Susan LegalEEE 3 5/1/10 13:155/1/10 13:155/1/10 13:15
Assistant, Another LegalEEE 1 6/1/10 14:156/1/10 14:156/1/10 14:15
Here is the test tables/data and the query I am using:
/*
#C table with insert data
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'#c') IS NOT NULL
DROP TABLE #c
GO
CREATE TABLE #c
(
case_sk int NOT NULL,
case_number varchar(15) NULL,
style varchar(150) NULL
)
GO
INSERT INTO #c (case_sk, case_number, style)
SELECT 000001, 111111, 'Somebody, Joe A.' UNION ALL
SELECT 000002, 222222, 'SomebodyElse, Tom B.' UNION ALL
SELECT 000003, 333333, 'Doe, Susan' UNION ALL
SELECT 000004, 444444, 'Doe, John' UNION ALL
SELECT 000005, 555555, 'Nobody, Joe'
/*
#CPA table with insert data
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'#cpa') IS NOT NULL
DROP TABLE #cpa
GO
CREATE TABLE #cpa
(
case_parties_audit_sk int NOT NULL,
case_sk int NOT NULL,
action_sk int NOT NULL,
action_date datetime NOT NULL,
entity_sk int NOT NULL,
role_sk int NOT NULL,
create_user_id varchar(10) NULL
)
INSERT INTO #cpa (case_parties_audit_sk, case_sk, action_sk, action_date, entity_sk, role_sk, create_user_id)
SELECT 00001, 000001, 1, '9/1/2009 8:48:18 AM', 100, 212, 'BBB' UNION ALL
SELECT 00002, 000001, 3, '9/1/2009 8:48:18 AM', 100, 212, 'BBB' UNION ALL
SELECT 00003, 000001, 1, '10/1/2009 8:48:18 AM', 101, 212, 'AAA' UNION ALL
SELECT 00004, 000002, 1, '1/1/2010 10:48:18 AM', 102, 212, 'CCC' UNION ALL
SELECT 00005, 000003, 1, '2/1/2010 10:48:18 AM', 100, 212, 'CCC' UNION ALL
SELECT 00006, 000004, 1, '3/1/2010 10:48:18 AM', 102, 212, 'CCC' UNION ALL
SELECT 00007, 000005, 1, '2/1/2010 1:48:18 PM', 100, 212, 'DDD' UNION ALL
SELECT 00008, 000005, 3, '2/1/2010 1:48:45 PM', 100, 212, 'DDD' UNION ALL
SELECT 00009, 000005, 1, '5/1/2010 1:15:18 PM', 103, 212, 'EEE' UNION ALL
SELECT 00010, 000005, 3, '5/1/2010 1:15:18 PM', 103, 212, 'EEE' UNION ALL
SELECT 00011, 000005, 1, '6/1/2010 2:15:18 PM', 101, 212, 'EEE'
/*
#VLE table with insert data
*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID(N'#vle') IS NOT NULL
DROP TABLE #vle
GO
CREATE TABLE #vle
(
legal_entity_sk int NOT NULL,
computename varchar (100) NULL
)
INSERT INTO #vle (legal_entity_sk, computename)
SELECT 100, 'Assistant, A. Legal' UNION ALL
SELECT 101, 'Assistant, Another Legal' UNION ALL
SELECT 102, 'Assistant, Joe Legal' UNION ALL
SELECT 103, 'Assistant, Susan Legal'
DECLARE
@begindate datetime,
@enddate datetime,
@name varchar(100)
SELECT vc.case_sk, vc.case_number, vc.style, vle.computename AS PartyName, cpa.create_user_id, cpa.action_sk, cpa.action_date,
cpa.action_date AS BeginDate, cpa.action_date AS EndDate, cpa.entity_sk, vle.legal_entity_sk
FROM #vle AS vle RIGHT OUTER JOIN
#cpa AS cpa RIGHT OUTER JOIN
#c AS vc ON cpa.case_sk = vc.case_sk ON vle.legal_entity_sk = cpa.entity_sk
WHERE (cpa.role_sk = 212) AND (vc.case_number <> '') AND (cpa.action_date > @BeginDate) AND (cpa.action_date < @EndDate)
AND (vle.computename = @name)
ORDER BY vc.case_number
DROP TABLE #c
DROP TABLE #cpa
DROP TABLE #vle
thanks in advance!!!
June 14, 2010 at 7:35 pm
Hi there,
This query might produce your expected result:
SELECT vc.case_sk, vc.case_number, vc.style, vle.computename AS PartyName, cpa.create_user_id, cpa.action_sk, cpa.action_date,
cpa.action_date AS BeginDate, cpa.action_date AS EndDate, cpa.entity_sk, vle.legal_entity_sk
FROM#vle AS vle
JOIN#cpa AS cpa ON vle.legal_entity_sk = cpa.entity_sk
JOIN#c AS vc ON cpa.case_sk = vc.case_sk
WHERE(cpa.role_sk = 212)
AND(vc.case_number <> '')
AND(cpa.action_date > @BeginDate)
AND(cpa.action_date < @EndDate)
AND EXISTS (SELECT 1 FROM #vle name
JOIN #cpa cpa2 ON cpa2.entity_sk=name.legal_entity_sk
WHERE name.computename=@name AND cpa2.case_sk=cpa.case_sk)
ORDER BYvc.case_number
Note: I replace the RIGHT OUTER JOIN with JOIN since the tables does not contain NULL values.
Just post your reply if you need further help..
Cheer,
shield_21
June 15, 2010 at 7:17 am
Thanks that did the trick!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply