Transact SQL :: Generate Unique Username From Data Columns

Oct 19, 2015

I am trying to achieve the below problem statement, however I am getting stuck at the looping part.

EX: We have three columns

First Name |  Last Name | Mothers Name

I want to generate the username field using above columns, so lets consider, I have following data

First Name |  Last Name | Mothers Name
a                    b                  cdef
a                    b                  cdfg
a                    b                  cdfj

Expected Usernames:

1: a.b
2: a.b.c
3. a.b.cd

Basically, it has to be FirstName.LastName.(incremental letters from MothersName until the name becomes unique)

View 4 Replies


ADVERTISEMENT

Transact SQL :: Case Statement For A New Column Based On Date And Username Columns

Sep 2, 2015

Below is the SQL Query i am currently having

SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate

[Code] ....

And here is the result dataset

ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL

[Code] .....

So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.

View 13 Replies View Related

Transact SQL :: Generate A Report Based Of Columns In Each Table

Oct 31, 2015

I have 5 different tables with same structure. Each table has an account column and another column count. I am trying to generate a report based of the columns in each table. Account is same in all tables with same account numbers but count will be different. 

Eg:

Table 1 has Account Count

Table 2 has Account Count

Table 3 has Account Count

Table 4 has Account Count

Table 5 has Account Count

I want output as: 

Account Count Account Count Account Count Account Count Account Count 

View 4 Replies View Related

Transact SQL :: ORDER BY Clause Is Ignored When A Unique Index On Criteria Columns Exist

Sep 16, 2015

In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).

View 4 Replies View Related

Transact SQL :: Only Generate Email If Table Contains Data?

Jul 1, 2015

I am wanting to fire-off an email with the failed jobs anytime they are deposited into a table.  My syntax fires off an email even when the table does not contain data, it just sends a blank email.  this will only generate an email if teh table contains data?  

if exists (Select from FailedJobs)
exec msdb.dbo.sp_send_dbmail
@profile_name = 'DatabaseMail'
@recipients = 'asdfasdfsdf@aafas.com'
@from_address = 'asdfasdfacasca@cc.com'
@query = 'Select * from failedjobs'
@subject = 'List Of Failed Jobs'
@attach_query_result_as_file = 1;

View 3 Replies View Related

Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?

Apr 29, 2015

I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:

I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.

1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B

If updated my query (see below) and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.

2. My second question: How to i get around this error?

SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B,  fnSplitJson2(A.ITEM6,NULL) C

I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.

View 14 Replies View Related

Transact SQL :: Unique Pairs Of Data?

Sep 23, 2015

In my table, I have some duplicate rows. I want to select only the unique value pairs.

So, the table/data look like this:

ColumnA  -- ColumnB
123  -- 234
234  -- 345
234  -- 345
345  -- 456

I want to create a select statement that will only return:

123  -- 234
234  -- 345
345  -- 456

In other words, the duplicate row i.e. the second 234 - 345 -- won't be included in the set my SELECT statement will return.

View 2 Replies View Related

Data Flows And Unique Columns.

Feb 9, 2007

Perhaps one too many 2000 DTS packages have permanently damaged my ability to think clearly - however, I've find myself very frustrated attempting to create a SSIS Data Flow which replaces a very simple 2000 DTS package.

Take data from table1 in database1, put it in table2 in database2. Table2 in Database2 has an additional column as part of the primary key - so I need to add an arbitrary unique value in each row as it's inserted. Previously, I did this in the transformation script through a variable I incremented.

What's the recommend method to do this now - since row level processing of variables seem to be a no-no?

View 5 Replies View Related

Transact SQL :: Method To Generate Insert Statements For Data In A Table

May 30, 2013

I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)

View 7 Replies View Related

Generate Unique Code Help

Nov 6, 2007

Hi Guys,

I am trying to insert a unique code, like a voucher code, into a table in MSSQL, 10 numbers (0-9) which obviously needs to be unique.

I can code this in ASP, but I think the process could be a bit long winded, i.e. create a key - run a stored procedure to see if it is free if not loop and check again. Surely there would be a way to do this in a stored procedure in one?

So the code would look like 3928492834.

1. Generate Key in SQL
2. Check if not used in column 'kcode' if it is, regenerate and check again.

Any advice would be greatly appreciated, I know how to do this in ASP but bit of a SQL newbie!

