Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I have some data that looks like this:
StudentID | DOB | StartDate | EndDate | Birthday? |
31421 | 08/11/1960 | 22/10/2017 | 28/10/2017 | |
46287 | 16/08/2002 | 22/10/2017 | 28/10/2017 | |
51771 | 28/09/2000 | 22/10/2017 | 28/10/2017 | |
51776 | 03/11/2004 | 22/10/2017 | 04/11/2017 | Yes |
51858 | 04/01/1961 | 11/10/2017 | 18/10/2017 |
I'm trying to determine whether a student will have a birthday during their course so I need to check whether the month/day of the DOB will fall between the StartDate and EndDate (inclusive). What's the best way of doing this?
Are you sure this returns the correct result even when the course crosses year change?
I would like to suggest
If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as Birthday?
SET DATEFORMAT = 'DD/MM/YYYY';
LOAD *,
If(DayNumberOfYear(DOB) >= DayNumberOfYear(StartDate) and DayNumberOfYear(DOB) <= DayNumberOfYear(EndDate),'Yes','No') as B1?,
If(SetDateYear(DOB, Year(Today())) >= SetDateYear(StartDate, Year(Today())) and SetDateYear(DOB, Year(Today())) <= SetDateYear(EndDate, Year(Today())), 'Yes') as Birthday?,
If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as B2?
;
LOAD * INLINE [
StudentID, DOB, StartDate, EndDate
31421, 08/11/1960, 22/10/2017, 28/10/2017
46287, 16/08/2002, 22/10/2017, 28/10/2017
51771, 28/09/2000, 22/10/2017, 28/10/2017
51776, 03/11/2004, 22/10/2017, 04/11/2017
51858, 04/01/1961, 11/10/2017, 18/10/2017
52222, 01/01/1970, 31/12/2017, 02/01/2018
52223, 31/12/1970, 31/12/2017, 02/01/2018
52224, 02/01/1970, 31/12/2017, 02/01/2018
52225, 30/12/1970, 31/12/2017, 02/01/2018
52226, 03/01/1970, 31/12/2017, 02/01/2018
];
May be like this
LOAD *,
If(SetDateYear(DOB, Year(Today())) >= SetDateYear(StartDate, Year(Today())) and SetDateYear(DOB, Year(Today())) <= SetDateYear(EndDate, Year(Today())), 'Yes') as Birthday?;
LOAD * INLINE [
StudentID, DOB, StartDate, EndDate
31421, 08/11/1960, 22/10/2017, 28/10/2017
46287, 16/08/2002, 22/10/2017, 28/10/2017
51771, 28/09/2000, 22/10/2017, 28/10/2017
51776, 03/11/2004, 22/10/2017, 04/11/2017
51858, 04/01/1961, 11/10/2017, 18/10/2017
];
If(DayNumberOfYear(DOB) >= DayNumberOfYear(StartDate) and DayNumberOfYear(DOB) <= DayNumberOfYear(EndDate),'Yes','No')
Brilliant - thank you both!
Are you sure this returns the correct result even when the course crosses year change?
I would like to suggest
If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as Birthday?
SET DATEFORMAT = 'DD/MM/YYYY';
LOAD *,
If(DayNumberOfYear(DOB) >= DayNumberOfYear(StartDate) and DayNumberOfYear(DOB) <= DayNumberOfYear(EndDate),'Yes','No') as B1?,
If(SetDateYear(DOB, Year(Today())) >= SetDateYear(StartDate, Year(Today())) and SetDateYear(DOB, Year(Today())) <= SetDateYear(EndDate, Year(Today())), 'Yes') as Birthday?,
If( Age(StartDate-1,DOB)<> Age(EndDate,DOB),'Yes','No') as B2?
;
LOAD * INLINE [
StudentID, DOB, StartDate, EndDate
31421, 08/11/1960, 22/10/2017, 28/10/2017
46287, 16/08/2002, 22/10/2017, 28/10/2017
51771, 28/09/2000, 22/10/2017, 28/10/2017
51776, 03/11/2004, 22/10/2017, 04/11/2017
51858, 04/01/1961, 11/10/2017, 18/10/2017
52222, 01/01/1970, 31/12/2017, 02/01/2018
52223, 31/12/1970, 31/12/2017, 02/01/2018
52224, 02/01/1970, 31/12/2017, 02/01/2018
52225, 30/12/1970, 31/12/2017, 02/01/2018
52226, 03/01/1970, 31/12/2017, 02/01/2018
];
This is even better! Thank you 🙂