Solved

I need assistance using a query to populate missing data

Posted on 2014-02-11
4
372 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 29

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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to shrink a transaction log file down to a reasonable size.

617 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