Updating Values In One Row From Values In Another Row
Oct 13, 2007
Hi
I have a table containing:
ProductCat Product Qty NewQty
TypeA ProductA -10
TypeA ProductB 5
TypeB ProductX 8
TypeB ProductY -5
I need to get rid of negative values in the same ProductCat by adding surplus values to the negative values in the same ProductCat ie. This is what I ant the table to look like.
ProductCat Product Qty NewQty
TypeA ProductA -10 -5 (Add ProductB to ProductA in the same ProductCat to reduce the deficit)
TypeA ProductB 5 0 (Due to the fact that I added 5 to ProductA NewQty must be reduced by 5)
TypeB ProductX 8 3
TypeB ProductY -5 0 (Deficit only has to be reduced to 0 where possible therefor the balance
of 3 for ProductX)
Note the values in Qty can stay as the same. I am interested in the New values.
Could someone please help me with the correct method of doing this?
Thank you.
View 2 Replies
ADVERTISEMENT
May 14, 2008
Hi!
I am trying to retrieve and update data. I am retrieving the data into the respective controls such as textboxes. The codes are follow:
Retrieve data - working
Dim sqlConn As New SqlConnection()
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString()
Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=" & "@ID", sqlConn)
Dim paramID As New SqlParameter("@ID", SqlDbType.Int)
paramID.Direction = ParameterDirection.Input
com.Parameters.Add(paramID)
paramID.Value = e.Value
sqlConn.Open()
Dim reader As SqlDataReader = com.ExecuteReader()
While reader.Read()
StartTimeTextBox.Text = reader("StartTime")
EndTimeTextBox.Text = reader("EndTime")
CompanyNameTextBox.Text = reader("CompanyName")
PurposeTextBox.Text = reader("Purpose")
AccountManagerTextBox.Text = reader("AccountManager")
PresenterTextBox.Text = reader("Presenter")
ColorDDL.SelectedValue = reader("ColorCode")
StatusRadioButtonList.SelectedValue = reader("Status")
CommentTextBox.Text = reader("Comments")
End While
update the data - NOT WORKING
Dim sqlConn As New SqlConnection()
sqlConn.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString()
Dim cmd As SqlCommand = New SqlCommand("UPDATE Demo_Theatre_DB SET StartTime = @StartTime, EndTime = @EndTime, CompanyName = @CompanyName, Purpose = @Purpose, AccountManager = @AccountManager, Presenter = @Presenter, ColorCode = @ColorCode, Status = @Status, Comments = @Comments WHERE ID=@ID")
cmd.Parameters.AddWithValue("StartTime", StartTimeTextBox.Text)
cmd.Parameters.AddWithValue("EndTime", EndTimeTextBox.Text)
cmd.Parameters.AddWithValue("CompanyName", CompanyNameTextBox.Text)
cmd.Parameters.AddWithValue("Purpose", PurposeTextBox.Text)
cmd.Parameters.AddWithValue("AccountManager", AccountManagerTextBox.Text)
cmd.Parameters.AddWithValue("Presenter", PresenterTextBox.Text)
cmd.Parameters.AddWithValue("ColorCode", ColorDDL.SelectedValue)
cmd.Parameters.AddWithValue("Status", StatusRadioButtonList.SelectedValue)
cmd.Parameters.AddWithValue("Comments", CommentTextBox.Text)
Try
sqlConn.Open()
cmd.ExecuteScalar()
Catch ex As Exception
InsertMsgLabel.Text = ex.ToString
End Try
End Sub
I am getting the following error whenever I click update button.
"ExecuteScalar: Connection property has not been initialized"
Your help will be appriciated!
Thanks!
View 24 Replies
View Related
Mar 9, 2004
I have table A which has and accountid,df_date1,df_date2. The table is a demographic one which has 1 record for each account
I have a table B which I need to populate from the first df_date1 fields in table A. Table B which is normalized and has an accountid and a df_date1 field but may have several records per accountid. I need the max(date) from this table. I wanted to do an update statement like below
update A
set df_date1
= max(df_date1) from b
where a.account_id = b.account_id
I get the error message
Server: Msg 157, Level 15, State 1, Line 3
An aggregate may not appear in the set list of an UPDATE statement.
Is there another way to do this with a subselect and update?
View 2 Replies
View Related
Jun 23, 2008
how to use update statement to update fields in a table that has Null values
update sheet1 set [Middle Initial]='' where isnull([Middle Initial],'')=''
this statement some times works sometimes not.
View 7 Replies
View Related
Nov 7, 2007
I have a table named 'Personal' that has a field that contains values that represent colors (ex: BLACK, BROWN, BLUE, etc.). How do I run an update statement to update the values to defined set. For example (BLACK = BLK, BLUE = BLU, etc.)?
Thanks
View 5 Replies
View Related
Nov 16, 2007
Hi
I'm new to SQL and have a problem with the following script:-
INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)
VALUES 2311, 19219, 'BRAN', '01/12/2007', 'Jon')
The above script works OK on a one-off basis but....
Basically, organisation_number_2 (i.e. 19219) always stays the same. However, I need to update organisation_number_1 several times (i.e. 2311 will then change to 2312, 2313, 2314 etc.).
Rather than pasting the script several hundred times and changing the organisation_number_1 value each time, is there a quick way to encompass all the organisation_number_1 values in one go?
I've tried e.g. VALUES (2311,2312), 19219, 'BRAN' etc. but this doesn't seem to work.
Thanks for your help.
Jon
View 6 Replies
View Related
Feb 14, 2008
I have a table like that:
ColumnA ColumnB ColumnC
-------------------------------
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown
John Brown John.Brown
George Gotham George.Gotham
I want to update duplicated values at ColumnC like:
Alice Lukas Alice.Lucas
James Redford James.Redford
James Redford James.Redford1
Michael Jackson Michael.Jackson
John Brown John.Brown
John Brown John.Brown1
John Brown John.Brown2
George Gotham George.Gotham
How can i do it?
Thanks in advance!
Note: Table is for creating email aliases from names...
View 2 Replies
View Related
May 2, 2008
SELECT
#followups.suspectid,
#followups.planid,
cond4,
cond5,
cond6,
cond7,
cond8,
tbpdmmembers.firstname tbpdmmembers_firstname,
dbo.GetMemID_PartC_D(#followups.hic,#followups.IsPart_C) tbpdmmembers_MemberID,
--tbpdmmembers.Plan1 tbpdmmembers_MemberID,
tbaddress.address1 tbaddress_address1,
tbaddress.address2 tbaddress_address2,
tbaddress.city tbaddress_city,
tbaddress.state tbaddress_state,
tbaddress.zip tbaddress_zip
from #followups
I want to check if the tbaddress.address1 tbaddress_address1 if it is =null or empty then make it = tbaddress.address2 tbaddress_address2, at the same time I make the tbaddress.address2 tbaddress_address2, null. Otherwise leave it as it is.
Actually, I am working with the report and the tbaddress.address1 carrying the apt number. if there is no aprt # then the line print blanks. so that brings the empty line between the street address and the citystate line.
That is what i am tryig to cover. There could be a way to do this from the crystal report but I am thinking of doing this way cause I can't get the solution to do it from the crystal report.
View 5 Replies
View Related
May 16, 2008
Hello, I have been SSIS for a bit now, but really only it its most straight forward simple tasks I have the following issue which for the life of me I can't seem to sort out
1)Excel Source - - -> all data pushed into a initial SQL table
2) Data in initial SQL table gets sorted, certain fields get made UPPER , right trim that sort of thing
3) now I select a set of distinct values of a certain column in the SQL table and get a list of maybe 60 results
these results I want to enter into a table in a different database on same server, the table in the destination db has a identity column and a value column
adding / appending all the distinct value from the import data works fine,
but I want to add only the values which are not already in the destination table what is the best way to update the destination table, should I be using stored proc or is there a easier way in SSIS to do this
Thanks
View 1 Replies
View Related
Sep 19, 2007
Hi,
I'm very new to stored procedures and I've been searching on google to find a way to custom page my results using SQL Server 2005. I'm trying to use Row_Number() and a CTE to keep things efficient and scaleable but I keep getting an error next to my UPDATE statement.
I get the following error: "[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'UPDATE'."
The sproc works without the UPDATE statement. Does anyone know where I need to put the UPDATE statement to update the "searched" field for each record selected?
CREATE PROCEDURE [zk_update_request_england](@property_type tinyint,@market_status tinyint,@price int,@bedrooms tinyint,@search_location varchar(30),@search_district varchar(30),@PageSize int,@PageIndex int)
AS
BEGIN
WITH SearchResults AS(
UPDATE dbo.zk_request_england SET searched = searched + 1 WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price
SELECT user_id, min_price, max_price, property_description, searched, ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber FROM dbo.zk_request_england WHERE property_type = @property_type AND market_status = @market_status AND bedrooms = @bedrooms AND search_location = @search_location AND search_district = @search_district AND min_price <= @price AND max_price >= @price
)
SELECT user_id, min_price, max_price, property_description FROM SearchResults WHERE RowNumber BETWEEN (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize
END
Thanks in advance for any help.
View 3 Replies
View Related
Apr 5, 2007
In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.
I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.
E.G.
AffiliateID, TypeID, Revenue
0, 1, £50
22, 1, £55
33, 1, £57
0,2,£60
22, 2, £66
33, 2, £69
To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.
UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0))
FROM facttable FT
LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID
LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID
(In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).
This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.
However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.
Has anyone come across a situation like this (and found a neater solution).
View 2 Replies
View Related
Oct 6, 2006
Im trying to update the values int the database and the query is executed correctly but database is not updated and the returned message is one, I really dont know whats causing this here is my code.1 private void UpdatetheDatabase(string connection)
2 {
3 SqlConnection connect = new SqlConnection(connection);
4 SqlCommand update_customer_details = new SqlCommand("UPDATE [app_CustomerDetails] " +
5 " SET [Name]=@Name,[City]=@City, " +
6 " WHERE [ID]=@ID", connect);
7 update_customer_details.Parameters.Add("@Name", SqlDbType.NVarChar, 50).Value = txtCustomerName.Text;
8 update_customer_details.Parameters.Add("@City", SqlDbType.NVarChar, 50).Value = txtCity.Text;
9 update_customer_details.Parameters.Add("@ID", SqlDbType.Int, 4).Value = ID;
10 connect.Open();
11 int x = update_customer_details.ExecuteNonQuery();
12 connect.Close();
13 StatusLabel.Text = x.ToString();
14 }
your help will be highly appreciated.
View 7 Replies
View Related
Oct 24, 2006
HiI am new to the world of aspx, .net and C#.In aspx .net 2.0. I am trying to work out how to get a datagrid to perform an update. Using Visual Developer I have successfully added the control and specifed a select statement to return data via my SQLData Source. This works fine. However having specifed the control as editable I would like to perform an update through the datagrid and SQLDatasource. I see in the properties for the SQLDatasource object I can specify my update statement.However I do not understand how to get that update statement to have variable values and how newly entered values from the grid can be placed into these variables when the update takes place. Can someone please point me in the right direction? I have not found the MS doc very illuminating thus far and have not found any examples.Many ThanksT
View 1 Replies
View Related
May 26, 2000
I am looking for suggestions on how to accomplish the following task with the least amount of hand keying that I can get away with.
I have a main table named Office and 4 other tables that have the Office table ID field value in them.
I have been given a new set of ID values that need to replace the values that are currently in the Office table and then update the ID field in the other 4 tables.
I have only thought of 2 solution and I don’t like either one.
1 Add a new column to the Office table and key in the new ID’s then go through the pages that reference the old ID field and change the SQL queries to use the new field.
2 Change the value of the current Office ID field to the new value. Search the other 4 tables for the old value and then update them to the new value.
Anyone got a better Idea?
View 1 Replies
View Related
Aug 25, 2004
Hello!
I have a "current" table which users update on a daily basis, it holds forecast data. This table is designed to only hold 7 days of data and is therfore always rolling over by date.
What I want to do is have a procedure that copies this "Current" table to a "History" table every day so the historical information is stored. The copy is simple, but how do I check for and over ride values that are already in the "history" table with the newest values from the "current" table because the "current" values have the possibility of changing a few times while the forecast is updated. I can define a primary key but am not sure how to check for and update the necessary values?
Any help??
Many thanks!
View 1 Replies
View Related
Nov 11, 2005
hi there,
i am new to sql server database.i am doing small projects right
now using asp.net and sql to create webpages (very basic webpages)
My problem is:
Problem :
i have two tables .....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course .
table1 student id is the primary key refrencing table 2 student id.
Now i delete a record in table 1 which will in turn also get deleted in
table 2 . so for eg if i have three records 1 ,2 and 3 ....then i
delete 2 in table 1 ...i will have 1 and 3 in both table 1 and table
2....now i want 3 to become 2 in both table 1 and table 2...so that i
dont have empty space between two student id's 1 and 3. so this is my
problem....if any one can help me out with suggestions please
do.
thank you all........
ahmed_ind
View 4 Replies
View Related
Mar 3, 2006
hi ,
I recently moved from SQL 2000 to SQL 2005.
The client side is vb6 and using Dcom dll's hosted on the db server.
I have a table which has oninsert trigger
When the recordset is updated in the com class it throws the foll error :
"Row cannot be located for updating. Some values may have been changed since it was last read."
The same code was working with SQL 2000 !!!
Any clues.
implicit tran are off on the sql 2005 server.
Thanks in advance.
View 2 Replies
View Related
May 2, 2008
Hi Folks,
I have a task I wrote which does not always update the property value (as seen in the properties pane)
Basically, change something on the form, then update the task host property with:
this.taskHostValue.Properties["Duration"].SetValue(this.taskHostValue, Convert.ToInt32(spnDuration.Value));
Stepping through this, it does exactly what it is supposed to. Having a look at the property value, it confirms it has changed.
Reopening the UI and resetting all the controls returns the expected results.
The package however does not realise it has changed. There is no * next to the package name in the top tabs.
As long as the package thinks it is unchanged, SaveXML does not get called either so the tasks do not persist.
Changing the value on the properties pane works fine though.
The frustrating thing is this is slightly random. Slight in the sense that sometimes it works but most of the time it does not.
The sample code I used was the MS download IncrementTask (Which works BTW) so I can't see it as being a VS / SSIS bug but rather something I am / am not doing. 3 tasks I have written all behave the same. I have to "nudge" them before savign the package.
Any ideas what the problem might be?
TIA
Cheers,
Crispin
View 3 Replies
View Related
Apr 25, 2007
I am having an issue when trying to do something that looks simple on the face of it.. I want to replace the value during update in the same table.
Here's my situation
tst_update
ID int
Col1 varchar(10
I want to ensure that everytime Col1 is updated to "A" I want to set it to "X". Otherwise leave it as is.
Can someone help.
Thanks,
Ashish
View 6 Replies
View Related
Oct 24, 2006
I am attempting to update a sql db using the update and parameter code in VB.net 2003 through MSDE for a web application. It updates changed data OK, but if the textbox value is deleted, the code does not update the sql db. I am new to this, and I'm sure it is something simple. Here is some sample code.
SqlConnection1.Open()
strSQLu = "UPDATE table1 " _
& "SET Field1Tag = @Field1Tag, Field2Tag = @Field2Tag " _
& "WHERE (Field1Tag = @Field1Tag) "
cmdCategoriesUpdate.CommandText = strSQLu
With cmdCategoriesUpdate
.Parameters("@Field1Tag").Value = txtFld1.Text
.Parameters("@Field2Tag").Value = txtFld2.Text
End With
cmdCategoriesUpdate.ExecuteNonQuery()
SqlConnection1.Close()
View 3 Replies
View Related
Jun 16, 2015
Here is a requirement. Need to update the columns in the tables with the latest values available in CSV.
The file is based on department so the list of tables which is under this department all the corresponding tables needs to updated.
The CSV file which is dynamic in nature the number of columns changes it has header of the columns that needs to be updated.
The destination tables are listed under department table for each department. So I have to update the columns in the tables with the values in csv.
View 4 Replies
View Related
Apr 16, 2014
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
View 2 Replies
View Related
Jun 4, 2007
Hello all,
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Any suggestions?
thanks for your time...
View 5 Replies
View Related
Feb 23, 2007
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies
View Related
Nov 13, 2015
I am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies
View Related
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Apr 11, 2008
I have my stored procedure set to
Territory_code IN (@Territory)
, now , how do i enter in more then one value. When i select the multi value check box, it gives me more spaces. But then doesnt recognize the values when i put in more then one. am i doing something wrong?
The field is a Varchar 20
View 1 Replies
View Related
Jan 23, 2008
Hi All,
I receive the input file with some 100 columns and some 20k+ rows and I want to check the incoming input row is existed in the database or not based on 2 key columns. If the row is existed then I need to check all the columns (nearly 100 columns) values in input and the database are equal or not. If both are equal I need to treat them seperately if not there is a seperate logic. How Can I do that check for each row and for each column?
Basically the algorithm is like this, if the input file row is not existed in the database then treat that as new row else if the input row is existed in the database then check all the columns are equal or not. If all the columns are equal then treat that as existing row and do nothing else if some columns are not equal then treat this row seperately.
I found some thing to achieve the above thing.
1. Take the input row and check in the database.
2. If the row is not found in the database then treat it as new row.
3. If row is found in the database then
a) Take the source row and prepare a concatenated string for all the columns
b) Take the database row and prepare a concatenated string for all the columns
c) Find out the hash code for the 2 strings and then compare hash codes for equal.
The disadvantage of this is running a loop 2*m*n times where m is the number of rows and n is the number of columns. It should be done 2 times for input file row and database row.
Can anybody suggest a good method to do this?
What does the function "GetHashCode" for InputBuffer in method "Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)" will do?
Will it generates hash code based on all the columns values?
Pls clarify.
Regards
Venkat.
View 1 Replies
View Related
May 6, 2015
I have a situation in SSRS to get the common values between the two columns where the values are sorted comma separated as below.Ex:
ColumnA :  abc,cde,efg  Â
ColumnB : cde,xyz,abc  Â
the result in  Â
ColumnC : cde,abc
similarly Column A and B will have n number records. I need to right an expression or the Code function to get the required result in ColumnC. I am using SharePoint Lists as Datasource. Cannot write SQL query to achieve this requirement.
View 5 Replies
View Related
Jun 17, 2012
I am SSRS user, We have a .net UI from where we want to pass multi select values, but these values are comma separated in the database. how can I write a sql query such that when I select multi values on my UI, the comma separated values are take care of.
View 5 Replies
View Related
Jan 13, 2015
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
View 8 Replies
View Related
Feb 27, 2014
I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the
1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:
where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)
2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.
3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)
If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.
Again, the series are usually discontinuous and specific to each specialty.
Here's the portion of the script (it's at the end) that I'm talking about:
--change values in square brackets below for each specialty as needed and change the denom number in the very last query.
if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T
[Code] ....
If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.
View 6 Replies
View Related
Mar 19, 2014
I have a table that lists math Calculations with "User Friendly Names" that look like the following:
([Sales Units]*[AUR])
([Comp Sales Units]*[Comp AUR])
I need to replace all the "User Friendly Names" with "System Names" in the calculations, i.e., I need "Sales Units" to be replaced with "cSalesUnits", "AUR" replaced with "cAUR", "Comp Sales Units" with "cCompSalesUnits", and "Comp AUR" with "cCompAUR". (It isn't always as easy as removing spaces and added 'c' to the beginning of the string...)
The new formulas need to look like the following:
([cSalesUnits]*[cAUR])
([cCompSalesUnits]*[cCompAUR])
I have created a CTE of all the "Look-up" values, and have tried all kinds of joins, and other functions to achieve this, but so far nothing has quite worked.
How can I accomplish this?
Here is some SQL for set up. There are over 500 formulas that need updating with over 400 different "look up" possibilities, so hard coding something isn't really an option.
DECLARE @Synonyms TABLE
(
UserFriendlyName VARCHAR(128)
, SystemNames VARCHAR(128)
)
INSERT INTO @Synonyms
( UserFriendlyName, SystemNames )
[Code] .....
View 3 Replies
View Related
