Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Difficult Sum

Author  Topic 

montecarlo2079
Starting Member

20 Posts

Posted - 2013-01-30 : 08:50:25
I am dealing with medical claims data where I need to count the total number of patient visits. Each Claim number can have multiple date of service. Each distinct date of service per claim number is considered one visit.

The database is set up like this
|Claim Number|From DOS|TO DOS|

How do I write a statement that will sum up each distinct DOS as 1 visit per claim number?

Example

12345|1/1/13|1/1/13|
12345|1/2/13|1/2/13|

This would technically be 2 visits.


Also as a caveat, the from DOS and TO DOS may not equal

Example

12345|1/1/13|1/3/13|
12345|1/4/13|1/6/13|

This is technically 6 visits.

any help would be great as I have been racking my brain

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-01-30 : 09:02:08
are you counting the number of days, one day as one visit for each claim number ?

12345|1/1/13|1/3/13| >> 3 days = 3 visits
12345|1/4/13|1/6/13| >> 3 days = 3 visits
>> total 6 visits ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

montecarlo2079
Starting Member

20 Posts

Posted - 2013-01-30 : 09:10:04
Correct.

But each distinct DOS per claim is only counted once.

So if

12345|1/1/13|1/1/13| >> 1 days = 1 visits
12345|1/1/13|1/1/13| >> 0 (already counted once)
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-30 : 09:43:45
Create a calendar table first, if you don't have one already like this:
CREATE TABLE #Calendar(Dt DATETIME NOT NULL PRIMARY KEY);
;WITH cte AS
(
SELECT CAST('20130101' AS DATETIME) AS c
UNION ALL
SELECT DATEADD(dd,1,c) FROM cte
WHERE c < '20131231'
)
INSERT INTO #Calendar SELECT c FROM cte OPTION (MAXRECURSION 0);
Now you can query like shown below - I am just creating a test table, you wouldn't need to do that, you would simply use your actual claims table in place of the temp table.
CREATE TABLE #Claims (ClaimNo INT, From_DOS DATETIME, To_DOS DATETIME);
INSERT INTO #Claims VALUES
(12345,'20130101','20130103'),(12345,'20130104','20130106')

SELECT
ClaimNo,
COUNT(DISTINCT dt) AS Claims
FROM
#Claims a
INNER JOIN #Calendar b ON
a.From_DOS <= b.Dt AND a.To_DOS >= b.Dt
GROUP BY
ClaimNo;

DROP TABLE #Claims
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-01-30 : 22:21:41
[code]
select [Claim Number], Visits = sum(datediff(day, [From DOS], [TO DOS]))
from claims
group by [Claim Number]
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-31 : 07:47:28
quote:
Originally posted by khtan


select [Claim Number], Visits = sum(1+ datediff(day, [From DOS], [TO DOS]))
from claims
group by [Claim Number]



[font=Comic Sans MS][size=1]KH

See change in red. Even if you add the +1 as I have indicated, this will work correctly only if there are no overlapping rows for the same claimId as the OP indicated in his reply on 01/30/2013 : 09:10:04
quote:
Correct.

But each distinct DOS per claim is only counted once.

So if

12345|1/1/13|1/1/13| >> 1 days = 1 visits
12345|1/1/13|1/1/13| >> 0 (already counted once)


Go to Top of Page
   

- Advertisement -