asked on

Oracle - SQL Script to test for bill dates 3 or more times in SAME WEEK?

Experts,

My data looks like this:

ProviderID,ClaimID,MemberID,ProcedureCode,ServiceBeginDate
1000,20000,3000,12345,2015-12-31
1000,20000,3000,12345,2016-01-01
1000,20001,3000,12345,2016-01-02
1000,20005,3000,12345,2016-06-01
1000,20002,3001,23456,2016-02-01
1000,20003,3002,23466,2016-02-02
1001,20011,3003,24565,2016-03-01
....
....
....

What would be the best way to analyze this by WEEK.

I need to know how to write a query that will return a list of ProviderId,MembeID,ClaimID that have had a CLAIM happen TWICE DURING ANY GIVEN WEEK OF THE YEAR.

For example from the above example these records would be returned since ProcedureCode "12345" was billed 3 or more times in the same week:

ProviderID,ClaimID,MemberID,ProcedureCode,ServiceBeginDate
1000,20000,3000,12345,2015-12-31
1000,20000,3000,12345,2016-01-01
1000,20001,3000,12345,2016-01-02

WHAT IS THE BEST WAY TO QUERY THIS?

slightwv (䄆 Netminder)

This seems to pretty much be the same logic from your previous question:
https://www.experts-exchange.com/questions/28978832/Oracle-How-to-analyze-data-using-DATE-COLUMN.html

to_char formats:
http://docs.oracle.com/database/121/SQLRF/sql_elements004.htm#SQLRF00210

IW is week of year: 1-52.

Add to_char(servicebegindate,'IW') to the group by and change the having count to >= 2

johnsone

Rather than use TO_CHAR, use the solution that I posted in the previous question. Just change the format in the TRUNC function from YYYY to IW.

slightwv (䄆 Netminder)

Just to clarify:
You need the two individual values in the group by or it will group the same week number across multiple years.

I might be inclined to do a single to_char(servicebegindate,'IW-YYYY') and just group by that.

It switches data types so you'll need to test performance to see if that is an issue.

johnsone

Using TRUNC wouldn't have the issue of year. The TRUNC function still returns a date, it just returns the first day of the week given the date.

No type conversion necessary either.

Marcus Aurelius

ASKER

Its NOT the same questions. I need to now know....HOW TO ANALYZE WITHIN THE SAME WEEK. Not within the YEAR......

Please provide a sample SQL SCRIPT....

slightwv (䄆 Netminder)

>> The TRUNC function still returns a date, it just returns the first day of the week given the date.

Correct. I should have thought it through a little more before posting...

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

slightwv (䄆 Netminder)

>>Its NOT the same questions. I need to now know....HOW TO ANALYZE WITHIN THE SAME WEEK. Not within the YEAR......

I didn't say it was the same question. I said same logic.

Use the same query from the previous question and replace the YYYY with IW

Marcus Aurelius

ASKER

I'll check into the recommendations that you've provided.....thanks

johnsone

Sorry, missed the twice instead of thrice.

SELECT providerid, 
       memberid, 
       Trunc(servicebegindate, 'iw') year, 
       Count(1) 
FROM   mytab 
WHERE  procedurecode = 12345 
GROUP  BY providerid, 
          memberid, 
          Trunc(servicebegindate, 'iw') 
HAVING Count(1) >= 2;

Open in new window

slightwv (䄆 Netminder)

You may need to change the >=3 to >=2

In the original question in one place you have "TWICE DURING ANY GIVEN WEEK" then "was billed 3 or more times in the same week".

If you want 2 or 3, make the appropriate change.