Helo experts
the Above query i am using to calculate the profit based on sale and purchase price but the problem which i am facing is it includes the credit memo invoice also ...According to my need i only want those invoice whose credit memo is not made means those are not cancelled . pls update this query according to my need ........
SELECT T0.[ItmsGrpCod] as grpcode,
MAX (T1.ItmsGrpNam) as GroupName,
I1.DocNum as docnum,I1.doctype as doctype , D0.CardCode as cardcode ,
MAX ( D0.CardName ) as Name ,
D1.ItemCode as 'itmcode' , D1.ocrcode as [Branch] ,
E1.firstname as [Owner Name] ,
max ( D1.Dscription ) as Descp ,
sum ( D1.Quantity ) as Quantity ,
Sum ( D1.StockPrice ) * sum ( D1.Quantity ) as [Purchase Price] ,
sum ( D1.Price ) * sum ( D1.Quantity ) as [Sales Price] ,
(sum ( D1.Price ) * sum ( D1.Quantity )) - (Sum ( D1.StockPrice ) * sum ( D1.Quantity )) AS [Actual Proft / Loss]
from ODLN D0 , DLN1 D1 INNER JOIN OITM T0 ON D1.ItemCode = T0.ItemCode INNER JOIN OITB T1 ON T0.ItmsGrpCod= T1.ItmsGrpCod, OHEM E1 , OINV I1
where
D0.Docentry = D1.Docentry and D1.ownercode = E1.empid
and D1.TrgetEntry = I1.DocEntry and I1.doctype = 'I' and upper( D1.[Dscription]) not like upper('%Debit%')
And I1.TaxDate BETWEEN CASE WHEN [%0] = '' THEN '01/01/1900' ELSE [%0] END AND CASE WHEN [%1] = '' THEN GETDATE() ELSE [%1] END
Group By I1.DocNum, D0.CardCode , T0.[ItmsGrpCod],D1.ItemCode,D1.ocrcode , E1.firstname ,I1.doctype
union all
SELECT '' as grpcode ,
'no' as GroupName,
I1.DocNum as docnum,I1.doctype as doctype , I1.CardCode as cardcode ,
MAX ( I1.CardName ) as Name ,
' ' as itmcode , I2.ocrcode as [Branch] ,
E1.firstname as [Owner Name] ,
max ( I2.Dscription ) as Descp ,
sum ( I2.Quantity ) as Quantity ,
' 0' as [Purchase Price] ,
sum ( I2.linetotal) as [Sales Price] ,
sum ( I2.linetotal) AS [Actual Proft / Loss]
from OHEM E1 , OINV I1 ,inv1 I2
where
I1.[DocEntry] = I2.[DocEntry] and I2.ownercode = E1.empid and upper( I2.[Dscription]) not like upper('%Debit%')
and I1.doctype = 'S'
And I1.TaxDate BETWEEN CASE WHEN [%0] = '' THEN '01/01/1900' ELSE [%0] END AND CASE WHEN [%1] = '' THEN GETDATE() ELSE [%1] END
Group By I1.DocNum, I1.CardCode , I2.ocrcode , E1.firstname,I1.doctype
Regards
saurav Gupta
Hi Saurav..........
SELECT T0.ItmsGrpCod as grpcode, MAX (T1.ItmsGrpNam) as GroupName, I1.DocNum as docnum,I1.doctype as doctype , D0.CardCode as cardcode , MAX ( D0.CardName ) as Name , D1.ItemCode as 'itmcode' , D1.ocrcode as Branch , E1.firstname as Owner Name , max ( D1.Dscription ) as Descp , sum ( D1.Quantity ) as Quantity , Sum ( D1.StockPrice ) * sum ( D1.Quantity ) as Purchase Price , sum ( D1.Price ) * sum ( D1.Quantity ) as Sales Price , (sum ( D1.Price ) * sum ( D1.Quantity )) - (Sum ( D1.StockPrice ) * sum ( D1.Quantity )) AS Actual Proft / Loss from ODLN D0 , DLN1 D1 INNER JOIN OITM T0 ON D1.ItemCode = T0.ItemCode INNER JOIN OITB T1 ON T0.ItmsGrpCod= T1.ItmsGrpCod, OHEM E1 , OINV I1, inv1 I2 where D0.Docentry = D1.Docentry and D1.ownercode = E1.empid and I2.TargetType=-1 and D1.TrgetEntry = I1.DocEntry and I1.doctype = 'I' and upper( D1.Dscription) not like upper('%Debit%') And I1.TaxDate BETWEEN CASE WHEN %0 = '' THEN '01/01/1900' ELSE %0 END AND CASE WHEN %1 = '' THEN GETDATE() ELSE %1 END Group By I1.DocNum, D0.CardCode , T0.ItmsGrpCod,D1.ItemCode,D1.ocrcode , E1.firstname ,I1.doctype union all SELECT '' as grpcode , 'no' as GroupName, I1.DocNum as docnum,I1.doctype as doctype , I1.CardCode as cardcode , MAX ( I1.CardName ) as Name , ' ' as itmcode , I2.ocrcode as Branch , E1.firstname as Owner Name , max ( I2.Dscription ) as Descp , sum ( I2.Quantity ) as Quantity , ' 0' as Purchase Price , sum ( I2.linetotal) as Sales Price , sum ( I2.linetotal) AS Actual Proft / Loss from OHEM E1 , OINV I1 ,inv1 I2 where I1.DocEntry = I2.DocEntry and I2.ownercode = E1.empid and upper( I2.Dscription) not like upper('%Debit%') and I1.doctype = 'S' And I2.TargetType=-1 And I1.TaxDate BETWEEN CASE WHEN %0 = '' THEN '01/01/1900' ELSE %0 END AND CASE WHEN %1 = '' THEN GETDATE() ELSE %1 END Group By I1.DocNum, I1.CardCode , I2.ocrcode , E1.firstname,I1.doctype
This will not include Invoice whose Credit Memo is raised.
Regards,
Rahul
Add a comment