T-SQL (SS2K8) :: Comparing And Splitting Data

Sep 29, 2015

I'm trying to create some records for a data table (Table1) based on values in another table (Table2).

Table1 has the simple structure of

ID (INT), PolicyID (INT) (this is a foreign key linked to a Policies table), Premium (FLOAT), StartDate (DATETIME), EndDate (DATETIME)

Here is the structure and some example data from Table2

- PremID - PolicyID - Year - Prem01 - Prem02 - Prem03 - Prem04 - Prem05 - Prem06 - Prem07 - Prem08 - Prem09 - Prem10 - Prem11 - Prem12 -
- 000001 - 00000001 - 2013 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 100.00 - 130.00 - 130.00 - 130.00 -
- 000002 - 00000001 - 2014 - 130.00 - 130.00 - 130.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 - 140.00 -

PremID is just the PrimaryKey. Then there's the year, and then a load of float fields, each representing a premium paid for each month of the year (01=Jan, 02=Feb etc).

Now what I am looking to achieve is to create separate rows for Table1 from this data.

Starting in January (Prem01), I wish to record a separate line each time the premium amount changes. The best way of exlaining this is to give an example based on the above data.

For the first row (2013), the premium at the start of the year (Prem01) is 100.00. This premium remains until it changes in Prem10 to 130.00. It then remains 130.00 for the rest of the year (Prem10 to Prem12). For this I would want to create 2 rows of data for Table1. The result should look like below.

- ID - PolicyID - Premium - StartDate - EndDate -
- 000001 - 00000001 - 100.00 - 01-Jan-2013 - 30-Sep-2013 -
- 000002 - 00000001 - 130.00 - 01-Oct-2013 - 31-Dec-2013 -

I'd then repeat this for each record in Table2, so based on the 2 lines I have shown above, the end result would be:

- ID - PolicyID - Premium - StartDate - EndDate -
- 000001 - 00000001 - 100.00 - 01-Jan-2013 - 30-Sep-2013 -
- 000002 - 00000001 - 130.00 - 01-Oct-2013 - 31-Dec-2013 -
- 000003 - 00000001 - 130.00 - 01-Jan-2013 - 31-Mar-2014 -
- 000004 - 00000001 - 140.00 - 01-Apr-2013 - 31-Dec-2014 -

The source data is inherited from another system which I'm trying to change the layout of how the data is recorded to fit in with the target database.

Would it be easier to create a view of some sort from Table2, then use some form of WHILE LOOP to do this?

View 6 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Splitting Row Into Multiple Rows

May 14, 2015

I have a table that has for example data

I am looking to write a script that will change the first table into the second table.......

Table 1

Account No Name other field
1 Mr T and Mrs M Lambert xxx

I need to rewrite this as

Table 2

Account No split Name other field
1 a Mr T Lambert xxx
1 b Mrs M Lambert xxx

View 9 Replies View Related

T-SQL (SS2K8) :: Comparing Various Currencies Hedged Rates

Jun 13, 2014

I have a task to provide users with a list of currency rates in the following format:

Base Currency
Hedged Currency
Base to Hedge Rate
Hedged to Base Rate

E.g.

"Base Currency"..."Hedged Currency"..."Base to Hedge Rate"..."Hedged to Base Rate"
GBP....................USD.......................1.70.........................0.59
GBP....................EUR.......................1.25..........................0.80

(the dots are for the visual only)

The table I have been provided in SQL has the following structure:

Currency Code
Currency Rate (Rate against the Base Currency)
Base Currency (BIT to recognise which currency is the base one as it can vary from site to site. Only once currency can be Base per a given site)

I have started to implement, but ended up with cursor and temporary table, where I insert the data.

View 3 Replies View Related

T-SQL (SS2K8) :: Comparing Column Values In Same Table

Jun 16, 2014

How to resolve the below task

create table #temp ( idx int identity(1,1), col1 int, col2 int )

