SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[sp_DateWiseProfit]        
@FromDate datetime,@ToDate datetime        
AS        
begin 
SELECT a.entrydate,a.Mnthno,a.Mnthname,a.Yr,(a.Mnthname+'/'+a.Yr) 'Month',sum(Grossamount) as Grossamount,sum(Vattotal) as Vattotal,sum(Netamount) as Netamount,sum(Cost) as Cost,(sum(Grossamount)-sum(Cost)) as Profit FROM (  
SELECT  Invoice.entrydate  ,    
month(Invoice.entrydate) 'Mnthno',datename(month,invoice.entrydate) 'Mnthname',convert(varchar(25),year(invoice.entrydate)) 'Yr' ,(ISNULL(INVOICE.GROSSAMOUNT,0)) AS Grossamount,(ISNULL(INVOICE.vattotal,0)) AS Vattotal,
(ISNULL(INVOICE.netAMOUNT,0)) AS Netamount,sum(ISNULL(InvoiceItem.Qty,0)*ISNULL(InvoiceItem.QTYPERUNIT,1)*
(case when isnull(invoiceitem.amount,0)<0 then (isnull(InvoiceItem.Rate,0)-(((((isnull(Invoice.LessAmount,0)+isnull(Invoice.AddLess,0)+Invoice.Commision)*100)/Invoice.GrossAmount)/100)*isnull(InvoiceItem.Rate,0))) 
else (CASE WHEN isnull(InvoiceItem.stdcost,0)=0 THEN ISNULL(ITEM.StandardCost,0) ELSE isnull(InvoiceItem.stdcost,0) END) end)) as Cost FROM   InvoiceItem INNER JOIN ITEM ON InvoiceItem.ItemCode = ITEM.itemcode         
INNER JOIN Invoice ON Invoice.Invno = Invoiceitem.Invno and InvoiceItem.SR=Invoice.SR       
where Invoice.SR='Sales' and isnull(Invoice.deleted,'N')='N' and Invoice.GrossAmount <> 0 and Invoiceitem.qty<>0 and InvoiceItem.SR='Sales'  
and invoice.entrydate between @FromDate and @ToDate 
group by Invoice.INVOICEDate,Grossamount,Vattotal,Netamount,invoice.invno, Invoice.entrydate
union    ALL  
SELECT    
  Invoicereturn.entrydate  ,
month(Invoicereturn.EntryDate) 'Mnthno',datename(month,Invoicereturn.EntryDate) 'Mnthname',convert(varchar(25),year(Invoicereturn.EntryDate)) 'Yr' ,-(ISNULL(Invoicereturn.GROSSAMOUNT,0)) AS Grossamount,-(ISNULL(Invoicereturn.vattotal,0)) AS Vattotal,
-(ISNULL(Invoicereturn.netAMOUNT,0)) AS Netamount,-sum(ISNULL(InvoicereturnItem.Qty,0)*ISNULL(InvoicereturnItem.QTYPERUNIT,1)*
(case when isnull(Invoicereturnitem.amount,0)<0 then (isnull(InvoicereturnItem.Rate,0)-(((((isnull(Invoicereturn.LessAmount,0)+isnull(Invoicereturn.AddLess,0)+isnull(Invoicereturn.Commision,0))*100)/Invoicereturn.GrossAmount)/100)*isnull(InvoicereturnItem.Rate,0))) 
else (CASE WHEN isnull(InvoicereturnItem.stdcost,0)=0 THEN ISNULL(ITEM.StandardCost,0) ELSE isnull(InvoicereturnItem.stdcost,0) END) end)) as Cost 
FROM   InvoicereturnItem INNER JOIN ITEM ON InvoicereturnItem.ItemCode = ITEM.itemcode         
INNER JOIN Invoicereturn ON Invoicereturn.InvoiceRetNO = Invoicereturnitem.InvoiceRetNO and InvoicereturnItem.SR=Invoicereturn.SR          
where Invoicereturn.SR='RETURNS' and isnull(Invoicereturn.deleted,'N')='N' and Invoicereturn.GrossAmount <> 0 and Invoicereturnitem.qty<>0 and InvoicereturnItem.SR='RETURNS' and InvoiceReturn.EntryDate         
between @FromDate  and @ToDate   group by Invoicereturn.EntryDate,Grossamount,Vattotal,Netamount ,Invoicereturn.entrydate
)A group by a.Yr,a.Mnthno,a.Mnthname,a.entrydate  order by a.entrydate,a.Yr,a.Mnthno
end