Moving Average Using Select Statement Or Cursor Based?
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 Complete Forum Thread with Replies
Related Forum Messages:
Using Select Statement Instead Of Cursor
Hi All, Can anyone please help? TableA has data as below: ssn sex dob rel_code 111111111 m 19500403 m 111111111 f 19570908 w 111111111 f 19770804 d 111111111 f 19801203 d 111111111 f 19869712 d 111111111 m 19870907 s 111111111 m 19901211 s I have to convert the rel_code into a specific manner so the data will look as below in TableB: ssn sex dob rel_code 111111111 m 19500403 01 111111111 f 19570908 02 111111111 f 19770804 20 111111111 f 19801203 21 111111111 f 19869712 22 111111111 m 19870907 30 111111111 m 19901211 31 Member's rel_code = 01 spouse's rel_code = 02 daughter's rel_code starts from 20 with the oldest and increments by 1. Son's rel_code starts from 30 and increments by 1 from oldest to the youngest. I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor. Thanks in advance. Jannat.
View Replies !
Select Statement In Cursor
Hi... I have a stored procedure that rertrieves data from an sql database and sends out a mail to each receipient who meets the criteria I am using SQL mail. I dynamically generate the where clause for my sql query based on criteria taken from other stored procedures and store it in a varchar variable called @sqlquery When i have the following code to run my cursor DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary. What should i do. i have tested the variable @sqlquery and it is definately not blank. There is no bracket error or anything. Please help!!! Thanks much indeed Ramesh
View Replies !
Select Statement In Cursor...Please Help
Sorry to disturb you guys but I have a problem on the select statement in sql cursor My select statement is stored in 2 variables one holds the select clause and the other holds the where clause I am doing a small test as my seelct statement is very complicated lots of joins and it is built up from lots of parameters from other queries and from another stored procedure as well Hope you can help when i type the following code: declare @query varchar(100) declare @query2 varchar(100) set @query = "SELECT FROM ml_testMaillist " set @query2 = " WHERE m_Email= 'ramesh@go-events.com' " DECLARE overdue2 CURSOR LOCAL FORWARD_ONLY exec(@query + @query2) open overdue2 I get the error Server: Msg 156, Level 15, State 1, Line 11 Incorrect syntax near the keyword 'exec'. Please please help as this is very impt to me Thanks Thanks Regards
View Replies !
How To Specify Which Database To Use For A Select Statement Within A Cursor?
Hi everyone, I have been trying to perform the following task: Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns: Database name Table name Column name from that table with a clustered index I have already created the following script which displays all the databases for a given server: declare @DBname nvarchar(128) declare testCursorForDB cursor for select name from sys.databases with (nolock) where name not in ('master','tempdb','model','msdb') order by name open testCursorForDB fetch next from testCursorForDB into @DBname while @@fetch_status = 0 begin print @DBname fetch next from testCursorForDB into @DBname end close testCursorForDB deallocate testCursorForDB I also have created the following query which will display all the table and column names which have a clustered index for a given database: select object_name(i.id) as TableName, i.name as IndexName from sys.sysindexes as i with (nolock) where i.indid = '1' However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor. Any help is greatly appreciated Thanks!
View Replies !
How To Put Condition In Select Statement To Write A Cursor
col1 col2 col3 col4 36930.60 145 N . 00 17618.43 190 N . 00 6259.20 115 N .00 8175.45 19 N .00 18022.54 212 N .00 111.07 212 B .00 13393.05 67 N .00 In above 4 col if col3 value is B then cursor has to fectch appropriate value from col4. if col3 value is N then cursor has to fectch appropriate value from col1. here col2 values are unique. Can any one reply for this..............
View Replies !
Select Statement Based On A Date
i am not getting a result back when i run the query below. select * from users where DateCreated = '2004-02-19' so i went into the table and looked at the record. for DateCreated field i have both date and time. ex: 2004-02-19 08:40:00 how can i select this record with out using the time in the select statement. what i want to see is how many users signed up for a day. any ideas?
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile... Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'. However the select statement itself runs perfectly well as a query, no errors. The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs. What gives with this? Thanks in advance R. The code: Code Snippet -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --IF (@authCode <> 'TX-TEC') --BEGIN -- SET @errmsg = 'Unsupported reporting format:' + @authCode -- RAISERROR(@errmsg, 11, 1); --END DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! ); END
View Replies !
Moving Average
Can any one be able to help me to write a querry on Moving Average Example Product Volume Moving Average Working Fish 300 300 300 /1 Fish 100 200 (300 + 100) /2 Fish 500 300 (300 + 100 + 500) /3 Fish 100 200 (300 + 100 + 500 + 200 ) /4 Thanks
View Replies !
Moving Average
Hi All, I am trying to create a report that shows moving average. Well I found this template: // /*Returns the average value of a member over a specified time interval.*/ CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average] AS Avg ( [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) : [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember, [Measures].[<<Target Measure>>] ) // This calculation returns the average value of a member over the specified time interval. , FORMAT_STRING = "Standard"; Only I do not have a specified interal or a constant number that I can place into .Lag(<<Periods to Lag>>) The report I have has a parameter that allows you to select a specific team project from a list. Although I have not figured out how to get the iteration paramter to update to only show the specified team project iterations, I want the graph to only show the iterations for that team project and the moving average (in the graph). The problem is that I can't just plug in the number 6 because different projects we have, have different numbers of iterations, and the user can pick any project from the list in the parameter. Does anyone have suggestions on this? Thanks! -Nici
View Replies !
Moving Average
Can anyone tell me how to calculate a moving average for the below table? -------------------- MONS /vol /counter(the date) jan/8 / 2006-01-01 feb/9 / etc. ........ oct/5 / etc. nov/6 / dec/7 / --------------------- I am using the below code, but it is having no effect...... SELECT i1.MONS, i1.COUNTER, ( SELECT AVG( i2.VOL ) FROM #RES1 AS i2 WHERE i2.MONS = i1.MONS And DATEDIFF( month , i2.COUNTER , i1.COUNTER ) Between 0 And 12 ) AS MovingAverageOver12Months FROM #RES1 AS i1 ORDER BY i1.COUNTER thanks in advance, dirwin
View Replies !
Need Gridview To Display Different Columns Based On New SELECT Statement
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great. My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around. So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting. I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions? Thanks, Brad
View Replies !
SSRS2K5 Moving Average
Hello Guys, Im quite new to SSRS and SQL in general and i've found this site to be a goldmine, and I found a thread on here relating to moving averages but I don't think it meets my needs. I have a simple bar chart in SSRS with a Category Field (Fields!WeekYear.Value) which has the week number and year value on the axis, and a Data Field (Fields!Amount.Value) which is just a quantity for the given week. Now what I need is a Moving Average of the amount with each new week generated (Simple and 3-point and/or 5-point), I've tried using some code and even functions but nothing is working for me so I was wondering if anyone here can help me out? PS I've been tinkering with the RunningValue function and my DataSet is called "Cube" so I assumed that the scope should be "Cube" aswell but it only returns an error, am I right or am I being retarded? As I say I am very new so please be gentle with me :) Thank You in advance. Man im a n00b!
View Replies !
Problem Calculating Moving Average
Firstly, sorry for the long post, but I've included a fair bit ofsample data.Im doing a comparision of 10yr Bond prices and CPI adjustments, withan 18 week moving average of the CPI. I'm using a nested sub-query tocalculate the moving average, but I'm having difficulty selectingexactly 18 data points (ie When I include the 'HAVING COUNT(C1.Closes)= 18' line, I get no results).Can anyone help?-- Some sample data:CREATE TABLE Bond10 (Closes [datetime] NOT NULL ,Prices [smallmoney] NOT NULL ,)INSERT INTO Bond10SELECT '1994-01-14', 6.57 UNIONSELECT '1994-01-21', 6.53 UNIONSELECT '1994-01-28', 6.44 UNIONSELECT '1994-02-04', 6.51 UNIONSELECT '1994-02-11', 6.54 UNIONSELECT '1994-02-18', 6.89 UNIONSELECT '1994-02-25', 7.18 UNIONSELECT '1994-03-04', 7.43 UNIONSELECT '1994-03-11', 7.43 UNIONSELECT '1994-03-18', 7.44 UNIONSELECT '1994-03-25', 7.66 UNIONSELECT '1994-04-01', 7.96 UNIONSELECT '1994-04-08', 8.07 UNIONSELECT '1994-04-15', 8.24 UNIONSELECT '1994-04-22', 8.23 UNIONSELECT '1994-04-29', 8.45 UNIONSELECT '1994-05-06', 8.82 UNIONSELECT '1994-05-13', 8.86 UNIONSELECT '1994-05-20', 8.44 UNIONSELECT '1994-05-27', 8.75 UNIONSELECT '1994-06-03', 8.79 UNIONSELECT '1994-06-10', 8.77 UNIONSELECT '1994-06-17', 9.24 UNIONSELECT '1994-06-24', 9.63 UNIONSELECT '1994-07-01', 9.66 UNIONSELECT '1994-07-08', 9.59 UNIONSELECT '1994-07-15', 9.41 UNIONSELECT '1994-07-22', 9.56 UNIONSELECT '1994-07-29', 9.58 UNIONSELECT '1994-08-05', 9.31CREATE TABLE AvgCPI (Closes [datetime] NOT NULL ,AvgCPI [smallmoney] NOT NULL ,)INSERT INTO AvgCPISELECT '1994-01-14', 2.04 UNIONSELECT '1994-01-21', 2.04 UNIONSELECT '1994-01-28', 2.04 UNIONSELECT '1994-02-04', 2.04 UNIONSELECT '1994-02-11', 2.04 UNIONSELECT '1994-02-18', 2.04 UNIONSELECT '1994-02-25', 2.04 UNIONSELECT '1994-03-04', 1.51 UNIONSELECT '1994-03-11', 1.51 UNIONSELECT '1994-03-18', 1.51 UNIONSELECT '1994-03-25', 1.51 UNIONSELECT '1994-04-01', 1.51 UNIONSELECT '1994-04-08', 1.51 UNIONSELECT '1994-04-15', 1.51 UNIONSELECT '1994-04-22', 1.51 UNIONSELECT '1994-04-29', 1.51 UNIONSELECT '1994-05-06', 1.51 UNIONSELECT '1994-05-13', 1.51 UNIONSELECT '1994-05-20', 1.51 UNIONSELECT '1994-05-27', 1.51 UNIONSELECT '1994-06-03', 1.80 UNIONSELECT '1994-06-10', 1.80 UNIONSELECT '1994-06-17', 1.80 UNIONSELECT '1994-06-24', 1.80 UNIONSELECT '1994-07-01', 1.80 UNIONSELECT '1994-07-08', 1.80 UNIONSELECT '1994-07-15', 1.80 UNIONSELECT '1994-07-22', 1.80 UNIONSELECT '1994-07-29', 1.80 UNIONSELECT '1994-08-05', 1.80-- My query so far:SELECT A1.Closes, A1.Prices, B1.AvgCPI, SUM(C1.AvgCPI) AS MovSumCPI,AVG(C1.AvgCPI) AS MovAvgCPI, COUNT(C1.AvgCPI) AS CounterFROM (SELECT Closes, Prices FROM Bond10) A1LEFT JOIN (SELECT Closes, AvgCPI FROM AvgCPI) B1 ON A1.Closes = B1.ClosesLEFT JOIN (SELECT Closes, AvgCPI FROM AvgCPI) C1 ON C1.Closes >= A1.Closes AND DATEADD(Week,-18,C1.Closes) <A1.ClosesGROUP BY A1.Closes, A1.Prices, B1.AvgCPI, C1.AvgCPI-- HAVING COUNT(C1.Closes) = 18ORDER BY A1.ClosesDROP TABLE Bond10DROP TABLE AvgCPIExpected ResultsCloses Bon10 AvCPI MovAvg========== ==== ==== ====14-Jan-94 6.57 2.0414-Jan-94 6.57 2.0421-Jan-94 6.53 2.0421-Jan-94 6.53 2.0428-Jan-94 6.44 2.0428-Jan-94 6.44 2.044-Feb-94 6.51 2.044-Feb-94 6.51 2.044-Feb-94 6.51 2.0411-Feb-94 6.54 2.0411-Feb-94 6.54 2.0411-Feb-94 6.54 2.0418-Feb-94 6.89 2.0418-Feb-94 6.89 2.0418-Feb-94 6.89 2.0425-Feb-94 7.18 2.0425-Feb-94 7.18 2.0425-Feb-94 7.18 2.04 2.044-Mar-94 7.43 1.51 2.014-Mar-94 7.43 1.51 1.9811-Mar-94 7.43 1.51 1.9511-Mar-94 7.43 1.51 1.9218-Mar-94 7.44 1.51 1.8918-Mar-94 7.44 1.51 1.8625-Mar-94 7.66 1.51 1.8325-Mar-94 7.66 1.51 1.801-Apr-94 7.96 1.51 1.781-Apr-94 7.96 1.51 1.758-Apr-94 8.07 1.51 1.728-Apr-94 8.07 1.51 1.6915-Apr-94 8.24 1.51 1.6615-Apr-94 8.24 1.51 1.6322-Apr-94 8.23 1.51 1.6022-Apr-94 8.23 1.51 1.5729-Apr-94 8.45 1.51 1.5429-Apr-94 8.45 1.51 1.516-May-94 8.82 1.51 1.516-May-94 8.82 1.51 1.5113-May-94 8.86 1.51 1.5113-May-94 8.86 1.51 1.5120-May-94 8.44 1.51 1.5120-May-94 8.44 1.51 1.5127-May-94 8.75 1.51 1.5127-May-94 8.75 1.51 1.513-Jun-94 8.79 1.8 1.5310-Jun-94 8.77 1.8 1.5417-Jun-94 9.24 1.8 1.5624-Jun-94 9.63 1.8 1.571-Jul-94 9.66 1.8 1.598-Jul-94 9.59 1.8 1.6115-Jul-94 9.41 1.8 1.6222-Jul-94 9.56 1.8 1.6429-Jul-94 9.58 1.8 1.665-Aug-94 9.31 1.8 1.67Thanks,Stephen
View Replies !
GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following): SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated! Thanks, Randy
View Replies !
Getting The Average Results Based On Time.
Hello~, The table has columns like this. ________________________________ time smalldatetime value1 int value2 int ---------------------------------------------------------- for example, .... '2006-11-16 12:00:00',100,200 '2006-11-16 13:00:00',110,210 '2006-11-16 14:00:00',120,220 .... The record is inserted at every hour. I want get daily,monthly,yearly average and display the result ordered by time.
View Replies !
Matrix Report:Adding Average Column/Sorting Based On Last Month/Conditional Formating
I have a matrix report with 2 column SaleAmount and ProfitAmounts by Month like Sale Profit Dealer 5/1/2007 6/1/2007 7/1/2007 5/1/2007 6/1/2007 7/1/2007 A 100 200 300 20 25 15 B 200 250 50 30 45 19 how can i do following 3 things 1)Add Total column for Sale and Average column for Profit 2)Sort report by lastMonth of Sale (here 7/1/2007) High to low 3)if last month of sale(here 7/1/2007) is less than second last month here (6/1/2007) whole row should be red thanks
View Replies !
Select Duplicate Rows And Average Them.
I have a table with four columns: id, value1, value2, and name. I need to select all duplicate rows with the same name in the name field. Once selected I need to average the value1 and value2 rows with each other. Then I would like to delete the duplicate rows and replace with the new averaged values. Should I first move the duplicate rows to a temp table and then average? Any help would be appreciated.
View Replies !
SELECT Average Values FROM Another Table
So I have this table called "listings"... there are 100 unique listings with an integer ID for each. I have another table called "ratings"... in there are multiple entries that have a listing_id field and a rating field. The rating field is a value from 0-10. I want to select ALL "listings" from the listings table... and then sort based on the average number that the multiple rating fields in the ratings table has for that listing. I CAN NOT figure it out!! Any help would be greatly appreciated. Please respond if I have not explained this properly. Thanks in advance.
View Replies !
Cursor Based SQL?
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 Replies !
How To Write Set-based SQL Instead Of Cursor
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 Replies !
Cursor, Set Based Approach
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 Replies !
Avoiding Cursor - Want Set Based Solution
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 Replies !
How To Replace Cursor Based Statements With Set Ba
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 Replies !
Moving From Access To A Web Based Approach
Hi all. I am not a programer but managed to piece together an Access database that is the backbone of my company. My IT group advised me to move to an SQL server back end and move to a web based front end. My question. I am very comfortible with Access and modify the databases regularly. What front end is most like Access for modifying forms, reports, macros, etc. Thanks!
View Replies !
User-Defined Functions To Calculate Average And Relative Percent Difference(RPD):How To Designate The Variables Of Average/RPD
Hi all, This is my first time to do T-SQL User-Defined Functions programming. I just learned some basic DDL and DML statements. But I do not know how to designate the variables of the User-Defined Function. I have 3 dbo tables in SQL Server Management Studio Express (SSMSE): dbo.Projects: ProjectID ProjectName LabName 1 Blueriver ALSI 2 Greentree GPL 3 Redrock STL-NJ NULL NULL NULL dbo.Samples: SampleID SampleName Matrix SampleType Chemical ProjectID 1 Blueriver01 Water Primary VOCs 1 2 Blueriver02 Water Duplicate VOCs 1 3 Blueriverr03 Water QA VOCs 1 4 Greentree11 Soil Primary VOCs 2 5 Greentree12 Soil Duplicate VOCs 2 6 Greentree13 Soil QA VOCs 2 NULL NULL NULL NULL NULL NULL dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 2 Benzene 25.60 ug/L 1 3 Trichloroethene 13.00 ug/L 1 4 Xylenes 0.00 ug/L 1 5 Acetone 90.70 ug/L 2 6 Benzene 31.40 ug/L 2 7 Trichloroethene 19.20 ug/L 2 8 Xylenes 2.00 ug/L 2 9 Acetone 140.30 ug/L 3 10 Benzene 21.50 ug/L 3 11 Trichloroethene 22.20 ug/L 3 12 Xylenes 0.00 ug/L 3 13 Acetone 222.10 ug/Kg 4 14 Benzene 10.30 ug/Kg 4 15 Trichloroethene 30.20 ug/Kg 4 16 Xylenes 50.70 ug/Kg 4 17 Acetone 211.90 ug/Kg 5 18 Benzene 16.40 ug/Kg 5 19 Trichloroethene 34.70 ug/Kg 5 20 Xylenes 60.00 ug/Kg 5 21 Acetone 220.30 ug/Kg 6 22 Benzene 13.20 ug/Kg 6 23 Trichloroethene 32.00 ug/Kg 6 24 Xylenes 55.50 ug/Kg 6 NULL NULL NULL NULL NULL The Average of chemical/analyte in 2 samples is defined as: Average = Abs(the result of primary sample + the result of duplicate sample)/2. Average = Abs(the result of primary sample + the result of QA sample)/2. Average = Abs(the result of duplicate sample + the result of QA sample)/2. The RPD of chemical/analyte in 2 samples is defined as: RPD = Abs(the result of primary sample - the result of duplicate sample)/ Average RPD = Abs(the result of primary sample - the result of QA sample)/ Average RPD = Abs(the result of Duplicate sample - the result of QA sample)/ Average I want to calculate the average and RPD of each chemical/analyte for the following 3 pairs of the related samples: (i) Between the primary and duplucate samples (ii) Between the primary and QA samples (iii) Between the duplucate and QA samples. In the Table "dbo.Projects", ProjectID is the primary key. In the Table "dbo.Samples", SampleID is the primary key and ProjectID is the foreign key. In the Table "dbo.LabTests", AnalyteID is the primary key and SampleID is the foreign key. I do not know how to designate the variable of each result of analyte in a sample and use it to calucalte the Average and RPD for each pair (i.e. for (i), (ii), or (iii)). For example: I do not know how to designate the following 2 results: dbo.LabTests: AnalyteID AnalyteName Result Unit SampleID 1 Acetone 120.80 ug/L 1 5 Acetone 90.70 ug/L 2 Please help and advise me how to designate the 2 results in T-SQL User Defined Functions to calculate the Average and RPD. Thanks in advance, Scott Chang
View Replies !
Moving From 2000 To 2005: Issue Accessing Cursor Returned By Sp
I have a number of triggers that call a stored procedure that returns a cursor. The triggers then use the results of this cursor to do other actions. My problem is that this works fine in SQL2000 but just won't work in SQL2005. When I try to access the results of the returned cursor, I get an error -2147217900 could not complete cursor operation because the set options have changed since the cursor was declared. If I port the code contained in the sp into the trigger, it runs fine. But having to port over the sp's code defeats the whole concept of being able to re-use the sp. Does anybody have any ideas of what could be going on? I look forward to a quick response. Dennis
View Replies !
Cursor Looping Versus Set-based Queries
I know this question has been asked. And the usual answer is don't usecursors or any other looping method. Instead, try to find a solutionthat uses set-based queries.But this brings up several questions / senarios:* I created several stored procedures that take parameters and insertsthe data into the appropriate tables. This was done for easy access/usefrom client side apps (i.e. web-based).Proper development tactics says to try and do "code reuse". So, if Ialready have stored procs that do my logic, should I be writing asecond way of handling the data? If I ever need to change the way thedata 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 multipletables. "Common sense" (maybe "gut instinct" is better) says to handleeach row as a "unit". Seems weird to process the entire set for onetable, then to process the entire set AGAIN for another table, and thenYET AGAIN for a third table, and so on.* Exception handling. Set based processing means that if one row failsthe entire set fails. Looping through allows you to fail a row butallow everything else to be processed properly. It also allows you togather statistics. (How many failed, how many worked, how many wereskipped, etc.)?? Good idea ?? The alternative is to create a temporary table (sandboxor workspace type thing), copy the data to there along with "status" or"valdation" columns, run through the set many times over looking forany rows that may fail, marking them as such, and then at the end onlydealing with those rows which "passed" the testing. Of course, in orderfor this to work you must know (and duplicate) all constraints so youknow what to look for in your testing.
View Replies !
Declare Cursor Based On Dynamic Query
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 Replies !
Query Data Based On Moving Datetime.
I'm trying to query data from a database for a report that looks for the last 2 weeks starting at 10pm, taking a value once every 24 hours. Using AND DateTime >= DateAdd(wk,-2,GETDATE()) AND DateTime <= GetDate()") I easily get the last two weeks but the query obviously only grabs the data at the time the query runs. I need to be able to run it any time of the day but only grab the data at 10pm. I'm very new at this so please excuse my ignorance but I could really use some help with this. Thanks very much.
View Replies !
Breakout Records Based On UNIT Total Without Cursor
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 Replies !
Sql Server Express Problem When Moving From VWD File Based To IIS
Hi All, We have a file based asp.net app built using Visual Web Developer and Sql Server Express 2005. We have finished development and testing and are now moving to the deployment stage. As a first step, we would like to be able to view it on a test machine using IIS (instead of VWDs built in web server). We have created a virtual directory in IIS and can view our app correctly at http:localhost/ForIIS_test. However, when we get to a page that tries to access our Sql Sever Express database, we get the following error: An attempt to attach an auto-named database for file C:Documents and SettingsClaudeMy DocumentsVisual Studio 2005WebSitesForIIS_testApp_Data estDatastore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share. Does anyone know how to overcome this problem? Any help appreciated. Claude.
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
How To Convert Cursor Based Stored Proc In Set Based Simple Stored Proc.
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] @Var1 varchar(20), @Var2 varchar(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 @ActionTxt varchar(1) declare @Description varchar(20) declare @effDate varchar(10) declare @transEffDate varchar(10) declare @expDate varchar(10) declare @lastTransDate varchar(10) declare @policyStatus varchar(2) declare @reasAmendDesc varchar(50) declare @policyNumber varchar(20) declare @riskState varchar(20) declare @PriorPrem money declare @AmendPrem money declare @PremDiff money 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) ' -- start 33385 set @ConvertSQL = @ConvertSQL + ' declare @Var3 varchar(2) ' set @ConvertSQL = @ConvertSQL + ' set @Var3 = ''' + @Var3 + '''' --end 33385 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 /* LOB-specific data conversions */ if @Var2 = 'PA ' --exec PAConfirmCovConversions @Var1 = @Var1 exec PAConfirmCovConversions @Var1 = @Var1, @Var3 = @Var3 -- End issue 33385 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) /* Case 33385 */ 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 = @PremDiff Select ClientNumber from SHClient with (nolock) where Id=@Var1 and ApplicantRecordInd = 1 delete from MyTable2 where Id = @Var1 return
View Replies !
Latches - Average Waits, Average Duration?
I have been monitoring the average number of latch waits and the average duration of each latch wait on my primary SQL box. On average, I see around 30 latch waits per second with an average duration of under 1 second. What type of average are you seeing on your production machines? What is a normal average? I have tried researching for suggested values on these two counters, but I haven't turned up any information. Thanks, MV
View Replies !
Need An Average By Year Of An Average By Month
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged... I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25. The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement. ?confused? Here's the original query. accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): ' SELECT name, country_cd from weather_station where platformId=&&platformId; SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS FROM daily WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime GROUP BY to_char(datetime,'MM') ORDER BY to_char(datetime,'MM'); with a result of: NAME_________________CO -------------------- -- OFFUTT AFB___________US MO______MAX_T _____AVG_T__MIN_TEMTP_TOTAL_YEARS -- ---------- ---------- ---------- ----------- 01_________21______-5.31________-30__________60 02_________26______-2.19______-28.3__________61 03_______31.1_______3.61______-26.1__________60 04_______35.6______11.07______-12.2__________60 05_______37.2_______17.2_______-3.3__________60 06_______41.1______22.44__________5__________60 07_______43.3______24.92________7.2__________60 08_______40.6______23.71________5.6__________60 09_________40______18.84_______-2.2__________59 10_______34.4_______12.5_______-8.9__________59 11_________29_______4.13______-23.9__________60 12_________21______-2.52______-28.3__________60
View Replies !
Need Help With A SQL Statement - Trying Not To Use A Cursor
I'm just know basic SQL but not enough to write any complex queries.The problem I'm facing right now keeps me thinking to use a Cursor butI've seen a lot of posts on here saying Cursors are bad so I'm hopingthere is a complex query that can give me the data I need.I have about 6 pages in website where I need to display a datagrid ofinformation. There should be 5 columns, Filename, and then 4 CategoryTitles (These category titles are stored in a table calledPageCategory). I have another table, XREF_Doc_Page that stores thePageID, DocID (ID to know what file it is), and PageCategoryID. So Ican query this table with a pageID to see all the results that shouldbe on this page but I don't know how to format it the way I need mydatagrid?In order to have the records from PageCategory be columns, is this acrosstab query or something?My only thoughts right now are to user a cursor to query Pagecategoryand build a temp table somehow with these as the columns?? (Not surehow'd that would work yet).So the datagrid would have the 5 columns like I said and then justlist all files associated with this page and put a checkmark underwhichever category it was assigned to (example below...)Files PageCat1 PageCat2PageCat3 PageCat4abc.pdf Xxyz.pdf Xjkl.pdfx
View Replies !
How To Capture The Value For A CURSOR Statement
Hi everyone, The following snippet of code returns something like that: string;string1;string2 Up to here fine. I woner how to export such value to ssis variable??? That variable will contain the value needed for the FILEATTACHMENTS property (Send Mail Task) Thanks a lot, declare @anex as varchar(500) declare @anex2 as varchar(700) set @anex2 = '' DECLARE anexos CURSOR FOR SELECT [Ruta] + [Fichero] as ANEXO FROM SVC_FICHEROS INNER JOIN SVC_ENVIOS ON SVC_FICHEROS.IDENVIO = SVC_ENVIOS.IDENVIO WHERE ENVIADO = 0 OPEN anexos; FETCH NEXT FROM anexos INTO @anex WHILE @@FETCH_STATUS = 0 BEGIN IF @anex2 = '' begin set @anex2 = @anex end else begin set @anex2 = @anex2 + ';' + @anex end FETCH NEXT FROM anexos INTO @anex END CLOSE anexos DEALLOCATE anexos
View Replies !
Help With Cursor And Fetch Statement
Hello, I am hoping someone can help me with using the cursor and fetch functions. I have not used these features in the past and I am now stuck when trying to use IF statements with the fetch function. I have a temp table populated with the below headers and their associated data. The headers are as follows: ItemRcvdKey, TranID, TranDate, QtyReceived, UnitCost, ItemKey, WhseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost. The information contained in this temp table lists every single receipt of goods against all of our inventoried items. The QtyOnHand listed on each record is the total QtyOnHand for that item in that warehouse. What I need the fetch to do is grab the receipt of goods records, starting with the most recent TranDate, and pull them into the new temp table until the QtyOnHand is reached. The QtyonHand it should be comparing too is the one listed on the first fetched record. Once the Sum of the QtyRcvd is equal to or is greater than the QtyOnHand for that item I need the fetch to move on to the next item number and perform the same function. One thing I need to be clear on is that if there are 3 Receipt Records(TranID) for Item A in Warehouse A, the total QtyOnHand will be listed 3 times. I need to make sure that the Fetch is comparing all the records for Item A in Warehouse A to one instance of the QtyOnHand. The other aspect is that there will be receipt of goods for the same item in multiple warehouses. So I also need the Fetch to be sure that when it is grabbing records and putting them in the temp table, it makes sure it is matching the ItemID and the WhseID with the record it started with. The current script I have written is below. If you can offer any help I would greatly appreciate it. Code SnippetDeclare @ItemID VarChar(30), @QtyOnHand Decimal (16,8), @WhseID VarChar (6), @SumRcvd Int, @TranID VarChar(30), @TranDate DateTime, @QtyRcvd Decimal (16,8), @UnitCost Decimal (16,8), @ItemKey Int, @WhseKey Int, @ShortDesc VarChar (40), @StdCost Decimal (16,8) DECLARE Temp_cursor CURSOR FOR SELECT TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost FROM #Temp1 tem OPEN Temp_cursor FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost WHILE @@FETCH_STATUS = 0 BEGIN -- 0 Insert Into #Temp3 (TranID, TranDate, QtyRcvd, UnitCost, ItemKey, WHseKey, ItemID, ShortDesc, WhseID, QtyOnHand, StdCost) Values (@TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost) FETCH NEXT FROM Temp_cursor INTO @TranID, @TranDate, @QtyRcvd, @UnitCost, @ItemKey, @WHseKey, @ItemID, @ShortDesc, @WhseID, @QtyOnHand, @StdCost
View Replies !
One Statement Update - Join, No Cursor ?
HI AllI have a process that I am trying to accomplish with one statement. Icannot think of any way to do it other than using a cursor.I was wondering if anyone could point me in the right direction.I want to update the Domain in Table A with the Domain in Table Bwhere A.Account = B.Account with the highest rank.----------------------------------Table A--------------------------------------------------------------------Account|Domain--------------------------------------------------------------------Micorsoft|null----------------------------------IBM|null-------------------------------------------------------------TAble B--------------------------------------------------------------------------------------------------------------------------Account|Domain|Rank--------------------------------------------------------------------------------------------------------------------------Micorsoft|microsoft.com|9-------------------------------------------------------------Micorsoft|yahoo.com|2-------------------------------------------------------------Micorsoft|hotmail.com|1Thanks!!!
View Replies !
Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code' Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"' but I can't seem to come up with the right statement. Any help greatly appreciated. Thanks, Judith
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
[SQL Server 2000] How Can I Create Cursor For A SQL Statement?
I have a SQL statement stored in a SQL varriable (after a lot of conditions) Code: declare @sql char(100) set @sql = 'select ma_kh, ten from _khang' Now, I want to create a cursor to recalculate some values I've tried: Code: declare cur_T cursor for exec(@sql) open cur_T but it doesn't work. Can I have another way to do that???
View Replies !
Cursor Vs. Select
buddies,situation: a processing must take place on every row of a table, andoutput results to another table, that can't be done via an insertinto..select query (let's assume that it's not possible for now).There're 2 solutions I have in mind:1) open a cursor and cycle through each row (The table can have up to1M rows)2) create a clustered index (i.e on an identity column) then have aloop like:declare @i int, @rows int,@col1 varchar(20), @col2 varchar(20),... @coln varchar(20),@outval1 varchar(20),... -- output valuesselect @i=1, @rows = max(xid) from tblname -- xid is clustered indexedwhile (@i<=@rows)beginselect @col1 = col1, @col2 = col2,...@coln = colnfrom tblnamewhere xid = i-- do the processing on the variables-- then insert results to another tableset @i = @i+1endI'd like to know your ideas of which one would be more efficient. Anyother solutions are much appreciatedthanks,Tamy
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
24000 Invalid Cursor State. Prepared Statement
I have written a routine to search a unique record using prepared statement. Its my first sql coding with c++. I am not using / importing any dlls. I connect+allocs handels , then use SQLPrepare(StmtHandle, SQLStmt,SQL_NTS); to generate a guery. I have written bind parameters and sqlexecute +sqlFetch in a loop and loop gets executed till ESC key is pressed. First time when I bind paramaters using SQLBindParameter it works perfect. When loop gets executed secondtime onwards, it gives an error. SQLState: 24000 [ODBC Client Interface]Invalid cursor state. If I open connection, handles, and prepared starement in same loop, THEN it gives correct record without 24000 error. I want the advantage of prepared staement. So I do not want to close and open connection and prepare statement every time. Have I missed any step? Where & when I should code the cursor type? Any specific libraries I need to link? Thanks
View Replies !
|
TRACKER