Thanks

John

View 2 Replies View Related

Generate Unique Primary Key

Aug 17, 2007

Is there any way to auto generate the unique primary key when inserting data in MS SQL?
there is a way to create the increasing integer as primary key, but is there any ways other than that?

thank you

View 5 Replies View Related

Generate Unique Numbers

May 1, 2008

Hi

Hi

I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.

Something like table A has


Code:

FRUITS
-------------
Oranges
Mangoes
Apricots
....

I want to write a sql query that does something like

SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
fruits
from table A

so the output is


[code]
my_number Fruits
080501000001 Oranges
080501000002 Mangoes
080501000003 Apricots
[code]

is there a way to do this without resorting to identity columns

View 3 Replies View Related

How To Generate A Unique Barcode?

Jan 25, 2008

Hy all,

I'm working on a final project for school. It is a warehouse application. For this application I will need to generate a barcode. I have read some articles online and found out that I could use the code 39 barcode font.

I just don't fully understand how to generate the unique barcode. Could someone please help me? I also would like to store the barcodes in a sql server table. Could I use reports to print the barcode?

View 2 Replies View Related

Generate Unique Numbers For Each Record

May 1, 2008

Hi

I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.

Something like table A has



FRUITS
-------------
Oranges
Mangoes
Apricots
....



I want to write a sql query that does something like

SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
fruits
from table A

so the output is


my_number Fruits
080501000001 Oranges
080501000002 Mangoes
080501000003 Apricots
....


regards
Hrishy

View 4 Replies View Related

SQL 2012 :: Generate Unique Values

Apr 23, 2014

I am working on an ASP.net web application which inserts new record into an underlying table.

It is actually a ConfirmationNumber and should be unique. I have used abs(checksum(newid()))

For this purpose. Is there a better way to accomplice this?

View 9 Replies View Related

T-SQL (SS2K8) :: Generate Unique String ID

Jun 30, 2015

I need to generate unique string Id from the range AAAAA to PZZZZ. This id will be unique for each Users for e.g.

User ---- TransactionNo ---- UniqueId
--------------------------------------------
12001 --- 101 --- AAAAA
12001 ---- 102 --- AAAAB
12001 --- 103 --- AAAAC
12001 ---- 104 --- AAAAD

13001 --- 105 --- AAAAA
13001 ---- 106 --- AAAAB
13001 --- 107 --- AAAAC
13001 ---- 108 --- AAAAD

more the records of each user, unique should be the ID as displayed above.. Optimized way to generate the same...

View 3 Replies View Related

Generate Random And Unique Number Within Sql ?

Dec 11, 2007

I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?

If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.

thoughts ?

View 5 Replies View Related

How To Generate Random UNIQUE 13 Digit Number?

Aug 22, 2007

Hi

I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.

How do I generate 13 digit UNIQUE random in sql procedure?

Advance thanks

View 20 Replies View Related

Transact SQL - Combining Two Columns Of Data

Oct 23, 2001

How do I combine two columns (ex: [first name] [last name]) to get a combined result in a new column of ([first name] [last name]).

Thank you,

Andrew

View 2 Replies View Related

Transact SQL :: Striping Data Into Two Columns

Jun 3, 2015

In SQL statement is it possible to split the below column into two columns?

COLUMN_AAAAA AAAAAAA CO A.A. # 4030
BBBBBB BBB BBB CO. # 4260
CCC CCCCC #3001

View 16 Replies View Related

Transact SQL :: Put One Column Data Into Many Columns?

Oct 15, 2015

how to split a single column into multiple columns with | delimited. For example I have column old_values in this I have a data with | delimited and I need to split this data into multiple columns.

Old_values
xxx|yyy|zzz|aaa|bbb|ccc|ddd
into
A     B       C        D       E        F       G
xxx  yyy    zzz     aaa    bbb     ccc    ddd

View 41 Replies View Related

How Can I Add A Unique Key Column To A Table And Generate Its Values Automatically?

May 2, 2006

Hi, all,

I have a question about adding a unique key column to an existing table.

what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?

Thanks a lot in advance for any guidance.

View 6 Replies View Related

Renaming Columns Based On Data Using Transact Sql

Jan 18, 2008

A happy belated New Year to all!

Can someone please help me with the following. How do I, in SS 2000, use a value to label a column?

