Selecting Alphanumeric Combinations That Do Not Exist Already Intable?????
Mar 24, 2008
Hello all, I have an odd requirement. I have a column with a system
generated username that is a 6 character, alphanumeric, field. These
usernames are randomly generated by code. I need to create a stored
procedure that will return all combinations that are not already
being
used. Maybe the result of still trying to wake-up from a long
weekend.
But, I cannot think of an easy way to do this.
Any help would be greatly appreciated.
Best regards,
rbr
View 4 Replies
ADVERTISEMENT
Jul 31, 2004
I want to start with a table that has 4 records:
-Self
-Supervisor
-Peer
-Direct Rep
And I want to end with a table that has every unique combination of these records (the order being reversed would be considered 'unique' in this context)
-Self , Supervisor
-Supervisor , Self
-Self , Peer
-Peer , Self
-Self , Direct Rep
-Direct Rep , Self
-Peer , Direct Rep
-Direct Rep, Peer
How would I do this in an SQL Query? Thanks for your help!
View 1 Replies
View Related
Sep 20, 2007
I've got a big problem that I'm trying to figure out:
I have an address table out-of-which I am trying to select mailing addresses for companies UNLESS a mailing address doesn't exist; then I want to select the physical addresses for that company. If I get multiple mailing or physical addresses returned I only want the most recently edited out of those.
I don't need this for an individual ID select, I need it applied to every record from the table.
My address table has some columns that look like:
[AddressID] [int]
[LocationID] [int]
[Type] [nvarchar](10)
[Address] [varchar](50)
[City] [varchar](50)
[State] [char](2)
[Zip] [varchar](5)
[AddDate] [datetime]
[EditDate] [datetime]
AddressID is a primary-key non-null column to the address table and the LocationID is a foreign key value from a seperate Companies table.
So there will be multiple addresses to one LocationID, but each address will have it's own AddressID.
How can I do this efficiently with perfomance in mind???
Thank you in advance for any and all replies...
View 2 Replies
View Related
Mar 4, 2006
Hi,
I did some tsql a year or so ago, it wasnt much but it allows me to do most things i need to do in my job.
I have created a new database for bookings, each booking has all the relevant details and also the date that it will occur. im trying to create a script that will look for the 10 soonest dates that there is no booking for. at first i thought it would be easy, but now once i have got round to creating it i cant seem to think of a way to make it work!
TABLE: BOOKINGS
FIELD: DATE_BOOKED
has anybody advice on how i can do this?
many thanks,
Ben
View 6 Replies
View Related
Apr 3, 2015
Suppose I have string like
@strname varchar= = '3 April 15 abcd Oh rrrrrrrAAAAdd HJHJG'
and table contains two columns having rows like,
ID text
1 abcd ER
2 abcd AS
3 abcd Oh
4 xyz TR
5 azs WS
6 abcd O
7 OP trx
how can I search a ID's which are exist in my string.
result should be,
3 abcd Oh
6 abcd O
View 4 Replies
View Related
Jan 14, 2008
Hi,
I have a table with 2 columns (S.No,Name) with rows
1,Raja
2,Ramu
3,Rane
I need to generate all 2 pair combinations of the names(i,e)
(Raja -Ramu)
(Raja -Rane)
(Ramu -Rane)
Can some one give me a generalized query for this problem?
Thanks,
Prakash.P
View 4 Replies
View Related
Oct 12, 2007
I was using the code in this thread (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1371094&SiteID=1) to create a console application which can build the SSIS package dynamically and run the package.
I'm not clear on what all could be the possible key / value combinations for the SetComponentProperty Method. From the examples I have seen its either SqlCommand or OpenRowSet. But I'm not sure about the "AccessMode" with values either 0 or 2. Is there any reference where I get more information on these?
Code Block
// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 2);
srcDesignTime.SetComponentProperty("SqlCommand", "Select * from devdb..empl_karun");
Thanks
View 5 Replies
View Related
Jul 9, 2006
The requirement in to write a query which will return top 2 rows (in terms of lst_updt_timestamp column) for every combinations of cust_alias_nm, carrier_cd, acct_nbr columns.
Here I wrote a query which selects top 1 only.
Please help me to write to get the top 2nd along with the top 1st row.
select A.cust_alias_nm_id,
A.carrier_cd_id, A.acct_nbr_id,
sum(A.pd_clm_amt) clm_amt,
sum(A.pd_med_amt) med_amt,
sum(A.pd_exp_amt) exp_amt,
A.lst_updt_timestamp
from bal_load_stg A
group by A.cust_alias_nm_id, A.carrier_cd_id, A.acct_nbr_id, A.lst_updt_timestamp
having A.lst_updt_timestamp
in (
(select max(lst_updt_timestamp) from bal_load_stg B
where
A.cust_alias_nm_id = B.cust_alias_nm_id
and A.carrier_cd_id = B.carrier_cd_id
and A.acct_nbr_id = B.acct_nbr_id))
The output looks like:
cust_alias_nm_id carrier_cd_id acct_nbr_id clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ------------------------------------------------------
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
The desired output should look like:
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
All rows present in the table are
cust_alias_nm carrier_cd acct_nbr clm_amt med_amt exp_amt lst_updt_timestamp
---------------- ------------- -------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
aaa 100 1234567890 700.00 700.00 700.00 2005-10-31 00:00:00.000
aaa 100 1234567890 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 102 1234567891 500.00 500.00 500.00 2005-08-31 00:00:00.000
aaa 101 1234567891 500.00 500.00 500.00 2005-07-31 00:00:00.000
aaa 100 1234567890 400.00 400.00 400.00 2005-05-31 00:00:00.000
aaa 101 1234567891 400.00 400.00 400.00 2005-05-31 00:00:00.000
Thanks in advance.
View 3 Replies
View Related
May 7, 2008
I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"
**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)
Thanks in advance
I.Shaame
View 9 Replies
View Related
Sep 6, 2013
How would I write a query to produce all combinations for a situation such as the following?
Suppose I wanted to write a sentence, "This is [adjective] [noun]." where [adjective] comes from the Adjective table and [noun] come from the Noun table.
Adjective table looks like e.g.
ID Adj
1 good
2 so so
3 bad
Noun table looks like e.g.
ID Noun
1 apple
2 orange
3 banana
And the result set would look like
This is good apple.
This is so so apple.
This is bad apple.
This is good orange.
This is so so orange.
This is bad orange.
This is good banana.
This is so so banana.
This is bad banana.
I would take a stab at this myself and post even something that doesn't work...
View 1 Replies
View Related
Oct 26, 2007
Let's say I have a table MyTable with two colums, One and Two. If I wanted to return unique combinations of these two fields I can do
select distict One, Two from MyTable
How do I return the count of such unique combinations? The following of course does not work but you get the idea.
select count(distinct One, Two) from MyTable
Thanks.
View 6 Replies
View Related
Mar 29, 2013
how to find all possible combinations of values, for example:
My table includes:
Code:
CREATE TABLE temp1 (item varchar(50), ORDER int);
INSERT INTO temp1 (item, order) VALUES ('apple',1);
INSERT INTO temp1 (item, order) VALUES ('pear',2);
INSERT INTO temp1 (item, order) VALUES ('blueberry',3);
I need the output to be like this:
apple
pear
blueberry
apple, pear
apple, blueberry
pear, blueberry
apple, pear, blueberry
I don't need the reverse of each. For example, I need only 'apple, pear'... I don't need 'pear, apple'.
View 5 Replies
View Related
Jul 23, 2005
We need to store land title information about properties in variousAustralian states, but each state maintains it's own land titleregistry and use different columns (well actually differentcombinations of the same columns). For example:Victoria store:TorrensUnitTorrensVolumeTorrensFolioQueensland store:TorrensCountyTorrensLotTorrensPlanTorrensParishTorrensUnitTorrensVolumeTorrensTitleRefThere are 11 different columns and they are used in 8 differentcombinations depending on the state.Since we need to store information about land in different states I seetwo possible solutions:1. A sparse table containing the 11 columns with a CHECK constraint toenforce the valid combinations.2. A table for each state containing only the columns relevant to thestate with a foreign key relationship to the table containing thecommon columns.I'm not sure if the data type and length is consistent between statesyet (waiting to find this out) but assuming that it is which of theseapproaches is going to be the most rigorous? I'm leaning towards (2)but I don't like the feel of a table per state.
View 4 Replies
View Related
Jan 11, 2008
I need a function or a routine in sql for making all possible combinations of strings with given letters.
means if I give a string 'ab' the function should return
'a,b,ab,ba' if we give 'abc' it should return 'a,b,c,ab,ac,ba,bc,ca,cb,abc,acb,bac,bca,cab,cba'.
return data can be a single list or the list elements can be printed to screen one by one.
I need to use this routine for inserting bulk sample values for a few tables.
Also have limited time to make one by myself.
Please help.
View 4 Replies
View Related
Aug 27, 2015
Below query:
Declare @table table
(
consumerID varchar(10),
customerNumber varchar(10)
)
Insert into @table
Select 1,123
union all
[Code] ...
--Expected output concatenation of consumer numbers all permutation and combinations having same
customernumber
1 2
1 3
1 4
2 3
2 4
3 4
5 6
5 7
6 7
View 4 Replies
View Related
Jul 2, 2007
I need to populate a table which have 10 columns with four values. Each row should be a different combination of these four values and the columns can be null too. In other words how can I get all the different combinations for the 4 values that can be in 10 buckets. The final result column based on these values will be generated manually.
For example , I have for grades (P,F, WP, WF) and I have 8 terms and two exams. 8 terms and two exams can have any of the above four values. Based on these grades and terms and exams I need to generate a table which wil be used to determine the student final status Pass/Fail.
What will be the best way to do this and how is it possible. Is there a T-SQL or C# program for this.
If I need to submit this in another forum please let me know.
View 3 Replies
View Related
May 31, 2006
I have developed an ASP.NET form with 12 different fields that will allow end users the cability to query 3 tables that are relational to one another.
I was curious what is the best way to perform this in a Stored Procedure?
Can I use a UNION -- Not sure if this is the best choice
or account for evey kind of WHERE clause based off IF statements on the data that is passed through the parameters?
View 3 Replies
View Related
Dec 3, 2013
I have data as below: select ID,code,go,back from tableA
ID code go back
2 C US UK
2 A UK US
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
I only want to pull out whole ID group which code Z exist. I tried as below:
select ID,code,go,back from tableA A
where exists
( select ID,go,back
from tableA B with (nolock)
where A.ID = B.ID
and A.code='Z')
But it only appear:
ID code go back
1 Z UK US
How can I appear with the group as well?
ID code go back
1 A US UK
1 Z UK US
1 C UK US
1 A US UK
View 3 Replies
View Related
Jun 15, 2005
Is it possible to have an auto increment alphanumeric primary key eg A1, A2, A3
Thanks
Paul.
View 1 Replies
View Related
Apr 9, 2008
I have set a column called "Anumber", I am
using a computed value to get the PK ID # and
a the letter "A" to it.
('A'+CONVERT([varchar](10),[requestid],(0)))
The above works fine unless I copy a record,
The copied record never displays the correct
value like "A55" it keeps "NULL" as it value.
Is their a better way to achieve a result as
described above ?
I am coming from Foxpro to SQL, Any advice would
be great.
Thanks
View 6 Replies
View Related
Apr 10, 2008
Hi experts,
I am executing a script to add a column to few tables and drop existing constraints, create new foreign key constraints, indexes etc. After we test this on our development database we have to deliver the script to the customer.
My concern here is, when I have to drop the constraint with the name, I am not able to do it because its been created with alphanumeric char in the constraint name. So I end up getting the constraint name for each table. And moreover,
I wont be able to deliver the same script to the customer as the alphanumeric in the constraint names on the customer's database will be different.
Any suggestions to overcome these alphanumeric in the constraint names?
Thanks much
View 20 Replies
View Related
Jan 10, 2008
Greetings all,
I have upsized 2 foxpro table to SQL Express.
In table 2 called Orphan I have/had a field that
would autonumber with the letter "U" first.
Example would create a AlphaNumber Like "U00001223"
In Foxpro this is a easy function, But I have been
unable to find a similiar way to do this in SQLEXPRESS.
Foxpro would have you define the field as Unique Number with
template defintion of "U"N8
In SQL I know you can use Numeric on a field for a unique number
but cant find setting to proced number with a "U"
Is this doable is in SQL and does anybody have a
reference point or example they could share ?
Any help would be great and hope my question made sense..
View 8 Replies
View Related
Dec 20, 2007
All,
I'm having trouble with a query where I need to limit a resultset by comparing (using a WHERE clause) a field that is alphanumeric with one that is numeric. I've tried converting, casting, and case statements, but I either get an overflow error or a big slap on the wrist by SQL Server 2005. Does anyone have any good solutions to this? I've been racking my brain for a while now.
Thanks for the help!
-Scott Mescall
View 3 Replies
View Related
Feb 3, 2007
Hello,
I have a SQL database with about 300 company names and corresponding phone numbers. I would like to show a list of linkbuttons titled A-Z and when pressed, rebind the sqldatasource so that my GridView will only show company names that start with that letter.
I know there are some examples on codeproject.com, but they are a bit over my head... besides, I don't mind writing a custom select statement for the OnClick of every linkbutton if that's what I have to do. Problem is I haven't a clue how to write a select statement that will return items who's first letter matches my desired letter?
Any idea?
Thanks,
-Derek
View 3 Replies
View Related
Oct 26, 2004
Hi there,The age old question of creating a unique alphanumeric value automatically like ABC0001, ABC0002 Is it possible to do this automatically? That is, without having to update it which will slow the db down horribly?
View 4 Replies
View Related
Nov 27, 2014
I'm using SQL 2008 with table [AgentDetails] and fields [IDCode],[FirstName],[LastName],..etc. [IDCode] is alphanumeric [AAA001].
Is it possible to increment both alpha & numeric when new record is inserted. e.g.
AAA001,AAA002......AAA999,AAB000,AAB001,...AAB999,AAC000,AAC001...etc. with a user function or some stored procedure.
View 2 Replies
View Related
Mar 26, 2015
I have a column called firstname ..in that it stores value like this
john smith
andrew jr
jim sr
andrew bar
tina *^
don $%
I need to retrieve all those rows where name consists of non alphabets...for example 5 and 6 has non alphabets..
I am using PATINDEX('%[^a-z]%',Firstname) function but if it finds space between names it is considering as error..I would like to find only non alphabets in name ..space is fine..is there any function to find out?
View 2 Replies
View Related
Nov 24, 2005
Hello all,
I have a branch a data, now i need to search through a database to check whther it is exist in that database or not, any syggestion?
Example:
Now i have data 123, 234, 345. Let say data 123 and 234 is exist in that database, but data 345 is not exist in that database.
What SQL am i suitable to use to get those result exist(123, 234) and those result not exist (345)?
The database structure is someting like this:
ID NAME O_NAME
-- ----- --------
1 120 123
2 234 234
3 345 345
1) The data consider 'exist' if it exist in column NAME or column O_NAME (either one).
2) The data consider 'not exist' if it not exist in neither NAME column nor O_NAME column
After the SQL query, someting is expected:
Exist:
ID NAME O_NAME
-- ----- --------
1 120 123
2 234 234
Not Exist:
ID NAME O_NAME
-- ----- --------
3 345 345
I'll appreciate if anyone can provide me a solution..Thanks!
View 3 Replies
View Related
Aug 6, 2007
How to auto increment an alphanumeric Primary Key in SQL? :( Because I want to add something like this in the Primary Key, for example i'll add a new data with an alphanumeric value of ABC-0001, then I'll add another 1, and it auto increments to ABC-0002 and so on.. How can I do it? And if I'll add a new alpha character in the Primary Key field, for example DEF-0001, then I'll add another and it auto increments to 002, and so on, and it will go back to 0001 if i'll use another combination of alpha characters. for example, i'll add a new alpha character AAA, will it go back to 0001 or it will continue? T___T I hope u get my point.. I want my table to look like this if i have added the dataABC-0001ABC-0002DEF-0001DEF-0002AAA-0001then if il add a new 1, for example ABCit will auto increment to 0003 in the same field, it will look like this after addingABC-0001ABC-0002ABC-0003DEF-0001DEF-0002AAA-0001Will it be possible? :(
View 4 Replies
View Related
Feb 15, 2000
I have seen this problem posted several times and not seen an applicable solution yet. No offense, the suggestions may have been good for those scenarios, but not this one.
Several (at least 4) Excel Spreadsheets come from a vendor that I need to import to a table. They are uploaded via web to a directory so they can be imported by a scheduled DTS package.
Darren Green's site gave me some good info on how to get this far, btw.
But I cannot overcome the NULL-ifying of Column Headers for Numeric fields when the spreadsheet is imported.
I've tried:
DTSDestination("Yr1") = trim(DTSSource("F7"))
DTSDestination("Yr1") = CHR(34) & trim(DTSSource("F7")) & CHR(34)
DTSDestination("Yr1") = cstr("0" & trim(DTSSource("F7)))
DTSDestination("Yr1") = clng("0" & DTSSource("F7")))
Named ranges, along with any sort of spreadsheet editing is too time consuming.
And since they come from a vendor, having them come to us with the required items in place is not possible.
Even though it seems easy enough, it's a toughy.
Any help would be rewarded with respect and admiration.
Thanks, Mike
View 4 Replies
View Related
Jul 13, 2001
Hi
Everyone
I have a problem my table structure is like this
book_id varchar2(30)
book_name varchar2(30)
Now i want to enter the data into the fields like this
B001 Java Unleashed
B002 ASp Unleashed
and so on
So my Problemis how i will auto increment the book_id field though that is an alphanumeric field.
PLease help me that how i will insert and select from this table
Waiting for your help
Manish
View 1 Replies
View Related
Aug 9, 2006
Hi,
I need to get Sort numerically from an alphanumeric column
Regards,
Kihsore
View 3 Replies
View Related
Dec 4, 2011
I have alphanumeric data in a Table Assets. The column name is Milepost.
Column Values are like below
MilePost
1.24
1.61
4.56
4.78
5.45
6.91
7.19
[code]....
Now I want to select records between 1 to LR 4.41 which should return all records between 1 to LR 4.41
My below query is not returning proper values. So i need a correct sql query.
Select Milepost from Assets where Milepost between '1' and 'LR 4.41'
View 4 Replies
View Related
