Need To Compare Data For What Is Missing...

Nov 4, 2005

I have a billing database with patient names in it. I received a tab delimited file from insurance plan of our roster of assigned patients.

I now want to compare the insurance roster to our database to see who is missing.

The roster is layed out like this (info jumbled to protect privacy):
Eligibility List Sample
Last Name First Name Date of Birth Gender Insured ID VW Acct #
ALLEN CARRIE A4/16/1939FDH36664A572576-02
BAKER AMBER S11/24/1956FFXI2824C596439-02
BARKLOWLOREN R12/15/1956MKVF0092A588878-01
BRENNANPATRICIA A 1/14/1959FFXI8763A549675-02
BROWN MARTHA E8/14/1967FBD65508A366963-02
CALDWELL MICHAEL V 12/19/1969MLR500N2J595087-01
CLARK CYNTHIA A4/24/1971FVO600M8O596011-02
DEMPSTER SCOTT A 2/21/1976MCC85242A573371-01
DUNNE ANNETTE M10/26/1976FAE88375D598423-02
DUNNE CHRISTOPHER M 8/1/2021MBV81536A598423-01


I have loaded the text into an Excel Spreadsheet to work with it.


I was able to query our patient profile data base to get people with this insurance plan...but of course the data is never an ideal match.

For instance, some of the roster patients above have Middle Initials Concatenated to the First Name. In my database it is a mixed bag of missing initials, initials concatenated to first name or initials in separate Middle field. Thus a strict match on name is not going to work.

Date of Birth should hopefully be valid between both data sources.

Probably the best source of data to validate on would be the VW Acct# as I trust this to be the same in both sets of data. However in my patient data base it is buried in a note field preceded by a "Vital Works ID: " and then the number 602659-02. Generally it is the first part of the note field, but there could be additional notes preceding or trailing this Vital Works ID info.

An example of the query I was able to pull from the patient data base is as follows:



LastFirstMiddleDate of BirthGenderNotes
ClarkLawrence J9/7/1955MVital Works ID: 7575-01
ClarkKayleeann NULL1/3/1955FVital Works ID: 7575-02
ColeCodyNULL8/19/1948FVital Works ID: 8771-02 snt ref req to ohms for impact appt tbs Sent ref req back to ohms for Impact-DX.
CreaseyWadeL7/9/1988FVital Works ID: 602659-02
KennyRoyJ2/27/1953FVital Works ID: 602679-02
UttJannieC4/11/1984MVital Works ID: 602715-01
WestAliciaG9/9/1992MVital Works ID: 602736-02
WrightMinnieO2/17/1991MVital Works ID: 602736-03
YankeeDonald E10/27/1996MVital Works ID: 602762-03
YankeeStephana A4/4/2001FVital Works ID: 602762-04



How could I now construct a query that would tell me what patients were in the eligibility roster that didnt have a match in the patient database?

I would like to then save that to Excel or somewhere that I could print it out from so I can have someone up date the database.

Thanks for your help.

View 6 Replies


ADVERTISEMENT

Integration Services :: SSIS VB Script Loading Data Into Oracle DB Missing Some Data

Nov 10, 2015

I'm using Script Component to load data into Oracle DB due to the poor performance issue. Now, I found it will missing some data during the transmission. Please see the screenshot below: 

SQL Server:
Oracle:
DDL:

create table Person
(
BusinessEntityID Integer,
FirstName nvarchar2(50),
MiddleName nvarchar2(50),
LastName nvarchar2(50)
);

Result:

I follow up this article: [URL] ....

VB Script: 
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

[Code] ..........

View 8 Replies View Related

SQL 2012 :: SSIS Data Flow Items Tab Missing For Adding Data Source / Destination

Apr 3, 2014

I need to see inside a SSIS 2012 project a new SSIS installed component, but in the SSDT 2010 I cannot see the SSIS Data Flow Items tab for adding data source/data destination respect to the choose toolbox items pane.

View 4 Replies View Related

Missing Data With SSAS Cube As A Report Data Source

May 9, 2006

I've got a report that is using a cube as a data source and I can't get the report to show all the data. Only data at the lowest level of the cube is displayed. The problem is that most of the data I'm concerned with is at higher levels. There's no problem with the MDX. I get the correct results when I run the query.

I'm using a table to show the results. I've also tried a matrix, but I get the same results. I'm using SSRS 2005 and SSAS 2000.

Anyone have experience with this? Am I missing something simple?

View 7 Replies View Related

Data Missing When Loading Data Into Sql Server 2005

Jan 17, 2008



Hi, Experts

The project is a C/S data analysis system built with .Net 2.0 in windows environment, OS: Microsoft Windows 2003 R2 standard Edition Service Pack2, Database used in this project is: Sql server 2005. As a data analysis system, we need to load large amount of data from file to database, we do it by create a dts package and then do data loading by execute "m_Package.Execute(null, variables, m_PackageEvents, null, null)".

The problem is, we fount that DTS miss some data randomly sometimes, we can't find the rule till now. for example we've data as follows in data file, all data field splited by '|'
11234|26341|2007-09-03 00:00|0|0|0.0|0|0.011470833793282509|1|0.045497223734855652|0|0|1|0|3|2929|13130|43|0|2|0|0|40|1|0|0|0|0|0|1||0|0|3|0|0|0|0|0||0|3|0|0|43|43|0|41270|0|3|0|0|10|3|0|0|0|0|0||0|1912|0|0|3|0|0|0|0|0|0|0|3|0|0|5|0|40|0|9|0|0|0|0|0|0|0|0|29|1|1|24|24.0|16|16.0|0|0|0.0|0|0|24|23.980693817138672|0|0.0|0|0.0|0|0.0|0|0.0|11|2.0764460563659668|43|2|0|0|30|11|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|3|3|0|0|0|0|0|0|0|0|0|6|0|0|0|0|0|6|0|0|45|1|0|0|0|2|42|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|2|0|0|0|2|0|0|0|0|0|0|51|47|85|0|0|||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|||||||||||||0|0|0|0|0|97.117401123046875|0|0|83|57|||0.011738888919353485|0|1|0.065955556929111481|0|4|||0.00026658669230528176|1|0.00014440112863667309|1|68|53|12|2|1|2.0562667846679688|10|94|2|0|0|30|11|47|4|13902|7024|6878|18|85|4.9072666168212891|5|0.0|0|0.0|0|0.0|0|0.0|0|358|6448|6324|0|0|0|0||0||462|967|0|41|39|2|0|0|0|1|0|0|0|0|0|0|0|0|3|0|0|3|0|0|0|0|0|0|0|0|0|3|0|3|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|46|0|1|0|1|37|0|0|46|0|1|0|1|37|0|0|0|0|0|0|0|0|0.0|0|0|6|4|2|0|0|2|1|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|1|0.012290795333683491|0|44|44.0|0|0.0|0|0|0|30|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|2|0|2|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|2|1|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|1|0|0|0|0|0|0|0|0|0|0|0|0|27|0|0|2112|411|411|45|437|2|0|2|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|4|0|4|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|0|1|0|0|0|0|0|0|0|0|0|0|0|6|6|0|3|2|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|5|5.0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|600|600|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|6|0|0|0|0|0|0|6|0|9|1|2|2|3|0|1|0|0|0|0|0|0|0|0|0|0|0|13|3|2|5|1|1|1|0|0|0|102|0|1|1|0|0|0|3|3|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||0|0|0|0|0|0|0|0|0|0|0||0|0|0|0|0|0|0|0|0||||||||||0|0|0|0|0|0|0||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|46.0|46|0.0|0|0.0|0|0.011469460092484951|1|0.0|0|0.0|0|3|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|0.0|0|0|0|0|0|0|0|0|0|0|0|0|0|||0|100.0|100.0|0|1|0|1|0|0|0.02481505274772644|1|0.014951236546039581|1|0|0|0|0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|||0|||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|4695.5556640625|42260|7126.66650390625|62040|||||||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||||||0|0||||||||||

