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