CODEWITHSUNDEEP
sqlsql-server-2008t-sqlcommon-table-expression
bala3569
bala3569

Reputation: 11010

combining two CTE in a single query

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.

Here int the below query i can able to select values from second CTE

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

I need to merge the below query with the above one

  SELECT   CTE1.CONSUMERID,              
                  CTE1.CHILDID,     
                  CTE1.APPLICATIONUSAGESTARTDATE,              
                  CTE1.WEEKNUMBER
      FROM CTE1              
WHERE RN = 1 ORDER BY CTE1.CHILDID ASC

Any suggestion?

Upvotes: 0

Views: 3183

Answers (1)

Deepshikha
Deepshikha

Reputation: 10264

If you are joining values from CTE1 and CTE2 based on CHILDID column then you can write as:

SELECT 
  CTE1.CONSUMERID, 
  CTE1.CHILDID,
  CTE1.APPLICATIONUSAGESTARTDATE,
  CTE1.WEEKNUMBER,
  -- 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
   LEFT  JOIN CTE1   on CTE1.CHILDID = a.CHILDID and CTE1.RN = 1 
   group by CTE1.CONSUMERID,CTE1.CHILDID,CTE1.APPLICATIONUSAGESTARTDATE,CTE1.WEEKNUMBER
   ORDER BY CTE1.CHILDID ASC

Upvotes: 1

Related Questions