T-SQL (SS2K8) :: Combine Data Into One Row

Jul 1, 2014

I have two tables that I need to combine, they are as follows:

AccountCheckInDT
3289442/14/2014 15:11
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35
3289442/19/2014 9:41

AccountCheckOutDT
3289442/14/2014 16:18
3289442/16/2014 0:27
3289442/19/2014 9:28
3289442/19/2014 10:00

This is how I need the end result to look:

AccountCheckInDT CheckOutDT
3289442/14/2014 15:11 2/14/2014 16:18
3289442/14/2014 15:21
3289442/15/2014 15:24
3289442/15/2014 17:45
3289442/15/2014 23:58 2/16/2014 0:27
3289442/16/2014 20:09
3289442/17/2014 11:27
3289442/19/2014 9:35 2/19/2014 9:28
3289442/19/2014 9:41 2/19/2014 10:0

View 3 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: How To Combine Results From 4 Rows Into 1

Oct 8, 2015

How can I get the results of this query>>

SELECT
RoleName = pr.name
,RoleType = pr.type_desc
,PermissionType = pe.state_desc
,Permission = pe.permission_name
,ObjectName = s.name + '.' + o.name

[Code] .....

WHICH RESULTS IN THIS>>

RoleNameRoleTypePermissionType PermissionObjectNameObjectType
publicDATABASE_ROLEGRANTDELETEdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTINSERTdbo.AgencyUSER_TABLE
publicDATABASE_ROLEGRANTSELECTdbo.AgencyUSER_TABLE

[Code] ....

View 3 Replies View Related

T-SQL (SS2K8) :: Combine Two Tables To Display Iteratively

May 13, 2014

I am looking for a way to combine the following two tables to get a result set that would look like this:

1ProjectOrange 2014-05-08 orange
1update1
1update2
1update3
2ProjectRed 2014-05-09 red

[Code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Query To Combine Records In A Single Row

Jun 5, 2014

I'm working on a report where my table is as follows:

WITH SampleData (ID,NAME,[VALUE]) AS
(
SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'employee','1010'

[Code] .....

Here is my query against the table above:

SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE

FROM SampleData

Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):

IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
170983NULL6/04/2014NULL NULL NULL NULL
1709831010NULL NULL NULL NULL NULL
170983NULLNULL NULL NULL 8:00 NULL
170983NULLNULL NULL 6/16/2014 NULL NULL

[Code] .....

My Desired results are as follows:

IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
17098310106/04/20146/16/2014 6/16/2014 8:00 Personal
17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement

View 2 Replies View Related

T-SQL (SS2K8) :: Elegant Query Needed To Combine Multi Rows Into One

Apr 8, 2014

Table:

CREATE TABLE [dbo].[KPI](
[SVP] [varchar](20) NULL,
[Wk1] [int] NULL,
[Wk2] [int] NULL,
[Wk3] [int] NULL,
[Wk4] [int] NULL,
[Wk5] [int] NULL,
[Y] [int] NULL,
[Q] [int] NULL,
[Wk] [int] NULL
)

To generate sample data:

insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 1,0,0,0,0,2014,1,1)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,2,0,0,0,2014,1,2)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,3,0,0,2014,1,3)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,4,0,2014,1,4)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,0,2014,1,5)

[Code] ....

Current result:
SVPWk1Wk2Wk3Wk4Wk5YQWk
SVP10000201411
SVP02000201412
SVP00300201413
SVP00040201414

[Code] ....

Expected result:

SVPWk1Wk2Wk3Wk4Wk5YQ
SVP1234020141
SVP30 1 2 6 9 20142
SVP103226820143
SVP17233141120144

I surely can loop each row and insert the needed value into the result, I want to know if there is a better way to generate the result.

View 2 Replies View Related

T-SQL (SS2K8) :: Combine 2 Selects - Insert Results In Two Columns Of New Table

Sep 11, 2014

DECLARE @EmployeeID nvarchar(1000)
DECLARE @FiscalYear nvarchar(1000)

