July 27, 2006 at 9:17 am
I have a table with the following test data
id serial_num status last_updated_by last_updated_date
1 1-45540381 ACCEPT tom 7/26/2006 3:14:31 PM
2 1-45540505 ACCEPT tom 7/26/2006 3:15:08 PM
3 1-45540381 UNACCEPT tom 7/26/2006 5:16:11 PM
4 1-45540589 ACCEPT tom 7/26/2006 5:17:18 PM
5 1-45541221 ACCEPT tom 7/26/2006 5:17:34 PM
My query needs to return a total count for the ACCEPT'ed records and exclude those last updated records of same serial_num where the status is not equal to ACCEPT
From the above data the query should return 3 as the result to include
recod 2, 4, and 5 since the record 1 and 3 are the same and its last updated timestamp with a status is UNACCEPT.
My current query statement returns a value of 2 which is not correct.
SELECT count(1) AS value
FROM ra_wf_audit_t
WHERE wf_action_cd = 'ACC'
AND last_updated_by = 'nancy'
AND last_updated_dt > (SELECT last_updated_dt
FROM ra_wf_audit_t
WHERE wf_action_cd != 'ACC')
Thanks in advance for your help!
Tuan
July 27, 2006 at 9:41 am
Your query doesn't match your sample table at all. Given the sample table, this would be your query to get the described result. (Replace "YourTable" with the appropriate table name.)
select
COUNT(DISTINCT serial_num)
from YourTable
where status = 'ACCEPT'
July 27, 2006 at 9:47 am
create table #temp
(
id int,
serial_num varchar(15),
status varchar(10),
last_updated_by varchar(10),
last_updated_date datetime
)
insert into #temp values (1,'1-45540381','ACCEPT','tom','7/26/2006 3:14:31 PM')
insert into #temp values (2,'1-45540505','ACCEPT','tom','7/26/2006 3:15:08 PM')
insert into #temp values (3,'1-45540381','UNACCEPT','tom','7/26/2006 5:16:11 PM')
insert into #temp values (4,'1-45540589','ACCEPT','tom','7/26/2006 5:17:18 PM')
insert into #temp values (5,'1-45541221','ACCEPT','tom','7/26/2006 5:17:34 PM')
select *
from #temp a
where status = 'ACCEPT' and
not exists (select 'X' from #temp where Serial_num = a.Serial_num
and status = 'UNACCEPT'
and last_updated_date > a.last_updated_date)
drop table #temp
July 27, 2006 at 10:21 am
Excellent! Thanks very much for your quick responses.
Tuan
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply