Using dateRangeInput with BETWEEN logic in oracle

sql
#1

Hi,
I'm stuck with in a SQL code.My data is not responding according to the dateRangeInput. Help me in fixing it.

#server.r

 ORA_DB <- reactive({
    
                      if(is.null(input$range[1]) || is.null(input$range[2])) {
                        dayStrt <- '2019-01-16'
                        dayend <- '2019-02-16'
                      } else {
                        dayStrt <- input$range[1]
                        dayend <- input$range[2]
                      }
                      
    metaQuery <- paste("SELECT DA.AGENT_NAME,DW.WEEK_DAY,DS.SHIFT_TYPE,DF.CONTACT_DATE,TOTAL_CONNECTED_IN_HRS,
                       TOTAL_WAITING_IN_HRS, TOTAL_PAUSED_IN_HRS, TOTAL_DEASSIGN_IN_HRS,
                       TOTAL_SUMMARY_IN_HRS, TOTAL_CONNECTED_IN_MINS, TOTAL_WAITING_IN_MINS,
                       TOTAL_PAUSED_IN_MINS, TOTAL_DEASSIGN_IN_MINS, TOTAL_SUMMARY_IN_MINS 
                       FROM 
                       DIM_AGENT DA,
                       DIM_SHIFTS DS,
                       DIM_WEEKS DW,
                       DAILY_NOBLE_DATA_FACT DF
                       WHERE 
                       DF.AGENT_ID =DA.AGENT_ID
                       AND DS.SHIFT_ID=DF.SHIFT_ID
                       AND DW.WEEK_ID = DF.WEEK_ID
                       AND TO_DATE(DF.CONTACT_DATE , 'MM/DD/YYYY') BETWEEN TO_DATE('",dayStrt,"', 'YYYY-MM-DD') 
                       AND TO_DATE('",dayend,"', 'YYYY-MM-DD') ;")
    
    # fetch all data from  daily_noble_data_fact
    data_temp  <- sqlQuery(connHandle, metaQuery,stringsAsFactors = F)
    
    print(nrow(data_temp))
    print(input$range)
    days_present <- unique(data_temp$WEEK_DAY)
    # Chanege the date Oracle format to R date
    data_temp$CONTACT_DATE <- as.Date(data_temp$CONTACT_DATE, "%m/%d/%Y")
    
  
  
  observeEvent(input$range,{
    print(input$range)
  })

#ui
 dateRangeInput("daterange3", "",
                 start  = "2019-01-15",
                 end    = "2019-01-31",
                 min    = "2005-01-01",
                 max    = "3000-12-21",
                 format = "mm/dd/yy",
                 separator = icon(class="far fa-arrow-alt-circle-right", "fa-1x")),align="center",
      br()
0 Likes

closed #3

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.

0 Likes