Complex query I need help with.

CREATE TABLE test (stk_num varchar(3), avg_num real, import_dt smalldatetime
)
INSERT INTO test values('aaa',27.44,'1/23/2006')
INSERT INTO test values('aaa',25.00,'1/30/2006')
INSERT INTO test values('aaa',1.76,'2/6/2006')
INSERT INTO test values('bbb',2.45,'1/23/2006')
INSERT INTO test values('bbb',3.98,'1/30/2006')
INSERT INTO test values('bbb',11.99,'2/6/2006')
INSERT INTO test values('ccc',0.00,'1/23/2006')
INSERT INTO test values('ccc',0.00,'1/30/2006')
INSERT INTO test values('ccc',4.11,'2/6/2006')
INSERT INTO test values('ddd',1.87,'1/23/2006')
INSERT INTO test values('ddd',3.87,'1/30/2006')
INSERT INTO test values('ddd',0.0,'2/6/2006')
INSERT INTO test values('eee',0.00,'1/23/2006')
INSERT INTO test values('eee',0.00,'1/30/2006')
INSERT INTO test values('eee',0.00,'2/6/2006')
INSERT INTO test values('fff',57.89,'1/23/2006')
INSERT INTO test values('fff',9.80,'1/30/2006')
INSERT INTO test values('fff',10.15,'2/6/2006')
INSERT INTO test values('ggg',22.09,'1/23/2006')
INSERT INTO test values('ggg',2.44,'1/30/2006')
INSERT INTO test values('ggg',17.82,'2/6/2006')
I have a table that contains the stock # and avg # and import date.
I need to return all the records with the same stock numbers that have a +
or - >= 20% change between their avg numbers but only for the last two impor
t
dates.
So for the info given above I need the output to look like this:
Stk_num avg_num import_dt
aaa 25.00 1/30/2006
aaa 1.76 2/6/2006
bbb 3.98 1/30/2006
bbb 11.99 2/6/2006
ccc 0.00 1/30/2006
ccc 4.11 2/6/2006
ddd 3.87 1/30/2006
ddd 0.00 2/6/2006
ggg 2.44 1/30/2006
ggg 17.82 2/6/2006
TIAPlease chaec if this works for you and let me know.
Thanks!
-- BEGIN SCRIPT
select a.stk_num
, a.avg_num
, a.import_dt
from (
select t1.stk_num
, t1.avg_num
, t1.import_dt
from dbo.test t1
join ( SELECT rank
, stk_num
, import_dt import_dt
FROM ( SELECT T1.stk_num
, T1.import_dt
, (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE
T1.stk_num = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
FROM dbo.test T1) AS X
where rank < 3
) t2
on t1.stk_num = t2.stk_num
and
t1.import_dt = t2.import_dt
) a
inner join
(
select stk_num
from
(
SELECT stk_num
, MAX(CASE rank WHEN 2 THEN import_dt ELSE NULL END) import_dt1
, MAX(CASE rank WHEN 2 THEN avg_num ELSE NULL END) avg_num1
, MAX(CASE rank WHEN 1 THEN import_dt ELSE NULL END) import_dt2
, MAX(CASE rank WHEN 1 THEN avg_num ELSE NULL END) avg_num2
FROM ( SELECT T1.stk_num
, T1.avg_num
, T1.import_dt
, (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE T1.stk_num
= T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
FROM dbo.test T1) AS X
where rank < 3
group by stk_num
) t
where avg_num2 > ((avg_num1*0.2)+avg_num1)
or
(avg_num2 < (avg_num1-(avg_num1*0.2)))
) b
on b.stk_num = a.stk_num
-- END SCRIPT
"Chesster" wrote:

> CREATE TABLE test (stk_num varchar(3), avg_num real, import_dt smalldateti
me)
> INSERT INTO test values('aaa',27.44,'1/23/2006')
> INSERT INTO test values('aaa',25.00,'1/30/2006')
> INSERT INTO test values('aaa',1.76,'2/6/2006')
> INSERT INTO test values('bbb',2.45,'1/23/2006')
> INSERT INTO test values('bbb',3.98,'1/30/2006')
> INSERT INTO test values('bbb',11.99,'2/6/2006')
> INSERT INTO test values('ccc',0.00,'1/23/2006')
> INSERT INTO test values('ccc',0.00,'1/30/2006')
> INSERT INTO test values('ccc',4.11,'2/6/2006')
> INSERT INTO test values('ddd',1.87,'1/23/2006')
> INSERT INTO test values('ddd',3.87,'1/30/2006')
> INSERT INTO test values('ddd',0.0,'2/6/2006')
> INSERT INTO test values('eee',0.00,'1/23/2006')
> INSERT INTO test values('eee',0.00,'1/30/2006')
> INSERT INTO test values('eee',0.00,'2/6/2006')
> INSERT INTO test values('fff',57.89,'1/23/2006')
> INSERT INTO test values('fff',9.80,'1/30/2006')
> INSERT INTO test values('fff',10.15,'2/6/2006')
> INSERT INTO test values('ggg',22.09,'1/23/2006')
> INSERT INTO test values('ggg',2.44,'1/30/2006')
> INSERT INTO test values('ggg',17.82,'2/6/2006')
>
> I have a table that contains the stock # and avg # and import date.
> I need to return all the records with the same stock numbers that have a +
> or - >= 20% change between their avg numbers but only for the last two imp
ort
> dates.
> So for the info given above I need the output to look like this:
> Stk_num avg_num import_dt
> aaa 25.00 1/30/2006
> aaa 1.76 2/6/2006
> bbb 3.98 1/30/2006
> bbb 11.99 2/6/2006
> ccc 0.00 1/30/2006
> ccc 4.11 2/6/2006
> ddd 3.87 1/30/2006
> ddd 0.00 2/6/2006
> ggg 2.44 1/30/2006
> ggg 17.82 2/6/2006
>
> TIA
>|||Chesster,
it's easy to accomplish with a join:
select firsts.*, seconds.import_dt dt2, seconds.avg_num num2
from
(select * from #test t1 where not exists(
select 1 from #test t2 where t1.stk_num = t2.stk_num
and t1.import_dt < t2.import_dt)) firsts,
(select * from #test t1 where(
select count(*) from #test t2 where t1.stk_num = t2.stk_num
and t1.import_dt < t2.import_dt
) = 1) seconds
where firsts.stk_num = seconds.stk_num
and firsts.avg_num NOT between seconds.avg_num*0.8 and
seconds.avg_num*1.2
it'll give you 5 rows, not 10 as you requested. To get 10 rows, use
cross join:
select stk_num,
case when n=1 then import_dt else dt2 end import_dt,
case when n=1 then avg_num else num2 end avg_num
from(
select firsts.*, seconds.import_dt dt2, seconds.avg_num num2
from
(select * from #test t1 where not exists(
select 1 from #test t2 where t1.stk_num = t2.stk_num
and t1.import_dt < t2.import_dt)) firsts,
(select * from #test t1 where(
select count(*) from #test t2 where t1.stk_num = t2.stk_num
and t1.import_dt < t2.import_dt
) = 1) seconds
where firsts.stk_num = seconds.stk_num
and firsts.avg_num NOT between seconds.avg_num*0.8 and
seconds.avg_num*1.2
)t1,
(select 1 n union all select 2) t2|||Today and maybe tomorrow I will not be at work to try this because my
daughter is sick. When I get back to work I will try it and let you know.
Thanks!
"Edgardo Valdez, MCSD, MCDBA" wrote:
> Please chaec if this works for you and let me know.
> Thanks!
> -- BEGIN SCRIPT
> select a.stk_num
> , a.avg_num
> , a.import_dt
> from (
> select t1.stk_num
> , t1.avg_num
> , t1.import_dt
> from dbo.test t1
> join ( SELECT rank
> , stk_num
> , import_dt import_dt
> FROM ( SELECT T1.stk_num
> , T1.import_dt
> , (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE
> T1.stk_num = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
> FROM dbo.test T1) AS X
> where rank < 3
> ) t2
> on t1.stk_num = t2.stk_num
> and
> t1.import_dt = t2.import_dt
> ) a
> inner join
> (
> select stk_num
> from
> (
> SELECT stk_num
> , MAX(CASE rank WHEN 2 THEN import_dt ELSE NULL END) import_dt1
> , MAX(CASE rank WHEN 2 THEN avg_num ELSE NULL END) avg_num1
> , MAX(CASE rank WHEN 1 THEN import_dt ELSE NULL END) import_dt2
> , MAX(CASE rank WHEN 1 THEN avg_num ELSE NULL END) avg_num2
> FROM ( SELECT T1.stk_num
> , T1.avg_num
> , T1.import_dt
> , (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE T1.stk_n
um
> = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
> FROM dbo.test T1) AS X
> where rank < 3
> group by stk_num
> ) t
> where avg_num2 > ((avg_num1*0.2)+avg_num1)
> or
> (avg_num2 < (avg_num1-(avg_num1*0.2)))
> ) b
> on b.stk_num = a.stk_num
> -- END SCRIPT
> "Chesster" wrote:
>|||Both queries appear to have worked.
Thanks!
"Edgardo Valdez, MCSD, MCDBA" wrote:
> Please chaec if this works for you and let me know.
> Thanks!
> -- BEGIN SCRIPT
> select a.stk_num
> , a.avg_num
> , a.import_dt
> from (
> select t1.stk_num
> , t1.avg_num
> , t1.import_dt
> from dbo.test t1
> join ( SELECT rank
> , stk_num
> , import_dt import_dt
> FROM ( SELECT T1.stk_num
> , T1.import_dt
> , (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE
> T1.stk_num = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
> FROM dbo.test T1) AS X
> where rank < 3
> ) t2
> on t1.stk_num = t2.stk_num
> and
> t1.import_dt = t2.import_dt
> ) a
> inner join
> (
> select stk_num
> from
> (
> SELECT stk_num
> , MAX(CASE rank WHEN 2 THEN import_dt ELSE NULL END) import_dt1
> , MAX(CASE rank WHEN 2 THEN avg_num ELSE NULL END) avg_num1
> , MAX(CASE rank WHEN 1 THEN import_dt ELSE NULL END) import_dt2
> , MAX(CASE rank WHEN 1 THEN avg_num ELSE NULL END) avg_num2
> FROM ( SELECT T1.stk_num
> , T1.avg_num
> , T1.import_dt
> , (SELECT COUNT(DISTINCT T2.import_dt) FROM test T2 WHERE T1.stk_n
um
> = T2.stk_num and T1.import_dt <= T2.import_dt) AS rank
> FROM dbo.test T1) AS X
> where rank < 3
> group by stk_num
> ) t
> where avg_num2 > ((avg_num1*0.2)+avg_num1)
> or
> (avg_num2 < (avg_num1-(avg_num1*0.2)))
> ) b
> on b.stk_num = a.stk_num
> -- END SCRIPT
> "Chesster" wrote:
>