example

Col A Col B Col C Col D
CA '12/1/07' '11/1/07' '10/1/07'

I want to use the month and year of the date to rename the column.

Col A 12-2007 11-2007 10-2007
CA '12/1/07' '11/1/07' '10/1/07'




Thank you in advance for any help you can offer.


TMendez

View 5 Replies View Related

SQL Server 2008 :: Generate 8 Char Alphanumeric Unique Sequence

Apr 20, 2015

GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES

Requirements
• ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O

Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records

Below is my Sql Function -

CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)

[Code] .....

View 9 Replies View Related

Transact SQL :: Split Data Into Two Columns Based On Where Space Appears

Jul 8, 2015

I have a field in a table that contains addresses e.g

15 Green Street
5F Brown Steet
127 Blue Street
1512 Red Road

I want to output the numbers into one column and the address to another column as i need to produce a report that only shows streets and roads but no numbers.

So basically no matter how many characters before the first space which can be numbers or letters i want these output into two columns.

View 6 Replies View Related

Transact SQL :: Get DataTypes And Other Schema Related Data Of Columns In A Query

Sep 11, 2015

Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the following 

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'

But instead of TABLE_NAME i would like to give a query like

SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].

View 10 Replies View Related

Transact SQL :: Query To Generate XML And Grouping?

Jul 29, 2015

I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as below 

CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT

[code]....

what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?

View 3 Replies View Related

Transact SQL :: How To Generate GO After Each Drop Statement

May 21, 2015

I have this script which generates droptable tablename.

select  top 10000 'drop table '+name, * from sys.tables (nolock) where name like '%4[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' order by create_date

I would like to generate 'GO' after each drop statement.

View 3 Replies View Related

MS SQL Compare Columns To Generate Display Name

Sep 17, 2007

Hello, I have the following table with 4 columns....firstname, lastname1, lastname2, EMAILTable has user names and email, I would like to generate a 5th columncalled DisplayName.The email Id is sometimes firstname.lastname1.lastname2@ and othersjust firstname.lastname1@I would like to generate the display name exactly like the email egfirstname.lastname1.lastname2@ displayName = firstname lastname1lastname2......so for james.smith display name = James Smith and forjames.earl.smith displayName = James Earl Smith etc etcIs there a way that I can check/compare email Id (before the @ part)with firstname, lastname1 and lastname2 and generate a display namebased on what was used for the email address?I hope I've explained this well :-)Many thanks in advance for any help/adviseYas

View 5 Replies View Related

Transact SQL :: Generate Drop Table Statements

Oct 13, 2015

I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?

View 3 Replies View Related

Transact SQL :: Method To Generate Statement Using DML Triggers

Sep 29, 2015

Is there any method to get the T-SQL command/ statement using DML triggers?.

i.e. While we insert a record using INSERT Statement, is there any possible way to get the T-SQL Statement using DML trigger for AFTER INSERT.

View 4 Replies View Related

Transact SQL :: Generate Insert Order Script

Apr 22, 2015

I am using SQL 2012 SE. I have 2 databases say A and B with same structure and relationships. There are 65 tables in each database. A is already replicating data to database C for 35 tables. Now I need to move data from A to B which is greater than getdate()-1 everyday  for all the tables and once the move is done I need to delete this data from A. And samething the next day. Since this is for 65 tables its challenging to identify the insert order. Once the insert order is identified the delete order will be the reverse of it.

Is there a tool or any SP that could generate the insert order script? The generate scripts data only is generating the entire data and these databases are almost 400GB. So I thought of generating a schema only script and then create an empty database with it and then generate data only to identify the order of insert. But it wont generate anything since there is no data.

View 10 Replies View Related

SQL Server 2014 :: Using Value From Columns To Generate A New Value For New Column

Oct 26, 2015

I've data as below

account period01 period02 period03 period04
1111 null null null null
1112 782 null null null
1113 null null null 345
1114 765 882 67 321

What I want to achieve is to get values from period1 till period04 and used the lasted value to code the value of accoutperiod, if value is from period1 then code it as 01, period2 as 02, period03 as 03 and period04 as 04. So the output should be like this

account period01 period02 period03 period04 accoutPeriod
1111 null null null null null
1112 782 null null null 01
1113 null null null 345 04
1114 765 882 67 321 04

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved