Skip to Content
0
Former Member
Jun 29, 2011 at 10:04 AM

query to calculate profit

14 Views

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