Hi
I am trying to write a simple if statement to group my dates by financial year. (I am dealing with just 14 dates, so happy to hard code this, rather than use a more complicated script to get a financial year for any date.)
This is the script:
MonthlyReportDatesTemp:
Load
Distinct UploadEngagementDate as UploadEngagementDate
Resident UploadEngagementCLRN;
MonthlyReportDates:
Load
UploadEngagementDate,
Month(UploadEngagementDate) as UploadEngagementMonth,
if(((UploadEngagementDate >= 01/04/2012) and (UploadEngagementDate < 01/04/2013)),'1213','1314') as UploadEngagementFY
Resident MonthlyReportDatesTemp
Order By UploadEngagementDate asc;
DROP Table MonthlyReportDatesTemp;
This is the table that the script is pointed at.
| LeadCLRN | UploadEngagementDate | UploadEngagement%CLRN |
|---|
| BBC | 16/04/2012 | 0.92895586652314 |
| BBC | 21/05/2012 | 0.99891540130152 |
| BBC | 18/06/2012 | 0.98692810457516 |
| BBC | 16/07/2012 | 0.97986577181208 |
| BBC | 20/08/2012 | 0.99325084364454 |
| BBC | 17/09/2012 | 0.9875 |
| BBC | 15/10/2012 | 0.98470588235294 |
| BBC | 19/11/2012 | 0.97690531177829 |
| BBC | 17/12/2012 | 0.9749430523918 |
| BBC | 21/01/2013 | 0.97477064220183 |
| BBC | 18/02/2013 | 0.97119815668203 |
| BBC | 18/03/2013 | 0.96579247434436 |
| BBC | 15/04/2013 | 0.95537757437071 |
| BBC | 07/05/2013 | 0.97368421052632 |
| CDTV | 16/04/2012 | 0.98888888888889 |
| CDTV | 21/05/2012 | 1 |
| CDTV | 18/06/2012 | 0.98958333333333 |
| CDTV | 16/07/2012 | 1 |
| CDTV | 20/08/2012 | 0.99029126213592 |
|
|
And this is the resulting table
| UploadEngagementDate | UploadEngagementFY | UploadEngagementMonth |
|---|
| 16/04/2012 | 1314 | Apr |
| 21/05/2012 | 1314 | May |
| 18/06/2012 | 1314 | Jun |
| 16/07/2012 | 1314 | Jul |
| 20/08/2012 | 1314 | Aug |
| 17/09/2012 | 1314 | Sep |
| 15/10/2012 | 1314 | Oct |
| 19/11/2012 | 1314 | Nov |
| 17/12/2012 | 1314 | Dec |
| 21/01/2013 | 1314 | Jan |
| 18/02/2013 | 1314 | Feb |
| 18/03/2013 | 1314 | Mar |
| 15/04/2013 | 1314 | Apr |
| 07/05/2013 | 1314 | May |
Am I using the wrong syntax for dates here or am I doing something else wrong? I have tried putting qualifiers round the date and using the real date format (e.g. 45786) but still not getting it right.
Any advice much appreciated.
Thanks
Rory