Here i want a flag success or fail on basis of below conditions.

I need to take all the col1 values and then i need to compare to each other.

if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".

if all the col1 values are ok , then we need to check Col2 values same as above.

--case 1

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522

--select * from #temp

--truncate table #temp

Because of difference in col1 values . the value of flag should be fail.

--case 2

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522

Here also the col1 is ok but col2 values have difference so it should be Fail.

Otherwise it should be success.

View 6 Replies View Related

T-SQL (SS2K8) :: Comparing Columns In Unpivot Table

Nov 21, 2014

SET NOCOUNT ON;

IF OBJECT_ID('dbo.TBL_SAMPLE_DATA') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATA;

CREATE TABLE dbo.TBL_SAMPLE_DATA
(
ROW_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,Airline VARCHAR(50) NOT NULL

[Code] ....

I have to compare the Aircraft1 and Aircraft1_unsub based on condition as below:

[case
when Aircraft1='N' or Aircraft1_unsub='Y' then 0
else 1
end ]

Based on the comparision output i have to update the main table with the outputvalue for each Aircraft based on the Airline

update t set t.Aircraft1=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline

update t set t.Aircraft2=outputvalue
from main_table t
inner join TBL_SAMPLE_DATA s
on t.Airline=s.Airline

View 7 Replies View Related

Splitting Data

Oct 10, 2006

I've read that we need to split data into a training and testing data set. How do I go about that?

View 7 Replies View Related

Splitting Data From One Column Into Two

Jul 11, 2012

Running SQL 2005
Current Query is as follows:

SELECT
vPerf.DateTime,
vPerf.SampleCount,
vPerf.AverageValue,
vPerf.MinValue,

[code]....

This query was working well because I used to only be interested in one counter that was returned in the column, which was 'Free Megabytes'...I now have additional data that shows up as 'Total Disk Space'...Ideally, the query would return the total disk space next to the free megabytes on the same row for the same disk drive. Here is a couple rows of sample output:

AverageValueInstanceNameObjectNameCounterName
44549 C: LogicalDiskFree Megabytes
44548 C: LogicalDiskFree Megabytes
69452 C: LogicalDiskTotal Disk Space
69452 C: LogicalDiskTotal Disk Space

This is the ideal format, the average value column goes away:

InstanceNameObjectNameFree MegabytesTotal Disk SpaceC: LogicalDisk44549 69452

View 1 Replies View Related

Splitting Data Between Two Tables

Feb 11, 2008

Hello and thanks in advance to any and all help on this post!

I am trying to create a report that uses a simple select against one table in the database:

select a,b,c
from MyTable
where d = 1
order by a




For ease of explanation, this returns 75 records. This report is to be used as a one page Flyer. Now I can create a single table and format it but I end up with three pages instead of one. My thought was to split the data returned between two side by side tables on this report. I cannot seem to find a way to do this through the properties of each table or an example of any expression that could help with this outside of RowCount (which simply does a page break), nested SELECTs to emulate LIMIT as in MySQL, or SELECT TOP n ORDER BY ASC / DESC to get a TOP N or BOTTOM N from a SQL Query.

I know I can't be the only one to have ever thought of this as a solution, I hope not at least , so I was hoping someone here may be able to help out. Thanks again in advance!

View 10 Replies View Related

Splitting Data By Coma

Mar 4, 2008



One table is like this
CREATE TABLE p1
(SNo VARCHAR(30))
And Data in the Table p1 is:





Sno

1,0

12,0

1,20

100,21

1001,21

There is One more Table p2
CREATE TABLE p2
(TravelerID INT, GDId INT)

Now my Requirement is Left side part of SNo(Before comma) Whatever the data is there like 1,12,1,100,1001
will be pushed into p2 table of TravelerID Column.And Right side part (Data like 0,0,0,21,21) will be pushed into p2 table of
GDId Column.Ultimately Table should look like below format.







TravelerID
GDId

1
0

12
0

1
20

100
21

1001
21

Thanks
Ramesh.M

View 5 Replies View Related

Help Splitting 1 Column Of Data Into Multiple

Apr 30, 2008



Hi

I have a table which has data "dumped" into it nightly and i need to create a "clean" table. Below are some sample rows:

1|name1|john|1234
2|name2|fred|2378
DS|address1|address2|postcode|telephoneno|area|propertytype


all the columns are separated with a "|" but the amount of columns are not fixed, so in lines 1 & 2 they are 4 columns and in line 3 there is 7 columns

thanks in advanced

Rich

View 4 Replies View Related

SQL 2012 :: String Splitting Using Like And Data Cleansing

Mar 28, 2014

I have a large poorly designed table (inherited) With a Name field that contains comma delimited text containing address information. I need to do several things with it but unfortunately there doesn't appear to be any true consistency in it. When it displays in its own text box it works by placing each section on a new Line and looks ok.But I need to pull it apart and place things like unit number, Building Name in its own column etc. In the data it could be in either the 2nd,3rd, 4th, dependent on what came 1st. the data looks some thing like the following

unitNumber/StreetNumber Space StreetName (Building Name), Subub,City,Country

Some addresses won't have unit number or Suburb or country so when splitting you could have Suburbs and Citys in multiple columns even if you try and stagger the split process.Has any body go a good tool or reference site for dealing for this sort of problem. I have a table that I have made up that has some of the street names that could be used for comparing against existing records but it is by no means fool proof due to spelling inconsistencies . I also have another list of Common building names that could be used to compare, remove and place in the new building column.

View 1 Replies View Related

SQL Server 2012 :: Splitting Data Between Two Columns

Dec 15, 2014

Here's the resultset:

Main
CC-09-00032-D
CC-09-00113-A
PR-10-01004-2

Expected result:

P C
PR-10-01004-2 CC-09-00032-D
CC-09-00113-A

What I need is split the data into two columns if data in column Main starts with 'PR-' then output result to column P and if it starts with 'CC-' then to column C (the output needs to be in one table).

View 3 Replies View Related

Splitting One Column Data Into Number Of Columns

Nov 20, 2014

I need splitting one column data into number of columns.

Name. Below are the sample of names

Doxycycline 200 mg capsule
Effexor 100 mg tablet
Duragesic 50 mcg/hr Transderm Patch
Ambien CR 6.25 mg Tab

My desired results are below..

name dosemeasmisc
Doxcycline200mgcapsule
Effexor 100mgtablet
Duragesic 50mcgTransderm Patch
Ambien CR 6.25mgTab

View 1 Replies View Related

Splitting One Data Field With Hyphens To Separate Records

Feb 16, 2004

I receive a file that will have hyphens between data items such as
123-aed-edr-45r-ui9
1-ed3-45r-rrr-98u
I need to split the values to load into a table that will hold the 5 separate data itens. The fields will always have the hyphens but could be different lengths. Any idea on a best approach to split this in tsql

View 2 Replies View Related

Opinion About Design Needed (splitting String Data)

Dec 14, 2006

Hi to everyone,My problem is, that I'm not so quite sure, which way should I go.The user is inputing by second part application a long string (let'ssay 128 characters), which are separated by semiclon.Example:A20;BU;AC40;MA50;E;E;IC;GREENNow: each from this position, is already defined in any other table, asa separate record. These are the keys lets say. It means, a have someproperities for A20, BU, aso.Because this long inputed string, is a property of device (whih alsohas a lot of different properities) I could do two different ways ofstoring data:1. By writing, in SP, just encapsulate each of the position separatedby semicolon, and write into a different table with index of device,and the position in long stirng nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3123 MZD12 XX77 .... any comment text124 MZD13 XY55 ... any other commentString data Tablefk_deviceId position value123 1 A20123 2 BU123 3 AC40.....123 8 GREENThe device table, contains also a pointer (position), which mightchange, to "hglight" specified position.Then, I can very easly find all necessary data. The problem is, I needto move the device record data (from other table) very often into otherhistory table (by each update). That will mean, that I also need tomove all these records from 1 -8 for example to a separate historytable, holding the index for a history device dataset. This is a littleinconvinience in this, and in my opinion, it will use to much storagedata, and by programming, I need always to shift this properities intohistory table, whith indexes to a history table of other properities.2. Table will be build nearly in this way:Major device data tableID AnyData1 AnyData2 ... AnyData3 stringProperty pointer123 MZD12 XX77 .... any comment text A20;BU;AC40;MA50;E;E;IC;GREEN 3124 MZD13 XY55 ... any other comment A20;BU;AC40;MA50;E;E;IC;GREEN 2By writng into device table, there will be just a additional field forthis string, and I will have a function, which according to specifiedpointer, will get me the string part on the fly, while I need it.This will not require the other table, and will reduce the amout ofdata, not a lot ... but always.This solution, has a inconvinance, that it will be not so fast doing asearch over the part of this strings, while there will be no real indexon this.If I woould like to search all devices, by which the curent pointervalue is equal GREEN, then I need to use function for getting thevalue, and this one will be not indexed, means, by a lot amount ofdata, might be slow.I would like to know Your opinion about booth solutions.Also, if you might point me the other problems with any of thissolution, I might not have noticed.With Best RegardsMatik

