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 characters needed characters left characters exceeded

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

1 Answer

· Write an 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 characters needed characters left characters exceeded

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

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 ·
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 ·
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 ·
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.