SET @EmployeeID = '101,102,103,104,105'
SET @FiscalYear = '2013,2014'

SELECT Data FROM dbo.Split(@EmployeeID, ',')
SELECT Data FROM dbo.Split(@fiscalyear, ',')
_______________________________________

This is part of a bigger project but I am stuck on this part. I get back 2 result sets

Data Data
101 2013
102 2014
103
104
105

I want to insert the results in a new table 2 columns and get the results below.

New Table
ID Fiscal Year
101 2013
101 2014
102 2013
102 2014
103 2013
103 2014
104 2013
104 2014
105 2013
105 2014

View 2 Replies View Related

Combine Data In Single Row

Apr 7, 2006

SELECT *
FROM dbo.empBenefits q
WHERE (StartDate IN
(SELECT TOP 2 STARTDATE
FROM EMPBENEFITS
WHERE EMPBENEFITS.employeeno = q.employeeno AND Benefitcode = 'HON'
ORDER BY startdate ASC))


I have this select statement working however I need to combine 2 records in a single row in a single table. The unique key is Employee No.

View 1 Replies View Related

Combine Data Into One Row, Add Columns

Apr 12, 2006

Hey guys,

I realize I've posted something like this before, but i'm confused how to do something new, what I am trying to do is combine rows of data when certain columns equal one another.

For example, I have to sets of data who have in common the columns ctp_code and a mod_code (they are the same) however they have two other columns called billing_amt and amount_owed that need to be added together (literally taking the data and doing the math, so 100.00 + 100.00 = 200.00)


Code:

id ctp_code mod_code billing_amt amount_owed
1 1 1 100.00 100.00
2 1 1 100.00 100.00



My results should be this:


Code:

id ctp_code mod_code billing_amt amount_owed
1 1 1 200.00 200.00



Any help, thoughts?

View 10 Replies View Related

Getting Data And Combine Two Column

Oct 3, 2006

I have a table Table, there are two set of Number and store in Tel1 and Tel2

I want to get all the number of each reacod that start with '1' on each record, the number I need that may in Tel1 or Tel2, I want to get the result like 'Result' table how can I do it ?

THX

TableA
+----+-------+-------+
| ID | Tel1 |Tel2 |
+----+-------+-------+
| 1 | 12223 | 92269 |
| 2 | 12269 | 97789 |
| 3 | 96636 | 13369 |
| 4 | 12259 | 97781 |
| 5 | 92889 | 12263 |
+----+-------+-------+


Result
+----+-------+
| ID | Tel |
+----+-------+
| 1 | 12223 |
| 2 | 12269 |
| 3 | 13369 |
| 4 | 12259 |
| 5 | 12263 |
+----+-------+

View 1 Replies View Related

How To Combine 2 Tables Data

Oct 6, 2015

I have 2 tables. first table contains name and age and the second table contains class and roll num.  I have a 3rd table which contains all these 4 columns. Now I want to fill the 3rd table with the 1st and 2nd table's data.

View 8 Replies View Related

Combine Data From Multiple Rows

Feb 5, 2015

I am running a query to pull data from 2 tables. However multiple data elements could be attached to one unique ID which when I run the query it repeats causing the entire data set to give inaccurate numbers. How to achieve this:

xxx.001A3264
xxx.001A3685
xxx.002A3261
xxx.002A3685

I would like my results to look like this:

xxx.001A3264 & A3685
xxx.002A3261 & A3685

View 2 Replies View Related

How To Combine Date And Time Before Import Data?

Aug 20, 2007

I have extracted the data from Point of Sales. it has two columns one for date and one for time in the database.
I need to combine both column into single column with the format "mm/dd/yyyy hh:mm:ss" before i import the data into the SQL server for my BI project.

Example: Data extract from Point of Sales
FDATEFTIME
20060114063616
20060115070743
20060116071020

How can i combine those two when i import into SQL server like below:
FDATE
01/14/2006 06:36:16
01/15/2006 07:07:43
01/16/2006 07:10:20

million thanks.

View 2 Replies View Related

