none
Display data from last day only

    Question

  • I have a SSRS report that display Test execution status, that contains value for Test Statuses(Passed, failed, N/A, No Run, etc). I need to display only the last executed status by date..
    Tuesday, October 22, 2013 9:35 PM

All replies

  • Try a window function (ROW_NUMBER) in your dataset, e.g.:

    DECLARE @data TABLE (
    	 testid int
    	,status varchar(16)
    	,testdate datetime
    );
    
    INSERT @data VALUES
     (1, 'FAILED', '10/22/2013 01:00:00')
    ,(1, 'NO RUN', '10/22/2013 02:00:00')
    ,(1, 'PASSED', '10/22/2013 03:00:00')
    ,(2, 'PASSED', '10/22/2013 04:00:00')
    ,(2, 'N/A', '10/22/2013 04:30:00')
    ,(2, 'NO RUN', '10/22/2013 05:00:00')
    ,(3, 'NO RUN', '10/22/2013 06:00:00')
    ,(3, 'FAILED', '10/22/2013 07:00:00')
    ,(3, 'PASSED', '10/22/2013 08:00:00')
    ,(3, 'PASSED', '10/22/2013 08:30:00');
    
    WITH data (testid, status, testdate, runsequence)
    AS (
    	SELECT testid, status, testdate, ROW_NUMBER() OVER (PARTITION BY testid ORDER BY testdate DESC)
    	FROM @data
    )
    
    SELECT testid, status, testdate
    FROM data 
    WHERE runsequence = 1


    Jason


    Tuesday, October 22, 2013 10:52 PM
  • Hi Jason,

    Thank you very much for your reply. Let me show what have have and what I expected.

    Please see the Image Attached. As you see in the Image in this case I just want to display is The Passed results, cause they are the last execution test.

    Thank you very Much,

    I really appreciate your help.

    Angel

    This is my query:

    SELECT A.rel_id As 'RelID', A.REL_NAME as 'Release',b.RCYC_NAME As 'Cycle',F.TS_USER_06 As 'Category', F.TS_NAME,MAX(E.TC_EXEC_DATE + E.TC_EXEC_TIME) AS LastExecDate,
    SUM(CASE WHEN (E.TC_STATUS = 'Passed' OR
     E.TC_STATUS = 'Failed' OR
     E.TC_STATUS = 'Deferred' OR
     E.TC_STATUS = 'Blocked' OR
     E.TC_STATUS = 'Failed' OR
     E.TC_STATUS = 'No Run' OR
     E.TC_STATUS = 'N/A' OR
     E.TC_STATUS = 'Not Completed') THEN 1 ELSE 0 END) AS 'Total',
    max(Convert(varchar(10),A.REL_START_DATE,101)) as REL_START_DATE,
    max(Convert(varchar(10),A.REL_END_DATE,101)) as REL_END_DATE,
    max(Convert(varchar(10),B.RCYC_START_DATE,101)) as StartDate,
    max(Convert(varchar(10),B.RCYC_END_DATE,101)) as EndDate,
    SUM(CASE E.TC_STATUS WHEN 'Passed' THEN 1 ELSE 0 END)  AS 'Passed',
    SUM(CASE E.TC_STATUS WHEN 'Failed' THEN 1 ELSE 0 END) AS 'Failed',
    SUM(CASE E.TC_STATUS WHEN 'Blocked' THEN 1 ELSE 0 END) AS 'Blocked',
    SUM(CASE E.TC_STATUS WHEN 'No Run' THEN 1 ELSE 0 END) AS 'No Run',
    SUM(CASE E.TC_STATUS WHEN 'N/A' THEN 1 ELSE 0 END) AS 'N/A',
    SUM(CASE E.TC_STATUS WHEN 'Not Completed' THEN 1 ELSE 0 END) AS 'Not Completed'

    from td.releases A inner join  td.release_cycles B on A.rel_id = B.rcyc_parent_id
    inner join td.cycle D on D.cy_Assign_rcyc =B.rcyc_id
    inner join td.CYCL_fold C on d.CY_FOLDER_ID = c.CF_ITEM_ID
    inner join td.testcycl E on E.tc_cycle_id = D.cy_cycle_id
    inner join td.test F on F.TS_TEST_ID = E.TC_TEST_ID


    WHERE A.rel_id in (@Release) and b.RCYC_NAME in (@Cycle) AND F.TS_USER_06 in (@ComponentID) /* and (E.TC_Status in (@Status))*/

    group by F.TS_USER_06,A.rel_id,F.TS_NAME,E.TC_STATUS,A.REL_NAME, A.REL_START_DATE, A.REL_END_DATE,B.RCYC_NAME,convert(varchar(10),B.RCYC_START_DATE,101),Convert(varchar(10),B.RCYC_END_DATE,101),B.RCYC_NAME
    order by E.TC_STATUS


    angelatino2013

    Wednesday, October 23, 2013 1:13 PM
  • I don't see any image posted.

    Jason

    Wednesday, October 23, 2013 3:57 PM

  • angelatino2013

    Wednesday, October 23, 2013 7:31 PM
  • Jason,

    As you could see in the image, I need to display in the report only the last execution status in this case is Passed, so i need display blank or 0 the rest of the values(failed, blocked, etc.). In the query you could see i select the lastExecDate.

    Thanks,

    Angel


    angelatino2013

    Wednesday, October 23, 2013 7:35 PM
  • In that case, I would probably use PIVOT, e.g.:
    SELECT testid, MAX(testdate) [testdate], SUM([Failed]) [failed], SUM([No Run]) [no run], SUM([Passed]) [passed], SUM([N/A]) [N/A]
    
    FROM (
    	SELECT testid, status, testdate
    	FROM @data
    ) [data]
    
    PIVOT (
    	COUNT(status) FOR [status] IN (
    		 [FAILED]
    		,[NO RUN]
    		,[PASSED]
    		,[N/A]
    		)
    ) [pivot]
    
    WHERE CONVERT(date, testdate) = (SELECT MAX(CONVERT(date, testdate)) FROM @data)
    
    GROUP BY testid, CONVERT(date, testdate)
    Wednesday, October 23, 2013 7:52 PM
  • Jason,

    thank you very much, I will plug this query and I will let you know the result.

    Angel


    angelatino2013

    Wednesday, October 23, 2013 9:00 PM