View 7 Replies View Related

Reporting Services :: Splitting Data In Tablix On Two Or More Rows

Jun 17, 2015

I have a report I'm designing where, as a simple SQL report viewed only on a screen, it was irrelevant how wide it was. However, now I've been asked to duplicate this report in SSRS and to include the option to print it out.

Well, the problem is, as it stands - with 8pt font, even - it will require a sheet of paper about 24" wide to get all of a single row to print.

So, I'm trying to create a Tablix that will split the data into two sets of header/detail rows in the same Tablix. Any workable solution that doesn't involve writing an app in basic or C.

View 10 Replies View Related

DB Engine :: Splitting Data Of Single Large MDF File

Nov 13, 2015

We have a 5 TB database in our environment. Both MDF & LDF are location in 1 single drive which is of 10 TB.

Now, we want to move to new server but we have multiple drives each of max 1 TB per drive. How can I go about splitting the data from 1 MDF files into multiple data files? How about moving indexes ?

SQL Version : Microsoft SQL Server 2012 (SP1) - 11.0.3513.0 (X64) -  Enterprise Edition (64-bit)

View 10 Replies View Related

Transact SQL :: Splitting Column Value While Keeping Existing Data

Jun 22, 2015

Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.

For example
PostCode            Latitude               Longitude
66000/66100       42.696595            2.899370
20251/20270       42.196471            9.404951