SQL Server 2012 :: How To Combine Data From Different Tables Using A Query

Dec 17, 2013

I have a set of tables that look like what I have shown below. How I can achieve the desired output ?

CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);

INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);

[Code] ....

I have a query currently that looks like this . @Month and @Year are supplied as parameters

SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]

[Code] ...

Right now I see an output like this : for a particular value of @Month and @Year

SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :

[Customer],SalesofProductA, SalesofProductB, SalesofProductC

How it can be done ?

View 2 Replies View Related

SQL Server 2014 :: Combine Data From Historical Table?

Aug 13, 2014

Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.

View 9 Replies View Related

Need To Combine String Data From Multiple Columns Into One Column

Aug 31, 2007

When quering a table with given criteria, For ex:

select notes, jobid, caller from contact where status in (6) and jobid = 173
I am getting this:




This job will be posted to Monster for 2 weeks. 173 906
Waiting for full budget approval 173 906
TUrns out we're uppin 173 906

What should I do so that these three columns for the same jobid from the same caller appears in only one column, either separated by a comma or semicolon?

Please HELP!!!!!

View 4 Replies View Related

Combine Tables From 2 SQL Servers With Different Schemas And Update As New Data Is Entered

Mar 28, 2008

I have 2 SQL server 2000 machines, I need to take a table from each one and combine them together based on a date time stamp. The first machine has a database that records information based on an event it is given a timestamp the value of variable is stored and a few other fields are stored in Table A. The second machine Table B has test data entered in a lab scenario. This is a manufacturing facility so the Table A data is recorded by means of a third party software. Whenever a sample is taken in the plant the event for Table A is triggered and recorded in the table. The test data may be entered on that sample in Table B several hours later the lab technician records the time that the sample was taken in Table B but it is not exact to match with the timestamp in Table A. I need to combine each of these tables into a new SQL server 2005 database on a new machine. After combining the tables which I am assuming I can based on a query that looks at the timestamp on both Tables A & B and match the rows up based on the closest timestamp. I need to continuously update these tables with the new data as it comes in. I havent worked with SQL for a couple of years and have looked at several ways to complete this task but havent had much luck. I have researched linked servers, SSIS, etc Any help would be greatly appreciated.

View 10 Replies View Related

Combine Data And Split Into Separate Txt Files For Each Header/detail Row Groupings

Mar 16, 2006

Ive created with the help of some great people an SSIS 2005 package which does the follow so far:
 
1)       Takes an incoming txt file.  Example txt file: http://www.webfound.net/split.txt    
 
The txt file going from top to bottom is sort of grouped like this
     Header Row (designated by HD)
          Corresponding Detail Rows for the Header Row
           ..
     Next Header Row
          Corresponding Detail Rows
 
     and so on  
 
       http://www.webfound.net/rows.jpg
 
2)       Header Rows are split into one table, Maintenance Detail Rows into another, and Payment Detail Rows into a third table.  A uniqueID has been created for each header and its related detail rows to form a PK/FK relationship as there was non prior to the import, only the relation was in order of header / related rows below it when we first started.  The reason I split this out is so I can massage it later with stored proc filters, whatever
 
Now Im trying to somehow bring back the data in those table together like it was initially using a query so that I can cut out each of the Header / Detail Row sections into their own txt file.  So, if you look at the original txt file, each new header and its related detail rows (example of a cut piece would be http://www.webfound.net/rows.jpg) need to be cut out and put into their own separate txt file. 
 
This is where Im stuck.  How to create a query to combine it all back into an OLE DB Souce component, then somehow read that souce and split out the sections into their own individual txt files.
 
The filenames of the txt files will vary and be based on one of the column values already in the header table.
 
Here is a print screen of my package so far:
 
http://www.webfound.net/tasks.jpg
 
http://www.webfound.net/Import_MaintenanceFile_Task_components.jpg
 
http://www.webfound.net/DataFlow_Task_components.jpg
 
Let me know if you need more info.  Examples of the actual data in the tables are here:
 
http://www.webfound.net/mnt_headerRows.txt
http://www.webfound.net/mnt_MaintenanceRows.txt
http://www.webfound.net/mnt_PaymentRows.txt
 
Here's a print screen of the table schema:
http://www.webfound.net/schema.jpg

View 17 Replies View Related

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

Nov 18, 2007

Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department:                                            Sells:
1                                                              Meat
1                                                              Rice
1                                                              Orange
2                                                              Orange
2                                                              Apple
3                                                             Pears
The Data I would like read separated by Semi-colon:
Department:                                            Sells:
1                                                             Meat;Rice;Orange
2                                                             Orange;Apple
3                                                             Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
 
 

View 2 Replies View Related

T-SQL (SS2K8) :: Convert Column Data Into Comma Separated Data?

Mar 14, 2014

I have data in the below format .

NameValuecategory
AAA510
BBB510
CCC510
DDD512
EEE512
FFF512

I want the result in the below format

NAMEValuecategory
AAA,BBB,CCC510
DDD,EEE,FFF5120

I have tried stuff but all six values(AAA...FFF) are coming in one row , however i need them as per the category.

View 2 Replies View Related

T-SQL (SS2K8) :: Load Data From Flat File Source Into OleDB Destination By Changing Data Types In SSIS

Apr 16, 2014

I have an source file and i have to load it into the data base by changing datatype of the columns in ssis

View 1 Replies View Related

T-SQL (SS2K8) :: Finding One Set Of Data Within Another Set Of Data For Update?

Nov 4, 2015

finding one set of data within another set of data for update,so if within column 1 it finds the results from another select on another table, it updates a different column with the result.

Example

SELECT Title
from H..Import_Table
returns
'PRN - Concord'
'PRN - San Jose'
'SLP - PRN - San Jose'
'San Jose - PT - PRN'

[code]....

View 1 Replies View Related

Grouping By One Field And Combine Data Of Another Field In SSRS

Apr 2, 2008

Hi all experters,

Please suggest how to build the report in below case:

Raw data:






ID
Member
Functions

1
Alan
A

1
Alan
B

2
Tom
A

2
Tom
B

2
Tom
C

3
Mary
D

3
Mary
E


Report Shows:






ID
Member
Functions

1
Alan
A,B

2
Tom
A,B,C

3
Mary
D,E


I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID.

Any good suggestion?

Thanks in advance,
Steve Wang
2008/4/2

View 6 Replies View Related

Combine Data In Single Row From Single Table

Apr 4, 2006

How can i combine my data in single row ? All data are in a single table sorted as employeeno, date


Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000


Should Appear


Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View 3 Replies View Related

T-SQL (SS2K8) :: Get Data In 2 Columns

Oct 17, 2014

I am currently working on extracting data from a text file and loading into sql server.I have a table with 1 column and the data in the column is in this format:

TableName1
A
1
10

TableName2
D
2
20

I want to convert this to 2 columns in this format:

ColumnA ColumnB
TableName1 A
TableName1 1
TableName1 10
TableName2 D
TableName2 2
TableName2 20

View 1 Replies View Related

T-SQL (SS2K8) :: Sort Data By Using ORDER BY

Mar 7, 2013

When We sort the data by using ORDER BY , which sorting algorithm method will be used by SQL optimizer?

1) Bubble Sort
2) Quick Sort
3) Merge Sort
4) Heap Sort
5) Insertion Sort

View 9 Replies View Related

T-SQL (SS2K8) :: Using XML Data With For XML Path Error

Apr 24, 2014

I have some T-SQL that generates HTML code by using the FOR XML PATH functionality. Once the HTML is built, I am using the following T-SQL to convert it to XML so I am not limited by the output

SELECT @vHTML AS [processing-instruction(x)]
FOR XML PATH('')

where @vHTML is varchar(MAX)

Now, when I run my code in VB.NET and use the ExecuteXMLReader() command, I get nothing back into the XML Reader. I noticed that I needed the XMLDATA directive also, but when I add it, I get the following error

