February 17, 2011 at 2:34 pm
I am looking for a solution to automate the following report requirements:
1.I receive a list of new members each month with their names, unique IDs, and date they started
2.I need to match this list against our SQL DB to see if they have any previous claims with us or not.
3.However, one the requirement is to look for each new member in our DB within their start date month. For example, if John Doe started in 01/06/2010 and Jane Doe started 01/08/2010, then I need to look for John’s claims during from June 01/06 thru 06/30/2010 and look for Jane from 01/08 thru 08/31/2010. I may have a new list of 500 every month with different start date. And, if John Doe was a member twice already and terminated, he has two Start dates one 01/06/2010 and the second may be 01/10/2010. Thus, even for him I have to look for two start dates.
What I have done so far:
Since I was lucky so far to have my list short (10 to 15 members ), I have a script that I plug in their unique ID numbers and start date and end date to extract claims for that period. Obviously, this is manual work and I need to have either a dynamic script or other alternatives to be able to handle list of 500+ a month. Below is the script I am currently using
use DB1
SET NOCOUNT ON
DECLARE
@RptStartDt varchar(20),
@RptEndDt varchar(20)
SET @RptStartDt = '06/01/2010 00:00:00' -- I change this every time
SET @RptEndDt = '06/30/2010 23:59:01' -- I change this every time
/**********************************************************************************/
if object_id('tempdb..#claim') is not null begin drop table #claim end
/**********************************************************************************/
selectDISTINCT
c.claimid,
convert(varchar(10), cd.dosfrom, 101) as DOS,
convert(varchar(10), c.startdate, 101) as ClmStDT,
convert(varchar(10), c.okpaydate, 101) as OkyPDate,
convert(varchar(10), c.paiddate, 101) as PaidDate,
RTRIM(m.fullname) as MbrName,
e.carriermemid as MbrCarID,
convert(varchar(10), e.effdate, 101) as MbEffDT,
convert(varchar(10), e.termdate, 101) as MbTrmDT,
c.totalpaid as TotPaid,
c.status as ClmStatus
into#claim
fromclaim c
left outer join claimdetail cd (nolock) on c.claimid = cd.claimid
left outer join memberview m (nolock) on c.memid = m.memid
left outer join enrollkeys e (nolock) on c.enrollid = e.enrollid
left outer join enrollcoverage ec (nolock) on e.enrollid = ec.enrollid left outer join benefitplan bp (nolock) on e.planid = bp.planid
left outer join program pr (nolock) on bp.programid = pr.programid
left outer join contract co1 (nolock) on c.contractid = co1.contractid
left outer join providerview RenderProv (nolock) on c.provid = RenderProv.provid
left outer join affiliation af1 (nolock) on c.affiliationid = af1.affiliationid
left outer join provider RenderPayto (nolock) on af1.affiliateid = RenderPayto.provid
where ((c.startdate <= e.effdate) or (c.startdate >= e.termdate))
and cd.dosfrom between @RptStartDt and @RptEndDt
and c.status = 'paid'
and c.resubclaimid = ''
and c.totalpaid <> 0
and e.carriermemid = ('99999999999')
Select *
from #claim
Order by MbrCarID
Sample Data:
Member List Received Every Month
fullnameID startdate
Mem11111111115/17/2010
Mem22222222225/1/2010
Mem33333333336/8/2010
Mem44444444446/1/2010
Mem55555555556/30/2010
Mem66666666668/21/2010
Claims for above List
fullnameID startdate claimid Total Piad
Mem11111111115/17/201011100000000000 $ 5.06
Mem11111111115/17/201011110000000000 $ 400.56
Mem22222222225/1/2010 12100000000000 $ 1,100.00
Mem22222222225/1/2010 12700000000000 $ 3,000.76
Mem33333333336/8/2010 13100000000000 $ 87.25
Mem44444444446/1/2010 14100000000000 $ 8.07
Mem55555555556/30/201015100000000000 $ 113.74
Mem66666666668/21/201016100000000000 $ 14.02
Thanks
Helal
February 17, 2011 at 3:01 pm
Helal, welcome to sqlservercentral. In order for us to help you, you first need to help us. Please see the link here[/url] for examples on how to post your questions in a format that will help you get the help you desire. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply