Database
Fan

Add overlapping days covered with different ID types

`--DATA--MEMBER | ID | DATE_BEGIN | DATE_END | DAYS_COVERED--------------------------------------------------1      | A  | 20150101   | 20150131 | 301      | A  | 20150101   | 20150131 | 301      | A  | 20150201   | 20150228 | 271      | B  | 20150115   | 20150215 | 311      | B  | 20150401   | 20150430 | 29`

My data looks like the sample above. The ouput needs to calculate the actual number of days covered. A's can be grouped together, and B's can be grouped together. But if A or B (or C...) overlap, then it only counts one for either A or B, not twice. If A overlaps with A, it counts twice.

So in the example above, ID A would cover 87 days, ID B would cover 29 days.

How could you do something in SQL to get this sort of output?

Tags (1)
3 REPLIES
Senior Apprentice

Re: Add overlapping days covered with different ID types

That's the same question you posted on SO a few weeks ago :-)

You need to add more details:

How many rows per member, avg & max?

How many members?

Is there any rule to determine for which member a range should be counted, why it's not 60 for B and 56 for A?

Fan

Re: Add overlapping days covered with different ID types

It sure is, it's been troublesome. On average, there's about 10-15 rows per member. Highest I've seen is around 30, which was a bunch of small days_covered. There are approximately 100,000 members.

Now that I've read it again, technically I think you are correct that it is 60 for B and 56 for A. The logic says to choose whichever days_covered is longer.

The end result would be to sum these and see how many days the member has covered total, so it's really not important whether A is 56 (as in your example) or if A is 87 (as in mine). What is important is that the total (which both of ours does) ends up at 116. Dates will range from Jan 1 to Dec 31. Does that help explain it a little bit more? I can add more information if that doesn't clear up any questions. So for Member 1, they would have 116 days covered for 2015.

The issue that I've had is (maybe because of my approach), since you have these two dates on 1/1/15, you essentially get the span from 1/1/15 - 3/1/15. Then you have the 2/1/15 date, which you would tack on to the end of that, giving you 1/1/15 - 4/1/15. Then you have to know whether to add B in there or not given this "new" span.

Senior Apprentice

Re: Add overlapping days covered with different ID types

I still don't get it, sounds a bit like this question a few weeeks ago: