Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

I need assistance using a query to populate missing data

Posted on 2014-02-11
4
Medium Priority
?
381 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 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

670 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