11213|26341|2007-09-03 00:00|0|0|0.0|0|0.068584725260734558|2|0.14375555515289307|27|0|2|1|11|3966|13162|97|0|13|0|0|83|3|2|3|0|0|0|26||0|0|11|0|0|0|1|0||0|1|0|3|97|98|0|246795|0|11|1|0|3|14|0|0|0|0|0||0|1912|0|0|12|0|0|0|0|0|0|0|12|0|0|17|0|83|2|2|2|0|0|0|0|0|0|0|73|3|1|24|24.0|16|16.0|0|0|0.0|3|0|24|23.905364990234375|2|0.0040000001899898052|0|0.0|0|0.0|0|0.0|11|2.0772171020507812|97|7|0|0|80|10|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|12|12|0|0|0|0|0|0|0|0|0|41|0|0|0|0|0|41|0|0|99|25|0|0|0|0|74|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|2|0|0|0|0|0|0|0|0|0|0|177|158|332|0|0|||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|3|||||||||||||0|0|0|0|0|0.0|0|0|321|198|||0.041950233280658722|0|2|0.1999288946390152|0|5|||0.00088306842371821404|1|0.00051651173271238804|1|529|113|4|8|2|2.0671226978302002|10|274|7|0|0|80|10|66|17|13934|7024|6910|31|332|4.7173333168029785|5|0.000033333333703922108|1|0.000033333333703922108|1|0.000033333333703922108|1|0.0|0|358|6448|6356|0|0|0|0||0||1609|3423|0|83|78|5|0|0|26|0|0|0|0|0|0|0|0|0|2|0|1|1|0|0|0|0|3|0|0|0|0|2|0|2|0|0|0|0|0|0|0|0|0|0|2|0|0|0|0|0|0.0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|65|0|1|0|1|72|0|0|65|0|1|0|1|72|0|0|0|0|0|0|0|0|0.0|0|0|12|7|0|2|3|16|5|5|6|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|2|0.04131799191236496|0|48|48.0|5|5.0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|0|0|0|0|0|0|1|0|0|0|0|0|0|9|0|5|1|0|0|0|1|0|0|0|1|1|0|0|0|0|0|0|0|0|0|0|4|2|1|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|3|0|0|0|0|3|0|0|0|0|0|0|0|0|121|0|1410|6046|558|1400|192|2467|10|0|5|1|0|0|0|2|0|0|0|1|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|15|0|10|0|0|0|0|3|0|1|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|0|1|0|0|0|0|0|0|0|0|0|0|0|21|9|12|10|3|1|0|1|4|0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|163|4|144|91|92|2|92|0|0|0|0|0|101|92|0|0|0|0|101|0|0|0|0|600|596|1|0|0|3|0|0|0|0|0|0|0|0|0|0|0|9|0|0|1|0|0|0|8|0|34|3|4|14|7|2|3|0|1|0|0|0|0|0|0|0|0|0|41|6|4|23|5|2|1|0|0|0|289|0|7|7|0|0|0|11|11|0|0|4|4|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||0|0|0|0|0|0|0|0|0|0|0||0|0|4|0|0|0|0|0|4||||||||||3|0|0|0|0|0|3||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|0|0|0|0|0|0|0|0|0|0|55.814689636230469|47.931411743164062|48|0.0|0|0.0|0|0.068584725260734558|2|0.0|0|0.0|0|14|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|0.0|0|0|0|0|1|0|0|1|0|0|0|0|0|||0|100.0|100.0|0|26|26|0|0|0|0.088263392448425293|2|0.056161552667617798|2|0|0|0|0|0|0|0|0|0|5|22|0|0|||||||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|||0|||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|16308.888671875|146780|23162.22265625|184840|||||||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||||||0|0||||||||||