Would become
PostCode            Latitude               Longitude
66000                    42.696595            2.899370
66100                    42.696595            2.899370
20251                    42.196471            9.404951
20270                    42.196471            9.404951

View 6 Replies View Related

SQL Server 2012 :: Splitting Column Value While Keeping Existing Data?

Jun 22, 2015

Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.

For example:

PostCodeLatitudeLongitude
66000/6610042.6965952.899370
20251/2027042.1964719.404951

Would become

PostCodeLatitudeLongitude
6600042.6965952.899370
6610042.6965952.899370
2025142.1964719.404951
2027042.1964719.404951

how this can be done?

View 2 Replies View Related

SQL Server 2014 :: Splitting Similar Data Into Separate Columns?

Aug 18, 2015

If you see below there are 2 customer names on 1 loan, most of them share the same lastname and address, I want to separate it with fields,LoanID, customer 1 Firstname, Customer 1 Lastname, Customer 2 FirstName, Customer 2 Lastname, Adddress,zip

Loan IDFirst NameLastnameAddressaddress 2CityStateZip
1236048Joey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1236048Dickey Yesen xxxx abc GROVE RDNULLCLEVELANDTX77327
1235983Randy Seany xxxx abc Haleyville StNULLAuroraCO80018
1235983Barry Seanyxxxx abc Haleyville StNULLAuroraCO80018

