question

edgared avatar image
edgared asked

SQL Query Consecutive Day Count and Sum and Group

Table: claimid, mbrid, dateofsrvfrom, dateofsrvto 1, m1, 1-1-14 , 1-5-14 2, m3, 1-15-14 , 2-1-14 3, m1, 1-6-14 , 1-8-14 4, m2, 2-3-14 , 2-5-14 5, m2, 2-6-14 , 2-8-14 6, m1, 2-1-14 , 2-1-14 7, m3, 1-3-14 , 1-15-14 8, m2, 2-4-14 , 2-30-14 output: I need consecutive dates of service so for claimid 1 and 2 its consecutive and claim id 6 its not because the dateofservice and dateofserviceto dates are not consecutive. new record Dateofservice old record Dateofserviceto must be consecutive or overlapping. If there are 2 claims with identical dos then use only 1 of them (pick first one or second doesn’t matter). Calculated fields: Order field (list of claimID in order of date ranges see below) , Total Days (total days from first date of serv start to last date of serviceto date see below I think I did math right  ). Claimid, memberid, order, total days 1, m1, 1, 8 3, m1, 2, 8 4, m2, 1, 27 8,m2, 2, 27 5,m2, 3, 27 7,m3, 1, 29 2,m3, 2, 29
sql-server-2008sql-server-2005sqlsql-serverquery
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
@SQLShark avatar image
@SQLShark answered
Hi, I don't fully understand your requirement but this solution might help a little. You can take what I have done so far and shape it better to your requirement. Firstly there seems to be issues with your data as last time I checked there are not 30 days in February :D Anyway: DECLARE @example TABLE ( claimid INT , mbrid CHAR(2) , dateofsrvfrom DATE , dateofsrvto DATE ) SET DATEFORMAT mdy INSERT INTO @example ( claimid, mbrid, dateofsrvfrom, dateofsrvto ) VALUES ( 1, 'm1', '1/1/2014', '1/5/2014' ) , ( 2, 'm3', '1/15/2014', '2/1/2014' ) , ( 3, 'm1', '1/6/2014', '1/8/2014' ) , ( 4, 'm2', '2/3/2014', '2/5/2014' ) , ( 5, 'm2', '2/6/2014', '2/8/2014' ) , ( 6, 'm1', '2/1/2014', '2/1/2014' ) , ( 7, 'm3', '1/3/2014', '1/15/2014' ) , ( 8, 'm2', '2/4/2014', '2/28/2014' ); WITH CTE AS ( SELECT mbrid , dateofsrvfrom , dateofsrvto , claimid , temp.Consecutive , temp.Mindateofsrvfrom , temp.Mindateofsrvto , temp.minClaimID FROM @example main CROSS APPLY ( SELECT CASE WHEN DATEDIFF(DAY, main.dateofsrvto, MIN(dateofsrvfrom)) = 1 THEN 1 ELSE 0 END AS Consecutive , MIN(dateofsrvfrom) Mindateofsrvfrom , MIN(sub.dateofsrvto) Mindateofsrvto , CASE WHEN DATEDIFF(DAY, main.dateofsrvto, MIN(dateofsrvfrom)) = 1 THEN MIN(sub.claimid) ELSE 0 END minClaimID FROM @example sub WHERE sub.mbrid = main.mbrid AND sub.dateofsrvfrom >= main.dateofsrvto ) temp ) SELECT CTE.claimid , CTE.mbrid , CTE.dateofsrvfrom , CASE WHEN CTE.Consecutive = 1 THEN CTE.Mindateofsrvto ELSE CTE.dateofsrvto END AS 'RevisedEndOfService' , DATEDIFF(DAY, dateofsrvfrom, CASE WHEN CTE.Consecutive = 1 THEN CTE.Mindateofsrvto ELSE CTE.dateofsrvto END) + 1 AS 'LengthOfService' FROM CTE WHERE CTE.claimid NOT IN ( SELECT DISTINCT minClaimID FROM CTE ) ORDER BY CTE.claimid , CTE.mbrid
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

edgared avatar image edgared commented ·
THE MEMBERID AND CLAIMID are not always numeric they can be alphanumeric. The script you had failed "conversion failed when converting the nvarchar value 'R123456' to data type int." Also u can exclude claimid if that complicates things just group by memberid .... if thats easier
0 Likes 0 ·
edgared avatar image edgared commented ·
well integer is to small everything needs to be nchar so how do i convert temp.Consecutive , temp.Mindateofsrvfrom , temp.Mindateofsrvto , to nchar or nvar or var .... ?
0 Likes 0 ·
@SQLShark avatar image @SQLShark commented ·
Post some more representative values and we will go from there. I have not tested this but I would imagine the conversion error is coming from CASE WHEN DATEDIFF(DAY, main.dateofsrvto, MIN(dateofsrvfrom)) = 1 THEN MIN(sub.claimid) ELSE 0 The ELSE 0 is doing an implicit conversion to INT. Change that to '0' if you are changing ClaimID to CHAR/NCHAR etc
0 Likes 0 ·
edgared avatar image edgared commented ·
awesome worked i just swapped out claimid with row number ... thank you!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.