Hi All,
I just take over a new project and there is a sql statement in a schedule job which I really don't understand.
I would be very appreciated if some expert can explain it for me.
Database : SQL Server 2016
(Some unnecessary field was removed for easy to read)
1 select top 50 sp_key, stat_txt, count, status, lst_mod_dt, category, item_type
2 from (
3 select cast(s.sp_key as varchar(255)) as sp_key,
4 cast(s.stat_txt as varchar(255)) as stat_txt, 1 as count,
5 'Created' as status, s.lst_mod_dt,
6 cast(p.CATG_TXT as varchar(255)) as category,
7 row_number() over (partition by s.sp_key order by s.sp_key desc) as rownum,
8 row_number() over (order by s.sp_key) as row_count,
9 cast(p.itype_txt as varchar(255)) as item_type
10 from TableA s
11 inner hash join TableB p on s.PRD_KEY = p.PRD_KEY
12 left hash join TableC lc on lc.sample_request_key = s.sp_key
13 left hash join (select * from TableA where stat_txt not in ('Created')) st on st.sp_key = s.sp_key
14 where s.CRT_DT >'2015-02-15' and lc.sample_request_key is null and st.stat_txt is not null
15 ) as result where result.rownum = 1
select top 50 sp_key, stat_txt, count, status, lst_mod_dt, category, item_type
from (
select cast(s.sp_key as varchar(255)) as sp_key,
cast(s.stat_txt as varchar(255)) as stat_txt, 1 as count,
'Created' as status, s.lst_mod_dt,
cast(p.CATG_TXT as varchar(255)) as category,
row_number() over (partition by s.sp_key order by s.sp_key desc) as rownum,
row_number() over (order by s.sp_key) as row_count,
cast(p.itype_txt as varchar(255)) as item_type
from TableA s
inner hash join TableB p on s.PRD_KEY = p.PRD_KEY
left hash join TableC lc on lc.sample_request_key = s.sp_key
left hash join (select * from TableA where stat_txt not in ('Created')) st on st.sp_key = s.sp_key
where s.CRT_DT >'2015-02-15' and lc.sample_request_key is null and st.stat_txt is not null
) as result where result.rownum = 1
what I don't understand is line 13 and line 15
for line 13, why it have a left join on the same table and not simple place "stat_txt not in ('Created')" on the where clause on line 14?
for line 15, what is the purpose "as result where result.rownum = 1"?
Thanks