cols <- qShowCols("analytics")

itunes.sql <- itunes_544(out="SQL")



QRY.itunes.raw <- 
"SELECT <datecol>, sale_return, royalty_currency AS currency, sum(units * royalty_price) AS gross_local_currency
FROM staging_raw_itunes
WHERE download_activity_date >= '2012-12-20'
GROUP BY 1, 2, 3
"
QRY.itunes.raw <-  sub("<datecol>", itunes.sql, QRY.itunes.raw)
------------

QRY.itunes.raw.royalty <- QRY.itunes.raw
QRY.itunes.raw.customer <- gsub("royalty", "customer", QRY.itunes.raw)

makeDTitunes <- function(QRY.itunes.raw) {
  DT.itunes.raw <- runQry(QRY.itunes.raw, verbose=FALSE)

  DT.itunes.raw[, sale_return := {x <- rep(1, .N); x[sale_return=="R"] <- -1; x}]

  DT.itunes.raw <- DT.itunes.raw[, list(gross_local_currency= sum(sale_return * gross_local_currency, na.rm=TRUE)), keyby=list(month=itunesmonth, currency)]

  cat("Total for May is $", DT.itunes.raw[.("2014-05-01")][, sum(gross_local_currency)], "\n")
  return(DT.itunes.raw)
}

DT.royalty  <- makeDTitunes(QRY.itunes.raw.royalty)
DT.customer <- makeDTitunes(QRY.itunes.raw.customer)


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

runQry("select * from dim_day where displaydate in ('2014-05-04', '2014-05-31')")

5604 - '2014-05-04'
5631 - '2014-05-31'

itunes_may_toal <-  runQry("
SELECT storeid, sum(units * royaltydollar) AS gross
FROM fact_analytics
WHERE dayid BETWEEN 5604 AND 5631
  AND storeid = 1
GROUP BY storeid")

itunes_may_toal <-  runQry("
SELECT storeid, sum(units * royaltydollar) AS gross
FROM fact_analytics
WHERE dayid >= 5604
  AND dayid <= 5631
  AND storeid = 1
GROUP BY storeid")

itunes_may_toal <-  runQry("
SELECT storeid, sum(units * royaltydollar) AS gross
FROM fact_analytics
WHERE download_activity_date between '2014-05-01' and '2014-05-31'
  AND storeid = 1
GROUP BY storeid")

itunes_544("2013", out="S")



formnumb(itunes_may_toal)

itunes_may_statement <- 8347967

fwp(percOf(itunes_may_toal$gross, itunes_may_statement))


runQry("select * from dim_day ")

 [1] "artistid"               "dayid"                  "labelid"                "storeid"               
 [5] "genreid"                "releaseid"              "trackid"                "countryid"             
 [9] "transactiontypeid"      "zipid"                  "catalogid"              "isrcid"                
[13] "imprintid"              "currencyid"             "storeuserid"            "processeddaytime"      
[17] "royalty"                "royaltydollar"          "units"                  "paidunits"             
[21] "freeunits"              "uuid"                   "subaccountid"           "download_activity_date"



cols <- qShowCols("iTunes")

 [1] "provider"                 "service_provider_country" "vendor_identifier"        "upc"                     
 [5] "isrc"                     "artist"                   "title"                    "record_company"          
 [9] "product_type_identifier"  "units"                    "royalty_price"            "download_activity_date"  
[13] "order_id"                 "postal_code"              "customer_identifier"      "download_date"           
[17] "sale_return"              "customer_currency"        "country_code"             "royalty_currency"        
[21] "preorder"                 "isan"                     "customer_price"           "apple_id"                
[25] "cma"                      "asset"                    "grid"                     "promo_code"              
[29] "parent_id"                "season_pass"              "belongs_to_ioda"          "attributable_purchase"   
[33] "primary_genre"            "parent_type_id"           "original_upc"            

units
royalty_price
royalty_currency
customer_currency
customer_price