locked
Need Urgent Query RRS feed

Answers

  • Try below code.

    ;WITH cteTable AS
    (
    select CID,RID,SDATE, SUM(B_STATUS) OVER (Partition by CID, SDATE) StatusCnt,COUNT(1) OVER (Partition by CID, SDATE) RowCnt
    from @TableB
    )
    
    SELECT A.TID,A.CID,B.RID,A.SDATE, 
    		B.StatusCnt/B.RowCnt AS A_Status
    FROM @TableA A
    INNER JOIN cteTable B
    	ON B.CID = A.CID
    	AND B.SDATE = A.SDATE
    	AND B.RowCnt <=2

    • Proposed as answer by Charlie Liao Sunday, August 24, 2014 11:36 AM
    • Marked as answer by Charlie Liao Thursday, August 28, 2014 1:16 PM
    Wednesday, August 20, 2014 6:19 AM
  • SELECT TID,CID,RID,SDATE,MinStatus AS A_STATUS
    FROM
    (
    SELECT *,MIN(B_STATUS) OVER (PARTITION BY C_ID,SDATE) AS MinStatus
    FROM @TableB
    )t
    WHERE MinStatus = 1


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Charlie Liao Sunday, August 24, 2014 11:36 AM
    • Marked as answer by Charlie Liao Thursday, August 28, 2014 1:16 PM
    Wednesday, August 20, 2014 8:17 AM

All replies

  • SELECT TID ,sdate 
    FROM @TableA

    EXCEPT 

    SELECT A.TID ,A.sdate 
    FROM @TableA A
    INNER JOIN  @TableB B ON A.CID = B.CID AND A.SDATE = B.SDATE
    WHERE b.B_STATUS = 0

    Thanks and regards, Rishabh K

    Wednesday, August 20, 2014 6:06 AM
  • Try below code.

    ;WITH cteTable AS
    (
    select CID,RID,SDATE, SUM(B_STATUS) OVER (Partition by CID, SDATE) StatusCnt,COUNT(1) OVER (Partition by CID, SDATE) RowCnt
    from @TableB
    )
    
    SELECT A.TID,A.CID,B.RID,A.SDATE, 
    		B.StatusCnt/B.RowCnt AS A_Status
    FROM @TableA A
    INNER JOIN cteTable B
    	ON B.CID = A.CID
    	AND B.SDATE = A.SDATE
    	AND B.RowCnt <=2

    • Proposed as answer by Charlie Liao Sunday, August 24, 2014 11:36 AM
    • Marked as answer by Charlie Liao Thursday, August 28, 2014 1:16 PM
    Wednesday, August 20, 2014 6:19 AM
  • SELECT TID,CID,RID,SDATE,MinStatus AS A_STATUS
    FROM
    (
    SELECT *,MIN(B_STATUS) OVER (PARTITION BY C_ID,SDATE) AS MinStatus
    FROM @TableB
    )t
    WHERE MinStatus = 1


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    • Proposed as answer by Charlie Liao Sunday, August 24, 2014 11:36 AM
    • Marked as answer by Charlie Liao Thursday, August 28, 2014 1:16 PM
    Wednesday, August 20, 2014 8:17 AM