Code:
CREATE TABLE TEMP_table
(
ACCTNUM NUMBER,
l_DATE TIMESTAMP(3),
CODE VARCHAR2(35 BYTE),
VENDOR VARCHAR2(35 BYTE)
);
insert into temp_table values (1,sysdate+1/60,'bso','v1');
insert into temp_table values (1,sysdate+2/60,'bsof','v1');
insert into temp_table values (1,sysdate+3/60,'bsof','v2');
insert into temp_table values (1,sysdate+4/60,'bsof','v1');
iam getting desired result with this query but it is more complex
can any one simplify this query:
SELECT acctnum,code, vendor, l_date,
CASE
WHEN code = 'bsof'
THEN ( CASE
WHEN COUNT(DISTINCT code) OVER (PARTITION BY acctnum, vendor) >1 THEN
(CASE
WHEN LAG (code,1,0)
OVER (PARTITION BY acctnum, vendor
ORDER BY vendor, l_date) <> '0'
THEN
(CASE
WHEN RANK ()
OVER (PARTITION BY acctnum, vendor ORDER BY 0) = 1
THEN 'yes'
ELSE
'no' END)
ELSE
'no' END)
ELSE 'no' END )
ELSE
'no' END
result
FROM temp_table
WHERE code IN ('bso', 'bsof') and acctnum=1
ORDER BY acctnum ,l_date;
when iam trying to run this :;
my result is :
1 bso v1 3/23/2011 5:24:34.000 PM no
1 bsof v1 3/23/2011 5:48:36.000 PM yes
1 bsof v2 3/23/2011 6:12:38.000 PM no
1 bsof v1 3/23/2011 6:36:41.000 PM yes
can any one help me urgent
thanks in advance