Drop Table Medical_Insurance;
CREATE TABLE Medical_Insurance (
  MemberID varchar(30) NOT NULL,
  Insurance_Start_Date date NOT NULL,
  Insurance_End_Date date NOT NULL
);

INSERT INTO Medical_Insurance VALUES ('M1', '1-1-2017', '11-20-2017');
INSERT INTO Medical_Insurance VALUES ('M1', '12-31-2017', '02-01-2018');
INSERT INTO Medical_Insurance VALUES ('M1', '02-15-2018', '04-30-2018');
INSERT INTO Medical_Insurance VALUES ('M1', '06-10-2018', '12-31-2018');
INSERT INTO Medical_Insurance VALUES ('M2', '1-1-2017', '11-20-2017');
INSERT INTO Medical_Insurance VALUES ('M2', '12-31-2017', '02-01-2018');
INSERT INTO Medical_Insurance VALUES ('M3', '02-15-2018', '04-30-2018');
INSERT INTO Medical_Insurance VALUES ('M3', '06-10-2018', '12-31-2018');
INSERT INTO Medical_Insurance VALUES ('M4', '1-1-2017', '11-20-2017');
INSERT INTO Medical_Insurance VALUES ('M4', '12-31-2017', '02-01-2018');
INSERT INTO Medical_Insurance VALUES ('M5', '02-15-2018', '04-30-2018');
INSERT INTO Medical_Insurance VALUES ('M5', '06-10-2018', '12-31-2018');
INSERT INTO Medical_Insurance VALUES ('M6', '01-01-2017', '12-31-2019');
INSERT INTO Medical_Insurance VALUES ('M7', '12-31-2017', '12-30-2018');
INSERT INTO Medical_Insurance VALUES ('M8', '1-1-2020', '12-31-2020');
INSERT INTO Medical_Insurance VALUES ('M9', '06-30-2018', '01-31-2020');

-- fetch some values
SELECT * FROM Medical_Insurance;

-- Task 1.1 Write a SQL query to generate a count of members who were insured in 2018 on a specific given date(parameterize).
-- Hint:Only two members (M6, M7) were insured on date “02-11-2018”.

select count(*), MemberID
from Medical_Insurance
where '02-11-2018' Between Insurance_Start_Date and Insurance_End_Date;
-- where Insurance_Start_Date<='02-11-2018' and Insurance_End_Date>='02-11-2018';


-- Task 1.3 Write a SQL query to generate the list of members whose insurance starts on date “12-31-2017”.
-- Hint: - 1.3.1 Member M1 insurance starts on “12-31-2017”.

select MemberID
from Medical_Insurance
where Insurance_Start_Date='12-31-2017';

-- Task 1.2 Write a SQL query to generate the list of members and their number of insured days in 2018.
-- Hint: - 1.2.1 Member M1 was insured for 309 days in year 2018.
--         1.2.2 Member M7 was insured for 363 days in year 2018.

select MemberID, DATEDIFF(day,Insurance_Start_Date,Insurance_End_Date) as Insured_days
from Medical_Insurance
where Insurance_Start_Date<='01-01-2018' and Insurance_End_Date>='12-31-2018';

-- Task 1.4 Write a SQL query to generate the list of members who were not insured on a specific given date(parameterize).
-- Hint: - 1.4.1 Member M5 was not insured on date “05-24-2018”.

select MemberID
from Medical_Insurance
where