|
![]() |
#1 |
Участник
|
Цитата:
X++: if (object_id('tempdb..#vendsettlement') IS NOT NULL) begin drop table #vendsettlement; end if (object_id('tempdb..#custsettlement') IS NOT NULL) begin drop table #custsettlement; end if (object_id('tempdb..#saldo') IS NOT NULL) begin drop table #saldo; end if (object_id('tempdb..#saldoPr') IS NOT NULL) begin drop table #saldoPr; end select transrecid, sum(settleamountcur) as settlecur, sum(settleamountmst) as settlemst, sum(exchadjustment) as exchadj into #vendsettlement from vendsettlement (nolock) where transdate < '2013.01.01' and ltrim(accountnum) like '%19265155' and dataareaid = 'zkz' group by transrecid select transrecid, sum(settleamountcur) as settlecur, sum(settleamountmst) as settlemst, sum(exchadjustment) as exchadj into #custsettlement from custsettlement (nolock) where transdate < '2013.01.01' and ltrim(accountnum) like '%19265155' and dataareaid = 'zkz' group by transrecid select 'Поставщик' as k, ltrim(LedgerAccount_ZTR) as LedgerAccount_ZTR, --isNull(CodeId_ZTR, '') as Dimension, (select CompanyGroup from Dimensions (nolock) where Dimensions.Num = custtrans.Dimension and Dimensions.dataareaid = 'zkz' ) as Dimension, custtrans.RContractCode, custtrans.accountnum, substring(custtable.name,1, 100) as name, substring(' ',1,140) as n, isnull(country.name, '') as name1, case ---> 3780 26.02.2013 -- when (Journaltype = 10) -- then substring(LedgerjournalTrans.PAYMPURPOSE_ZTR,1,1000) --<- when SubjectOfAgreement_ZTR != '' then SubjectOfAgreement_ZTR when (LedgerjournalTrans.BankCentralBankPurposeText is not null and LedgerjournalTrans.BankCentralBankPurposeText != '' and Journaltype = 4) then LedgerjournalTrans.BankCentralBankPurposeText when (LedgerjournalTrans.Txt is not null and LedgerjournalTrans.Txt != '' and (Journaltype = 0 or Journaltype = 45)) then custtrans.Txt when (substring(rContractTable.RContractSubject,1,1000) is not null and substring(rContractTable.RContractSubject,1,1000) != '') then substring(rContractTable.RContractSubject,1,1000) else '' end as RContractSubject, isnull(rContractTable.RCONTRACTNUMBER, '') as RCONTRACTNUMBER, sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) as mst, CustTrans.transdate, custtrans.DueDate, case when dateDiff(month, CustTrans.Duedate, '2013.01.01') = 0 and day('2013.01.01') > day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when dateDiff(month, CustTrans.Duedate, '2013.01.01') = 1 and day('2013.01.01') <= day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) else 0 end as 'p1', case when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 1 and day('2013.01.01') > day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 1) and (dateDiff(month, CustTrans.Duedate,'2013.01.01') < 3)) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 3 and day('2013.01.01') <= day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) else 0 end as 'p2', case when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 3 and day('2013.01.01') > day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 3) and (dateDiff(month, CustTrans.Duedate,'2013.01.01') < 36)) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 36 and day('2013.01.01') <= day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) else 0 end as 'p3', case when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 36 and day('2013.01.01') > day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 36)) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) else 0 end as 'p4', sum(-amountcur)-sum(-isnull(settlecur,0)) as cur, custtrans.currencycode, ' ' as f, isnull(rContractTable.RContractAccount, '') as RContractAccount, isnull(RContractPartnerType,'') as RContractPartnerType, case when (LedgerjournalTrans.PaymentStatusUserId_ZTR != '') then LedgerjournalTrans.PaymentStatusUserId_ZTR when custtrans.CreatedBy != '' then custtrans.CreatedBy else '' end as CreatedBy , custtrans.Dimension3_, custtrans.Dimension7_ into #saldo from vendtrans custtrans (nolock) left join #vendsettlement (nolock) on custtrans.recid = #vendsettlement.transrecid inner join vendtable custtable (nolock) on custtable.accountnum = custtrans.accountnum and custtable.dataareaid = 'zkz'left join AddressCountryRegion country (nolock) on country.CountryRegionId = custtable.CountryRegionId and country.dataareaid = 'zkz'left join rContractTable (Nolock) on rContractTable.RContractPartnerType = '1' and rContractTable.RContractCode = custtrans.RContractCode and rContractTable.RContractAccount = custtrans.RContractAccount --and RContractStatus = '1' and rContractTable.dataareaid = 'zkz' left join (select voucher, POSTINGPROFILE, offsetpostingprofile_ru, PaymentStatusUserId_ZTR, LedgerjournalTrans.BankCentralBankPurposeText,LedgerjournalTrans.txt, Journaltype from LedgerjournalTrans (nolock) inner join LedgerjournalTable on LedgerjournalTrans.journalnum = LedgerjournalTable.journalnum and posted = 1 and Journaltype in(0, 4, 45) and LedgerjournalTrans.dataareaid = 'zkz' and LedgerjournalTrans.dataareaid = 'zkz' ) as LedgerjournalTrans on custtrans.voucher = LedgerjournalTrans.voucher and (LedgerjournalTrans.postingprofile in (select vendledgeraccounts.postingprofile from vendledgeraccounts (nolock) where (accountCode = 2 or (accountcode = 1 and num = vendgroup)) and vendledgeraccounts.dataareaid = 'zkz' and ltrim(sumaccount) = ltrim(LedgerAccount_ZTR)) or (offsetpostingprofile_ru in (select vendledgeraccounts.postingprofile from vendledgeraccounts (nolock) where (accountCode = 2 or (accountcode = 1 and num = vendgroup)) and vendledgeraccounts.dataareaid = 'zkz' and ltrim(sumaccount) = ltrim(LedgerAccount_ZTR)))) --left join RContractTypes (nolock) on RContractTypes.ContractCode = custtrans.RContractCode and RContractTypes.ContractPartnerType = 1 where CustTrans.transdate < '2013.01.01' and (closed = '19000101' or closed >= '2013.01.01') and ltrim(LedgerAccount_ZTR) in ('6312100') and ltrim(custtrans.accountnum) like '%19265155' and custtrans.dataareaid = 'zkz' and LedgerAccount_ZTR not in(select accountnum from NotIncludeAccount_ZTR (nolock) where NotIncludeAccount_ZTR.dataareaid = 'zkz') group by LedgerAccount_ZTR, custtrans.RContractCode, custtrans.accountnum, custtable.name, country.name, substring(rContractTable.RContractSubject,1,1000), rContractTable.RCONTRACTNUMBER, CustTrans.transdate, custtrans.DueDate,custtrans.Dimension, custtrans.currencycode,rContractTable.RContractAccount,RContractPartnerType ,PaymentStatusUserId_ZTR, LedgerjournalTrans.BankCentralBankPurposeText ,custtrans.CreatedBy, --CodeId_ZTR, LedgerjournalTrans.txt, Journaltype, SubjectOfAgreement_ZTR ,custtrans.Txt, --Ledgerjournaltrans.PAYMPURPOSE_ZTR, --having sum(amountcur) != 0 or sum(amountmst) != 0 custtrans.Dimension3_, custtrans.Dimension7_ insert into #saldo select 'Клиент' as k ,/*' ' as g,*/ ltrim(LedgerAccount_ZTR) as LedgerAccount_ZTR, isNull(CodeId_ZTR, '') as Dimension, --(select CompanyGroup from Dimensions (nolock) where Dimensions.Num = custtrans.Dimension) as Dimension, custtrans.RContractCode, custtrans.accountnum, substring(custtable.name,1, 100), (select FinalDelvCustName_ZTR from SalesTable where SalesTable.SalesId = custtrans.DocId_ZTR) as n, isnull(country.name, '') as name1, case ---> 3780 26.02.2013 when (Journaltype = 10) then substring(LedgerjournalTrans.PAYMPURPOSE_ZTR,1,500) --<- when SubjectOfAgreement_ZTR != '' then SubjectOfAgreement_ZTR /*when (LedgerjournalTrans.BankCentralBankPurposeText is not null and LedgerjournalTrans.BankCentralBankPurposeText != '' and Journaltype = 7) then LedgerjournalTrans.BankCentralBankPurposeText*/ when (LedgerjournalTrans.Txt is not null and LedgerjournalTrans.Txt != '' /*and (Journaltype = 0 or Journaltype = 45)*/) then custtrans.Txt when (substring(rContractTable.RContractSubject,1,1000) is not null and substring(rContractTable.RContractSubject,1,1000) != '') then substring(rContractTable.RContractSubject,1,1000) else '' end as RContractSubject, isnull(rContractTable.RCONTRACTNUMBER, '') as RCONTRACTNUMBER, sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) as mst, Custtrans.transdate, custtrans.DueDate, case when dateDiff(month, CustTrans.Duedate, '2013.01.01') = 0 and day('2013.01.01') > day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when dateDiff(month, CustTrans.Duedate, '2013.01.01') = 1 and day('2013.01.01') <= day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) else 0 end as 'p1', case when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 1 and day('2013.01.01') > day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 1) and (dateDiff(month, CustTrans.Duedate,'2013.01.01') < 3)) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 3 and day('2013.01.01') <= day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) else 0 end as 'p2', case when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 3 and day('2013.01.01') > day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 3) and (dateDiff(month, CustTrans.Duedate,'2013.01.01') < 36)) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 36 and day('2013.01.01') <= day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) else 0 end as 'p3', case when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 36 and day('2013.01.01') > day(CustTrans.Duedate) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 36)) then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) else 0 end as 'p4', sum(-amountcur)-sum(-isnull(settlecur,0)) as cur, custtrans.currencycode, '' as f, isnull(rContractTable.RContractAccount,'') as RContractAccount, isnull(RContractPartnerType,'') as RContractPartnerType, case when (LedgerjournalTrans.PaymentStatusUserId_ZTR != '') then LedgerjournalTrans.PaymentStatusUserId_ZTR when custtrans.CreatedBy != '' then custtrans.CreatedBy else '' end as CreatedBy , custtrans.Dimension3_, custtrans.Dimension7_ from custtrans (nolock) left join #custsettlement on custtrans.recid = #custsettlement.transrecid inner join custtable (nolock) on custtable.accountnum = custtrans.accountnum and custtable.dataareaid = 'zkz'left join AddressCountryRegion country (nolock) on country.CountryRegionId = custtable.CountryRegionId and country.dataareaid = 'zkz'left join rContractTable (Nolock) on rContractTable.RContractPartnerType = '0' and rContractTable.RContractCode = custtrans.RContractCode and rContractTable.RContractAccount = custtrans.RContractAccount --and RContractStatus = '1' and rContractTable.dataareaid = 'zkz' left join LedgerjournalTrans (nolock) on custtrans.voucher = LedgerjournalTrans.voucher and LedgerjournalTrans.dataareaid = 'zkz' and (LedgerjournalTrans.postingprofile in (select custledgeraccounts.postingprofile from custledgeraccounts (nolock) where (accountCode = 2 or (accountcode = 1 and num = custgroup)) and custledgeraccounts.dataareaid = 'zkz' and ltrim(sumaccount) = ltrim(LedgerAccount_ZTR)) or (offsetpostingprofile_ru in (select custledgeraccounts.postingprofile from custledgeraccounts (nolock) where (accountCode = 2 or (accountcode = 1 and num = custgroup)) and custledgeraccounts.dataareaid = 'zkz' and ltrim(sumaccount) = ltrim(LedgerAccount_ZTR)))) left join LedgerjournalTable on LedgerjournalTrans.journalnum = LedgerjournalTable.journalnum and posted = 1 and Journaltype in(0, 7, 45) and LedgerjournalTable.dataareaid = 'zkz' left join RContractTypes (nolock) on RContractTypes.ContractCode = custtrans.RContractCode and RContractTypes.ContractPartnerType = 0 and RContractTypes.dataareaid = 'zkz' where Custtrans.transdate < '2013.01.01' and (closed = '19000101' or closed >= '2013.01.01') and ltrim(LedgerAccount_ZTR) in ('6312100') and ltrim(custtrans.accountnum) like '%19265155' and LedgerAccount_ZTR not in(select accountnum from NotIncludeAccount_ZTR (nolock) where NotIncludeAccount_ZTR.dataareaid = 'zkz') and custtrans.dataareaid = 'zkz' group by custtrans.accountnum, custtrans.currencycode, custtrans.RContractCode, custtrans.Dimension, rContractTable.RContractCode, substring(rContractTable.RContractSubject,1,1000), LedgerAccount_ZTR, Custtrans.transdate,RContractPartnerType, rContractTable.RCONTRACTNUMBER, custtrans.DueDate, custtable.name, country.name,rContractTable.RContractAccount,RContractPartnerType, PaymentStatusUserId_ZTR, LedgerjournalTrans.BankCentralBankPurposeText, custtrans.CreatedBy, CodeId_ZTR, LedgerjournalTrans.txt, SubjectOfAgreement_ZTR, custtrans.Txt, custtrans.DocId_ZTR, substring(Ledgerjournaltrans.PAYMPURPOSE_ZTR,1,500), Ledgerjournaltable.JOURNALTYPE, --, Journaltype --having sum(amountcur) != 0 or sum(amountmst) != 0 custtrans.Dimension3_, custtrans.Dimension7_ select k, case when (mst < 0 or cur < 0) then 'Д' else 'К' end as pr, case when (mst < 0 or cur < 0) then BalSheetDt_ZTR when (mst > 0 or cur > 0) then BalSheetKr_ZTR end as bal, ltrim(LedgerAccount_ZTR) as LedgerAccount_ZTR, #saldo.Dimension, #saldo.accountnum, #saldo.name, n, name1, #saldo.RContractSubject, #saldo.RCONTRACTNUMBER, case when (mst < 0) then -mst else mst end as mst, transdate, #saldo.DueDate, case when (p1 < 0) then -p1 else p1 end as p1, case when (p2 < 0) then -p2 else p2 end as p2, case when (p3 < 0) then -p3 else p3 end as p3, case when (p4 < 0) then -p4 else p4 end as p4, case when (cur < 0) then -cur else cur end as cur, #saldo.currencycode, #saldo.CreatedBy , #saldo.Dimension3_, #saldo.Dimension7_ into #saldoPr from #saldo inner join LedgerTable (nolock) on ltrim(LedgerTable.accountNum) = ltrim(LedgerAccount_ZTR) and LedgerTable.dataareaid = 'zkz' left join rContractTable (Nolock) on rContractTable.RContractPartnerType = #saldo.RContractPartnerType and rContractTable.RContractCode = #saldo.RContractCode and rContractTable.RContractAccount = #saldo.RContractAccount --and RContractStatus = '1' and rContractTable.dataareaid = 'zkz' where #saldo.CreatedBy like '%' order by #saldo.accountnum,#saldo.currencycode,ltrim(LedgerAccount_ZTR),transdate o Но в первом случае он ругается, что не знает такого поля, хотя выбираю его из выпадающего списка, я так понимаю, что если решить проблему с первым case-ом, тогда будет отображаться то, что нужно. Последний раз редактировалось user_ax; 27.02.2013 в 18:29. |
|
![]() |
#2 |
Модератор
|
Цитата:
Код: left join (select voucher, POSTINGPROFILE, offsetpostingprofile_ru, PaymentStatusUserId_ZTR, LedgerjournalTrans.BankCentralBankPurposeText,LedgerjournalTrans.txt, Journaltype from LedgerjournalTrans (nolock) inner join LedgerjournalTable on LedgerjournalTrans.journalnum = LedgerjournalTable.journalnum and posted = 1 and Journaltype in(0, 4, 45) and LedgerjournalTrans.dataareaid = 'zkz' and LedgerjournalTrans.dataareaid = 'zkz' ) as LedgerjournalTrans там как раз и не хватает поля PAYMPURPOSE_ZTR Добавьте его во вложенный запрос ![]() Код: left join (select voucher, POSTINGPROFILE, offsetpostingprofile_ru, PaymentStatusUserId_ZTR, PAYMPURPOSE_ZTR, LedgerjournalTrans.BankCentralBankPurposeText,LedgerjournalTrans.txt, Journaltype from LedgerjournalTrans (nolock) inner join LedgerjournalTable on LedgerjournalTrans.journalnum = LedgerjournalTable.journalnum and posted = 1 and Journaltype in(0, 4, 45) and LedgerjournalTrans.dataareaid = 'zkz' and LedgerjournalTrans.dataareaid = 'zkz' ) as LedgerjournalTrans В 141 строке приведенного кода, нормально. Там просто выборка из таблицы JOIN на 208 строке Код: left join LedgerjournalTrans (nolock) on custtrans.voucher = LedgerjournalTrans.voucher and LedgerjournalTrans.dataareaid = 'zkz' and (LedgerjournalTrans.postingprofile in (select custledgeraccounts.postingprofile from custledgeraccounts (nolock) where (accountCode = 2 or (accountcode = 1 and num = custgroup)) and custledgeraccounts.dataareaid = 'zkz' and ltrim(sumaccount) = ltrim(LedgerAccount_ZTR)) or (offsetpostingprofile_ru in (select custledgeraccounts.postingprofile from custledgeraccounts (nolock) where (accountCode = 2 or (accountcode = 1 and num = custgroup)) and custledgeraccounts.dataareaid = 'zkz' and ltrim(sumaccount) = ltrim(LedgerAccount_ZTR))))
__________________
This posting is provided "AS IS" with no warranties, and confers no rights. Последний раз редактировалось Poleax; 27.02.2013 в 19:08. |
|
|
За это сообщение автора поблагодарили: S.Kuskov (2). |
![]() |
#3 |
Модератор
|
Цитата:
Но у Вас там влож. запрос УРЕЗАННЫМ списком полей. При выполнении и вываливается с ошибкой. нюансы
__________________
This posting is provided "AS IS" with no warranties, and confers no rights. |
|
![]() |
#4 |
Участник
|
Цитата:
![]() |
|