Generally, there are two rows for each payer, with one row representing a success count and another row representing a failure count.
I want to have the two rows return as one with both a success and a failure column.
But sometimes there is only one row, either a success or a failure but not both.
I've tried joining the source table on itself, a left and right join don't pick up either the missing success or the missing failure. A full join returns four rows for the medicare row which really scrogges things up.
==> source data below <==
CorpName PayerName PlanName PayerID Status PlanUid Count
------------------ ------------------------- ------------------------ ------- ------- -------- -----
Medicare of Texas Novitas Solution Medicare - Texas, Part B 04412 FAILURE 660FED8E 19
Medicare of Texas Novitas Solution Medicare - Texas, Part B 04412 SUCCESS 660FED8E 29
GHI PPO GHI PPO Group Health Inc. - New 13551 FAILURE BFF5E581 1
United Healthcare Benefits of Texas, Inc. United Healthcare 87726 SUCCESS 9C1E2A67 5
==> desired output <==
CorpName PayerName PlanName PayerID PlanUid Success Failure
------------------ ------------------------- ------------------------ ------- -------- ------- -------
Medicare of Texas Novitas Solution Medicare - Texas, Part B 04412 660FED8E 29 19
GHI PPO GHI PPO Group Health Inc. - New 13551 BFF5E581 0 1
United Healthcare Benefits of Texas, Inc. United Healthcare 87726 9C1E2A67 5 0
CodePudding user response:
You can use a self-join
:
select p.corpname, p.payername, p.planname, p.payerid, max(coalesce(p1.`count`, 0)), max(coalesce(p2.`count`, 0)) from plans p
left join plans p1 on p.payerid = p1.payerid and p1.status='SUCCESS'
left join plans p2 on p.payerid = p2.payerid and p2.status='FAILURE'
group by p.corpname, p.payername, p.planname, p.payerid
CodePudding user response:
You are basically after a pivot, you can aggregate and use a condition case expression, untested but something like:
select
max(CorpName) CorpName,
max(PayerName) PayerName,
max(PlanName) PlanName,
max(PayerID) PayerID,
max(PlanUid) PlanUid,
coalesce(case when status='SUCCESS' then "count" end,0) as Success,
coalesce(case when status='FAILURE' then "count" end,0) as Failure
group by
CorpName, PayerName, PlanName, PayerID, PlanUid;
CodePudding user response:
You can try this:
IF (OBJECT_ID('tempdb..#MyTable') IS NOT NULL)
BEGIN
DROP TABLE #MyTable
END;
IF (OBJECT_ID('tempdb..#product') IS NOT NULL)
BEGIN
DROP TABLE #product
END;
CREATE TABLE #MyTable (CorpName varchar(50) NOT NULL, PayerName varchar(50) NOT NULL, PlanName varchar(50) NOT NULL, PayerID varchar(20) NOT NULL, [Status] varchar(20) NOT NULL, PlanUid varchar(20), [COUNT] int not null)
INSERT INTO #MyTable (CorpName ,PayerName,PlanName ,PayerID, [Status] , PlanUid, [Count])
Values ('Medicare of Texas','Novitas Solution', 'Medicare - Texas, Part B','04412','FAILURE', '660FED8E', 19),
('Medicare of Texas','Novitas Solution', 'Medicare - Texas, Part B','04412','SUCCESS', '660FED8E', 29),
('GHI PPO','GHI PPO', 'Group Health Inc. - New' , '13551', 'FAILURE', 'BFF5E581', 1),
('United Healthcare', 'Benefits of Texas, Inc.', 'United Healthcare', '87726','SUCCESS','9C1E2A67', 5)
-- select * from #MyTable
select CorpName ,PayerName,PlanName ,PayerID,PlanUid
, sum(coalesce(case when [status] = 'SUCCESS' then ([count]) end, 0)) as SuccessCnt
, sum(coalesce(case when [status] = 'FAILURE' then ([count]) end, 0)) as FailureCnt
from #MyTable
group by CorpName ,PayerName,PlanName ,PayerID,PlanUid