Conditional Scoring

Hi All,

I have data like below

empno date differences 999 2017-08-01 1 999 2017-09-01 1 999 2017-10-01 1 999 2017-11-01 1 999 2017-12-01 1

I need this data to be for 6 months (Jul to Dec) , with a difference of 0 for any missing months for an employee within that 6-month period. So for the employee above, missing month is Jul, so I will give him differences of 0.

empno date differences 999 2017-07-01 0 999 2017-08-01 1 999 2017-09-01 1 999 2017-10-01 1 999 2017-11-01 1 999 2017-12-01 1

THEN, I wish to score this employee, based on teh below rules:

A score of 5 if 0 differences in 6 consecutive months ( from July to December) ;

A score of 4 if 0 differences in 3 consecutive months (from July to December);

A score of 3 if 0 differences for 1 month ( from July to December);

A score of 2 if 1 to 3 differences for 1 month (from July to December);

A score of 1 if 4 or more differences in 1 month (from July to December).

Please assist with this requirement.

Many thanks Regards

If you post DDL for a table(create table) with inserts of sample data, what you've tried (queries), and what out put you're expecting from the sample data you provide; then perhaps someone will help you.