SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE   PROCEDURE [dbo].[sp_DateWiseProfit]        
    @FromDate DATETIME, 
    @ToDate DATETIME        
AS        
BEGIN 
    SET NOCOUNT ON;

    SELECT  
        YEAR(i.entrydate) AS Yr,
        MONTH(i.entrydate) AS Mnthno,
        DATENAME(MONTH, i.entrydate) AS Mnthname,i.entrydate,

   
        SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.NetAmount, 0) ELSE 0 END) AS SalesGrossAmount,
		 SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.NetAmount, 0) ELSE 0 END) AS SalesReturnGrossAmount,
		  SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.NetAmount, 0) ELSE 0 END) -
		 SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.NetAmount, 0) ELSE 0 END) AS NetSales,
		 		 
		(SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.Cost, 0) ELSE 0 END) - SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.Cost, 0) ELSE 0 END)) as Cost ,
		   
         
        ((SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.NetAmount, 0) ELSE 0 END) - 
          SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.Cost, 0) ELSE 0 END)) - 
         (SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.NetAmount, 0) ELSE 0 END) - 
          SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.Cost, 0) ELSE 0 END))) AS NetProfit,

		   case when ((SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.Cost, 0) ELSE 0 END) - SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.Cost, 0) ELSE 0 END)))<>0 then
		    round(  ( ((SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.NetAmount, 0) ELSE 0 END) - 
          SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.Cost, 0) ELSE 0 END)) - 
         (SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.NetAmount, 0) ELSE 0 END) - 
          SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.Cost, 0) ELSE 0 END))) /
		  (SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.Cost, 0) ELSE 0 END) - SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.Cost, 0) ELSE 0 END)))*100,4)
		  else
		  0
		  end 
		  as profitper




    FROM 
    (
     
        SELECT  
            Invoice.entrydate,
            'Sales' AS SR,
            Invoice.NetAmount,
            SUM(Item.StandardCost * InvoiceItem.Qty) 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.NetAmount <> 0 
            AND InvoiceItem.qty <> 0 
            AND InvoiceItem.SR = 'Sales' 
            AND Invoice.EntryDate BETWEEN @FromDate AND @ToDate
        GROUP BY Invoice.EntryDate, Invoice.NetAmount,Invoice.InvNo

        UNION ALL

     
        SELECT  
            Invoice.EntryDate,
            'RETURNS' AS SR,
            Invoice.NetAmount AS NetAmount,
		    SUM(Item.StandardCost * InvoiceItem.Qty) AS Cost
        FROM InvoiceItem 
        INNER JOIN ITEM ON InvoiceItem.ItemCode = ITEM.ItemCode
        INNER JOIN Invoice ON Invoice.InvoiceRetNO = InvoiceItem.InvoiceRetNO AND InvoiceItem.SR = Invoice.SR
        WHERE Invoice.SR = 'RETURNS' 
            AND ISNULL(Invoice.deleted, 'N') = 'N' 
            AND Invoice.NetAmount <> 0 
            AND InvoiceItem.qty <> 0 
            AND InvoiceItem.SR = 'RETURNS' 
            AND Invoice.EntryDate BETWEEN @FromDate AND @ToDate
        GROUP BY Invoice.EntryDate, Invoice.NetAmount,Invoice.InvoiceRetNo

    ) i 
    GROUP BY YEAR(i.entrydate), MONTH(i.entrydate), DATENAME(MONTH, i.entrydate), i.entrydate
    ORDER BY YEAR(i.entrydate), MONTH(i.entrydate), i.entrydate;
    
END;
GO