Solved

I need assistance using a query to populate missing data

Posted on 2014-02-11
4
320 Views
Last Modified: 2014-02-24
Hi Experts,
I have a file that I import into a SQL Server table with only one Column that I named RAW_DATA.  Here is a sample of the data that gets imported:

Record_NO      Trans_NO      Amount      Date
1000091            80012            10.00      1/5/2014
                        15.00      1/8/2014
                        18.00      1/9/2014

1000095            80145            10.00      1/2/2014
                        25.00      1/7/2014
                        35.00      1/8/2014
                        15.00      1/9/2014

1000155            80806            10.00      1/5/2014
                        12.00      1/8/2014
                        17.00      1/9/2014

The data only shows the Record_NO, and Trans_NO value for the first row, but not for the consecutive rows that belong to the same Record_NO, and Trans_NO.
Is there a query or a function I can run so that it fills in the missing informatio so that the data in the table looks as follows:

Record_NO      Trans_NO      Amount      Date
1000091            80012            10.00      1/5/2014
1000091            80012            15.00      1/8/2014
1000091            80012            18.00      1/9/2014

1000095            80145            10.00      1/2/2014
1000095            80145            25.00      1/7/2014
1000095            80145            35.00      1/8/2014
1000095            80145            15.00      1/9/2014

1000155            80806            10.00      1/5/2014
1000155            80806            12.00      1/8/2014
1000155            80806            17.00      1/9/2014

Thank you very much in advance,
mrotor
0
Comment
Question by:mainrotor
  • 2
4 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
ID: 39852300
This can be if and only if there is a primary key on both the excel you are importing from and the table you are importing the excel to.

Let's assume that you have primary key called RecordId on the excel that gets inserted into your table after the import, then this query below can help.

Let's call the table that data was imported to RealTable

First, create a staging table called yes, StagingTable.

Now, dump all the data from Excel into this stagingTable.

Then run the query:

INSERT INTO RealTable
            (RecordId
           ,Record_NO
           ,Trans_NO
           ,Amount
           ,Date)

SELECT     a2.RecordId
           ,a2.Record_NO
           ,a2.Trans_NO
           ,a2.Amount
           ,a2.Date

from StagingTable a2
where exists (
select a1.RecordId
FROM (
select RecordId from StagingTable
except
select RecordId from RealTable) a1 
where a2.RecordId = a1.RecordId)

Open in new window


This is the only way I know how.
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39852585
How do you identify values for different columns (Record_NO, Trans_NO, Amount and Date) from you single column (RAW_DATA) table?
0
 

Author Comment

by:mainrotor
ID: 39854882
Harish Varghese,
I identify the values using the Record_NO column.
0
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 39855526
I think I did not understand fully. You have a table with single column named RAW_DATA. And the values for this column will be:
Record_NO      Trans_NO      Amount      Date
1000091            80012            10.00      1/5/2014
                        15.00      1/8/2014
                        18.00      1/9/2014

Open in new window

So I assume all values in row#2 above will go into single column RAW_DATA. And same case with all other rows. If that is the case how do you identify that 1000091 is Record_NO and 80012 is Trans_No?

Okay, and if you have hanedled your data and somehow brought to a table with four columns as below:
Record_NO  Trans_NO  Amount     Date
1000091    80012      10.00     1/5/2014
NULL       NULL       15.00     1/8/2014
NULL       NULL       18.00     1/9/2014
1000095    80145      10.00     1/2/2014
NULL       NULL       25.00     1/7/2014
NULL       NULL       35.00     1/8/2014
NULL       NULL       15.00     1/9/2014
1000155    80806      10.00     1/5/2014
NULL       NULL       12.00     1/8/2014
NULL       NULL       17.00     1/9/2014

Open in new window

Then you can use below statement to update missing data, assuming your table name is TransData:
declare @PrevRecordNo int, @PrevTransNo int
Update T
Set @PrevRecordNo = case when T.Record_No Is not null then T.Record_No else @PrevRecordNo end,
	@PrevTransNo = case when T.Trans_No Is not null then T.Trans_No else @PrevTransNo end,
	T.Record_No = case when T.Record_No Is not null then T.Record_No else @PrevRecordNo end,
	T.Trans_No = case when T.Trans_No Is not null then T.Trans_No else @PrevTransNo end
From TransData T

Open in new window

Note:

For this solution to work, retrieval from this table should display the data in the same order as you want - all records with same Record_No and Trans_No should come together. This will happen if you do not have any indexes or primary key on your table (SELECT will retrieve in the order you inserted). OR you can add a column, say SerialNumber, as Primary Key and set it as IDENTITY, as below:
alter table TransData add SerialNumber int identity Primary Key

Open in new window

-Harish
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now