Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 299
  • Last Modified:

Assistance finding members with no claims

Hi Experts,

My thanks in advance for your assistance...

Code-wise, I am having a hard time with my SQL trying to figure out who has never had a claim for a certain time period yet is a member during the same time period.

It is easy for those with any claims between start_dt and end_dt but to state having a count of zero or NULL claims I realize is not logical.

The basic tables I am working with are MEMBERS and MASTER_CLAIM and maybe need to pull in a membership view vmembership.

I can provide some basic column names and data -- at this point I need more help jump-starting how to tackle the process!
0
britpopfan74
Asked:
britpopfan74
2 Solutions
 
Ross TurnerManagement Information Support AnalystCommented:
I can provide some basic column names and data -- at this point I need more help jump-starting how to tackle the process!

This will help to get our teeth stuck into your problem.....
0
 
BurundiLappCommented:
You could select all the members where membership was valid during the given period and perhaps use a not in statement to exclude them from the list of members that have had a claim in that period

e.g:
Select memberID, memberName, etc
from members
where membership_start_dt >= '20120101' and membership_end_dt < '20130101'
and memberID NOT IN (Select memberID from MasterClaim where where start_dt >= '20120101' and end_dt < '20130101')
0
 
Lee SavidgeCommented:
Just create a SQL statement that returns the member ID's where membership was valid and then put an surrounding select where you return all records where the member ID is not in the inner select statement.
0
 
britpopfan74Author Commented:
Thank you very much for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now