The query I am using

select
L.Loanid
,B.FirstMiddleName
,B.LastName
,MA.AddressLine1
,MA.AddressLine2
,MA.City
,MA.State
,MA.Zip

from Loan AS L

LEFT JOIN Status As S on S.LoanID = L.LoanID
LEFT JOIN Borrower B on B.LoanID = L.LoanID
LEFT JOIN MailingAddress MA on MA.LoanID = L.LoanID
where S.PrimStat = '1' and B.Deceased = '0'

View 3 Replies View Related

Power Pivot :: Comparing YTD Data To Average Of Data In Last 6 Months

Jun 9, 2015

I am trying to build various reports that compares data over time. I have one that measures Year Over Year % difference for number of incoming projects. I managed to do that easily by calculating the following

YTDProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(SUM('TrendData '[Projects]),DATESYTD(CalendarDate[FullDate])))
PYProjects:=if(ISBLANK(SUM('TrendData'[Projects])),blank(),CALCULATE(sum('TrendData '[Projects]),SAMEPERIODLASTYEAR(DATESYTD(CalendarDate[FullDate]))))
YoYDifference:=[YTDProjects]-[PYProjects]
YoYPercProjects:=IF([PYProjects]=0, BLANK(), [YoYDifference]/[PYProjects])

Where Projects is the metric in question, TrendData is the table that contains project data and CalendateDate is the Date Table. But now I am trying to compare the same YTD projects data to number of projects that came in the last 6 months. How do our projects compare to average number of projects that came in last 6 month period.

I tried the the DATEADD function instead but got no luck and data came out wrong!

PrevProjects:=CALCULATE(SUM([Projects]),DATEADD(CalendarDate[FullDate],-1,QUARTER))

For some reason, this also returns blank in my model:

QTDProjects:=TOTALQTD(SUM('TrendData'[Projects]),CalendarDate[FullDate])

View 2 Replies View Related

Monitoring Inserted Data And Comparing Against Selected Data

Oct 22, 2006

I made ahuge load script in SQL Server 2000 as i load data from manytables(select some of each one collumns) into one single table and iwant to test the loaded data against the selected data to make surethat the loaded data is the same the selected datais there a code or tool to make this test or monitoring ?? pleaseurgent ....

View 2 Replies View Related

Comparing SQL Data Against XLS

Apr 10, 2007

Hello,

I am looking for some "best practice" information on how to properly setup a comparison of SQL data -vs- an Excel spreadsheet. I've never performed this task before, and will likely have to do more in the future. How can I define the range of cells to be used from the xls? There are over 1000 rows in the xls, so a Select * from dbo.table where dbo.column = 'x' just isn't practical.

Any information/direction that's out there is most appreciated!

Many thanks

View 1 Replies View Related

Comparing Data - Urgent

Oct 17, 2001

Hello,


I need to do a query to compare the same field in two different databases. How can I go about doing that? Can you use an if not exists and how does that work?

I also need to compare two numbers in two different databases and then convert that number to a decimal. For example I need to compare 22940 in one column and 22942 in a different column and insert the value of 2.1 in a third column. How can that be done?

Thanks in advance,
Anita

View 2 Replies View Related

Comparing Data In Two Different Databases

Dec 20, 1999

Hi, I would like to know if it is possible to compare data in two different databases (both SQL7.0),
with same exact schema. If possible, how could we do it?

Thanks

- Sharma

View 1 Replies View Related

Comparing Data In Two Different Databases

Apr 2, 2003

Hi
Is there a way of comparing the data contained in two separate databases (on the same server)?

I have two databases: DevDB and DB and all I want to verify is that the data in both are *exactly* the same.

Is there a nifty function to do this in SQL Server?

