Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
did you try speech marks around dates, i.e. '01-04-2012' instead of just 01-04-2012?