11220|26341|2007-09-03 00:00|0|0|0.0|0|0.309184730052948|2|0.17757916450500488|0|0|7|4|18|3925|13682|172|0|19|0|0|164|10|5|4|0|0|0|2||0|0|20|0|0|1|4|0||0|5|0|4|172|172|0|1165085|0|20|4|0|20|8|0|0|0|0|0||0|1912|0|0|24|0|0|1|0|0|0|0|23|0|0|30|0|164|4|6|8|0|0|0|0|0|0|0|121|10|15|24|24.0|16|16.0|0|0|0.0|4|0|24|23.646148681640625|1|0.0040013887919485569|0|0.0|0|0.0|0|0.0|11|2.0849723815917969|172|5|0|0|123|44|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|26|24|0|0|0|2|0|0|0|0|0|192|1|0|0|0|0|191|0|0|190|12|0|0|0|0|178|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|3|0|0|0|0|0|1|0|0|0|0|1008|953|2758|0|5|||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|4|||||||||||||0|0|0|0|0|84.418106079101562|0|0|2626|1420|||0.29022222757339478|0|5|1.5045944452285767|0|5|||0.0058597764000296593|2|0.0046600494533777237|2|1340|1114|80|119|27|2.2584490776062012|10|1180|5|0|0|123|44|953|55|14462|7024|7438|52|2758|3.0037333965301514|5|0.021266667172312737|1|0.00036666667438112199|1|0.0|0|0.0|0|362|6440|6880|0|0|0|0||0||13711|27667|0|159|149|10|0|0|1|1|0|0|0|0|0|0|0|0|7|0|0|7|0|0|0|0|4|0|0|0|0|7|0|7|0|0|0|0|0|0|0|0|0|2|3|0|0|0|0|0|0.0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|842|0|111|0|102|1702|0|1|842|0|111|0|0|1703|0|0|0|0|0|0|0|0|0.0|0|0|47|26|11|3|7|37|1|20|16|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|4|0.24921548366546631|0|44|44.0|0|0.0|0|0|0|1003|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|10|0|8|2|0|0|0|0|0|0|0|0|0|0|81|1|60|10|10|0|0|0|0|0|0|0|0|0|1|1|0|0|0|0|0|0|0|25|16|4|2|3|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|53|27|17|4|5|0|0|0|0|0|0|0|0|0|421|0|8685|67179|2138|12104|80|26285|104|1|73|16|14|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|87|1|77|7|2|0|0|0|0|0|0|0|0|0|1|0|0|1|0|0|0|0|0|0|0|0|0|0|16|0|9|5|2|0|0|0|0|0|0|0|0|0|155|155|0|105|51|32|9|13|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|0|1|0|0|0|0|0|0|0|0|0|0|0|5|5.0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|102|0|0|0|0|0|600|445|4|20|32|63|16|15|4|1|0|0|0|0|0|0|0|37|0|0|5|0|0|0|32|0|230|7|10|99|68|22|21|0|3|0|0|0|0|0|0|0|0|0|286|18|10|182|53|17|6|0|0|0|2528|0|10|10|0|0|0|22|22|0|0|25|25|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||0|0|0|0|0|0|0|0|0|0|0||0|0|30|0|0|0|0|0|30||||||||||23|0|0|0|0|0|23||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1|0|0|0|0|0|0|0|0|0|0|0.0|45.998283386230469|46|0.0|0|0.0|0|0.30917638540267944|2|0.0|0|0.0|0|8|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0.0|0|0.0|0|0|0|0|1|1|0|0|0|0|0|0|0|||0|100.0|100.0|0|2|1|0|0|1|0.73375397920608521|5|0.41600942611694336|6|0|0|0|0|0|0|0|0|0|0|0|0|0|||||||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|||0|||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|98115.5546875|865520|176626.671875|1159360|||||||||||||||||||||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||||||||0|0||||||||||

We found that some of the data field become 'null' after the load action finished, if we load the same data again, problem disappeared, we can't 100% reproduce this issue each time, we don't know why, Anybody here can help us to solve this issue or give us some clue?


