title: combining two CTE in a single query
body: <p>Here i have two CTE and i need to select values from these two CTE as a single query. I can able to select values from a single CTE at a time but i dont know like how to merge these two CTE and select values from both CTE.</p>
  
  <p>Here int the below query i can able to select values from second CTE</p>
  
  <pre><code>DECLARE @now DateTime;              
  DECLARE @Firstweekstart DateTime;           
  DECLARE @FirstweekEnd DateTime;        
  
  SET @now = CONVERT(datetime, CONVERT(date,getdate()));      
  
  SET @Firstweekstart = DATEADD(              
                   DD,              
                   -(DATEPART(DW, @now - 7) - 1),              
                   @now - 7);               
  
                   SET @Firstweekstart =  dateadd(ms, -1, @Firstweekstart)       
  
  SET @Firstweekend = DATEADD(              
                 DD,              
                 7 - (DATEPART(DW, @now - 7)),              
                 @now - 7);           
  
                 SET @Firstweekend = dateadd(ms, -3, @Firstweekend+1)     
  
  
  
  
   ;WITH CTE1 AS              
        (              
           SELECT CHINFO.CHILDID,CHINFO.CONSUMERID,               
           DATEADD(DD, -(DATEPART(DW, CHINFO.Adddate)-1), CHINFO.Adddate) AS APPLICATIONUSAGESTARTDATE,              
           DATEDIFF(WW,CHINFO.Adddate,@now) AS WEEKNUMBER,              
                    ROW_NUMBER() OVER               
                     (PARTITION BY CHINFO.CHILDID ORDER BY CHINFO.Adddate ASC) AS RN              
           FROM BKA.CHILDINFORMATION CHINFO               
        LEFT OUTER JOIN  BKA.CHILDEVENTS CHE               
        ON CHE.CHILDID = CHINFO.CHILDID  
        GROUP BY CHINFO.CHILDID,CHINFO.CONSUMERID,CHINFO.Adddate              
        )              
  
  
  ,CTE2 as
  (SELECT Distinct CHINFO.CHILDID ,CHE.TIMESTAMP
     ,ROW_NUMBER() OVER (PARTITION BY CHINFO.CHILDID  ORDER BY CHE.TIMESTAMP) row
   FROM BKA.CHILDINFORMATION CHINFO               
         JOIN  BKA.CHILDEVENTS CHE               
         ON CHE.CHILDID = CHINFO.CHILDID 
          WHERE CHE.TYPE = 'pottybreak'
         AND CHE.ADDDATE BETWEEN @Firstweekstart AND @Firstweekend 
          GROUP BY CHINFO.CHILDID,CHE.TIMESTAMP
          )
  SELECT 
    a.CHILDID    ,
     AVG(CONVERT(DECIMAl,DATEDIFF ( minute , b.TIMESTAMP , a.TIMESTAMP))) as CURRENTWEEKTIMERRESTART
  FROM
     CTE2 a
     LEFT  JOIN CTE2 b
     on a.CHILDID = b.CHILDID
      and a.row = b.row+1
      group by a.childid
  </code></pre>
  
  <p>I need to merge the below query with the above one</p>
  
  <pre><code>  SELECT   CTE1.CONSUMERID,              
                    CTE1.CHILDID,     
                    CTE1.APPLICATIONUSAGESTARTDATE,              
                    CTE1.WEEKNUMBER
        FROM CTE1              
  WHERE RN = 1 ORDER BY CTE1.CHILDID ASC
  </code></pre>
  
  <p>Any suggestion?</p>