Hello,
I am writing a stored procedure that will take data from several
different tables and will combine the data into a single table for our
data warehouse. It is mostly pretty straightforward stuff, but there is
one issue that I am not sure how to handle.
The resulting table has a column that is an ugly concatenation from
several columns in the source. I didn't design this and I can't hunt
down and kill the person who did, so that option is out. Here is a
simplified version of what I'm trying to do:
CREATE TABLE Source (
grp_id INT NOT NULL,
mbr_id DECIMAL(18, 0) NOT NULL,
birth_date DATETIME NULL,
gender_code CHAR(1) NOT NULL,
ssn CHAR(9) NOT NULL )
GO
ALTER TABLE Source
ADD CONSTRAINT PK_Source
PRIMARY KEY CLUSTERED (grp_id, mbr_id)
GO
CREATE TABLE Destination (
grp_id INT NOT NULL,
mbr_id DECIMAL(18, 0) NOT NULL,
birth_date DATETIME NULL,
gender_code CHAR(1) NOT NULL,
member_ssn CHAR(9) NOT NULL,
subscriber_ssn CHAR(9) NOT NULL )
GO
ALTER TABLE Destination
ADD CONSTRAINT PK_Destination
PRIMARY KEY CLUSTERED (grp_id, mbr_id)
GO
The member_ssn is the ssn for the row being imported. Each member also
has a subscriber (think of it as a parent-child kind of relationship)
where the first 9 characters of the mbr_id (as a zero-padded string)
match and the last two are "00". For example, given the following
mbr_id values:
12345678900
12345678901
12345678902
11111111100
22222222200
They would have the following subscribers:
mbr_id subscriber mbr_id
12345678900 12345678900
12345678901 12345678900
12345678902 12345678900
11111111100 11111111100
22222222200 22222222200
So, for the subscriber_ssn I need to find the subscriber using the
above rule and fill in that ssn.
I have a couple of ideas on how I might do this, but I'm wondering if
anyone has tackled a similar situation and how you solved it.
The current system does an insert with an additional column for the
subscriber mbr_id then it updates the table using that column to join
back to the source. I could also join the source to itself in the first
place to fill it in without the extra update, but I'm not sure if the
extra complexity of the insert statement would offset any gains from
putting it all into one statement. I plan to test that on Monday.
Thanks for any ideas that you might have.
-Tom. 4 5808
Thomas R. Hummel (to********@hot mail.com) writes: The member_ssn is the ssn for the row being imported. Each member also has a subscriber (think of it as a parent-child kind of relationship) where the first 9 characters of the mbr_id (as a zero-padded string) match and the last two are "00". For example, given the following mbr_id values:
I am afraid that I will have to repeat the standard recommendations
(which you as a regular participant of the group should know by now :-)
o CREATE TABLE statements for the tables (OK, those we got)
o INSERT statements for sample data.
o The desired result given the sample data.
It might be that it's a late Friday night, and I am tired after a working
week, but I could not make out where this subscriber mbr_id comes from;
there was no such column in the table. Nor did I understand how the
SSNs came into the picture.
And it should come us no surprise that I don't like guessing.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Sorry, it was a late Friday for all of us :-)
Using the above CREATE TABLE statements:
INSERT INTO Source VALUES (1, 12345678900, '1968-02-02', 'M',
'111111111')
INSERT INTO Source VALUES (1, 12345678901, '1971-04-11', 'M',
'222222222')
INSERT INTO Source VALUES (1, 12345678902, '2001-10-03', 'F',
'333333333')
INSERT INTO Source VALUES (1, 11111111100, '1974-08-15', 'F',
'444444444')
INSERT INTO Source VALUES (1, 22222222200, '1974-09-22', 'F',
'555555555')
GO
I would like the destination table filled like this:
grp_id mbr_id birth_date gender_code member_ssn
subscriber_ssn
1 12345678900 1968-02-02 M 111111111 111111111
1 12345678901 1971-04-11 M 222222222 111111111
1 12345678902 2001-10-03 F 333333333 111111111
1 11111111100 1974-08-15 F 444444444 444444444
1 22222222200 1974-09-22 F 555555555 555555555
Since the first 9 characters of the mbr_id for the first three rows are
the same, they all of the same subscriber, which is the one that has
"00" as the last two characters. In other words, the first 9 characters
of the mbr_id are really more of a family ID, while the last two
differentiate the members of the family, with the primary family member
having "00". They need that primary family member's SSN included in the
destination table.
Right now I am planning to have an intermediate table anyway, so I will
either split up the mbr_id into those two parts so that I can easily
join the table to itself to fill the destination table, or I will just
fill in a new column (subscriber_mbr _id) in the intermediate table with
the first 9 characters plus a hard-coded "00" and use that to join the
table to itself. BTW, there will ALWAYS be a record that ends in "00"
for each SUBSTRING(mbr_i d, 1, 9). In other words, if there is a mbr_id
of 12345678901 then there is guaranteed to be a mbr_id of 12345678900
somewhere in the table.
Thanks,
-Tom.
Thomas R. Hummel (to********@hot mail.com) writes: I would like the destination table filled like this:
grp_id mbr_id birth_date gender_code member_ssn subscriber_ssn 1 12345678900 1968-02-02 M 111111111 111111111 1 12345678901 1971-04-11 M 222222222 111111111 1 12345678902 2001-10-03 F 333333333 111111111 1 11111111100 1974-08-15 F 444444444 444444444 1 22222222200 1974-09-22 F 555555555 555555555
Here's way to skin the cat:
INSERT Destination (grp_id, mbr_id, birth_date, gender_code, member_ssn,
subscriber_ssn)
SELECT a.grp_id, a.mbr_id, a.birth_date, a.gender_code, a.ssn,
b.ssn
FROM Source a
JOIN Source b ON b.grp_id = a.grp_id
AND b.mbr_id = floor(a.mbr_id / 100) * 100
Since mdr_id was declared as numeric, I used mathematical functrions
rather than string functions.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Erland,
Thanks for the SQL. I used something similar to that and it worked
well.
Thanks,
-Tom. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tuhin Kumar |
last post by:
Hi,
I would like to know how to insert multiple rows into a table, using a
single INSERT statement. My requirement is like this I have a table ABC
which contains multiple employees entries with empId as the primary key.
Now I want to insert the all the empid multiplied by X and y (<EMPID>*x*y)
into a table DEF plus filling other columns for each employee rows.
Table ABC
---------
EMPID QQQ LLL MMM
|
by: Bryan Guilliams |
last post by:
I'm trying to come up with an elegant, simple way to compare two
consecutive values from the same table.
For instance:
SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESC
That gives me the two latest values. I want to test the rate of
change of these values. If the top row is a 50% increase over the row
below it, I'll execute some special logic.
|
by: J.Haan |
last post by:
Hi all.
I'm currently coping with a problem on which I hope you could shed some
light.
Imagine the following:
I have table in DB2 8.1 (.5) which is defined as:
table test {
t1 smallint,
t2 smallint,
t3 smallint,
|
by: Bob Stearns |
last post by:
This is probably the wrong forum for this, but I thought it might start
some discussion.
The INSERT statement, in its current form, has problems being formatted
so a human reader can follow it. In particular, for a table with many
columns, the only to put the values in close proximity with the column
names is horizontally, which can lead to lines of 1000s of characters in
length which are difficult to read and edit. What I would like to...
|
by: Solution Seeker |
last post by:
I want to Store the String value with Single Quotes in the Field of
Database where if i try to Store the String value with Single Quotes
(as it is) then it is throwing the error as SQL String Truncated.
so we need a solution to store and retrieve user Entered value along
with single quotes into the Database.
i am using the String variable to frame the Qry(that is then passed
to Database for execution) which is as follows
| |
by: Mark Harrison |
last post by:
So I have some data that I want to put into a table. If the
row already exists (as defined by the primary key), I would
like to update the row. Otherwise, I would like to insert
the row.
I've been doing something like
delete from foo where name = 'xx';
insert into foo values('xx',1,2,...);
|
by: renatois |
last post by:
SqlDataSource UpdateCommand plus Insert
I have a SqlDataSource with an UpdateCommand but besides that i need also an Insert command triggered on the same update command.
That´s because i need to update a record and at the same time include a log of that update in another database.
Thanks
|
by: Atos |
last post by:
SINGLE-LINKED LIST
Let's start with the simplest kind of linked list : the single-linked list which only has one link per node. That node except from the data it contains, which might be anything from a short integer value to a complex struct type, also has a pointer to
the next node in the single-linked list. That pointer will be NULL if the end of the single-linked list is encountered.
The single-linked list travels only one...
|
by: =?Utf-8?B?c2lwcHl1Y29ubg==?= |
last post by:
Have a complex process where I need to Import a large amount of data
then run some transformations on this data then import into DataBase.
The transformation involves multiple fields and multiple process - so the
data needs to be read in 1 record at a time then run thru the transformation
that may create new data value then everything is imported into a db to store.
I have multiple questions
1)we used to have an internal data structure...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |