Select and insert query

first i have to select 'FromDate' and also 'TODate' from datetimepicker.then click preview button.in preview button ,passing parameters like "customerid","from date" and "to date" and called here "save function",because want to insert some datas into a 'newtable(tblreport)',so i written a query ij sql 2005, in this save function,which is used for select(by using inner join of two table like tblsales and tbl salesitem) and also insert here into a new table for design a report.i have mistake in that query in WHERE condition.i don't know how to give.THE date which is inserted into that new table(tblreport) is invoice date means from date....but first i need date should display one by one in order....if you want my query...i will forward....please help me as soon as possible....in c# windows application

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Maneesh A N12y ago
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
0
venkata kumar12y ago
hi
give mee your query
0
Maneesh A N12y ago
I GIVEN THE SAME IN QUERY.....BUT NOT GETTING OUTPUT AS PER THE REQUIREMENT
0
venkata kumar12y ago
hi
try fallowing

insert into purchasemast(Trans_ID,DistCode,Pur_Inv_No,Pur_Inv_Date,Inward_No,Inward_Date,grandtot,netamt,paymt,postages,freight,expense,packing,appno)
(select Trans_ID,DistCode,Pur_Inv_No,Pur_Inv_Date,Inward_No,Inward_Date,grandtot,Netamt,paymt,postages,freight,expense,packing,appno from Purchasemast where pur_invDate between @fromdate and @todate)
0