4 Replies Latest reply on Nov 7, 2016 9:57 AM by Nandhakumar Ramanathan

    Sub Query

    Nandhakumar Ramanathan



      Query1 will bring all the data from DB. To achieve a scenario i want use Query2.


      If you see in Query2 we used a subquery for filtering few values. How can we achieve this by using Query1?


      I know very well that we can use Query2 in custom sql and bring in data. But but i have lot of scenarios like this. I want to use only Query1 and achieve the scenario of Query2.


      Please help me here.


      Query 1:

      SELECT [sessionid]













        FROM [PRPTY_16] APP

        INNER JOIN [CustomerInfo] CI ON APP.billsubscriber =CI.subscribernbr



      Query 2:


      DATEPART(YEAR,process_start_dt) AS Y_ORDER,

      DATEPART(MONTH,process_start_dt) AS M_ORDER,

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

      count(sessionid) as Tot_request 

      from [approp].[PRPTY_16] APP

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

      AND app.sessionid in

      (select sessionid from [approp].[PRPTY_16]  where sequancenbr =2 AND (SUBSTRING(billsubscriber,1,3)!='041'

      AND  SUBSTRING(billsubscriber,12,1)!='R'))

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