|
giri prakash
Starting Member
5 Posts |
 Posted - 2009-08-13 : 00:56:21
|
| Hi sql experts i need a solution regarding this query my query is SELECT (T0.DocNum) As[BookingNum],(SELECT SlpName FROM OSLP WHERE SlpCode=T0.SlpCode) As[Sales Emp / Refere],(SELECT (IsNull(firstName,'')+IsNull(middleName,'')+IsNull(lastName,''))FROM OHEM WHERE empID=T0.OwnerCode) As[CSE],T0.[CardCode] As[CustomerCode], T0.[CardName] As[CustomerName], T0.[DocDate] As[BookingDate],T1.[ItemCode] As[UnitCode], T1.[Dscription] As[UnitName],(SELECT NAME FROM [@OPCK]WHERE Code = T0.U_Pricel) As[Packages],T0.DocTotal As[Total Amount],max(T3.U_InsNum)as[installment no] ,max(T3.u_duedt)as [istallment date],sum(T3.U_InsAmt) as [InsAmt] , sum(T3.U_paidAmt) as [paidAmt], sum(T3.U_insAmt-T3.U_paidamt) as [Bal],(T0.DocTotal-sum(T3.U_PaIdAmt)) as [Total Outstanding Amount],T0.U_AOSDt AS[AOS DATE],T0.Comments as [CustomerRemarks],(Select Free_Text FROM OCRD WHERE Cardcode = T0.CardCode) as [BP Remarks]FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode, [dbo].[@INPS] T3WHERE T2.[ItmsGrpCod] ='100' And T0.[DocNum] = T3.[U_SoNum] ANDT3.[U_Stat] ='Paid' And T3.[U_DueDt] <= '20090831'GROUP BY T0.[CardCode], T0.[CardName], T0.[DocNum], T0.[DocDate],T0.DocEntry,T3.[U_SoNum],T0.OwnerCode,T0.SlpCode,T1.[ItemCode], T1.[Dscription],T0.U_Pricel,T0.U_AOSDt,T0.DocTotal,T3.U_InsNum,T3.U_DueDt,T3.U_PadAmt, T3.U_BAlAmt ,T0.Comments,T3.[U_SoNum] order by T3.[U_SoNum]ex:for one sale order i may have 10 installments suppose i pay only 2then i should get for insatllment no 2 and second insatllment date giriprakash |
|
query help