select

        a.bs_sn,

        a.bs_bd_no,

        a.bs_bk_code,

        a.bs_kind_no,

        a.bs_flag,

        b.det_flag,

        c.bp_in_no,

        c.bp_name,

        c.bp_sex,

        c.bp_age,

        c.bp_age_unit,

        c.bp_dept,

        c.bp_bed_no,

        d.inf_date,

        d.inf_type,

        e.sta_date,

        e.sta_user

              from bd_stock as a

                left join (select row_number() over (partition by det_bs_sn order by det_flag DESC) as rownum,det_bs_sn,det_id,det_flag from dbo.bd_match_detail where det_flag!=-1) as b on a.bs_sn=b.det_bs_sn and b.rownum=1

                left join bd_patients as c on b.det_id=c.bp_id

                left join (select row_number() over (partition by inf_bp_id,inf_bd_sn order by inf_date DESC) as rownum,inf_bp_id,inf_bd_sn,inf_type,inf_date from bd_infusion where inf_type = 1) as d on b.det_id = d.inf_bp_id and b.det_bs_sn=d.inf_bd_sn and d.rownum=1

                left join (select row_number() over (partition by sta_code,sta_sta_code order by sta_date DESC) as rownum,sta_code,sta_sta_code,sta_user,sta_date from dbo.bd_status where sta_sta_code='') as e on a.bs_bd_no=e.sta_code

            where b.det_flag=6  and d.inf_date>='2013-12-02' and d.inf_date<='2014-12-02'