2 Replies Latest reply on Dec 1, 2016 7:59 AM by Nandhakumar Ramanathan

    Need to subquery, is custom SQL my only option to achieve this?

    Nandhakumar Ramanathan

      Hi All,

       

      I have a scenario to use Sub-query to achieve the result set for my dashboard.

       

      With the help of "query 1" have to achieve the "result set 1" of "Query 2". But I am getting the result exactly in tableau.

      But if I include the sub query (Query 3) for the end "result set 2" I am not getting the exact value which i have to get by using the Query 1.

       

      Please let me know how can I achieve both the result set by using the Query 1. 

       

      Query 1:

      SELECT [session_id]

            ,[seq_nbr]

            ,[fe_link]

            ,[bill_subscriber]

            ,[pre_process_start]

            ,[result_sent_dt]

            ,[tot_rec_matched]

            ,[hyg_state]

            ,[passport_flag]

            ,[sf_renewal_flag]

            ,[I_CST_ORD]

            ,[I_RQST]

        FROM [UW_SEERI].[approp].[APLUS_PRPTY_16] APP

       

       

      Query 2:

      SELECT

      DATEPART(YEAR,pre_process_start) AS Y_ORDER,

      DATEPART(MONTH,pre_process_start) AS M_ORDER,

      SUBSTRING(CAST(CAST(pre_process_start AS DATETIME) AS VARCHAR), 1, 3) AS M,

      Count(session_id) as Tot_request

      from [approp].[APLUS_PRPTY_16] APP

      WHERE bill_subscriber not in ('90019633637Q', 'RETROFARMERS', '001X0000CAPP', '001XXFARMERS')

      GROUP BY DATEPART(YEAR,pre_process_start ),DATEPART(MONTH,pre_process_start ),SUBSTRING(CAST(CAST(pre_process_start  AS DATETIME) AS VARCHAR), 1, 3)

       

      Result set 1:

       

      Y_ORDERM_ORDERMTot_request
      20161Jan1097990
      201512Dec1015599
      20162Feb1189253
      20164Apr1259142
      20163Mar1318111
      20165May1250200

       

      Tableau Result:

       

       

      Query 3:

       

      SELECT

      DATEPART(YEAR,pre_process_start) AS Y_ORDER,

      DATEPART(MONTH,pre_process_start) AS M_ORDER,

      SUBSTRING(CAST(CAST(pre_process_start AS DATETIME) AS VARCHAR), 1, 3) AS M,

      Count(session_id) as Tot_request

      from [approp].[APLUS_PRPTY_16] APP

      INNER JOIN [UW_SEERI].[approp].[CustomerInfo] CI ON APP.bill_subscriber =CI.subscriber_nbr

      WHERE bill_subscriber not in ('90019633637Q', 'RETROFARMERS', '001X0000CAPP', '001XXFARMERS')

      AND app.session_id in (select session_id from [approp].[APLUS_PRPTY_16]  where seq_nbr =2 AND (SUBSTRING(bill_subscriber,1,3)!='041' AND  SUBSTRING(bill_subscriber,12,1)!='R'))

      GROUP BY DATEPART(YEAR,pre_process_start ),DATEPART(MONTH,pre_process_start ),SUBSTRING(CAST(CAST(pre_process_start  AS DATETIME) AS VARCHAR), 1, 3)

       

       

      Result set 2:

       

      Y_ORDERM_ORDERMTot_request
      201512Dec11484
      20162Feb11394
      20164Apr25872
      20163Mar15014
      20161Jan10687
      20165May15885

       

       

      Tableau Result:

       

      Whenever I filter "seq_nbr=2" i am getting very low value.

       

      Regards,

      Nandha