Msg 6860, Level 16, State 1, Line 247
FOR XML directive XMLDATA is not allowed with ROOT directive or row tag name specified.

View 1 Replies View Related

T-SQL (SS2K8) :: Query XML Data For Blanks

May 1, 2014

I am try to see if there are any blanks in a node of a table that has xml data in one of the columns. The query I use is returning zero results.

Select COUNT(*)from ENTITY
Where CONVERT(XML, Ent_root_xml, 0 ).value('(//UD_PQ_FLAG/node())[1]', 'VARCHAR(50)')= ''

View 1 Replies View Related

T-SQL (SS2K8) :: Get Difference Between Data In Two Tables

May 27, 2014

I have two similar tables in different database and need to make query to select only the data from the first table that is not available in the second table and there is no unique record for each record , but each row is having different records for example:

CREATE TABLE table1(
[PNR] [nvarchar](10) NOT NULL,
[Tkt_Number] [nvarchar](10) NOT NULL,
[DepaCityName] [nvarchar](50) NULL,
[ArriCityCode] [nvarchar](3) NULL,

[Code] ....

Output should be like this

PNR | TKT_NUMBER | DEPACITYNAME | ARRCITYCODE | NAME
fdf44 12669 Sah DXB lamees

View 8 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

T-SQL (SS2K8) :: Get Data Changes From History Table?

Mar 25, 2015

how to get products on which SalesPerson changed. Here is table with data.

View 9 Replies View Related

T-SQL (SS2K8) :: Matching Two Data Sets

Apr 28, 2015

I have two tables - one with sales and another with payments against those. The payment may not match the exact amount of sales and I have to use FIFO method to apply payments. The payment month must >= sales month.

How can i write a query to do this? Examples are as below.

Table 1
Sales Sale DateSale Amt
1Jun-141200
2Oct-142400
3Dec-14600
4Feb-1512000

Table 2
Pay Month Pay YearPay Amount
5 2014 300
6 2014 1000
10 2014 500
11 2014 2000
12 2014 300
1 2015 900

create table tbl1
(
saleNo int
,saleDate date
,saleAmt float
)
insert into tbl1 (saleNo, saleDate, saleAmt)

[Code] ....

View 5 Replies View Related

T-SQL (SS2K8) :: Populating Data For Every Day Of The Week?

May 21, 2015

I have a date table(A) for every day of the year from 1990-2016 including the week number.There is another table(B) that has a number, date and week number.

I would like to join these 2 tables together, in order to populate the number from table B on every day of the week (of the week number in table B).

What makes it a little more complex is that the dates in table B not always correspond with the week number in the same row. Adding to the problem are some conditions:

- Every day of the week should have a number, and it should never be 0.

- If the 1st date of all rows in table B with the corresponding week number is not the monday of the week (but for example wednesday) it should start with this number on monday.

- Thousands of rows are in table B and for some of them the date corresponds with the week and for some of them they don't

- The rows can be grouped together using the week number.

Take the following example of table B:

B:
ID Date Number WeekNo
1 21-5-2015 25 21
2 23-5-2015 30 21

In this example the dates correspond with the weeknumber, because the 21st and the 23rd of may are week 21. By joining this with the date table (A), by using for example cross apply, I would hope to get the following result;

Date Number WeekNo
18-5 25 21
19-5 25 21
20-5 25 21
21-5 25 21
22-5 25 21
23-5 30 21
24-5 30 21

The same should work if the same example had the week number 22 in every row.

Take the following example of table B:

B:
ID Date Number WeekNo
1 21-5-2015 25 22
2 23-5-2015 30 22

In this example the dates do not correspond with the weeknumber, because the 21st and the 23rd of may are week 21 and not 22. By joining this with the date table (A), I would hope to get the following result;

Date Number WeekNo
25-5 30 22
26-5 30 22
27-5 30 22
28-5 30 22
29-5 30 22
30-5 30 22
31-5 30 22

Because the last result (ordered by date) of the week number 22 is 30, the whole week shows 30 on every day.

View 2 Replies View Related

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 View Related







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