HOW TO CORRECT THIS QUERY

Question

alter PROCEDURE [dbo].[PrcInvoiceReportPrint]          

(         

         

       

  @iAccID       INT,       

  @iItemID      INT,         

  @FromDate     DateTime,          

  @ToDate       DateTime          

)        

AS BEGIN        

--DECLARE @iSalesID       INT        

--DECLARE @iItemID        INT        

DECLARE @dInvoiceDate   INT       

DECLARE @iTeaQty        INT        

DECLARE @iTeaTotal      INT        

DECLARE @iCoffeeQty     INT       

DECLARE @iCoffeeTotal   INT      

DECLARE @iSoftQty       INT       

DECLARE @iSoftTotal     INT       

DECLARE @iMineralQty    INT        

DECLARE @iMineralTotal  INT      

DECLARE @iPhotoQty      INT        

DECLARE @iPhotoTotal    INT        

DECLARE @iFaxQty        iNT        

DECLARE @iFaxTotal      INT        

DECLARE @iGenQty        INT        

DECLARE @iGenTotal      INT        

DECLARE @iMiscallaQty   INT        

DECLARE @iMiscallaTotal INT               

        

        

        

        SET @iTeaQty=(SELECT SUM(iNos)     from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)         

        SET @iTeaTotal=(SELECT SUM(deAmount)from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID  WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)     

         

    

          

        SET @iCoffeeQty=(SELECT SUM(iNos)    from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID  WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)     

        SET @iCoffeeTotal=(SELECT SUM(deAmount)from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID  WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)     

          

          

        SET @iSoftQty=(SELECT SUM(iNos)    from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID  WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)        

        SET @iSoftTotal=(SELECT SUM(deAmount)from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID  WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)      

        

          

             

        SET @iMineralQty=(SELECT SUM(iNos)    from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)         

 

        SET @iMineralTotal=(SELECT SUM(deAmount)from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)       

  

     

                  

        SET @iPhotoQty=(SELECT SUM(iNos)    from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)           

        SET @iPhotoTotal=(SELECT SUM(deAmount)from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)         

 

          

          

        SET @iFaxQty=(SELECT SUM(iNos)    from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)    

        SET @iFaxTotal=(SELECT SUM(deAmount)from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)    

          

        SET @iGenQty=(SELECT SUM(iNos)    from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)       

        SET @iGenTotal=(SELECT SUM(deAmount)from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)       

        

         

              

        SET @iMiscallaQty=(SELECT SUM(iNos)    from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)    

        SET @iMiscallaTotal=(SELECT SUM(deAmount)from tblSalesItem SI INNER JOIN tblSales TS ON SI.iSalesID=TS.iSalesID   WHERE SI.iItemID= SI.iItemID AND TS.iAccID=TS.iAccID AND TS.dInvoiceDate=TS.dInvoiceDate)    

          

        INSERT INTO tblTempExpense(iAccID,iItemID,iTeaQty,iTeaTotal,iCoffeeQty,iCoffeeTotal,iSoftQty,iSoftTotal,iMineralQty,iMineralTotal,iPhotoQty,iPhotoTotal,iFaxQty,iFaxTotal,iGenQty,iGenTotal,iMiscallaQty,iMiscallaTotal,dInvoiceDate)VALUES(@iAccID,@iItemID,@iTeaQty,@iTeaTotal,@iCoffeeQty,@iCoffeeTotal,@iSoftQty,@iSoftTotal,@iMineralQty,@iMineralTotal,@iPhotoQty,@iPhotoTotal,@iFaxQty,@iFaxTotal,@iGenQty,@iGenTotal,@iMiscallaQty,@iMiscallaTotal,@FromDate)          

          

        SELECT ISNULL(iAccID,0) AS AccID,ISNULL(iItemID,0) AS ItemID,ISNULL(iTeaQty,0) AS TeaQty,ISNULL(iTeaTotal,0)AS TeaTotal,ISNULL(iCoffeeQty,0)AS COFFEEQTY,ISNULL(iCoffeeTotal,0)AS COFFEETOTAL,ISNULL(iSoftQty,0)AS SOFTQTY,ISNULL(iSoftTotal,0)AS SOFTTOTAL,ISNULL(iMineralQty,0)AS MINERALQUANTITY,ISNULL(iMineralTotal,0)AS MINERALTOTAL,ISNULL(iPhotoQty,0)AS PHOTOQUANTITY,ISNULL(iPhotoTotal,0)AS PHOTOTOTAL,ISNULL(iFaxQty,0)AS FAXQUANTITY,ISNULL(iFaxTotal,0)AS FAXTOTAL,ISNULL(iGenQty,0)AS GENERATORQTY,ISNULL(iGenTotal,0)AS GENERATORTOTAL,ISNULL(iMiscallaQty,0)AS MISCALLANEOUSQTY,ISNULL(iMiscallaTotal,0)AS MISCALLANEOUSTOTAL,dInvoiceDate FROM tblTempExpense       

        WHERE iAccID=@iAccID AND dInvoiceDate=@FromDate AND iItemID=@iItemID      

            

                   

END 



PLEASE CORRECT AS SOON AS POSSIBLE.....I NEED REPORT LIKE THIS

DATE                  TEA                COFFEE
                 QTY  AMOUNT       QTY   AMOUNT
01-11-13      10   100                5       50
02-11-13      12    120               6        60
03-11-13      13     130              7         70