Thanks
Sara

View 8 Replies View Related

Comparing Data - SQL Issues

May 6, 2008

Hi

I am relatively new to SQL. Basically i am trying to compare data that doesn't match properly. Firstly one set of data has spaces, secondly the first part of the data is missing.

After researching I am going to remove the spaces with the replace and only query the final 8 number - this would dow hat I want but I cannot get it to work.

Here is my code.


Code:



update aleads as a, leads as l
set a.lid = l.lid
where replace(right(a.htel,8), ' ', '') = replace(right(l.htel,8), ' ', '');




Of course this didn't work but I do not know why - or how to actually achieve this.

View 6 Replies View Related

Comparing Two Data Rows

Sep 25, 2013

create table #prints(id int IDENTITY(1,1) NOT NULL,
Printermarkersupplyid varchar(36),PrinterID varchar(10),Description varchar(10),SupplyLevel int,ModifiedDate datetime)
insert into #prints(Printermarkersupplyid,PrinterID,Description,SupplyLevel,ModifiedDate)
select newid(),'P1','D1',100,'2013-08-1 03:28:38.203'
union all

[code]....

my requirement is to get the difference between adjacent rows.ie difference between 2nd and 3rd or 6th and 7th but not 6th and 8th.if difference between 2nd and 3rd is less than zero and 3rd modified date > 2nd modified date,then i should get count as 1 against 3rd row.

View 2 Replies View Related

Comparing Two Sets Of Data

Jul 23, 2005

I have the following situation. One set of data has 274 rows (set2)and anther has 264 (set1). Both data sets are similar in structure aswell as values for both of them were extracts from the same parenttable. Hope the info would substitute DDL. I need to find the "gap"rows between these two sets.Attempted to run a query likeselect count(*)from set2where not exists(select *from set1)did not yield what I desired. What else to try?TIA.

View 12 Replies View Related

Comparing Data Between Two Tables...

Jul 23, 2005

I would like to compare data across two tables. I have partinformation in a table. I get a new set of information periodically.I would like to compare my new info to my old info. I recognize thatdoing a compare of every attribute of every part will take FOREVER. Isthere some way I can do a "diff" based on the columns that I careabout?Thanks!--gloria

View 2 Replies View Related

Comparing Database Data

Oct 31, 2006

Can anyone tell me how a program like this might work:http://www.red-gate.com/products/SQ...mpare/index.htmI want to backup databases into a central repository but I only wantthe records that have changed for that day. This program seems to do itefficiently. Does Sql Server, Oracle, etc offer any sort of built inway of doing this via metadata or a similar mechanism?I want to be able to mirror databases in such a way that I dont need tohave the admin password for them. I don't want to alter the databaseschema in any way.The only way I can think off the top of my head is to assign MD5checksum values to rows and then compare the checksums in the masterand copy, however, to do it efficiently, you'd have to save the MD5values as you go, which would involve altering the schema on the mastertable.

View 1 Replies View Related

Comparing Data In 2 Tables

Jan 15, 2008



I need to compare two tables in two different databases and get data that is only in one table.

Table 1 in DB1 and Table A in DB_A

I need all distinct IDs in Table 1 in DB1 that are NOT in Table A in DB_A
and copy those IDs into Table A.

Is the sql below correct in obtaining data?
Can I add the copy part also to this?



select ID

from DB1.dbo.Table1 aaa

where not exists ( select empID


from DB_A.dbo.TableA bbb
where aaa.ID = bbb.empID)

View 5 Replies View Related

Comparing Data In Tables/views.

May 13, 2008

I am looking for an efficient mechanism to compare data between 2 tables/views.

Rationale:
I use a proprietary tool to data transfer between 2 databases. The tool itself uses Microsoft SSIS (integration service) to transfer data. I want to make sure that the data is transfered properly. Both the source and target database are not live database. The source and target database are in seperate servers.

View 2 Replies View Related







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