# Need Urgent Query

• ### Question

• `test`
• Edited by Wednesday, August 20, 2014 2:34 PM
Wednesday, August 20, 2014 5:25 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 Sunday, August 24, 2014 11:36 AM
• Marked as answer by 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 Sunday, August 24, 2014 11:36 AM
• Marked as answer by 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 Sunday, August 24, 2014 11:36 AM
• Marked as answer by 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 Sunday, August 24, 2014 11:36 AM
• Marked as answer by Thursday, August 28, 2014 1:16 PM
Wednesday, August 20, 2014 8:17 AM