I have the old age question of how to process a string parameter that is passed to a Stored Procedure that has an unknown number values. The example below has 5 values but it could be anywhere between 1 and 20.
I basically need to extract each value to Insert these values into the appropriate tables.
In the SQL 2000 days I use to do this with some T-SQL code that determines where the comma is and then I get the value and so on.....
I have read somewherethat this can be achieved using the XML Data Type.
Can someone show me that or atleast get me started on how to achiev this?
With this algorithm you can sum up an unkown number of records, so that an aggregation matches a fixed value. If there is not an exakt match available, the algorithm returns the nearest possible value!-- Initialize the search parameter DECLARE@WantedValue INT
SET@WantedValue = 349
-- Stage the source data DECLARE@Data TABLE ( RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, MaxItems INT, CurrentItems INT DEFAULT 0, FaceValue INT, BestUnder INT DEFAULT 0, BestOver INT DEFAULT 1 )
-- Aggregate the source data INSERT@Data ( MaxItems, FaceValue ) SELECTCOUNT(*), Qty FROM( SELECT 899 AS Qty UNION ALL SELECT 100 UNION ALL SELECT 95 UNION ALL SELECT 50 UNION ALL SELECT 55 UNION ALL SELECT 40 UNION ALL SELECT 5 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 250 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 90 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 350 UNION ALL SELECT 450 UNION ALL SELECT 450 UNION ALL SELECT 100 UNION ALL SELECT 100 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 50 UNION ALL SELECT 1 UNION ALL SELECT 10 UNION ALL SELECT 1 ) AS d GROUP BYQty ORDER BYQty DESC
-- Declare some control variables DECLARE@CurrentSum INT, @BestUnder INT, @BestOver INT, @RecID INT
-- If productsum is less than or equal to the wanted sum, select all items! IF (SELECT SUM(MaxItems * FaceValue) FROM @Data) <= @WantedValue BEGIN SELECTMaxItems AS Items, FaceValue FROM@Data
RETURN END
-- Delete all unworkable FaceValues DELETE FROM@Data WHEREFaceValue > (SELECT MIN(FaceValue) FROM @Data WHERE FaceValue >= @WantedValue)
-- Update MaxItems to a proper value UPDATE@Data SETMaxItems =CASE WHEN 1 + (@WantedValue - 1) / FaceValue < MaxItems THEN 1 + (@WantedValue - 1) / FaceValue ELSE MaxItems END
-- Update BestOver to a proper value UPDATE@Data SETBestOver = MaxItems
-- Initialize the control mechanism SELECT@RecID = MIN(RecID), @BestUnder = 0, @BestOver = SUM(BestOver * FaceValue) FROM@Data
-- Do the loop! WHILE @RecID IS NOT NULL BEGIN -- Reset all "bits" not incremented UPDATE@Data SETCurrentItems = 0 WHERERecID < @RecID
-- Increment the current "bit" UPDATE@Data SETCurrentItems = CurrentItems + 1 WHERERecID = @RecID
-- Get the current sum SELECT@CurrentSum = SUM(CurrentItems * FaceValue) FROM@Data WHERECurrentItems > 0
-- Stop here if the current sum is equal to the sum we want IF @CurrentSum = @WantedValue BREAK ELSE -- Update the current BestUnder if previous BestUnder is less IF @CurrentSum > @BestUnder AND @CurrentSum < @WantedValue BEGIN UPDATE@Data SETBestUnder = CurrentItems
SET@BestUnder = @CurrentSum END ELSE -- Update the current BestOver if previous BestOver is more IF @CurrentSum > @WantedValue AND @CurrentSum < @BestOver BEGIN UPDATE@Data SETBestOver = CurrentItems
SET@BestOver = @CurrentSum END
-- Find the next proper "bit" to increment SELECT@RecID = MIN(RecID) FROM@Data WHERECurrentItems < MaxItems END
-- Now we have to investigate which type of sum to return IF @RecID IS NULL IF @WantedValue - @BestUnder < @BestOver - @WantedValue -- If BestUnder is closer to the sum we want, choose that SELECTBestUnder AS Items, FaceValue FROM@Data WHEREBestUnder > 0 ELSE -- If BestOver is closer to the sum we want, choose that SELECTBestOver AS Items, FaceValue FROM@Data WHEREBestOver > 0 ELSE -- We have an exact match SELECTCurrentItems AS Items, FaceValue FROM@Data WHERECurrentItems > 0With references to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73540 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73610 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78015 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79505
Keshka writes "I'm using function fnParseString form http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033 in some of my sp.
it's very helpfull, but my question is if there is a way to split variable into columns if I don't know how many columns I'll have? It could be 1 or 2 or 3 and etc.
I need to write a SSIS package. The source are in a Windows folder where there are a variable number of text files. These text files may vary in number everyday, How can i write a package for variable number of sources.
I've got a database with an unknown number of columns. Hence, the column names are also unknown. What's the easiest SQL to present the values in each column and the column headings?
Name Description Date Question Answer Customer A Profile Assessment 01/01/2015
How complex is the structure?
Customer A Profile Assessment 01/01/2015 The total value of assets? Less than GBP 1 million
Customer A Profile Assessment 01/01/2015 The volume of transactions undertaken? Low (-1 pmth)
[Code] ....
However, I would like it to output;
Name Description Date How complex is the structure? The total value of assets? The volume of transactions undertaken? How was the client introduced? Where does the Customer reside?
[Code] ....
The number of questions are unknown for each RiskReviewID and they can be added to in the future.
We are using a table that may give 1 to and unknown number of data elements (ie. years) .  How can we break this to show only three years in each row.  Since we don't know the number years we really won't know the number of rows needed.  Years are stored in their own table by line.   car make year1 year2 year3 A  volare 1995 1996 1997 a  volare 1997  1998  1999 b toyat  1965   1966  1968
We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.
I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.
I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?
Below is the DDL to create the table and populate it.
USE [DBA_UTIL] CREATE TABLE [dbo].[PARAMEXAMPLE]( [Entity] [varchar](16) NULL,
I'm using MS Report Designer 2005 and have created a report that uses a cube, with a dimension set up to convert null values to unknown (nullProcessing = UnknownMember).
When I create a parameter using the checkbox in the graphical design mode's filter pane, Report Designer automatically sets the constrained flag, eg:
When running the report and selecting the 'Unkown' value from the parameter list, the error 'the restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated' occurrs.
How can I prevent the constrained flag from being used, or am I doing something wrong with converting null values to 'Unknown'?
This is what I want to do:1. Delete all tables in database with table names that ends with anumber.2. Leave all other tables in tact.3. Table names are unknown.4. Numbers attached to table names are unknown.5. Unknown number of tables in database.For example:(Tables in database)AccountAccount1Account2BinderBinder1Binder2Binder3.......I want to delete all the tables in the database with the exceptionof Account and Binder.I know that there are no wildcards in the "Drop Table tablename"syntax. Does anyone have any suggestions on how to write this sqlstatement?Note: I am executing this statement in MS Access with the"DoCmd.RunSQL sql_statement" command.Thanks for any help!
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:
I get this with this query which might be altered somehow:
SELECT COUNT(*) AS Anzahl, users_statdata_hobbies
FROM vgetAuswertung2
GROUP BY users_statdata_hobbies
ORDER BY users_statdata_hobbies
Of course this is not normalized but I can't change this.
Nevertheless I need to get the full number of each Hobby and not only the combination of them.
So instead or in addition to the existing recordset I need e.g
357 Essen which ist the sum of all records containing 'Essen' in the above example
The list of individual hobbies is defined therefor I could loop through the list manually and search for 'WHERE Hobbies LIKE '%ESSEN%' and count but since it's quiet a big resultset and there are several other similar tasks already I'm looking for a more performant way and I'm sure it could be done in SQL directly.
Our application has a table, which is populated via ADO.Net from C# with data originating from a C++ COM call. Today I encountered an entry that is C++ code for an undefined value: -1.#IND stored in the database. However, I could only discover what was stored in the table by Casting the value to a varchar -- simply selecting returned an error.
Is this expected behavior or a bug? It does not seem correct that SQL Server should store a value that cannot be displayed. In essence, either the value should not be allowed in the table because it violated the domain or SQL Server ought to have a way to display it with a Select *.
As fas as our application is concerned, we will be masking these values -- initially by ignoring them in the queries and eventually the loading program will convert to null.
I have a strongly typed dataset, and I need to be able to do a search on multiple values of a parameter. The problem is I don't know how many. I have a textbox that the user can enter search words in. The select string is built from the string of words that are entered, like this:For iCount = 0 To UBound(sArray) strSQL = strSQL & "Description LIKE '%" & sArray(iCount) & "%' OR "Next Can I do this is a dataset method? How? If I can't, what are my options?Diane
here how to renumbering stickyId values when deleted particular stickyId from UI. Here stickyId field is Integer type only. not primarykey/identity field.
I have a requirement regarding a color combination data. I have a lookup table that holds a colorid, p1, p2, p3, p4 to p8 which will be having colors Red, Green and Amber. P1 to P8 columns holds these three colors based on their combinations.
I have attached the look up table data for reference.
I need to pass the color values to p1 to p8 and need to retrieve the color id based on the passed color.
If we pass values for all p1 to p8 then it is easy to get the color code, however it will not happen. The passed values may be dynamic. ie we will not have all 8 values all the times. sometimes we will have 2 colors passed, sometimes 5 colors will be passed.
If i pass only two colors say red and red, i need the color id of only the row that has red and red for p1 and p2 alone. i dont want want all the colorid's that has red and red in p1 and p2 and some other colors in p3 to p4.
The exact colorid of the combination must be returned on passing the values to p1 and p2.
To elaborate; I am passing Red and Red as values to P1 and P2. In the look up table we can have 10 rows that has red and red i p1 and p2 like
So the result must have only the colorid 1 and not all the colorid's listed above. when I pass 3 red as values for p1, p2, p3 then the result must be 10. Colorid 1, 20, 30, 40, 50, 60 and 70 must not come in the result.
I need a function or procedure that will accept the arguments and provide me the result based on the values.
with Sample(Size) as ( select '16.3 Oz.' union all '1' as union all '2 Tablespoons' union all '46. Oz. Each' )
Iwant to separate number and alphabets. But i wanted to keep the dot to have decimal values. Expected output
select '16.3' as val1 'Oz' as val2 union all '1' val1 as union all '2' as val1 'Tablespoons' as val2 union all '46' as val1 'Oz Each' as val2
Please note that i need to remove the extra dots at the end of the the val1 and no dots in val2
I rid some of the functions like dbo.fn_StripCharacters and dbo.fn_GetAlphabetsOnly ffound in the internet. evey with my own logic. i couldn't remove the dot which appear at the end of val1
HiI'm migrating from Access til MySQL.Works fine so far - but one thing is nearly killing me:I got the count of total records in a variabel - (antalRecords)I got the count for the Field Q1 where the value value is = 'nej'Now I just need to calculate how many % of my records have the value 'nej'I access this worked very fine - but with MySQL ( and ASP) I just cant getit right!!! I go crazy ....My code looks like this :strSQL="SELECT COUNT(Q1) AS Q1_nej FROM Tbl_evaluering " &_"WHERE Q1 = 'NEJ' "set RS = connection.Execute(strSQL)antal_nej = RS("Q1_nej")procent_nej = formatNumber((antal_nej),2)/antalrecords * 100Hope ...praying for help ...Please ;-)best wishes -Otto - Copenhagen
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[logMsg]( [logMsgID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [msg] [nvarchar](256) COLLATE Latin1_General_CI_AS NOT NULL, [AppId] [int] NULL, CONSTRAINT [PK_logMsg] PRIMARY KEY CLUSTERED ( [logMsgID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
and trying to insert values with
INSERT INTO [ProxyDB].[dbo].[logMsg] ([msg] ,[AppId]) VALUES ('Text Test',1)
Getting error message:
Msg 213, Level 16, State 1, Procedure TrgInslogMsg, Line 14 Insert Error: Column name or number of supplied values does not match table definition.
How do I count the number of positive dollar values in a query? I have various negative and positive dollar values and want to count how many positive I have then I will know how many are negative. I cannot put >0 or >0.00 in the where because it still returns everything cuz the programmer set the datatype to be money
As the Microsoft Time Series algorithm implementation is based upon the Autoregressive Tree approach described in:
C. Meek, D. M. Chickering, D. Heckerman. Autoregressive Tree Models for Time-Series Analysis. In Proc. 2nd Intl. SIAM Conf. on Data Mining, 2002 (SDM-02). SIAM, pp. 229 €“ 244. http://www.siam.org/meetings/sdm02/proceedings/sdm02-14.pdf.
The model estimated is refererred to as an instance of "... autoregressive tree models of length p, denoted ART(p). An ART(p) model is an ART model in which each leaf node of the decision tree contains an AR(p) model, and the split variables for the decision tree are chosen from among the previous p variables in the time series..." (see the last paragraph of p. 2 of the paper).
What is the value of "p" used in the Microsoft Time Series implementation -- specifically, how many previous time series variables are used in estimating the model? It doesn't appear that this value can be specified in the algorithm parameters -- is that correct?
Ok I have upgraded my works database from a poorly designed Access database to a SQL database. The previous system allowed NULL values and duplicates to be inserted into a field that should NOT ALLOW NULL Values or duplicates. Therefore, this issue has now been moved across to my new system as I cannot set these constraints on the field that has multiple NULL values.
My solution would be to use a sequential operator, so whatever = NULL would be changed to a sequential number that us as administrators would know was a bogus number starting at something like = 999999900 counting up from that. There are only 250 records that would require updating.
To make things more interesting this field is not a integer type, its a Nvarchar type as its a Hardware ID. Both numerical and characters are require.
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1) Days_in_warehouse 20 30 40
From what I've seen, the CheckSum_Agg function appears to returns 0 for even number of repeated values. If so, then what is the practical use of this function for implementing an aggregate checksum across a set of values?
For example, the following work as expected; it returns a non-zero checksum across (1) value or across (2) unequal values.
declare @t table ( ID int ); insert into @t ( ID ) values (-7077); select checksum_agg( ID ) from @t; ----------- -7077 declare @t table ( ID int ); insert into @t ( ID ) values (-7077), (-8112); select checksum_agg( ID ) from @t; ----------- 1035
However, the function appears to returns 0 for an even number of repeated values.
declare @t table ( ID int ); insert into @t ( ID ) values (-7077), (-7077); select checksum_agg( ID ) from @t; ----------- 0
It's not specific to -7077, for example:
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (-997777); select checksum_agg( ID ) from @t; ----------- 0
What's curious is that (3) repeated equal values will return a checksum > 0.
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (-997777), (-997777); select checksum_agg( ID ) from @t; ----------- -997777
But a set of (4) repeated equal values will return 0 again.
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (-997777), (-997777), (-997777); select checksum_agg( ID ) from @t; ----------- 0
Finally, a set of (2) uneuqal values repeated twice will return 0 again.
declare @t table ( ID int ); insert into @t ( ID ) values (-997777), (8112), (-997777), (8112); select checksum_agg( ID ) from @t; ----------- 0
Below is my sample data. I can't figure out how to select Unique phonenumber contacts for the same Ranked values from the set.
Basically the table is a mix of contactIDs. Some of them have duplicate phone numbers and through a separate mechanism we have ranked them.
It's easier then to pull out max(ranked) CLI_Numbers and their counterpart contactID(s). But I am also getting 2 or more records where the rank happens to be the same. I don't want that. Any one of the contactID will do for me.
The table has also same cliNumbers with different rank values, which are then correctly being picked up in the query below.
Note: ContactId is a unique value for each person in the table. RecordID is simply RowID.
( I have attempted to populate a sample data suited for this forum - not sure how it comes out on the browser)
if object_id('tempdb..#MyData') is not null drop table #MyData create table #MyData ( RecordID int, contactID int, forename varchar(25), surname varchar(25),
[Code] ....
This is my query attempt
With RankedmobileDuplicateSet as( select cliNumber, max(ranked_value) as ranked_max_value from #temp_UK_mobiledata group by cliNumber)
I have such a problem. Need to add additional column to my query. The column should consist of set of fixed number (same as number of query rows) values (text). At start thought it's simple but now Im lost. Is there any chance to do it. Apreciate any help. I need to tell that I have only access to select on this database so no use of operation on tables.
I am trying to write a report that includes different lab values for an account number depending on the test. What I mean is if patient xyz had lab work and procedure number 1012 was ordered I need to include one line for the highest result value and one for the lowest result value. If I have procedure number 1032 I only need a line for the lowest value. I have a list of about 40 lab procedures that some require both highest and lowest, some just the lowest and some the highest. I have played around with CASE, but that hasn't worked for me.
I have a table names Alert_Event and a new column named BSP_Phone has been added to the table. I am trying to set NULL values to the column and I get the below error. I am setting null values in the bolded text in the query.
Error Message:
Msg 213, Level 16, State 1, Procedure SaveBSPOutageInfo, Line 22 Column name or number of supplied values does not match table definition.USE [gg] GO
/****** Object: StoredProcedure [dbo].[SaveBSPOutageInfo] Script Date: 10/17/2013 19:01:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SaveBSPOutageInfo] @eventCreatedDate DATETIME, @eventOrigin varchar(10),
How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.
Here's the example I want to sum: 00:02:01:30 00:01:28:10 00:01:01:50 00:06:50:30 00:00:01:50
Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:
cmd.Add(new SqlCeParameter("@IntColumn", SqlInt32.Null)); cmd.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
cmd.CommandText = "select count(*) from TableName where not IntColumn = @IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@IntColumn", SqlDbType.Int32)); cmd.Parameters["@IntColumn"].Value = SqlInt32.Null; cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @IntColumn";
