[R] Case statement in sqldf

[Mangalani Peter Makananisa]

Hi all,

I am trying to create a new  variable called Fiscal Year (FY) using case expression in sqldf  and I am getting a null FY , see the code below .

Please advise me as to how I can do this mutation.

  library(zoo)
  library(lubridate)
  library(stringr)
  library(RH2)
  library(sqldf)

cr$ReportDate = as.Date(cr$ReportDate, format ='%Y-%m-%d')

> cr2 =  sqldf(" select ReportDate
+                      ,  case
+                        when ReportDate between  '2012-04-01'  and  '2013-03-31'
+                        then '2012_13'
+                        when  ReportDate between '2013-04-01'  and  '2014-03-31'
+                        then '2013_14'
+                        when  ReportDate between  '2014-04-01'  and  '2015-03-31'
+                        then'201415'
+                        when ReportDate between '2015-04-01'  and  '2016-03-31'
+                        then '2015_16'
+                        when ReportDate between '2016-04-01'  and  '2017-03-31'
+                        then '2016_17'
+                        when ReportDate between '2017-04-01'  and  '2018-03-3'
+                        then '2017_18' else null
+                        end as FY
+               from cr
+              where  ReportDate  >=  '2012-04-01'
+              ")

Thanking you in advance

Kind regards,

Mangalani Peter Makananisa (0005786)
South African Revenue Service (SARS)
Specialist: Statistical Support
TCEI_OR (Head Office)
Tell: +272 422 7357, Cell: +2782 456 4669

Please Note: This email and its contents are subject to our email legal notice which can be viewed at http://www.sars.gov.za/Pages/Email-disclaimer.aspx

	[[alternative HTML version deleted]]