Count the number of Code Types with a Subquery

Question

Post reply

Count the number of Code Types with a Subquery

netguykb

Ten Centuries

Points: 1310

April 24, 2020 at 9:04 pm

#3746200

I need help with a query to select against the following table:

CREATE TABLE dbo.Claims (
[Organization Name] nvarchar(64) NOT NULL,
[POS Code] nvarchar(8) NOT NULL
);
GO

-- In the real dataset there are many organizations
-- ORG 1 Inserts 
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 1','02:B:1');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 1','11:B:1');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 1','13:A:3');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 1','76<A<1');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 1','02:A:8');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 1','02:B:2');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 1','78:A:4');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 1','89:A:1');


-- ORG 2 Inserts (2 records)
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 2','02:B:1');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 2','11:B:1');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 2','13:A:1');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 2','74:A:8');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 2','69:A:1');
INSERT INTO dbo.claims ([Organization Name], [POS Code])
VALUES ('ORG 2','02:A:9');

NOTE: My production table has 50+ organizations and 30 or so different POS codes of which I only need to select the unique ORG names followed by a count for only 3 of the POS codes and another count for anything else.

I tried the following query but it is not correct. The Intended output is below:

SELECT 
[Organization Name],
[Telemedicine] = (SELECT COUNT([POS Code]) FROM  dbo.Claims WHERE SUBSTRING([POS Code],1,2) = '02'),
[Office] = (SELECT COUNT([POS Code]) FROM  dbo.Claims WHERE SUBSTRING([POS Code],1,2) = '11'),
[Assited Living] = (SELECT COUNT([POS Code]) FROM  dbo.Claims WHERE SUBSTRING([POS Code],1,2) = '13'),
[Unassigned] = (SELECT COUNT([POS Code]) FROM  dbo.Claims 
  WHERE NOT SUBSTRING([POS Code],1,2) = '13' OR
NOT SUBSTRING([POS Code],1,2) = '11' OR
NOT SUBSTRING([POS Code],1,2) = '02' 
)
FROM dbo.Claims
GROUP BY [Organization Name]

Desired Results listing the count for each ORG

SQL_RecNeeded

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

netguykb Ten Centuries Points: 1310 More actions · Answer 1

Looks like I figured it out again. OK, Sorry folks Look like I need to spend more time on my own rather than quick to ask a question here. Don't want to waste anyone's time. Sorry Folks!!!

SELECT 
A.[Organization Name],
[Telemedicine] = (SELECT COUNT([POS Code]) FROM  dbo.Claims WHERE SUBSTRING([POS Code],1,2) = '02' AND [Organization Name] = A.[Organization Name]),
[Office] = (SELECT COUNT([POS Code]) FROM  dbo.Claims WHERE SUBSTRING([POS Code],1,2) = '11'AND [Organization Name] = A.[Organization Name]),
[Assited Living] = (SELECT COUNT([POS Code]) FROM  dbo.Claims WHERE SUBSTRING([POS Code],1,2) = '13'AND [Organization Name] = A.[Organization Name]),
[Unassigned] = (SELECT COUNT([POS Code]) FROM  dbo.Claims 
  WHERE [POS Code] NOT LIKE '13%' AND
[POS Code] NOT LIKE '11%' AND
[POS Code] NOT LIKE '02%' 
AND [Organization Name] = A.[Organization Name]
)
FROM dbo.Claims A
GROUP BY [Organization Name]

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

That's not really the best way to do it as you have multiple "joins" to the same table when you can do it with a single one

try like this

SELECT A.[Organization Name]
     , [Telemedicine] = sum(case when SUBSTRING([POS Code],1,2) = '02'  then 1 else 0 end)
     , [Office] = sum(case when  SUBSTRING([POS Code],1,2) = '11' then 1 else 0 end)
     , [Assited Living] = sum(case when SUBSTRING([POS Code],1,2) = '13' then 1 else 0 end)
     , [Unassigned] = sum(case
                          when [POS Code] NOT LIKE '13%'
                          AND [POS Code] NOT LIKE '11%'
                          AND [POS Code] NOT LIKE '02%' 
                          then 1
                          else 0
                          end)
FROM dbo.Claims A
GROUP BY [Organization Name]

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

This can be simplified a bit further:

 Select A.[Organization Name]
      , [Telemedicine] = sum(Case When p.pos_code = '02' Then 1 Else 0 End)
      , [Office] = sum(Case When p.pos_code = '11' Then 1 Else 0 End)
      , [Assited Living] = sum(Case When p.pos_code = '13' Then 1 Else 0 End)
      , [Unassigned] = sum(Case When p.pos_code Not In ('02', '11', '13') Then 1 Else 0 End)
   From dbo.Claims A
  Cross Apply (Values (substring([POS Code], 1, 2))) As p(pos_code)
  Group By
        [Organization Name]

By moving the substring to a cross apply - we can then change the check for Unassigned to a NOT IN check instead of comparing using LIKE.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs