SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Difficult Sum
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

montecarlo2079
Starting Member

20 Posts

Posted - 01/30/2013 :  08:50:25  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 01/30/2013 :  09:02:08  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

montecarlo2079
Starting Member

20 Posts

Posted - 01/30/2013 :  09:10:04  Show Profile  Reply with 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

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/30/2013 :  09:43:45  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 01/30/2013 :  22:21:41  Show Profile  Reply with Quote

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



KH
Time is always against us


Edited by - khtan on 01/30/2013 23:11:00
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 01/31/2013 :  07:47:28  Show Profile  Reply with Quote
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)



Edited by - James K on 01/31/2013 08:36:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000