Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You can JOIN CTE1 with CTE2 –  Serpiton Jun 10 at 9:51
    
Crosspost with SO –  Serpiton Jun 10 at 10:29
    
You can think of a CTE as a view which exists only for the life of the statement in which it is defined. It can be used with the same syntax a view would be. –  Michael Green Jun 10 at 10:54

1 Answer 1

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.