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

    Sub Query

    Nandhakumar Ramanathan

      Hi,

       

      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]

            ,[sequancenbr]

            ,[felink]

            ,[billsubscriber]

               ,[company_group_name]

            ,[process_start_dt]

            ,[result_sent_dt]

            ,[total_record_matched]

            ,[hyg_state]

            ,[passport_flag]

            ,[sf_renewal_flag]

            ,[ICUSTOMERORDER]

            ,[IREQUEST]

        FROM [PRPTY_16] APP

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

       

       

      Query 2:

      SELECT

      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)

       

      Regards,

      Nandha