# PeriodbyYM.qry.r

PeriodbyYM.qry <- function(year, month, date=NULL, origin="1970-01-01") {

  if (!missing(year) && missing(month) && missing(date)) {
    if (is.Date(year) || grepl("\\d{4}-\\d{2}(-\\d{2})?", year))
      date <- year
  }

  if (!is.null(date)) {
    ## allow for just YYYY-MM
    if (grepl("^\\d{4}-\\d{2}$", date))
      date <- paste0(date, "-01")

    date  <- as.Date(date, origin=origin)
    year  <- format(date, "%Y")
    month <- format(date, "%m")
  }

  if (is.numeric(month))
    month <- sprintf("%02i", month)
  if (nchar(year) != 4)
    stop("year must be a four-digit year")

  sprintf("SELECT periodid FROM dim_period WHERE year = '%s' AND month = '%s'", year, month)
}

YMfromPeriod <- function() {
  "SELECT periodid, CAST(year AS SIGNED) AS year, CAST(month AS SIGNED) AS month FROM dim_period ORDER BY periodid DESC"
}