View 3 Replies View Related

Data Compare

May 13, 2008

Hello All,

How to I compare the data between prod and dev databases. I need to create sql script to list the new data and the modified data in development tables comparing with the production tables.

Can anyone give me suggestions on how to do it?

Thanks in advance,
-S

View 1 Replies View Related

Compare Data Pls

Jun 9, 2008

was able to load data from 2005 to 2000 about 100+ tables. Now i'm only concerned about if data is the same the rows and data types (not sure if there is any difference). Any ideas how to test it? should i go row-by-row???? its over 100 tables. OH MY goood...

View 5 Replies View Related

Compare Data

Jul 23, 2005

I've created two tables. One table (Classes) stores data about classesthat we offer. The Classes table stores the class id (classid) and themax number of students allows (maxstudents). The other table(Students) stores student data and the class they register for.When a user registers for a class, the classid column data from Classespopulates the class column in Students.I'm not sure how to count the number of students who registered forcourse X, subtract that from the max number of students in the Classestable, and display that the class if the max is reached either in awarning dialog box or as text on the page.I'm also populating a drop-down field on the registration form with theclass information from Classes. Confused yet?I don't know much about SQL or .ASP. Any help is appreciated.

View 2 Replies View Related

Compare Like Data

Jul 20, 2005

Hello all,I am new to sql and have some Access experience.In sql, how do I: compare 2 identical tables, (except for data); then updatetable 1 with new data from table 2TIAJake

View 1 Replies View Related

How To Compare Data In Tables

Sep 13, 2001

I know this sounds simple, but I haven't seen it in bol. I need to compare two tables, and list what rows are unique to each table. Thanks for the help!

rb

View 2 Replies View Related

How Do You Compare The Data In Two Tables

Sep 29, 2005

I have two tables and I want to know if every record from the first table is in the second one and if its data mathes exactly?

Any suggestion for a short way to do this?

Thank you!

View 10 Replies View Related

Parse Data And Then Compare

Aug 14, 2007

I am using MSSQL v8 (if that matters)

The data looks like the following
---------------------------
| PBP 20070420 2:26pm |
---------------------------

Now the data in this field is not uniform it can be blank, a sentence or have a different pre fix, instead of PBP, but the date will be YYYYMMDD when it is supplied.

I need to find all the dates that are within the last 10 months. How do I perform this task?

View 4 Replies View Related

Compare Data And Add To Table

Mar 4, 2004

Hi all,

Newbie here. I was wondering if any of you gurus could answer a question for me. Here is what I need to do (and I stress need):

I have 2 tables.

Table A has 3 columns, column 1 is unique customer numbers, column 2 is ticket numbers, column 3 is empty records.

Table B has 2 columns, column 1 is unique customer numbers (same numbers, although not the same order as Table A) , column 2 is invoice numbers.

I need to compare Table A where records in column 1 match records in column 1 in Table B. Where the records do match, I need to copy the records from Table B, column 2 to Table A column 3.

Can anyone here help me with this, please? It would really get me out of a jam with this, since it is the last step I have to take to finally get this new app rolled out.

Thanks a lot.

Mark

View 3 Replies View Related

Compare Data In Two Table

Jun 13, 2006

Hi all,
i have a question regarding data comparison in two tables in same database with same table structure.

my table structure is like this

CREATE TABLE xxgfs_gen_text_lookups_new (
lookup_type VARCHAR2(200) NOT NULL,
region_code VARCHAR2(30),
nongfs_value1 VARCHAR2(200),
nongfs_value2 VARCHAR2(50),
nongfs_value3 VARCHAR2(50),
gfs_value1 VARCHAR2(200),
gfs_value2 VARCHAR2(50),
gfs_value3 VARCHAR2(50),
retain_nongfs_for_dflts VARCHAR2(1),
retain_gfs_for_dflts VARCHAR2(1),
comments VARCHAR2(500),
created_by NUMBER(15),
creation_date DATE,
last_updated_by NUMBER(15),
last_update_date DATE,
last_update_login NUMBER(15),
Source_description varchar2(300),
Oracle_description varchar2(300),
defaults varchar2(300)
)



