SQL Query need help
DECLARE @D1 DATEDECLARE @D2 DATESET @D1= '2019-01-01'SET @D2= '2019-03-30'select T0.BaseRef as 'Doc Num' ,T0.RefDate as 'Posting Date' ,T0.TaxDate as 'Document Date',T0.Ref2 as 'Ref No2.',Account=(CASE WHEN T2.AcctName IS NULL THEN t3.Cardname else T2.AcctName end ),T0.TransId,T3.CardName,T1.Account as AccountCode,T1.BPLName as Branch,-- Opening balance and closing balance issue -- (SELECT SUM(DEBIT) - SUM(CREDIT) FROM JDT1 Where RefDate <= @D1 and Account = T1.Account Group by Account) AS 'Opening Balance',SUM(T1.Debit) as 'Debit',SUM(T1.Credit) as 'Credit',(SELECT SUM(DEBIT) - SUM(CREDIT) FROM JDT1 Where RefDate <= @D1 and Account = T1.Account Group by Account)+(Select SUM(T1.Debit) - SUM(T1.Credit) FROM JDT1 Where RefDate <= @D2 and Account = T1.Account Group by Account) as 'Closing Balance(Rs.)',t1.ShortName,case when T0.Memo like '%Outgoing Paymentst%' then(select top 1 Comments from OVPM where docnum = T0.BaseRef)when T0.Memo like '%A/P Invoices%' then(select top 1 Comments from opch where docnum = T0.BaseRef)elseT0.Memoend as Particular,case when T0.Memo like '%Outgoing%' then(select CardName from OVPM where docnum = T0.BaseRef)when T0.Memo like '%A/P Invoices%' then(select CardName from opch where docnum = T0.BaseRef)elseT0.Memoend as Vendorfrom OJDT T0inner join JDT1 T1 on T0.TransId =T1.TransIdinner join oact tr3 on tr3.acctcode = T1.accountleft outer Join OACT T2 ON T2.AcctCode=T1.ContraActleft outer join OCRD t3 on t3.CardCode=T1.ShortNamewhere T0.RefDate>=@d1 and T0.RefDate<=@d2 and t3.cardname='A.R ENTERPRIZE'Group By T0.BaseRef,T0.TransId ,T0.RefDate,T0.TaxDate,t1.Line_ID,T0.Ref2, T1.[ContraAct],Tr3.AcctName, T2.AcctName,t3.cardname,T1.Account,T1.Debit, T1.Credit,T1.BPLName,t1.ShortName,T1.SYSCred,T0.Memo,t3.cardnameOrder by T0.TransId,t1.Line_IDSee More
from Latest Activity on Virtual University of Pakistan http://bit.ly/2VqszeO
0 comments:
Post a Comment