Count the number of Code Types with a Subquery
-
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
-
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] -
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] -
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply