Cursor Looping Versus Set-based Queries
Mar 28, 2006
I know this question has been asked. And the usual answer is don't use
cursors or any other looping method. Instead, try to find a solution
that uses set-based queries.
But this brings up several questions / senarios:
* I created several stored procedures that take parameters and inserts
the data into the appropriate tables. This was done for easy access/use
from client side apps (i.e. web-based).
Proper development tactics says to try and do "code reuse". So, if I
already have stored procs that do my logic, should I be writing a
second way of handling the data? If I ever need to change the way the
data is handled, I now have to make the same change in two (or more)
places.
* Different data from the same row needs to be inserted into multiple
tables. "Common sense" (maybe "gut instinct" is better) says to handle
each row as a "unit". Seems weird to process the entire set for one
table, then to process the entire set AGAIN for another table, and then
YET AGAIN for a third table, and so on.
* Exception handling. Set based processing means that if one row fails
the entire set fails. Looping through allows you to fail a row but
allow everything else to be processed properly. It also allows you to
gather statistics. (How many failed, how many worked, how many were
skipped, etc.)
?? Good idea ?? The alternative is to create a temporary table (sandbox
or workspace type thing), copy the data to there along with "status" or
"valdation" columns, run through the set many times over looking for
any rows that may fail, marking them as such, and then at the end only
dealing with those rows which "passed" the testing. Of course, in order
for this to work you must know (and duplicate) all constraints so you
know what to look for in your testing.
View 13 Replies
ADVERTISEMENT
Jul 20, 2005
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin
View 1 Replies
View Related
Jul 22, 2015
I am creating an SSIS Script Task that will be used to process SSAS dimensions and partitions and ideally log the details of each in a table. Any info on the benefits or drawbacks of using the built-in SSAS parallel processing as opposed to doing it manually in a multi-threaded "Parallel.Foreach" loop using the .NET AMO library.
In my testing, when I use a Parallel.foreach loop, I am able to obtain and log information about the object such as end time and time to process immediately after each object is processed. This allows me to keep a history of processing time for each object:
public void processDimensions(Server Server, Database Database, ProcessType processType)
{
Parallel.ForEach(Database.Dimensions.OfType<Microsoft.AnalysisServices.Dimension>(), d =>
{
DateTime beginTime = DateTime.Now;
try
{
d.Process(processType);
[code]....
If circumventing the built-in SSAS parallel processing is not best practice I'd like to know in advance before I go too far down that path.
View 2 Replies
View Related
Oct 29, 2007
I have always been told that Cursors create a lot of overhead and consume a lot of system resources. Is it faster to store the data in a temp table and loop through it by using Select Top 1 and Delete statements or by using a static, Forward-Only Cursor? Both ways store the data in TempDB, but doesn't the While Loop statement generate more IO's than the Cursor? In theory, I am thinking that the Cursor is better. Any info will be appreciated.
Thanks!!
View 2 Replies
View Related
Jul 9, 2007
Hi,
I need to create a cursor that will loop through my customer database to return matching rows of data based on my select statement criteria. I have written most of it based on what I remember from my limited SQL exposure at a previous job afew years ago, but I can't remember how to make the @cust_id varaible increment by 1 and loop to the end of the customer table.
Can anyone steer me in the right direction here please?
DECLARE @cust_id INT
SET @cust_id = 371
DECLARE my_cursor CURSOR FOR
SELECT CUSTOMER_ID, FULL_NAME, ADDRESS_LINE1, SUBURB, STATE, POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE POSTCODE IN (SELECT POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE CUSTOMER_ID = @cust_id
AND INACTIVE = 0 )
AND CUSTOMER_ID <> @cust_id
SELECT CUSTOMER_ID, FULL_NAME, ADDRESS_LINE1, SUBURB, STATE, POSTCODE
FROM CUSTOMER_LANGUAGE_DETAILS
WHERE CUSTOMER_ID = @cust_id
AND INACTIVE = 0
OPEN my_cursor
--SET @cust_id = @cust_id + 1
FETCH NEXT FROM my_cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM my_cursor
END
CLOSE my_cursor
DEALLOCATE my_cursor
View 4 Replies
View Related
Jul 20, 2005
Hi, newbie here:I have created a small (5 fields) Access table which I will beconverting to MS-SQL Server format when the ASP code has beencompleted. It is basically a collection of links to news releases frommining companies. The group of people who will be browsing thisdatabase want to know if the news release pertains to their area.Sometimes the news release pertains to multiple areas if the miningproperties are scattered. Given the possibility of a one-to-manyrelationship, ie one news release, many areas, I created an additionaltable for the areas. I created the ASP code to pull down the newsrelease information, then loop through the area records such as:set RSNewsRelease = Server.CreateObject("ADODB.Recordset")NewsRelSQL = "Select date, company, title, newsreleaseID fromnewsreleases;"do while not RSNewsRelease.EOF'display news release date, company and titleresponse.write RSNewsRelease(0).Value & RSNewsRelease(1).Value &RSNewsRelease(2).Value'loop through areasset RSAreas = Server.CreateObject("ADODB.Recordset")'run querydo while not RSAreas.EOF'display areasLoopset RSAreas = nothingLoopIn other words, the only way I could get the results I wanted was toset the Recordset to nothing, then reset it with each iteration of theouter loop.Is there a better way to do this?Jules
View 3 Replies
View Related
Mar 7, 2007
Hello everyone,
I don't know what category would be appropriate for this question but security seems to be close enough.
I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.
Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.
Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?
No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.
Any thoughts or ideas?
Thanks for your time, Adrian
View 11 Replies
View Related
Oct 24, 2007
Hi,
I have a package that I need to loop (possibly multiple times) based on a return value from a stored procedure.
I know that in DTS you could use the return value of "execution status" to do this.
Is there a way in SSIS to loop based on a return value?
Thanks much
View 5 Replies
View Related
Oct 3, 2001
When I try to repeat a query using cursors the first run is succesful but the second returns nothing; I then have to disconnect and reconnect to get the query to work again. Here's sample code (which does nothing except print returned items)
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
declare @login sysname , @password sysname
declare sourcelogins cursor for
select name , password
from master.dbo.syslogins
open sourcelogins
while ( @@fetch_status = 0)
begin
fetch sourcelogins into @login , @password
print @login
end
close sourcelogins
deallocate sourcelogins
SET ANSI_NULLS OFF
SET ANSI_WARNINGS OFF
GO
First time through this prints all logins. Second time it returns "completed successfully" and prints nothing. Similarily, if I run two queries consecutively, the second using a differenct cursor name, the second still fails. Any ideas?
View 1 Replies
View Related
Jul 20, 2005
Does anyone have any good references they could recommend on Cursorbased SQL writing? I have to create SQL that can loop though recordssimular to VB loops and I have been told that this is the way to go.Any recommendations would be helpful.
View 7 Replies
View Related
Nov 9, 2005
Guys
Here's the scenario
create table data1 (dealid varchar(6) , datex smalldatetime , Tn INT)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-12-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '30-11-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('12345' , '31-10-2005' , 9999)
insert data1 (dealid , datex , Tn )
values ('98765' , '31-12-2005' , 2)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 1)
insert data1 (dealid , datex , Tn )
values ('98765' , '30-11-2005' , 0)
select * from data1
I need to update the Tn column from the default 9999 for the 3 rows in this table where the dealid is 12345 based on the value in the datex column so the row with the 'highest ie most recent date' gets a 0. I then need to assign the value 1 to the next highest and so on until all rows (in this case 3) get incrementing integer values. It's easy with a cursor but can't get my head round doing it in a set-based way
Any ideas
View 3 Replies
View Related
Sep 7, 2007
I am replacing cursor logic in a SP to a setbased approach to scale better. My setbased approach seems to be better but it runs very fractionaly faster (execution time) than the cursor approach for a single run in test environment. I think resource cost wise, my set based approach should be better. Number of rows iterated thru this cursor is small (0-150). This particular SP is called over 2000 times in production everyday. Is it worth the trouble changing this if we get only marginally benefits, will my set-based approach work better on a server that has lot of activity (lot of connections etc). Our db server runs at about 75-85% cpu usage daily and this particular SP accounts to 13% CPU usage for 2000+ executions.
If the data set involved in cursors is small, is it worth the trouble changing them to set based approaches?
Am I doing right to change this SP to setbased approach.
View 3 Replies
View Related
Feb 13, 2002
Hi there!
Here is my situation:
table 'ReceiptHeader'
IDCustomerIDDateCreated
1225102/06/2002
1332102/09/2002
1444002/15/2002
table 'ReceiptDiscount'
IDDiscountIDReceiptHeaderIDAmount
111210.00
241250.00
311325.00
**a receipt can have multiple discounts**
Table 'ReceiptDetail'
ReceiptHeaderID LineItemIDTotal
121155.33
131145.33
141241.66
**for this example there is only one line item per receipt**
Without using a cursor, i would like to return a result set
like this one below using a set based solution...
ReceiptIDCustomerIDDiscountTotal
1225160.00155.33
1332125.00145.33
144400.00241.66
Thanks,
SF
View 1 Replies
View Related
Jun 2, 2008
Hey All,
I am trying to convert cursor based stored proc in to set based simple statements stored proc. As this stored proc has created alot of performance issues. I am confuse now as I spent my most of time creating this stored proc. Please advise how can I convert this stored proc into set base simple statment.
Thanks in advance.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [SampleStoredProc]
@Var1varchar(20),
@Var2varchar(3),
@Var3 varchar(2) = 'Dummy'
As
declare @selectlist varchar(5000)
declare @tableBuild varchar(1000)
declare @FieldName varchar(50)
declare @FieldSelect varchar(500)
declare @FieldTitle varchar(50)
declare @TableName varchar(50)
declare @holdTable varchar(50)
declare @title varchar(50)
declare @holdTitle varchar(50)
declare @PageName varchar(50)
declare @sequence varchar(100)
declare @extraCriteria varchar(200)
declare @holdCriteria varchar(200)
declare @insertSQL varchar(5000)
declare @ConvertRoutine varchar(500)
declare @loopCtrl1 bit
declare @loopCtrl2 bit
declare @ConvertSQL varchar(5000)
declare @PrevValue varchar(50)
declare @NewValue varchar(50)
declare @ActionTxtvarchar(1)
declare @Descriptionvarchar(20)
declare @effDatevarchar(10)
declare @transEffDatevarchar(10)
declare @expDatevarchar(10)
declare @lastTransDatevarchar(10)
declare @policyStatusvarchar(2)
declare @reasAmendDescvarchar(50)
declare @policyNumbervarchar(20)
declare @riskStatevarchar(20)
declare @PriorPremmoney
declare @AmendPremmoney
declare @PremDiffmoney
declare mtcursor cursor for
select TableName, FieldName, FieldSelectTxt, FieldTitleTxt, SequenceFieldName, ExtraCriteriaTxt, PageTitleTxt, ConversionRoutineTxt from MyTable1
where Column1 = @Var2
order by PageDisplaySequenceNbr, TableName, ExtraCriteriaTxt, SequenceFieldName
open mtcursor
fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
set @loopCtrl1 = 0
set @loopCtrl2 = 0
WHILE (@loopCtrl1 = 0)
begin
set @holdTable = @TableName
set @holdCriteria = @extraCriteria
set @holdTitle = @title
if @FieldSelect = ''
set @selectlist = @selectlist + ',' + @FieldName
else
set @selectlist = @selectlist + ',' + @FieldSelect
set @tableBuild = @tableBuild + ',' + @FieldName + ' varchar(50)'
fetch next from mtcursor into @TableName, @FieldName, @FieldSelect, @FieldTitle, @sequence, @extraCriteria, @title, @ConvertRoutine
if @@fetch_status <> 0
set @loopCtrl2 = 1
if (@TableName <> @holdTable) or (@extraCriteria <> @holdCriteria) or (@title <> @holdTitle) or (@loopCtrl2 = 1)
begin
set @tableBuild = @tableBuild + ')'
set @insertSQL = '
declare mtcursor2 cursor for
select FieldName, FieldTitleTxt, ExtraUpdateMatchTxt, PullForUpdateInd, PullForAddInd, PullForDeleteInd, PullForAnyUpdateInd from MyTable1
where TableName = ''' + @holdTable + '''
and ExtraCriteriaTxt = ''' + @holdCriteria + '''
and PageTitleTxt = ''' + @holdTitle + '''
and Column1 = ''' + @Var2 + '''
order by FieldDisplaySequenceNbr
declare @FieldName varchar(50)
declare @FieldTitle varchar(50)
declare @ExtraUpdateMatch varchar(500)
declare @PullUpdate bit
declare @PullAdd bit
declare @PullDelete bit
declare @PullAnyUpdate bit
open mtcursor2
fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
WHILE (@@fetch_status = 0)
begin
if substring(@FieldTitle,1,1) = ''#''
set @FieldTitle = substring(@FieldTitle,2,len(@FieldTitle) - 1)
else
set @FieldTitle = '''''''' + @FieldTitle + ''''''''
if @PullAnyUpdate = 1
begin
exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
where A.Val1 = ''''O'''' and B.Val1 = ''''U'''''')
end
else
begin
if @PullUpdate = 1
exec (''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', A.UnitNbr, ''''' + @holdTitle + ''''', '' + @FieldTitle + '', A.'' + @FieldName + '', B.'' + @FieldName + '', ''''U''''
from #tempTable A left join #tempTable B on B.UnitNbr = A.UnitNbr and B.Val1 = ''''U'''' '' + @ExtraUpdateMatch + ''
where A.Val1 = ''''O'''' and B.Val1 = ''''U'''' and ((A.'' + @FieldName + '' <> B.'' + @FieldName + '') or (A.'' + @FieldName + '' is null and B.'' + @FieldName + '' is not null)
or (A.'' + @FieldName + '' is not null and B.'' + @FieldName + '' is null)) '')
end
if @PullAdd = 1
exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', ''''n/a'''', '' + @FieldName + '', ''''A'''' from #tempTable A where Val1 = ''''A'''''')
if @PullDelete = 1
exec(''INSERT MyTable2 SELECT ''''' + @Var1 + ''''', UnitNbr, ''''' + @holdTitle + ''''','' + @FieldTitle + '', '' + @FieldName + '', ''''n/a'''', ''''D''''
from #tempTable A where Val1 = ''''D'''''')
fetch next from mtcursor2 into @FieldName, @FieldTitle, @ExtraUpdateMatch, @PullUpdate, @PullAdd, @PullDelete, @PullAnyUpdate
end
close mtcursor2
deallocate mtcursor2'
exec (@tableBuild + ' insert into #tempTable select ' + @selectlist + ' from ' + @holdTable + ' where Id = ' + '''' + @Var1 + '''' + @holdCriteria + @insertSQL)
set @selectlist = 'Val1, Val2,' + @sequence + ' as UnitNbr'
set @tableBuild = 'Create table #tempTable (Val1 varchar, Val2 int, UnitNbr varchar(20)'
end
if @loopCtrl2 = 1
set @loopCtrl1 = 1
end
close mtcursor
deallocate mtcursor
Delete from MyTable2 where ltrim(rtrim(PreviousValueTxt)) = ltrim(rtrim(EndorsedValueTxt)) and ActionTxt='U' and ID=@Var1
declare deletecursor cursor for
select distinct PageNm from MyTable2 where Id = @Var1 and ActionTxt = 'U'
open deletecursor
fetch next from deletecursor into @PageName
while @@fetch_status = 0
begin
if (SELECT count(*) from MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U' and PreviousValueTxt <> EndorsedValueTxt ) = 0
DELETE FROM MyTable2 where Id = @Var1 and PageNm = @PageName and ActionTxt = 'U'
fetch next from deletecursor into @PageName
end
close deletecursor
deallocate deletecursor
declare convertcursor cursor for
select a.PreviousValueTxt, a.EndorsedValueTxt, A.EntrySequenceNbr, A.ActionTxt, b.ConversionRoutineTxt from MyTable2 a
inner join MyTable1 b
on a.PageNm = b.PageTitleTxt and a.FieldNm = b.FieldTitleTxt and b.ConversionRoutineTxt <> ''
where a.Id = @Var1
open convertcursor
fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
while @@fetch_status = 0
begin
set @ConvertSQL = 'declare @PrevConverted varchar(50) declare @NewConverted varchar(50)'
set @ConvertSQL = @ConvertSQL + ' declare @ConvertInput varchar(50) '
set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) '
set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + ''''
if @ActionTxt = 'A'
set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = ''' + @PrevValue + ''''
else
begin
set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @PrevValue + ''''
set @ConvertSQL = @ConvertSQL + ' set @PrevConverted = (' + @ConvertRoutine + ')'
end
if @ActionTxt = 'D'
set @ConvertSQL = @ConvertSQL + ' set @NewConverted = ''' + @NewValue + ''''
else
begin
set @ConvertSQL = @ConvertSQL + ' set @ConvertInput = ''' + @NewValue + ''''
set @ConvertSQL = @ConvertSQL + ' set @NewConverted = (' + @ConvertRoutine + ')'
end
set @ConvertSQL = @ConvertSQL + ' update MyTable2 set PreviousValueTxt = @PrevConverted, EndorsedValueTxt = @NewConverted
where EntrySequenceNbr = ''' + @Sequence + ''''
exec (@ConvertSQL)
fetch next from convertcursor into @PrevValue, @NewValue, @Sequence, @ActionTxt, @ConvertRoutine
end
close convertcursor
deallocate convertcursor
if @Var2 = 'PA '
--exec PAConfirmCovConversions @Var1 = @Var1
exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3
Create table #pageSeqTable (PageTitle varchar(50), PageSeq int)
insert into #pageSeqTable
select distinct PageTitleTxt, PageDisplaySequenceNbr
from MyTable1
where Column1 = @Var2
select PageNm, RowNumber, FieldNm, PreviousValueTxt, EndorsedValueTxt, ActionTxt
from MyTable2, #pageSeqTable b
where Id = @Var1 and PageNm = b.PageTitle
order by b.PageSeq, RowNumber, ActionTxt desc, EntrySequenceNbr
select @effDate = convert(char,EffectiveDate,101), @transEffDate = convert(char,TransactionEffectiveDt,101), @expDate = convert(char,LastTransactionEffectiveDt,101),
@policyStatus = PolicyStatusCd, @reasAmendDesc = ReasonAmendedDes,
@policyNumber = PolicyNumber,
@riskState = StateName,
@AmendPrem = convert(money,PremiumAmount)
from SHPlaninfo A, SHSeleReasonAmended B, SHSeleStateCode C
where Id = @Var1
AND Val2 = (select max(Val2)
from SHPlanInfo
where Id = @Var1)
AND B.ReasonAmendedCd = A.ReasonAmendedCd
AND C.StateCode = A.RiskState
Select @PriorPrem = convert(money,PremiumAmount) FROM SHPlanInfo WHERE Id = @Var1 and Val2 = '0'
Set @PremDiff = @AmendPrem - @PriorPrem
select EffectiveDate = @effDate
select TransactionEffectiveDt = @transEffDate, ExpirationDate = @expDate, LastTransactionEffectiveDt = @lastTransDate
select AmendXPolStat = @policyStatus
select ReasonAmendedDes = @reasAmendDesc
select PolicyNumber = @policyNumber
select RiskState = @riskState
select PriorPremium = @PriorPrem select AmendPremium = @AmendPrem select PremiumDifference = @PremDiffSelect ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1
delete from MyTable2 where Id = @Var1
return
View 1 Replies
View Related
Sep 18, 2006
Hi,
I am declaring the cursor based on a query which is generated dynamically. but it is not working
Declare @tempSQL varchar(1000)
--- This query will be generated based on my other conditon and will be stored in a variable
set @tempsql = 'select * from orders'
declare cursor test for @tempsql
open test
This code is not working.
please suggest
Nitin
View 12 Replies
View Related
Jan 30, 2008
I have a set of revenue records where there is a UNIT column and a REVCHARGE column. What I need to do is breakout the records into single records where the unit count is > 1 and calc the actual charge:
Ex:
Units REVCHG FIELD_A FIELD_B .....
3 3.00 ABCD EFGH
Needs to be converted to:
Units REVCHG FIELD_A FIELD_B .....
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH
1 1.00 ABCD EFGH
The calc is obvious but how can I do this with a cursor but would like to do it without a cursor if possible? Anybody got an idea?
Thanks.
View 6 Replies
View Related
Jul 30, 2007
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE)
1 01/01/2007 PURCHASE 10 1000
2 01/01/2007 PURCHAES 5 1100
3 01/01/2007 SALES -5 *TobeCalculated
4 02/01/2007 Purchase 20 9000
5 02/01/2007 SALES -10 *TobeCalculated
5 02/01/2007 purchase 50 8000
6 03/01/2007 Sales -10 *TobeCalculate
7 01/01/2007 Purchase 20 12000
I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View 20 Replies
View Related
Feb 22, 2015
I need to create a few select queries based on an er diagram.
These are my Create Table statements and import statements:
Create Table Agent (Aid integer primary key, Pid integer, aName text);
Create Table Product (Pid integer primary key, pName text);
Create Table Supplier (Sid integer primary key, sName text);
Create table Supplies (Sid integer, Pid integer, price decimal(8,2));
.import agent.txt Agent
.import product.txt Product
.import supplier.txt Supplier
.import supplies.txt Supplies
I think I got all my create table statements are correct.
I need to Find the number of agents for each supplier that has at least one agent. The result should be tuples of the form (sid, sName, number of agents)
-Select Sid, sName, count(Aid) from Agent A join Supplier S on (S.Sid = A.Sid) group by S.Sid, S.sName, Aid;
But it gives me this error: no such column: A.Sid
Im thinking I might have a problem with my create table statement and/or primary key statements?
View 9 Replies
View Related
Sep 20, 2006
Hi,
Using SQL Server 2000, I need to perform date range type queries that involve my company's Fiscal Year, which is not the same as the calendar year. My company's Fiscal Year if from Sept 1 to Aug 31, where Aug 31st year determines the Fiscal Year. For example, since today's date is 09/20/2006, the current Fiscal Year is 2007.
An example of a typical query requirement:
Find all the sales figures to-date for the current Fiscal Year. So, a WHERE clause will consist of a date range query from 09/01/2006 to 8/31/2007.
Initially, I created a Function to find the current Fiscal Year based on the current date, by calling the GETDATE() function and passing the results to the following function:
CREATE FUNCTION dbo.fnGetFY (@CurrentDatetime datetime)
RETURNS int
AS
BEGIN
DECLARE @FY int
IF (SELECT MONTH(@CurrentDatetime)) > 8
SET @FY = YEAR(@CurrentDatetime) + 1
ELSE
SET @FY = YEAR(@CurrentDatetime)
RETURN(@FY)
END
So, the view queries that involve the Fiscal Year call the above function.
However, these function calls drag down the VIEW query response time to the point where the time is either unacceptable or an ODBC Timeout occurs, even with Query Analyzer.
Is there a way to create a Global server parameter to hold the current Fiscal Year value, so function calls are not necessary? Or set Fiscal Year date ranges for a database or server system parameter?
Does anyone know of a efficent, response timewise, way to handle Fiscal Year date range queries?
Will appreciate the help!!!
View 5 Replies
View Related
Jul 20, 2005
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View 15 Replies
View Related
Mar 28, 2008
Im having a issue. Im not sure how I am going to carry out but I have two tables in SQL server 2005
TABLES
Category SubCategory (PK)CategoryName (PK) SubCategoryNameCategoryID SubCategoryIDDate Date (Just shows the date inserted) (FK)CategoryID
On the front page, I need to have it querys out the CategoryName from Categorys but also querys out all....Well not all but atleast 5 subcategorys that relate to that categoryName. Once its down it moves to the next category and does the same and so on. Does anyone know the trick ?
View 5 Replies
View Related
Aug 12, 2015
In MSDN file I read about static cursor
STATIC
Defines a cursor that makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in
tempdb; therefore, modifications made to base tables are not reflected in the data returned by fetches made to this cursor, and this cursor does not allow modifications
It say's that modifications is not allowed in the static cursor. I have a questions regarding that
Static Cursor
declare ll cursor global static
for select name, salary from ag
open ll
fetch from ll
while @@FETCH_STATUS=0
fetch from ll
update ag set salary=200 where 1=1
close ll
deallocate ll
In "AG" table, "SALARY" was 100 for all the entries. When I run the Cursor, it showed the salary value as "100" correctly.After the cursor was closed, I run the query select * from AG.But the result had updated to salary 200 as given in the cursor. file says modifications is not allowed in the static cursor.But I am able to update the data using static cursor.
View 3 Replies
View Related
Sep 20, 2007
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32
Could not complete cursor operation because the set options have changed since the cursor was declared.
Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153
Cursor is not open.
here is the stored procedure:
Alter PROCEDURE [dbo].[sp_MSforeachsp]
@command1 nvarchar(2000)
, @replacechar nchar(1) = N'?'
, @command2 nvarchar(2000) = null
, @command3 nvarchar(2000) = null
, @whereand nvarchar(2000) = null
, @precommand nvarchar(2000) = null
, @postcommand nvarchar(2000) = null
AS
/* This procedure belongs in the "master" database so it is acessible to all databases */
/* This proc returns one or more rows for each stored procedure */
/* @precommand and @postcommand may be used to force a single result set via a temp table. */
declare @retval int
if (@precommand is not null) EXECUTE(@precommand)
/* Create the select */
EXECUTE(N'declare hCForEachTable cursor global for
SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = ''PROCEDURE''
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '
+ @whereand)
select @retval = @@error
if (@retval = 0)
EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0
if (@retval = 0 and @postcommand is not null)
EXECUTE(@postcommand)
RETURN @retval
GO
example useage:
EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"
GO
View 7 Replies
View Related
Sep 25, 2007
part 1
Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)
DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]
OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME
END
CLOSE DBCur
DEALLOCATE DBCur
Part 2
SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS
my question is,
how to join the part 1 n part 2?
is there posibility?
View 1 Replies
View Related
Apr 13, 2006
I'm sorry to be ignorant on this point. It seems trivial, but what's the difference between @@ and @ when using variables in T-SQL? I have a developer that always uses @@ for local variables and @ for reference variables (meaning variables declared as parameters for a stored procedure or function).
Is that purely stylistic? Is it a holdover from some previous version? Or is it a legitimate best practice that I've not seen before?
My google-shui is weak today; I found nothing when searching.
Regards,
hmscott
View 5 Replies
View Related
Feb 21, 2007
Do you need SP1 installed before installing SP2?
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx
View 1 Replies
View Related
Apr 13, 2007
Hi everybody. I am just confused. What is the difference between SQL SP1 and SP2 ? Can anyone explain further to me.
Thanks.
-Ron-
View 10 Replies
View Related
Jan 30, 2004
I have two tables :
tableA
a_id (int)
value (varchar 255)
tableB
id (int)
a_id (int)
b_id (int)
c_id (int)
d_id (int)
Both these tables contain considerable amounts of rows, but over time tableA will end up containing orphaned values (i.e. the a_id is not used in tableB) and this problem cannot be rectified by setting, for example, cascade deletes.
To fix this problem I decided to write a simple stored procedure to purge all values in tableA where its a_id is not used in tableB :
DELETE FROM tableA WHERE a_id NOT IN (SELECT a_id FROM tableB)
Now although the following document relates to postgres :
http://archives.postgresql.org/pgsql-sql/2003-12/msg00174.php
I was interested to find out if I should be wary of using NOT IN in my query.
View 6 Replies
View Related
Feb 12, 2007
Hello,
I have an Output parameter as follows:
@Feedback INT OUTPUT
I want to give it a value and return it.
What is the difference between using:
SELECT @Feedback = -1
RETURN @Feedback
And
SET @Feedback = -2
RETURN @Feedback
Thanks,
Miguel
View 19 Replies
View Related
Jun 20, 2007
Hello,
I wonder if anyone else out there has the same impression that I have: I find that DTS works much better than SSIS.
I find that DTS is so easy to use and reliable: it gets the job done and fast! On the other hand, SSIS seems to be so needlessly complex that it takes hours of troubleshooting just to get it to work, and sometimes it doesn't work at all. For example I have just spent hours trying to get SSIS to import a flat file with 300,000 rows. It just crashes and doesn't even give an error message so that one can fix it. On the other hand I have just now successfully accomplished the same task with DTS and it took me 5 minutes!
I honestly don't see a valid reason for using SQL Server 2005 instead of 2000. So far it's much more productive to use 2000.
I hope Microsoft can clarify this issue.
Regards,
Jerome Smith
View 8 Replies
View Related
Mar 19, 2002
I want to loop through a recordset and do inserts into another table based on each record.
The way I have been doing it is copy my key data into a temp table,
Loop through temp finding the max ID
Doing what I need to do, deleting the max, then finding the new max and looping until no records exist.
I know there has to be a better way. The table I am working with is millions of records.
Thanks in advance,
Chris Reeder
View 2 Replies
View Related
Mar 28, 2002
I need to loop through a set of tables and move the data through a data pump from one server to another. This set of tables is dynamic so I have greated a global recordset and the looping is working fine.
During the looping process I need to change the transformations for each table so the source, destination, and transformation of the datapump are correct for the next table in the loop. I am using a VBS to handle this right now but cannot get the transformation to change. I essentially want to auto-remap using a vbs script. Is this possible?
Thanks for any help.
View 1 Replies
View Related
May 23, 2008
Hello clever people
I have a table that holds duplicates that I want to change into a table that has no duplicates. The current table is this
name
compound_id integer
name varchar(150)
name_type integer
This table stores chemical names. There is no primary key in the table so there are multiple compound_id's. I think the original idea was to have four name-types
1 = chemical name
2 = a description of the chemical
3 = a synonym of the chemical
4 = a formula of the chemical
I have created a new table called compound_name with this structure
id int primary key (auto identity)
compound_id int used as a foreign key
compound_name varchar(150)
compound_desc varchar(250)
compound_synonym varchar(150)
compound_formula varchar(50)
compound_trade_nme varchar(50)
I have also started to populate the new table by running this code
insert into compound_name(compound_id,compound_name)
SELECT DISTINCT compound_id, name
FROM dbo.name
WHERE (name_type = 1)
Now I need to somehow loop through the name table getting distinct compound_id's, and perform a case when name_type = 2 (which is synonym name_type) Then inside the loop update compound_name.compound_synonym for each compound_id which matches name_type 2
Then case 3 do the same for name_type 3 which is the name_type for descripton
Then case 4 do the same for name_type 4 which is the formula
Any help would be appreciated
View 6 Replies
View Related
