excelde
data -> from other source -> from microsoft query>new data source ilişklendirmeyi yap sorguyada aşağıdakileri yapıştır...
profilerda mizanı çekerken kod:
exec sp_executesql N' if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp create table #GLReceiptBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,ForexDebit decimal(28,8),ForexCredit decimal(28,8),ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8),ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLReceiptBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,ForexDebit,ForexCredit,ForexDebitBalance,ForexCreditBalance ,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance,InflowQty,OutflowQty) select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,isnull(sum(AR.Debit),0) Debit,0 Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,isnull(sum(AR.ForexAmount),0)ForexDebit,0 ForexCredit,isnull(sum(AR.ForexAmount),0)ForexDebitBalance,0 ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,isnull(sum(AR.Quantity),0)InflowQty,0 OutflowQty from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId) left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) and (AR.IsDeleted is null or AR.IsDeleted = 0) and AR.Debit <> 0 and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5) and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2 group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName
union all select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,0 Debit,isnull(sum(AR.Credit),0) Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,0 ForexDebit,isnull(sum(AR.ForexAmount),0) ForexCredit,0 ForexDebitBalance,isnull(sum(AR.ForexAmount),0) ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,0 InflowQty,isnull(sum(AR.Quantity),0) OutflowQty from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId) left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) and (AR.IsDeleted is null or AR.IsDeleted = 0) and AR.Credit <> 0 and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5) and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2 group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp create table #GLAccountBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,DBalance decimal(28,8),CBalance decimal(28,8),ForexDebit decimal(28,8),ForexCredit decimal(28,8) ,ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8),FDBalance decimal(28,8),FCBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8) ,ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8),FCDBalance decimal(28,8),FCCBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLAccountBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,DBalance,CBalance,ForexDebit,ForexCredit ,ForexDebitBalance,ForexCreditBalance,FDBalance,FCBalance,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance ,FCDBalance,FCCBalance,InflowQty,OutflowQty) select CompanyId,AccountId,AccountCode,AccountName,isnull(sum(Debit),0)Debit,isnull(sum(Credit),0)Credit,isnull(sum(DebitBalance),0)DebitBalance,isnull(sum(CreditBalance),0)CreditBalance ,case when isnull(sum(DebitBalance),0)>isnull(sum(CreditBalance),0) then round(isnull(sum(DebitBalance),0)-isnull(sum(CreditBalance),0),2) else 0 end DBalance ,case when isnull(sum(DebitBalance),0)<isnull(sum(CreditBalance),0) then round(isnull(sum(CreditBalance),0)-isnull(sum(DebitBalance),0),2) else 0 end CBalance ,isnull(sum(ForexDebit),0)ForexDebit,isnull(sum(ForexCredit),0)ForexCredit,isnull(sum(ForexDebitBalance),0)ForexDebitBalance,isnull(sum(ForexCreditBalance),0)ForexCreditBalance ,case when isnull(sum(ForexDebitBalance),0)>isnull(sum(ForexCreditBalance),0) then round(isnull(sum(ForexDebitBalance),0)-isnull(sum(ForexCreditBalance),0),2) else 0 end FDBalance ,case when isnull(sum(ForexDebitBalance),0)<isnull(sum(ForexCreditBalance),0) then round(isnull(sum(ForexCreditBalance),0)-isnull(sum(ForexDebitBalance),0),2) else 0 end FCBalance ,isnull(sum(ForexCalculateDebit),0)ForexCalculateDebit,isnull(sum(ForexCalculateCredit),0)ForexCalculateCredit ,isnull(sum(ForexCalculateDebitBalance),0)ForexCalculateDebitBalance,isnull(sum(ForexCalculateCreditBalance),0)ForexCalculateCreditBalance ,case when isnull(sum(ForexCalculateDebitBalance),0)>isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateDebitBalance),0)-isnull(sum(ForexCalculateCreditBalance),0),2) else 0 end FCDBalance ,case when isnull(sum(ForexCalculateDebitBalance),0)<isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateCreditBalance),0)-isnull(sum(ForexCalculateDebitBalance),0),2) else 0 end FCCBalance ,isnull(sum(InflowQty),0)InflowQty,isnull(sum(OutflowQty),0)OutflowQty from #GLReceiptBalanceTmp group by CompanyId,AccountId,AccountCode,AccountName having round(isnull(sum(Debit),0),2) <> round(isnull(sum(Credit),0),2) select A.CompanyId,'''' [Şirket],isnull((select MF.ForexCode from Meta_Forex MF where MF.RecId=A.ForexId),'''') [Dvz],'''' [HDvz],A.AccountCode [Hesap Kodu],A.AccountName [Hesap Adı],A.SpecialCode [Özel Kod],0.0 [Devir Borç],0.0 [Devir Alacak],sum(isnull(B.Debit,0)) [Borç],sum(isnull(B.Credit,0)) [Alacak],case when sum(isnull(B.DBalance,0)) > sum(isnull(B.CBalance,0)) then sum(isnull(B.DBalance,0)) - sum(isnull(B.CBalance,0)) when sum(isnull(B.DBalance,0)) < sum(isnull(B.CBalance,0)) then sum(isnull(B.CBalance,0)) - sum(isnull(B.DBalance,0)) else 0 end [Bakiye],case when sum(isnull(B.DBalance,0)) > sum(isnull(B.CBalance,0)) then ''BB'' when sum(isnull(B.DBalance,0)) < sum(isnull(B.CBalance,0)) then ''AB'' else '''' end [Bakiye Tipi],sum(isnull(B.DBalance,0)) [Borç Bakiye],sum(isnull(B.CBalance,0)) [Alacak Bakiye],sum(isnull(B.ForexDebit,0)) [Döviz Borç],sum(isnull(B.ForexCredit,0)) [Döviz Alacak],case when sum(isnull(B.FDBalance,0)) > sum(isnull(B.FCBalance,0)) then round(sum(isnull(B.FDBalance,0)) - sum(isnull(B.FCBalance,0)),2) when sum(isnull(B.FDBalance,0)) < sum(isnull(B.FCBalance,0)) then round(sum(isnull(B.FCBalance,0)) - sum(isnull(B.FDBalance,0)),2) else 0 end [Döviz Bakiye],case when sum(isnull(B.FDBalance,0)) > sum(isnull(B.FCBalance,0)) then ''BB'' when sum(isnull(B.FDBalance,0)) < sum(isnull(B.FCBalance,0)) then ''AB'' else '''' end [Döviz Bakiye Tipi],sum(isnull(B.FDBalance,0)) [Döviz Borç Bakiye],sum(isnull(B.FCBalance,0)) [Döviz Alacak Bakiye],sum(isnull(B.ForexCalculateDebit,0)) [Hesaplanan Döviz Borç],sum(isnull(B.ForexCalculateCredit,0)) [Hesaplanan Döviz Alacak],case when sum(isnull(B.FCDBalance,0)) > sum(isnull(B.FCCBalance,0)) then round(sum(isnull(B.FCDBalance,0)) - sum(isnull(B.FCCBalance,0)),2)when sum(isnull(B.FCDBalance,0)) < sum(isnull(B.FCCBalance,0)) then round(sum(isnull(B.FCCBalance,0)) - sum(isnull(B.FCDBalance,0)),2) else 0 end [Hesaplanan Döviz Bakiye],case when sum(isnull(B.FCDBalance,0)) > sum(isnull(B.FCCBalance,0)) then ''BB'' when sum(isnull(B.FCDBalance,0)) < sum(isnull(B.FCCBalance,0)) then ''AB'' else '''' end [Hesaplanan Döviz Bakiye Tipi],sum(isnull(B.FCDBalance,0)) [Hesaplanan Döviz Borç Bakiye],sum(isnull(B.FCCBalance,0)) [Hesaplanan Döviz Alacak Bakiye],sum(isnull(B.InflowQty,0)) [Giriş Miktarı],sum(isnull(B.OutflowQty,0)) [Çıkış Miktarı],sum(isnull(B.InflowQty,0)) - sum(isnull(B.OutflowQty,0)) [Kalan],-1 CAccount from Erp_GLAccount A with (nolock) left join #GLAccountBalanceTmp B on (B.AccountId in(select AA.RecId from Erp_GLAccount AA where AA.CompanyId=A.CompanyId and AA.AccountCode like rtrim(A.AccountCode)+''%'' )) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) group by A.CompanyId,A.ForexId,A.AccountCode,A.AccountName,A.SpecialCode having round(isnull(sum(B.Debit),0),2) <> round(isnull(sum(B.Credit),0),2) order by A.AccountCode
if object_id(''tempdb..#GLReceiptTmp'') is not null drop table #GLReceiptTmp if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp ',N'@0ARErp_GLReceiptItemReceiptDate1 datetime,@0ARErp_GLReceiptItemReceiptDate2 datetime',@0ARErp_GLReceiptItemReceiptDate1='2017-01-01 00:00:00',@0ARErp_GLReceiptItemReceiptDate2='2018-12-31 23:59:00'
data -> from other source -> from microsoft query>new data source ilişklendirmeyi yap sorguyada aşağıdakileri yapıştır...
profilerda mizanı çekerken kod:
exec sp_executesql N' if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp create table #GLReceiptBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,ForexDebit decimal(28,8),ForexCredit decimal(28,8),ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8),ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLReceiptBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,ForexDebit,ForexCredit,ForexDebitBalance,ForexCreditBalance ,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance,InflowQty,OutflowQty) select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,isnull(sum(AR.Debit),0) Debit,0 Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,isnull(sum(AR.ForexAmount),0)ForexDebit,0 ForexCredit,isnull(sum(AR.ForexAmount),0)ForexDebitBalance,0 ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,isnull(sum(AR.Quantity),0)InflowQty,0 OutflowQty from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId) left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) and (AR.IsDeleted is null or AR.IsDeleted = 0) and AR.Debit <> 0 and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5) and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2 group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName
union all select A.CompanyId,A.RecId AccountId,A.AccountCode AccountCode,A.AccountName AccountName,0 Debit,isnull(sum(AR.Credit),0) Credit ,case when isnull(sum(AR.Debit),0)>isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Debit),0)-isnull(sum(AR.Credit),0)),2) else 0 end DebitBalance ,case when isnull(sum(AR.Debit),0)<isnull(sum(AR.Credit),0) then round((isnull(sum(AR.Credit),0)-isnull(sum(AR.Debit),0)),2) else 0 end CreditBalance ,0 ForexDebit,isnull(sum(AR.ForexAmount),0) ForexCredit,0 ForexDebitBalance,isnull(sum(AR.ForexAmount),0) ForexCreditBalance ,0 ForexCalculateDebit,0 ForexCalculateCredit,0 ForexCalculateDebitBalance,0 ForexCalculateCreditBalance ,0 InflowQty,isnull(sum(AR.Quantity),0) OutflowQty from Erp_GLAccount A with (nolock) inner join Erp_GLReceiptItem AR with (nolock) on (A.RecId=AR.AccountId) left join Erp_GLReceipt ARB with (nolock) on (ARB.RecId=AR.GLReceiptId) left join Erp_CostCenter CC with (nolock) on (CC.RecId=AR.CostCenterId) left join Erp_Project P with (nolock) on (P.RecId=AR.ProjectId) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) and (AR.IsDeleted is null or AR.IsDeleted = 0) and AR.Credit <> 0 and isnull(AR.ReceiptType,0) in (1 , 2 , 3 , 4 , 5) and AR.ReceiptDate between @0ARErp_GLReceiptItemReceiptDate1 and @0ARErp_GLReceiptItemReceiptDate2 group by A.CompanyId,A.RecId,A.AccountCode,A.AccountName if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp create table #GLAccountBalanceTmp ( CompanyId int,AccountId int, AccountCode nvarchar(25) collate database_default, AccountName nvarchar(50) collate database_default ,Debit decimal(28,8),Credit decimal(28,8),DebitBalance decimal(28,8),CreditBalance decimal(28,8) ,DBalance decimal(28,8),CBalance decimal(28,8),ForexDebit decimal(28,8),ForexCredit decimal(28,8) ,ForexDebitBalance decimal(28,8),ForexCreditBalance decimal(28,8),FDBalance decimal(28,8),FCBalance decimal(28,8) ,ForexCalculateDebit decimal(28,8),ForexCalculateCredit decimal(28,8) ,ForexCalculateDebitBalance decimal(28,8),ForexCalculateCreditBalance decimal(28,8),FCDBalance decimal(28,8),FCCBalance decimal(28,8) ,InflowQty decimal(28,8),OutflowQty decimal(28,8)) insert into #GLAccountBalanceTmp (CompanyId,AccountId,AccountCode,AccountName,Debit,Credit,DebitBalance,CreditBalance,DBalance,CBalance,ForexDebit,ForexCredit ,ForexDebitBalance,ForexCreditBalance,FDBalance,FCBalance,ForexCalculateDebit,ForexCalculateCredit,ForexCalculateDebitBalance,ForexCalculateCreditBalance ,FCDBalance,FCCBalance,InflowQty,OutflowQty) select CompanyId,AccountId,AccountCode,AccountName,isnull(sum(Debit),0)Debit,isnull(sum(Credit),0)Credit,isnull(sum(DebitBalance),0)DebitBalance,isnull(sum(CreditBalance),0)CreditBalance ,case when isnull(sum(DebitBalance),0)>isnull(sum(CreditBalance),0) then round(isnull(sum(DebitBalance),0)-isnull(sum(CreditBalance),0),2) else 0 end DBalance ,case when isnull(sum(DebitBalance),0)<isnull(sum(CreditBalance),0) then round(isnull(sum(CreditBalance),0)-isnull(sum(DebitBalance),0),2) else 0 end CBalance ,isnull(sum(ForexDebit),0)ForexDebit,isnull(sum(ForexCredit),0)ForexCredit,isnull(sum(ForexDebitBalance),0)ForexDebitBalance,isnull(sum(ForexCreditBalance),0)ForexCreditBalance ,case when isnull(sum(ForexDebitBalance),0)>isnull(sum(ForexCreditBalance),0) then round(isnull(sum(ForexDebitBalance),0)-isnull(sum(ForexCreditBalance),0),2) else 0 end FDBalance ,case when isnull(sum(ForexDebitBalance),0)<isnull(sum(ForexCreditBalance),0) then round(isnull(sum(ForexCreditBalance),0)-isnull(sum(ForexDebitBalance),0),2) else 0 end FCBalance ,isnull(sum(ForexCalculateDebit),0)ForexCalculateDebit,isnull(sum(ForexCalculateCredit),0)ForexCalculateCredit ,isnull(sum(ForexCalculateDebitBalance),0)ForexCalculateDebitBalance,isnull(sum(ForexCalculateCreditBalance),0)ForexCalculateCreditBalance ,case when isnull(sum(ForexCalculateDebitBalance),0)>isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateDebitBalance),0)-isnull(sum(ForexCalculateCreditBalance),0),2) else 0 end FCDBalance ,case when isnull(sum(ForexCalculateDebitBalance),0)<isnull(sum(ForexCalculateCreditBalance),0) then round(isnull(sum(ForexCalculateCreditBalance),0)-isnull(sum(ForexCalculateDebitBalance),0),2) else 0 end FCCBalance ,isnull(sum(InflowQty),0)InflowQty,isnull(sum(OutflowQty),0)OutflowQty from #GLReceiptBalanceTmp group by CompanyId,AccountId,AccountCode,AccountName having round(isnull(sum(Debit),0),2) <> round(isnull(sum(Credit),0),2) select A.CompanyId,'''' [Şirket],isnull((select MF.ForexCode from Meta_Forex MF where MF.RecId=A.ForexId),'''') [Dvz],'''' [HDvz],A.AccountCode [Hesap Kodu],A.AccountName [Hesap Adı],A.SpecialCode [Özel Kod],0.0 [Devir Borç],0.0 [Devir Alacak],sum(isnull(B.Debit,0)) [Borç],sum(isnull(B.Credit,0)) [Alacak],case when sum(isnull(B.DBalance,0)) > sum(isnull(B.CBalance,0)) then sum(isnull(B.DBalance,0)) - sum(isnull(B.CBalance,0)) when sum(isnull(B.DBalance,0)) < sum(isnull(B.CBalance,0)) then sum(isnull(B.CBalance,0)) - sum(isnull(B.DBalance,0)) else 0 end [Bakiye],case when sum(isnull(B.DBalance,0)) > sum(isnull(B.CBalance,0)) then ''BB'' when sum(isnull(B.DBalance,0)) < sum(isnull(B.CBalance,0)) then ''AB'' else '''' end [Bakiye Tipi],sum(isnull(B.DBalance,0)) [Borç Bakiye],sum(isnull(B.CBalance,0)) [Alacak Bakiye],sum(isnull(B.ForexDebit,0)) [Döviz Borç],sum(isnull(B.ForexCredit,0)) [Döviz Alacak],case when sum(isnull(B.FDBalance,0)) > sum(isnull(B.FCBalance,0)) then round(sum(isnull(B.FDBalance,0)) - sum(isnull(B.FCBalance,0)),2) when sum(isnull(B.FDBalance,0)) < sum(isnull(B.FCBalance,0)) then round(sum(isnull(B.FCBalance,0)) - sum(isnull(B.FDBalance,0)),2) else 0 end [Döviz Bakiye],case when sum(isnull(B.FDBalance,0)) > sum(isnull(B.FCBalance,0)) then ''BB'' when sum(isnull(B.FDBalance,0)) < sum(isnull(B.FCBalance,0)) then ''AB'' else '''' end [Döviz Bakiye Tipi],sum(isnull(B.FDBalance,0)) [Döviz Borç Bakiye],sum(isnull(B.FCBalance,0)) [Döviz Alacak Bakiye],sum(isnull(B.ForexCalculateDebit,0)) [Hesaplanan Döviz Borç],sum(isnull(B.ForexCalculateCredit,0)) [Hesaplanan Döviz Alacak],case when sum(isnull(B.FCDBalance,0)) > sum(isnull(B.FCCBalance,0)) then round(sum(isnull(B.FCDBalance,0)) - sum(isnull(B.FCCBalance,0)),2)when sum(isnull(B.FCDBalance,0)) < sum(isnull(B.FCCBalance,0)) then round(sum(isnull(B.FCCBalance,0)) - sum(isnull(B.FCDBalance,0)),2) else 0 end [Hesaplanan Döviz Bakiye],case when sum(isnull(B.FCDBalance,0)) > sum(isnull(B.FCCBalance,0)) then ''BB'' when sum(isnull(B.FCDBalance,0)) < sum(isnull(B.FCCBalance,0)) then ''AB'' else '''' end [Hesaplanan Döviz Bakiye Tipi],sum(isnull(B.FCDBalance,0)) [Hesaplanan Döviz Borç Bakiye],sum(isnull(B.FCCBalance,0)) [Hesaplanan Döviz Alacak Bakiye],sum(isnull(B.InflowQty,0)) [Giriş Miktarı],sum(isnull(B.OutflowQty,0)) [Çıkış Miktarı],sum(isnull(B.InflowQty,0)) - sum(isnull(B.OutflowQty,0)) [Kalan],-1 CAccount from Erp_GLAccount A with (nolock) left join #GLAccountBalanceTmp B on (B.AccountId in(select AA.RecId from Erp_GLAccount AA where AA.CompanyId=A.CompanyId and AA.AccountCode like rtrim(A.AccountCode)+''%'' )) where A.CompanyId = 5 and (A.IsDeleted is null or A.IsDeleted = 0) group by A.CompanyId,A.ForexId,A.AccountCode,A.AccountName,A.SpecialCode having round(isnull(sum(B.Debit),0),2) <> round(isnull(sum(B.Credit),0),2) order by A.AccountCode
if object_id(''tempdb..#GLReceiptTmp'') is not null drop table #GLReceiptTmp if object_id(''tempdb..#GLReceiptBalanceTmp'') is not null drop table #GLReceiptBalanceTmp if object_id(''tempdb..#GLAccountBalanceTmp'') is not null drop table #GLAccountBalanceTmp ',N'@0ARErp_GLReceiptItemReceiptDate1 datetime,@0ARErp_GLReceiptItemReceiptDate2 datetime',@0ARErp_GLReceiptItemReceiptDate1='2017-01-01 00:00:00',@0ARErp_GLReceiptItemReceiptDate2='2018-12-31 23:59:00'
