Rachel Rachel - 2 months ago 8
SQL Question

Filtering a query with subqueries

In order to understand the question I will explain the result expected.
I have a db table where I save some data of the activity of the current day. Then I want to sum some numeric fields and the last register of the text fields, using a filter between two dates.

Example:

•DB TABLE

ID|CALLS|RESULT | DATE

1 | 2 |FAIL |15/09/16
1 | 1 |ERROR |16/09/16
1 | 3 |OK |17/09/16


•SUM BETWEEN 15 and 17

ID|TOTAL CALLS|LAST RESULT
1 | 6 | OK

•SUM BETWEEN 15 and 16

ID|TOTAL CALLS|LAST RESULT
1 | 3 | ERROR


-Would this be the solution?

SELECT DISTINCT ID,
TOTAL_CALLS=SUM(CALLS),
LAST_RESULT= (
SELECT RESULT FROM TABLE T2 where T2.DATE between MIN(T1.DATE) and MAX (T1.DATE) and T1.ID=T2.ID
)

FROM TABLE T1
WHERE
TIME BETWEEN 15/09/16 and 17/09/16
GROUP BY ID


Thank you very much!

Regards

Answer

Use the below query.

;WITH cte_1
AS
(SELECT  ID,SUM(CALLS)OVER( PARTITION BY ID) [TOTAL CALLS] 
 ,Result [LAST RESULT]
 ,ROW_NUMBER()OVER( PARTITION BY ID ORDER BY [DATE] desc) RNO
from #YourTable T
WHERE [DATE] between '09/15/2016' AND '09/16/16')
SELECT ID,[TOTAL CALLS],[LAST RESULT] 
FROM cte_1 
WHERE Rno=1