중등복습과정
A.l_idx, A.lp_idx, A.lp_title, A.lp_title_sub, A.lp_gb, A.lp_pack_lecture, A.lp_tot_lec2, A.lp_tot_term, A.lp_price, A.lp_realprice, isnull(A.lp_disc_rate, 0) as lp_disc_rate, isnull(A.lp_freepass, '') as lp_freepass, isnull(A.lp_freepass_gb, '') as lp_freepass_gb, A.lp_area, isnull(A.lp_ot, '') as lp_ot, isnull(A.lp_ot_p, '') as lp_ot_p, isnull(A.lp_mov1, '') as lp_mov1, isnull(A.lp_mov2, '') as lp_mov2, isnull(A.lp_mov3, '') as lp_mov3, isnull(A.lp_mov4, '') as lp_mov4, isnull(lp_mov5, '') as lp_mov5, isnull(lp_mov6, '') as lp_mov6, isnull(A.lp_made_type, '') as lp_made_type, isnull(A.lp_iscomplete, '') as lp_iscomplete, isnull(A.lp_year, '') as lp_year, isnull(A.lp_icon, '') as lp_icon, A.lp_grade, isnull(A.lp_isnew, '') as lp_isnew, isnull(A.lp_isopen, '') as lp_isopen, isnull(A.lp_start_date, '') as lp_start_date, A.lp_regdate, isnull(A.lp_isopen_date, '') as lp_isopen_date, isnull(A.lp_rev_comment, '') as lp_rev_comment, isnull(A.lp_focus_type, '') as lp_focus_type, isnull(A.lp_plus_term, 0) as lp_plus_term, isnull(A.lp_complete_comment, '') as lp_complete_comment, isnull(A.lp_szone, '') as lp_szone
, Case A.lp_item when 'AT001' Then 1 when 'AT002' Then 3 when 'AT003' Then 4 when 'AT004' Then 5 when 'AT005' Then 2 when 'AT006' Then 6 when 'AT007' Then 7 when 'AT008' Then 4 when 'AT009' Then 9 when 'AT010' Then 10 when 'AT011' Then 11 when 'AT012' Then 12 when 'AT013' Then 13 when 'AT014' Then 14 when 'AT015' Then 15 when 'AT016' Then 16 when 'AT017' Then 17 when 'AT018' Then 18 when 'AT019' Then 19 else 10000 end as lp_sort
, B.l_t_mem_id, B.l_item as item_code, isnull(B.l_book_info,'') as l_book_info, isnull(B.l_book_code,'') as l_book_code, isnull(B.l_book_code2,'') as l_book_code2, isnull(B.l_book_code3,'') as l_book_code3
, C.t_name, C.t_ord, Case C.t_tab_gb When 'e' then 3 When 'm' then 1 When 'h' then 2 else 4 end as sortmfirst, Case C.t_tab_gb When 'e' then 3 When 'm' then 2 When 'h' then 1 else 4 end as sorthfirst
, isnull(LB.lb_code1,'') as lb_code1, isnull(LB.lb_state1,'') as lb_state1, isnull(LB.lb_code2,'') as lb_code2, isnull(LB.lb_state2,'') as lb_state2, isnull(LB.lb_code3,'') as lb_code3, isnull(LB.lb_state3,'') as lb_state3, isnull(LB.lb_code4,'') as lb_code4, isnull(LB.lb_state4,'') as lb_state4
, isnull(LB.lb_subtitle1,'') as lb_subtitle1, isnull(LB.lb_subtitle2,'') as lb_subtitle2, isnull(LB.lb_subtitle3,'') as lb_subtitle3, isnull(LB.lb_subtitle4,'') as lb_subtitle4
, isnull((Select top 1 b_title From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code1),'') as bookName1
, isnull((Select top 1 b_title From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code2),'') as bookName2
, isnull((Select top 1 b_title From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code3),'') as bookName3
, isnull((Select top 1 b_title From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code4),'') as bookName4
, isnull((Select top 1 b_sell_gubun From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code1),'') as bookstatus1
, isnull((Select top 1 b_sell_gubun From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code2),'') as bookstatus2
, isnull((Select top 1 b_sell_gubun From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code3),'') as bookstatus3
, isnull((Select top 1 b_sell_gubun From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code4),'') as bookstatus4
, isnull((Select top 1 b_price_store From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code1),0) as bookPriceStore1
, isnull((Select top 1 b_price From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code1),0) as bookPrice1
, isnull((Select top 1 Case when (isnull(b_stockqty,0)-isnull(b_safetystockqty,0)) > 0 then 1 else 0 End From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code1),0) as bookisSale1
, isnull((Select top 1 b_price_store From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code2),0) as bookPriceStore2
, isnull((Select top 1 b_price From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code2),0) as bookPrice2
, isnull((Select top 1 Case when (isnull(b_stockqty,0)-isnull(b_safetystockqty,0)) > 0 then 1 else 0 End From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code2),0) as bookisSale2
, isnull((Select top 1 b_price_store From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code3),0) as bookPriceStore3
, isnull((Select top 1 b_price From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code3),0) as bookPrice3
, isnull((Select top 1 Case when (isnull(b_stockqty,0)-isnull(b_safetystockqty,0)) > 0 then 1 else 0 End From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code3),0) as bookisSale3
, isnull((Select top 1 b_price_store From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code4),0) as bookPriceStore4
, isnull((Select top 1 b_price From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code4),0) as bookPrice4
, isnull((Select top 1 Case when (isnull(b_stockqty,0)-isnull(b_safetystockqty,0)) > 0 then 1 else 0 End From bisang.dbo.bs_book with(Nolock) Where b_code = LB.lb_code4),0) as bookisSale4
, isnull((Select Count(*) From tbl_lecture_d_index with(Nolock) Where l_idx = A.l_idx),0) as index_cnt
, ld_total_cnt=(select count(ld_idx) from tbl_lecture_d with(Nolock) where ld_isDel='N' And (ld_l_gb is null or ld_l_gb='L') and ld_idx in (select c_idx from tbl_lecture_comp with(Nolock) where lp_idx = A.LP_IDX))
, isnull((Select Count(*) From tbl_lecture_d X with(Nolock) Join tbl_lecture_comp Y with(Nolock) On X.ld_idx = Y.c_idx Where X.l_idx = A.l_idx And Y.lp_idx = A.lp_idx And (X.ld_l_gb is null or X.ld_l_gb='L') And ld_vodchk = 'Y' And ld_isDel='N' ),0) as ld_complete_cnt
, isnull((Select Count(*) From ChapterTest_Database.dbo.sc_ChapterTest_Info X with(Nolock) Where X.l_idx = A.l_idx And (CI_STATUS = 'C' OR CI_STATUS = 'Y') And X.ld_idx in (Select [value] From dbo.fn_splitText(A.lp_pack_lecture,','))) ,0) as ChapterTestCNT
, (Select top 1 t_name From tbl_code with(Nolock) Where t_code = B.l_item) as subjectStr
, (Select dbo.ufnlecturePack_IconText(A.lp_icon)) as icon_text
, isNull((Select top 1 isUseQuestionboard From soobakc_test.dbo.tz_Member with(Nolock) Where tz_ID= A.lp_t_mem_ID),'1') As isUseQuestionboard
, A.isPriceShow
, CASE WHEN A.lp_icon_course in ('US004', 'US003') THEN (SELECT t_name FROM Soobakc_Test.dbo.TBL_CODE WHERE T_CODE = A.lp_icon_course) ELSE '' END as lp_icon_course_text
From tbl_lecture_pack A with(Nolock) Join tbl_lecture B with(Nolock) On A.l_idx = B.l_idx Join tbl_member_teacher C with(Nolock) On B.l_t_mem_id = C.t_mem_id Left Join tbl_lecture_book LB with(Nolock) On A.lp_idx = LB.lp_idx Where A.lp_cate = 7 And A.lp_isdel = 'N' And A.lp_isopen in ('Y','S','R','A') and A.lp_gb in ('M','H')
AND B.l_item = 'AT004' AND A.lp_area_sub LIKE '%LS311%' AND A.lp_area like '%AU038%' And A.lp_area <> 'AU015'
And A.lp_idx not in (Select carbonCopyLpidx From dbSoobakc.dbo.tblPromotionLectureCopyerList with(Nolock) ) Order by t_Ord Asc, A.lp_ord Asc






.gif)
.jpg)

