User list-Investment record SQL

--general standard, orientation, novice, old integration plan-investment record form
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-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.bo Rrow_product<>50 and Ext.borrow_product<>70 and bid.bid_user_id=1239

UNION ALL

--New Integration Plan investment record sheet
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 is 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

----Investment record form for individual creditor's rights transfer
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 is 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 joins Yyd_borrow_borrowinfo_ext as ext on bid.borrow_id = ext.borrow_id
where Ext.borrow_product=50 and bid.bid_user_id=1239

User list-investment record SQL