ALTER TABLE xxgfs_gen_text_lookups ADD CONSTRAINT xxgfs_gen_text_lookups_uq_1
UNIQUE (lookup_type,region_code,nongfs_value1,nongfs_value2,nongfs_value3);


i have some data in excel which i have uploaded using sql*loader using control card.Now i want to compare the data in both tables having same table structure only


do any body having idea how to compare the data using storeprocedure.
thanks in advance


regs,
Rajnish kumar

View 2 Replies View Related

Data Compare Tool

May 10, 2007

Does anyone have an opinion on specific “data comparison tools�?

We are looking for something to use in our test or dev environments that will be able to compare snaps shots of the data in a database before verse after a test event.

We have been able to record and compare data in specific tables but are learning that other tables were also being changed that we didn’t track. We want to be able to see all changes to a database.



Michael

View 16 Replies View Related

Compare Data In Tables

Jul 23, 2005

I am trying to determine the changes an application makes to a database.The plan is to copy the existing schema (active) to a reference schema, runthe application and then diff the table data between the reference and thea active schema. I have found one software vendor who has a tool to dothis, but it will only do one table at a time (interactively); I have morethen 300 and will run this a few times.One other way of determining the changes, I guess, would be to log all sqlstatements (in order), but I don't know how to do this (either).Any pointers would be greatly appreciated.Leo

View 3 Replies View Related

SQL Data Compare Error

Jul 20, 2005

Using SQL data compare i get the following error message:Could not allocate space for object '(SYSTEM table id: -701903460)' indatabase 'TEMPDB' because the 'DEFAULT' filegroup is full.The comparison is being run on one server between to databases. It hasbeen run several times but each time we get that error message, thetable ID number tends to change each time. We also had a problembefore where the 'TEMPDB' did not have enough space in its log filebut that was fixed by allowing the database to expand in size.

View 1 Replies View Related

How To Compare Data Before Deletion

Nov 5, 2007



SET identity_insert dbo.table1 on

GO

insert into dbo.table1(
PrimaryKeyCol,Col1, Col2 .....)


select

PrimaryKeyCol,Col1, Col2......

from [Sever].Database.dbo.table1 as ClientColumn

where not exists(

select * from dbo.table1 as ServerColumn

where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol

)

DELETE FROM [Server].Database.dbo.table1

where exists(

where ServerColumn.PrimaryKeyCol = ClientColumn.PrimaryKeyCol

)



SET identity_insert dbo.table1 off

GO


I can't complie this code.. anybody see where I went wrong??

Thanks for all your help.

View 10 Replies View Related

Import Excel Data - Missing Data

Mar 4, 2008

Hello all, I am using the Import Wizard to pull in data from an Excel spreadsheet. One column in particular SQL Server sees as a float data type but it contains varchar data. So I change this in the wizard but some of these values are missing when I select * from Sheet1$ in SQL Server 2005. Any ideas why this would happen? I have formatted the particular column as text in Excel.

View 6 Replies View Related

Compare Data Between 2 Sql Server Databases

Nov 20, 2000

How can I create a database link so that I can check data between 2 similar
SQL Server 7 databases ?

View 1 Replies View Related

Compare Data In Two Tables With SQL Query?

Jun 9, 2004

I am trying to QA data being put into a SQL database by an outside source (from Excel) and therefore need to compare two (for the sake of simplicity) tables within a database to one another.

The two tables should contain the same data, and the QA process is meant to find and report any discrepancies. The column names are slightly different.

My question then is, is it possible to write a simple SQL query which will compare the data from the two tables and select only those rows where the data in any given column does not match? My data is mostly text, not numerical.

I'm very new at using SQL and my knowledge of the query syntax is very basic.

Thanks for any help in advance!
~Lacy

