ALTER 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,
        --(DATENAME(MONTH, i.entrydate) + '/' + CAST(YEAR(i.entrydate) AS VARCHAR(25))) AS [Month],

   
        SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.GrossAmount, 0) ELSE 0 END) AS SalesGrossAmount,
		 SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.GrossAmount, 0) ELSE 0 END) AS SalesReturnGrossAmount,
		  SUM(CASE WHEN i.SR = 'Sales' THEN ISNULL(i.GrossAmount, 0) ELSE 0 END) -
		 SUM(CASE WHEN i.SR = 'RETURNS' THEN ISNULL(i.GrossAmount, 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.GrossAmount, 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.GrossAmount, 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.GrossAmount, 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.GrossAmount, 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.GrossAmount,
            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.EntryDate, Invoice.GrossAmount

        UNION ALL

     
        SELECT  
            Invoicereturn.EntryDate,
            'RETURNS' AS SR,
            Invoicereturn.GrossAmount AS GrossAmount,
            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, Invoicereturn.GrossAmount

    ) 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;