xlData

EXCEL.FIX <- as.Date("1904-01-01") - as.Date("1899-12-30")

country = c("ES", "IT")

keep <- xlData[country]

for (co in country) 
  keep[[co]] [, country := co]

keep <- rbindlist(keep)

date.cols <- grep("date", names(keep), value=TRUE, ignore.case=TRUE)
upc.cols <-  grep("upc", names(keep), value=TRUE, ignore.case=TRUE) 

upc.cols <- upc.cols[[1]]

USING <- keep[, c(upc.cols, date.cols), with=FALSE]
date.cols 
USING[, Date := Date + EXCEL.FIX]
USING[, lapply(.SD, function(x) as.Date(as.character(x), origin="1904-01-01")), .SDcols=date.cols]

Z <- USING[, as.character(Campaign.Phase.Start.Date)]
as.Date(Z, origin="1975-01-01", format="%Y-%m-%d")

as.Date(USING[, get(date.cols[[1]])])

setnames(USING, upc.cols[[1]], "UPC")

reproduce(USING, whole=TRUE)

===============================================================================================

 <<<<  THIS PART IN RSTUDIO  >>>>
library(RMySQL)
EXCEL.FIX <- as.Date("1904-01-01") - as.Date("1899-12-30")

pulling <- USING[, list(UPC, Date = Campaign.Phase.Start.Date)]

(P <- pulling[, list(Date=as.POSIXct(as.vector(sapply(Date, function(x) x+daysAdd)), origin="1970-01-01")), by=UPC])

# FOR MANUAL:  (P <- P[UPC %in% unique(UPC)[1:2]  & Date %in% unique(Date)[1:10], ])

tblName  <- "fact_analytics" # "itunes_raw"
date_col <- "processedDayTime" # "download_date" 
upc_col  <- "releaseId"    # "UPC


## OLD: 
# P.QRYs <- 
#   P[, list(QRY=paste("
#   SELECT * FROM", tblName, "
#   WHERE ", upc_col," = ", UPC, "
#   AND
#   ",date_col," > ", paste0("'", min(Date), "'"), "
#   AND
#   ",date_col," < ", paste0("'", max(Date), "'")
#   )), by=UPC]
# QRY.Full <- P.QRYs[, paste(QRY, collapse=" UNION ")]


QRY.Full <- 
  P[, 
    paste("
      SELECT * FROM", tblName, "
      WHERE ", upc_col," in (", paste0(unique(UPC), collapse=","), ") 
      AND
      ",date_col," > ", paste0("'", min(Date), "'"), "
      AND
      ",date_col," < ", paste0("'", max(Date), "'")
    )]


# for manual test
# gsub("\\n", " ", QRY.Full)

con <- getDB(host="analytics.dw.theorchard.com")

QRY.Results <- runQry(QRY.Full, getDB())

 <<<<  THIS PART IN RSTUDIO  >>>>

============================================================================================

  << MANUALLY RAN QUEREY >>

  Q <- 'select * FROM (
  SELECT * FROM `fact_analytics`        
  WHERE  releaseId  in (888003018822,888003119253,888003116115,888003168763,888003020702) 
  ) AS T
  WHERE T.processedDayTime > "2012-08-01" AND T.processedDayTime  <  "2013-10-26"'

  QRY.Results <- runQry(Q, getDB())

  QRY.Results

  ~~  EXPORTED TO CSV FILE ~~


  ~~~   MANUALLY IMPORT  ~~~


  file.manual <- "~/Desktop/fact_analytics_10-3-13_9-53 AM.csv" # OLD: "~/Desktop/query_result.csv" 
  Qres <- fread(file.manual, integer64="character", 
            colClasses=c(releaseId="character", uuid="character", subaccountId="character", processedDayTime="POSIXct"))
  Qres[Qres=="NULL"] <- NA
  Qres[, processedDayTime := as.POSIXct(processedDayTime)]
  Qres

================================================================================================


## DO THIS EARLIER
col.keyDate <- "Campaign.Phase.Start.Date"
col.compDate <- "processedDayTime"


USING [, keyDate :=  lapply(.SD, as.Date), .SDcols=col.keyDate]
USING [, releaseId := UPC]

USING[, releaseId := as.numeric(as.character(releaseId))]
Qres[,  releaseId := as.numeric(as.character(releaseId))]
setkey(USING, "releaseId")
setkey(Qres,  "releaseId")

M <- merge(USING, Qres, allow.cartesian=TRUE)
M[, compDate := lapply(.SD, as.Date), .SDcols=col.compDate]

cols.mets <- c("royalty", "royaltyDollar", "units", "paidUnits", "freeUnits")


M2 <- M[ , .SD, .SDcols=cols.mets, by=list(keyDate, releaseId, compDate)]

M2

DT <- M2
yearComp <- function(DT, daysBack=90) {
  DT[, abs(keyDate - compDate), by=keyDate]
}

~~~~~~~~~~~~~~~~~~~~~~~
    ? zipId
    catalogId
    "uuid"

    c("dayId", "artistId", "labelId", "storeId", "genreId", "releaseId", 
    "trackId", "countryId", "transactionTypeId", 
    "isrcId", "processedDayTime", 
    "royalty", "royaltyDollar", "units", "paidUnits", "freeUnits")


    SELECT artistId, displayDate as Date FROM  fact_analytics 
    LEFT JOIN dim_day
    ON dim_day.dayId=fact_analytics.dayId
    LIMIT 10
~~~~~~~~~~~~~~~~~~~~~~~