View 2 Replies View Related

Need To Compare Data In SQL To Oracle / Import Changes

Mar 10, 2005

We will need to routinely import only changed data from an Oracle data base into a SQL database.
So we need an agent that will
1) Compare data in both databases (From disparate tables)
2) Import only that which is changes or new.

I am new to SQL server administration and am looking for a best practice method that we can be run on a weekly basis.
I am open to using third party software solutions, but would prefer a native MS SQL 2000 solution.
Can someone point me in the right direction?
Thanks.

View 1 Replies View Related

Compare And Insert Data On Two Tables

Apr 29, 2008

Hi everybody...
looking for a way to compare and insert data on two tables..

I have two tables

Tbl_email1
emailID email
1 info@sample1.com
2 info@sample2.com
3 info@sample3.com

tbl_email2
emailID email
1 info@sample1.com
2 info@sample4.com
3 info@sample5.com

I'm trying to compare tbl_email2 (email filed) with tbl_email1 (email field)
if the record exist it it does nothing if not it adds the email field in tbl_email1
the result would be

Tbl_email1
emailID email
1 info@sample1.com
2 info@sample2.com
3 info@sample3.com
2 info@sample4.com
3 info@sample5.com

thanks

View 4 Replies View Related

Compare Data Values Of 2 Tables

Dec 4, 2014

I have 2 databases( "A" + "B") with identical number of tables and identical number of records for each table. There are also identical number of fields pre record per table. Table A has had the sensitive data within the fields scrambled.

I need to know if there is a way to read down each DB table by table, record by record, field by field and compare the data values. If they are different I need to output the Field name, the data value and the Table name from the Scrambled Table (lets say its "A").

View 1 Replies View Related

T-SQL (SS2K8) :: Compare Data Between 2 Rows?

Jun 27, 2014

I have the following recordset:

cmdBatchNbPdsLbsZONE
817159644 1.55320031
817159652 9.09590031
817159679 2.5891806
817159687 5.7123006
817159709 2.3903006
817159733 2.2792006
817159741 2.0647007
817159768 1.2430007
817159784 4.1547006
817159792 3.56576013

I need to extract the corresponding price from the following table:

Zone MaxWeight Price
---------------------- ---------------------------------------
31 1.70 7.14
31 2.20 8.76
31 3.30 9.47
31 4.40 9.69
31 5.50 10.61
31 6.60 11.05
31 7.70 11.49
31 8.80 11.93
31 9.90 12.37
31 11.00 12.81
31 12.10 13.23

In this case, the 2 first rows should give a price of

1) 7.14 (weight between 0 - 1.70)

2) 11.93 (weight between 8.80 - 9.90)

How can I do that with a query?

View 4 Replies View Related

Compare And Select Data Of Two Tables

Dec 4, 2014

I try to compare and select data from table that which not in other table

Select
AAA_Id
,BBB
From dbo.Table1 a
Where AAA_Id not in(Select b.AAA_Id
From dbo.Table2 b, dbo.Table1 a
Where b.AAA_Id = 11)

But when I modified it like:

Select
AAA_Id
,BBB
From dbo.Table1 a
union
Select
0
,'Select'
From dbo.Table1 a
Where AAA_Id not in(Select b.AAA_Id
From dbo.Table2 b, dbo.Table1 a
Where b.AAA_Id = 11)
Order by 2

I cannot to get same result.

View 2 Replies View Related

Compare Data And Populate In Same Table

Feb 10, 2015

I have the data as below,in which owner is assigning some task to another user.

INPUT
#########

OWNER ID TASK_ID TASK_ASSIGNEE

user1 11user2
user112user3
user1 13user4

PRIVILEGE table
#########

USER_DETAIL PRIVILEGE_ID

user110
user111
user112
user28
user35
user46

OWNER has one set of privilege details in privilege table. I need to check privilege for user2,user3 and user4 in privilege table, if privilege not matches with the user1 then i want to populate the data output as below

