|
может он у меня действительно навороченный ....
в начале это был просто select из одной таблицы а теперь в этот набор надо добавить данные из др. таблицы
select -- top 500
a.Lagr_id As Lagr_id,
isnull(a.AgrNum,'') As AgrNum, --!
isnull(c.Cust_name,'') as cust_name, --new
isnull(ct.cust_type,'') AS CUST_TYPE, --new
isnull(c.reg_no,'') As reg_no,-- new
isnull(c.reg_no_vat,'') As reg_no_vat,--new
isnull(c.address_formal,'') As address_formal,--new
isnull(c.address_real,'') As address_real,--new
isnull(c.phone1,'') As phone1,--new
isnull(c.phone2,'') As phone2,--new
isnull(c.fax,'') As fax,--new
isnull(c.representative,'') As representative,--new
isnull(c.rep_pi_nr,'') As rep_pi_nr,--new
isnull((select _old_id from industry_types where ind_type_id = c.industry_type),0) As industry_type, --!
isnull((select _old_id from dislocations where dislocation_id = c.dislocation),0) As cust_dislocation,--!
isnull((select top 1 old_id from users where user_id = c.ul_manager),'') As cust_ul_mngr,--!
isnull((select branch_id from branches where branch_id_int = a.UBBranch_id),'') As ub_branch,--!
isnull(a.AgrCDate,0) As AgrCDate,--!
isnull(a.AGrDeliverDate,0) As AgrDeliverDate,--!
case --uzliek veco līguma statusu
when a.Status = 8 then 11
else IsNull((select top 1 _old_id from LAgrStatus where Status = a.Status),0)
end As liguma_status,
IsNull(a.ObjDescr,'') As ObjDescr,--!
IsNull(a.ChassisNum,'') As ChassisNum,--!
isnull((select _old_id from LObjTypes where ObjType_id = a.ObjType_id),0) As ObjType_id,--!
isnull(a.oldagrmng,'') As agr_ul_mngr,
isnull(a.ObjCurr,'') As ObjCurr,
isnull(a.GraphCurr,'') As GraphCurr ,
IsNull((select top 1 CurrRate from LOutPayments where Lagr_id=a.Lagr_id and Type=2 order by PlanDate),0) as Mainrate,
CASE a.AgrSource
WHEN 3 THEN 1-(ISNULL(a.OldObjSum+0.0001,0)/ISNULL(a.ObjSum,0))
ELSE IsNull(a.FirstPay,0) / 100
END As FirstPay,
isnull(a.SplitFirstPay,'') As SplitFirstPay,
isnull(a.IsSplitedFirstPay,0) As IsSplitedFirstPay,
IsNull(a.FirstPayDate,0) As FirstPayDate,
(IsNull(a.FixedratePr,0) - IsNUll(a.SoursePartPr,0)) / 100 As FixedRatePr,
IsNull(a.FloatRatePr,0) / 100 As FloatRatePr, -- Viens vai otrs ?
--isnull((select top 1 rate from LFloatRates where FloatRate_id = a.FloatRate_id and Curr = a.GraphCurr and UseDate <= a.FirstInvDate order by UseDate desc),0) As FloatRatePr,--!
(IsNull(a.CommissionRatePr,0) - IsNull(a.SourseCommPr,0)) / 100 As CommissionRatePr,
IsNull(a.PeriodNum,0) As PeriodNum,
isnull(a.Period,0) As Period,
isnull((select _old_id from LAgrProdType where Type = a.ProdType),0) As agr_prod_type,
isnull((select rate_type_name from floating_rate_types where Rate_type_ID = a.FloatRate_id),'') as rate_type_name,--!
isnull(a.DiscountPr,0) / 100 As DiscountPr,
isnull(a.MainPVNPr,-100)/100 As MainPVNPr,
isnull(a.CommPVNPr,-100)/100 As CommPVNPr,
isnull(a.PrPVNPr,-100)/100 As PrPVNPr,
isnull(a.CBorderDeal,0) As CBorderDeal,
isnull(a.CarregNum,'') As carregnum,
isnull(a.BranchPr,0) As BranchPr,
IsNull(a.SourseCommPr,0) As SourseCommPr,
IsNull(a.SoursePartPr,0) As SoursePartPr,
IsNull(a.ObjectClass,'') As ObjectClass,
IsNull(a.ObjectClassA,'') As ObjectClassA,
isnull(c.classification,'') As cust_classification, --!
isnull(c.classification_adj,'') As cust_classification_adj, --!
isnull(a.GraphDays,0) As GraphDays, --
case
WHEN ISNULL(a.EndPricePr,0)>0 THEN a.EndPricePr/100
ELSE 0
end as EndPricePr, --!
--VENDOR
IsNull(v.cust_name,'--') as vendor_name, --new
IsNull(vt.cust_type,'') as vendor_bussines_type, --new
--SOURCE
IsNull(so.cust_name,'--') as source_name, --new
IsNull(sot.cust_type,'') as source_bussines_type, --new
--BYBACK
IsNull(bu.cust_name,'--') as byback_name, --new
IsNull(but.cust_type,'') as byback_bussines_type, --new
--GUARANTOR
IsNull(ga.cust_name,'--') as guarantor_name, --new
IsNull(gat.cust_type,'') as guarantor_bussines_type, --new
isnull(a.Dislocation_id,0) As Dislocation_id,
--disburse date
-- IsNull((select top 1 PlanDate from LOutPayments where Lagr_id = a.Lagr_id and type = 2 and PlanDate = (select Min(PlanDate) from LOutPayments where Lagr_id = a.Lagr_id and type = 2)),0) As disburse_date,
isnull(a.disbursedate,0) As disburse_date,
--cena grafika valūtā cenā nav PSN
dbo.fL_GetObjSumm(a.LAgr_id,1) as cena_grafika_valuta,
--cena LVL
-- ROUND((select SUM(ROUND(PlanSum * CurrRate,2)) from LOutPayments where Lagr_id = a.Lagr_id and Type in (2,7) and Status in (1,2,3,4)) * (select rate from curr_rates where currency=a.GraphCurr and date_from=@report_date),2) As cena_LVL,
--kurss
dbo.fL_GetLBRateLVL(a.GraphCurr,@report_date) as last_day_rate,
-- IsNull((select rate from curr_rates where currency=a.GraphCurr and date_from=@report_date),0) as last_day_rate,
IsNull(a.TakeOverFrom_id,0) As TakeOverFrom_id,
IsNull(a.BadDebt,0) As BadDebt,
IsNull(a.BadDebtPr,0) As BadDebtPr,
isnull(a.CBReverse,0) As CBReverse,
isnull(a.SubBranch_id,0) As SubBranch_id,
isnull(a.UBBranch_id,0) As UBBranch_id
--FINANSĒJUMS šājā reportā būs bez PSN
--1.
--finansējumu jāaprēķina tieši MS Excel makrosā!!!
--2.
--atsevišķos out_payments jāielasa ar citas storētās procedūras palīdzību
from LAgreements a left outer join
customers c on c.Cust_id = a.Cust_id left outer join
cust_types ct on c.Cust_type=ct.cust_type_id left outer join
customers v on v.Cust_id=a.Vendor_id left outer join
cust_types vt on v.Cust_type=vt.cust_type_id left outer join
customers so on so.Cust_id=a.Sourse_id left outer join
cust_types sot on so.Cust_type=sot.cust_type_id left outer join
customers bu on bu.Cust_id=a.BuyBack_id left outer join
cust_types but on bu.Cust_type=but.cust_type_id left outer join
customers ga on ga.Cust_id=a.Garantor_id left outer join
cust_types gat on ga.Cust_type=gat.cust_type_id
where a.User_id <> 'dbo'
union ALL
select
a.LKagr_id As Lagr_id,
isnull(a.AgrNum,'') As AgrNum, --!
isnull(c.Cust_name,'') as cust_name, --new
isnull(ct.cust_type,'') AS CUST_TYPE, --new
isnull(c.reg_no,'') As reg_no,-- new
isnull(c.reg_no_vat,'') As reg_no_vat,--new
isnull(c.address_formal,'') As address_formal,--new
isnull(c.address_real,'') As address_real,--new
isnull(c.phone1,'') As phone1,--new
isnull(c.phone2,'') As phone2,--new
isnull(c.fax,'') As fax,--new
isnull(c.representative,'') As representative,--new
isnull(c.rep_pi_nr,'') As rep_pi_nr,--new
isnull((select _old_id from industry_types where ind_type_id = c.industry_type),0) As industry_type, --!
isnull((select _old_id from dislocations where dislocation_id = c.dislocation),0) As cust_dislocation,--!
isnull((select top 1 old_id from users where user_id = c.ul_manager),'') As cust_ul_mngr,--!
isnull((select branch_id from branches where branch_id_int = a.UBBranch_id),'') As ub_branch,--!
isnull(a.AgrCDate,0) As AgrCDate,--!
isnull(a.AGrDeliverDate,0) As AgrDeliverDate,--!
case --uzliek veco līguma statusu
when a.Status = 8 then 11
else IsNull((select top 1 _old_id from LAgrStatus where Status = a.Status),0)
end As liguma_status,
IsNull(a.ObjDescr,'') As ObjDescr,--!
IsNull(a.ChassisNum,'') As ChassisNum,--!
-- isnull(a.ObjType_id,0) As ObjType_id,
isnull((select _old_id from LObjTypes where ObjType_id = a.ObjType_id),0) As ObjType_id,--!
isnull(a.oldagrmng,'') As agr_ul_mngr,
--IsNull((select top 1 old_id from users where user_id = a.user_id),'') As agr_ul_mngr,--!
isnull(a.ObjCurr,'') As ObjCurr,
isnull(a.GraphCurr,'') As GraphCurr ,
IsNull(a.MainRate,0) as Mainrate,
IsNull(a.FirstPayPr,0) / 100 As FirstPay,
'' As SplitFirstPay, -- LK līgumiem pagaidam nevar būt sadalīta pirma iemaksa
0 As IsSplitedFirstPay,-- LK līgumiem pagaidam nevar būt sadalīta pirma iemaksa
IsNull(a.FirstPayDate,0) As FirstPayDate,
IsNull(a.FixedratePr,0) / 100 As FixedRatePr,
IsNull(a.FloatRatePr,0) / 100 As FloatRatePr, -- Viens vai otrs ?
IsNull(a.CommRatePr,0) / 100 As CommissionRatePr,
IsNull(a.PeriodNum,0) As PeriodNum,
isnull(a.Period,0) As Period,
isnull((select _old_id from LAgrProdType where Type = a.ProdType),0) As agr_prod_type,
isnull((select rate_type_name from floating_rate_types where Rate_type_ID = a.FloatRate_id),'') as rate_type_name,--!
0 As DiscountPr,
isnull(a.MainPVNPr,-100)/100 As MainPVNPr,
isnull(a.CommPVNPr,-100)/100 As CommPVNPr,
isnull(a.PrPVNPr,-100)/100 As PrPVNPr,
0 As CBorderDeal,
isnull(a.CarregNum,'') As carregnum,
isnull(a.BranchPr,0) As BranchPr,
0 As SourseCommPr,
0 As SoursePartPr,
IsNull(a.ObjectClass,'') As ObjectClass,
IsNull(a.ObjectClassA,'') As ObjectClassA,
isnull(c.classification,'') As cust_classification, --!
isnull(c.classification_adj,'') As cust_classification_adj, --!
isnull(a.GraphDays,0) As GraphDays, --
0 as EndPricePr, --!
--VENDOR
IsNull(v.cust_name,'--') as vendor_name, --new
IsNull(vt.cust_type,'') as vendor_bussines_type, --new
--SOURCE
IsNull(so.cust_name,'--') as source_name, --new
IsNull(sot.cust_type,'') as source_bussines_type, --new
--BYBACK
'--' as byback_name, --new
'' as byback_bussines_type, --new
--GUARANTOR
IsNull(ga.cust_name,'--') as guarantor_name, --new
IsNull(gat.cust_type,'') as guarantor_bussines_type, --new
isnull(a.Dislocation_id,0) As Dislocation_id,
--disburse date
-- IsNull((select top 1 PlanDate from LOutPayments where Lagr_id = a.Lagr_id and type = 2 and PlanDate = (select Min(PlanDate) from LOutPayments where Lagr_id = a.Lagr_id and type = 2)),0) As disburse_date,
isnull(a.disbursedate,0) As disburse_date,
--cena grafika valūtā cenā nav PSN
ROUND(a.ObjSum*a.MainRate,2) as cena_grafika_valuta,
--cena LVL
-- ROUND((select SUM(ROUND(PlanSum * CurrRate,2)) from LOutPayments where Lagr_id = a.Lagr_id and Type in (2,7) and Status in (1,2,3,4)) * (select rate from curr_rates where currency=a.GraphCurr and date_from=@report_date),2) As cena_LVL,
--kurss
dbo.fL_GetLBRateLVL(a.GraphCurr,@report_date) as last_day_rate,
-- IsNull((select rate from curr_rates where currency=a.GraphCurr and date_from=@report_date),0) as last_day_rate,
IsNull(a.TakeOverFrom_id,0) As TakeOverFrom_id,
IsNull(a.BadDebt,0) As BadDebt,
IsNull(a.BadDebtPr,0) As BadDebtPr,
0 As CBReverse,
isnull(a.SubBranch_id,0) As SubBranch_id,
isnull(a.UBBranch_id,0) As UBBranch_id
--FINANSĒJUMS šājā reportā būs bez PSN
--1.
--finansējumu jāaprēķina tieši MS Excel makrosā!!!
--2.
--atsevišķos out_payments jāielasa ar citas storētās procedūras palīdzību
from LKAgreements a left outer join
customers c on c.Cust_id = a.Cust_id left outer join
cust_types ct on c.Cust_type=ct.cust_type_id left outer join
customers v on v.Cust_id=a.Vendor_id left outer join
cust_types vt on v.Cust_type=vt.cust_type_id left outer join
customers so on so.Cust_id=a.Source_id left outer join
cust_types sot on so.Cust_type=sot.cust_type_id left outer join
customers ga on ga.Cust_id=a.Garantor_id left outer join
cust_types gat on ga.Cust_type=gat.cust_type_id
where a.Manager_id <> 'dbo'
|
