--普通标、定向标、新手标、老互融计划-投资记录表

select bid.borrow_id,

(select yyb.borrow_valid_time from YYD_Borrow_BorrowInfo as yyb where yyb.ID=bid.borrow_id ) as borrow_valid_time,

(select yyb.borrow_end_time from YYD_Borrow_BorrowInfo as yyb where yyb.ID=bid.borrow_id ) as borrow_end_time,

(select yyb.BidComRate from YYD_Borrow_BorrowInfo as yyb where yyb.ID=bid.borrow_id ) as BidComRate,

(select yyb.Title from YYD_Borrow_BorrowInfo as yyb where yyb.ID=bid.borrow_id ) as Title,

bid.amount as amount,

(select yyb.BorrowCode from YYD_Borrow_BorrowInfo as yyb where yyb.ID=bid.borrow_id) as BorrowCode,

(select yyb.borrow_period from YYD_Borrow_BorrowInfo as yyb where yyb.ID=bid.borrow_id) as borrow_period,

(select yyb.status from YYD_Borrow_BorrowInfo as yyb where yyb.ID=bid.borrow_id) as statuss,

bid.createtime as createtime,

--(select DATEADD(MONTH,yyb.borrow_period,yyb.loanDate) from YYD_Borrow_BorrowInfo as yyb where yyb.ID=bid.borrow_id ) as endDate

(

select case when yyb.loanDate='1900-01-01 00:00:00.000' then '1900-01-01 00:00:00.000' else

DATEADD(MONTH,yyb.borrow_period,yyb.loanDate) end

from YYD_Borrow_BorrowInfo as yyb where yyb.ID=bid.borrow_id

) as endDate

from YYD_Borrow_BidRecord as bid inner join YYD_Borrow_borrowinfo_ext as ext on bid.borrow_id = ext.borrow_id where ext.borrow_product<>50 and ext.borrow_product<>70 and bid.bid_user_id=1239

union all

--新互融计划投资记录表

select newbid.nplid,'' as borrow_valid_time,'' as borrow_end_time,'' as BidComRate,

(select bTitle+'['+qishu+']' from HUR_NewPlanLoan where id=newbid.nplid)Title,

newbid.amount as amount,'' as BorrowCode,

(select bPeriod from HUR_NewPlanLoan where id=newbid.nplid)borrow_period,

newbid.nplStatus as statuss,newbid.createtime as createtime,

(select case when yyb.loanDate='1900-01-01 00:00:00.000' then '1900-01-01 00:00:00.000' else

DATEADD(MONTH,loan.bPeriod,yyb.loanDate) end from YYD_Borrow_BorrowInfo as yyb,HUR_NewPlanLoan as loan

where yyb.id in (select top 1 borrowId from HUR_NewPlanLoan_ItemLoan as item

where item.nplBid = newbid.id) and loan.id = newbid.nplid) as endDate

from HUR_NewPlanLoan_BidRecord as newbid where newbid.nplStatus=1 and newbid.userid=1337

union all

----个人债权转让的投资记录表

select bid.borrow_id,

(select yyb.borrow_valid_time from YYD_Borrow_BorrowInfo as yyb where yyb.ID=(

select top 1 ext.OrginBorrowId from YYD_Borrow_borrowinfo_ext as ext where ext.borrow_id=bid.borrow_id)) as borrow_valid_time,

(select yyb.borrow_end_time from YYD_Borrow_BorrowInfo as yyb where yyb.ID=(

select top 1 ext.OrginBorrowId from YYD_Borrow_borrowinfo_ext as ext where ext.borrow_id=bid.borrow_id)) as borrow_end_time,

(select yyb.BidComRate from YYD_Borrow_BorrowInfo as yyb where yyb.ID=(

select top 1 ext.OrginBorrowId from YYD_Borrow_borrowinfo_ext as ext where ext.borrow_id=bid.borrow_id)) as BidComRate,

(select yyb.Title from YYD_Borrow_BorrowInfo as yyb where yyb.id =(

select top 1 ext.OrginBorrowId from YYD_Borrow_borrowinfo_ext as ext where ext.borrow_id=bid.borrow_id)) as Title,

bid.amount as amount,

(select yyb.BorrowCode from YYD_Borrow_BorrowInfo as yyb where yyb.id =(

select top 1 ext.OrginBorrowId from YYD_Borrow_borrowinfo_ext as ext where ext.borrow_id=bid.borrow_id)) as BorrowCode,

(select yyb.borrow_period from YYD_Borrow_BorrowInfo as yyb where yyb.id =(

select top 1 ext.OrginBorrowId from YYD_Borrow_borrowinfo_ext as ext where ext.borrow_id=bid.borrow_id)) as borrow_period,

(select yyb.status from YYD_Borrow_BorrowInfo as yyb where yyb.id =(

select top 1 ext.OrginBorrowId from YYD_Borrow_borrowinfo_ext as ext where ext.borrow_id=bid.borrow_id)) as statuss,

bid.createtime as createtime,

--(select DATEADD(MONTH,yyb.borrow_period,yyb.loanDate) from YYD_Borrow_BorrowInfo as yyb where id =(

--select top 1 ext.OrginBorrowId from YYD_Borrow_borrowinfo_ext as ext where ext.borrow_id=bid.borrow_id)) as endDate

(select case when yyb.loanDate='1900-01-01 00:00:00.000' then '1900-01-01 00:00:00.000' else

DATEADD(MONTH,yyb.borrow_period,yyb.loanDate) end

from YYD_Borrow_BorrowInfo as yyb where yyb.id =(select top 1 ext.OrginBorrowId from YYD_Borrow_borrowinfo_ext as ext

where ext.borrow_id=bid.borrow_id)) as endDate

from YYD_Borrow_BidRecord as bid inner join YYD_Borrow_borrowinfo_ext as ext on bid.borrow_id = ext.borrow_id

where ext.borrow_product=50 and bid.bid_user_id=1239