| Author |
 Topic  |
|
|
tsimsnh1
Starting Member
4 Posts |
 Posted - 08/02/2012 : 11:19:27
|
I'm working on a view that will provide fields for end users to apply to business objects web intelligence reports. This part works as expected:
_________________________________________________________
WITH T1 AS
(
SELECT SAPR_STUDENT_ID, SAP_RESULTS_ID, SAPR_SAP_TYPE_ID, SAPR_OVR_SAP_DATE, SAP_RESULTS_ADDDATE, SAPR_OVR_SAP_STATUS, SAPR_CALC_SAP_STATUS,
CASE WHEN SAPR_OVR_SAP_STATUS IS NULL THEN SAPR_CALC_SAP_STATUS ELSE SAPR_OVR_SAP_STATUS END AS CURRENT_SAP_STATUS --If SAPR_OVR_SAP_STATUS IS NULL, USE SAPR_CALC_STATUS
FROM SAP_RESULTS
)
(SELECT SAPR_STUDENT_ID, SAP_RESULTS_ID, CURRENT_SAP_STATUS, SAPR_SAP_TYPE_ID,
ROW_NUMBER() OVER (PARTITION BY SAPR_STUDENT_ID ORDER BY SAPR_OVR_SAP_DATE DESC, SAP_RESULTS_ADDDATE DESC, SAPR_STUDENT_ID ASC) AS RANK_STATUS
FROM T1
WHERE SAPR_STUDENT_ID = '0035308'
AND SAPR_SAP_TYPE_ID = 'F05SPRSA'
_________________________________________________________
This is the ressult set using 'FO5SPRSA' as filter criteria shown in above query, which is the expected result.
-----------------
SAPR_STUDENT_ID SAP_RESULTS_ID CURRENT_SAP_STATUS SAPR_SAP_TYPE_ID
0035308 31465 EXT F05SPRSA
-----------------
The customer would now like to filter on SAPR_SAP_TYPE_ID LIKE 'F05%'
Here is the result set using the wildcard criteria.
-----------------
SAPR_STUDENT_ID SAP_RESULTS_ID CURRENT_SAP_STATUS SAPR_SAP_TYPE_ID
0035308 32264 SAT F05SPSSA
0035308 31465 EXT F05SPRSA
-----------------
From this result set the user would like the most recent of the 2 returned only. So in this case he only wants SAT displayed. If I move the ROW_NUMBER function further up in the query so I can filter on it, no results are displayed because the SAT result is ranked as number 5. Any suggestions on how I can resolve this. Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 08/02/2012 : 11:36:55
|
sorry your issue is not clear. we dont know how current rank values are coming so cant suggest on that. bust looks like what you're after is this
WITH T1 AS
(
SELECT SAPR_STUDENT_ID, SAP_RESULTS_ID, SAPR_SAP_TYPE_ID, SAPR_OVR_SAP_DATE, SAP_RESULTS_ADDDATE, SAPR_OVR_SAP_STATUS, SAPR_CALC_SAP_STATUS,
CASE WHEN SAPR_OVR_SAP_STATUS IS NULL THEN SAPR_CALC_SAP_STATUS ELSE SAPR_OVR_SAP_STATUS END AS CURRENT_SAP_STATUS --If SAPR_OVR_SAP_STATUS IS NULL, USE SAPR_CALC_STATUS
FROM SAP_RESULTS
)
SELECT *
FROM
(
(SELECT SAPR_STUDENT_ID, SAP_RESULTS_ID, CURRENT_SAP_STATUS, SAPR_SAP_TYPE_ID,
ROW_NUMBER() OVER (PARTITION BY SAPR_STUDENT_ID ORDER BY SAPR_OVR_SAP_DATE DESC, SAP_RESULTS_ADDDATE DESC, SAPR_STUDENT_ID ASC) AS RANK_STATUS
FROM T1
WHERE SAPR_STUDENT_ID = '0035308'
AND SAPR_SAP_TYPE_ID LIKE 'F05%'
)t
WHERE RANK_STATUS<=2
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
tsimsnh1
Starting Member
4 Posts |
Posted - 08/02/2012 : 11:57:59
|
visakh16 -- Thanks so much for you quick reply. Here is what I ended up with:----------------------------------
WITH T1 AS
(
SELECT SAPR_STUDENT_ID, SAP_RESULTS_ID, SAPR_SAP_TYPE_ID, SAPR_OVR_SAP_DATE, SAP_RESULTS_ADDDATE, SAPR_OVR_SAP_STATUS, SAPR_CALC_SAP_STATUS,
CASE WHEN SAPR_OVR_SAP_DATE IS NULL THEN SAP_RESULTS_ADDDATE ELSE SAPR_OVR_SAP_DATE END AS CURRENT_SAP_DATE,
CASE WHEN SAPR_OVR_SAP_STATUS IS NULL THEN SAPR_CALC_SAP_STATUS ELSE SAPR_OVR_SAP_STATUS END AS CURRENT_SAP_STATUS
FROM SAP_RESULTS
)
SELECT *
FROM
(SELECT SAPR_STUDENT_ID, SAP_RESULTS_ID, CURRENT_SAP_STATUS, SAPR_SAP_TYPE_ID, CURRENT_SAP_DATE,
DENSE_RANK() OVER (PARTITION BY SAPR_STUDENT_ID ORDER BY SAP_RESULTS_ID DESC, CURRENT_SAP_DATE ASC) AS RANK_STATUS
FROM T1
WHERE SAPR_STUDENT_ID = '0035308'
AND SAPR_SAP_TYPE_ID LIKE 'F05SPRSA%'
)T
WHERE RANK_STATUS =1
-------------------------------------
Works perfectly, thanks again! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 08/02/2012 : 12:48:47
|
welcome. but this will just give you ones with latest id and date value only not recent 2
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Query assistance
