Select and insert query

Question

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

Maneesh A N · Answer

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(iAccIDiItemIDiTeaQtyiTeaTotaliCoffeeQtyiCoffeeTotaliSoftQtyiSoftTotaliMineralQtyiMineralTotaliPhotoQtyiPhotoTotaliFaxQtyiFaxTotaliGenQtyiGenTotaliMiscallaQtyiMiscallaTotaldInvoiceDate)VALUES(@iAccID@iItemID@iTeaQty@iTeaTotal@iCoffeeQty@iCoffeeTotal@iSoftQty@iSoftTotal@iMineralQty@iMineralTotal@iPhotoQty@iPhotoTotal@iFaxQty@iFaxTotal@iGenQty@iGenTotal@iMiscallaQty@iMiscallaTotal@FromDate) SELECT ISNULL(iAccID0) AS AccIDISNULL(iItemID0) AS ItemIDISNULL(iTeaQty0) AS TeaQtyISNULL(iTeaTotal0)AS TeaTotalISNULL(iCoffeeQty0)AS COFFEEQTYISNULL(iCoffeeTotal0)AS COFFEETOTALISNULL(iSoftQty0)AS SOFTQTYISNULL(iSoftTotal0)AS SOFTTOTALISNULL(iMineralQty0)AS MINERALQUANTITYISNULL(iMineralTotal0)AS MINERALTOTALISNULL(iPhotoQty0)AS PHOTOQUANTITYISNULL(iPhotoTotal0)AS PHOTOTOTALISNULL(iFaxQty0)AS FAXQUANTITYISNULL(iFaxTotal0)AS FAXTOTALISNULL(iGenQty0)AS GENERATORQTYISNULL(iGenTotal0)AS GENERATORTOTALISNULL(iMiscallaQty0)AS MISCALLANEOUSQTYISNULL(iMiscallaTotal0)AS MISCALLANEOUSTOTALdInvoiceDate FROM tblTempExpense WHERE iAccID=@iAccID AND dInvoiceDate=@FromDate AND iItemID=@iItemID END

venkata kumar · Answer

hi give mee your query

Maneesh A N · Answer

I GIVEN THE SAME IN QUERY.....BUT NOT GETTING OUTPUT AS PER THE REQUIREMENT

venkata kumar · Answer

hi try fallowing insert into purchasemast(Trans_IDDistCodePur_Inv_NoPur_Inv_DateInward_NoInward_Dategrandtotnetamtpaymtpostagesfreightexpensepackingappno) (select Trans_IDDistCodePur_Inv_NoPur_Inv_DateInward_NoInward_DategrandtotNetamtpaymtpostagesfreightexpensepackingappno from Purchasemast where pur_invDate between @fromdate and @todate)