NEEDED OUTPUT
###########

OWNER ID TASK_ID TASK_ASSIGNEE

user1 11user2
user112user3
user1 13user4
user211user2
user312user3
user413user4

I am populating this data in the view.

View 5 Replies View Related

How To Compare Data In Two Tables Using SSIS

Oct 21, 2007

Hi there-

Here is my situation.

I have got Table A in database DB1 and Table B in database DB2 with same table structure. I need to compare the data in table A and Table B and add the missed records in Table B.

Can anyone help me on how to do this in a SSIS Package?

Thanks,
Sundar.

View 6 Replies View Related

Data Transform And Compare With In A Query

Jul 23, 2005

Okay all I have a problem. I have two list of adresses and phonenumbers. I do not have control over the contents of the lists The onlyunique field between the two is the phone number. I need to be able toinner join the two lists on phone number.This would normally be straigt forward but the problem is that they areformated different and one of them does't even have a control on theformating.*Phone numbers are US phone numbers only.The one list (table) that does have a control uses this formatAAA3334444where AAA is the area code333 is the 3 digit prefix4444 is the four digit suffixthe second list (table) does not have any standardized formating andcan be filled with extraneous spaces, parentheses and dashes not tomention the leading 1 in some instances.I thought that I could do some kind of regular expression to do acomparison but I havn't as yet found a good resource to tell me how todo it or if it is even possible. Or maybe break up the one I know hasa standardized format into something like this:'%AAA%333%4444%' and doing my comparison that way. however It is veryimportant that only those list items in both list that are truly thesame place be listed.suggestions and solutions are apreciated

View 3 Replies View Related

How Do I Compare Data In 2 Different Tables Using Objrs?????

Dec 14, 2006

Hey all, got abit of a problem. Ive got a database where ive got 2 tables. I want to create a command which selects * from table 1 and if the same values are in table2 - it updates otherwise adds.  Ive got a loop so i can run through the data records But i dont know how i check 1 objrs against another within an sql statement??  please look at coding example below:

Im calling my data from the 2 tables using select commands- which is ok.- uses objrs1 and objrs2
Then im running my loop to check if data from table 1 is in table2:

Dim Q
For J = 1 to iRowCount2 
Q= Q+1
Call select2Cmd( )
  response.write(Q)
  response.write("</br>") 
Next 
response.end 

 

Heres my sql statement which checks this:
sub select2Cmd( )
'========================================================================================
SET objrs5= SERVER.createObject( "ADODB.recordSet" )
dim sqlStatment

sqlStatment = "SELECT * FROM [table2] WHERE [Name] = objRs2(Name) "
objrs5.OPEN sqlStatment,objConn2,1,3
'========================================================================================
end sub

 

I want to check the field [Name] in table2 with data from table1 and then loop to the
next record. How do I do this?  Please note the bold part is what im stuck on.
Thanks

 

 

View 1 Replies View Related

Sqlserver And Binary Data How To Compare

Apr 16, 2008



Hi Guys,

is there a way to compare if some binary data in a sql database are the same or different?

Cheers


View 2 Replies View Related

How To Compare And Take Actions From 2 Different Data Sources

Mar 17, 2008



Hi,

I have to get all the customer records from one table and compare this records with another table and finally if there is same record I have to perform an update but the record does not exist I have to make an insert.
How can I do it with SSIS Except writing sql or tsql scripts ?

thanks,
J

View 1 Replies View Related

Cross Server Data Compare Using Findstr

Nov 6, 1998

I am trying to compare the data from 2 different servers. It is the same table. I did a select * and put the data into 2 different text files. I then used the Windows NT findstr utility to compare them to find the differences. I used the command findstr /v /i /x /g:file1 file2 > file3, where file1 is the larger table, and file2 is the smaller one. I want to find the records in file2 which are different or do not exist. I recieved no data back. I have ASCII characters in the text files. My question is does anyone know of a better way to do this, or how to make this command work?

View 1 Replies View Related







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