Skip to Content
author's profile photo Former Member
Former Member

query to calculate profit

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Jun 29, 2011 at 10:14 AM

    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
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.