March 7, 2012 at 5:34 pm
--Need sourcetable converted to target table format
--Also attached a spreadsheet with color coding, please help
DECLARE @SourceTable TABLE
(
[order]INT,
Student INT,
Teacher INT,
student_type VARCHAR(100),
color VARCHAR(100),
begin_date DATETIME,
end_date DATETIME,
bookvalue INT,
accvalue INT,
begin_placeVARCHAR(100),
end_place VARCHAR(100))
INSERT INTO @SourceTable
select 1,123,1,'asian','brown','1/1/2001','1/2/2001',1,1,'chicago','newjersey' union all
select 2,234,1,'african','black','1/2/2001','1/3/2001',2,2,'seattle','buffalo' union all
select 3,356,1,'asian','brown','1/3/2001','1/4/2001',3,3,'newyork','maine' union all
select 4,420,1,'asian','brown','1/4/2001','1/5/2001',4,4,'newjersey','dells' union all
select 5,521,1,'african','black','1/5/2001','1/6/2001',5,5,'buffalo','seattle' union all
select 6,678,1,'indian','brown','1/6/2001','1/7/2001',6,6,'maine','newyork' union all
select 7,740,1,'indian','brown','1/7/2001','1/8/2001',7,7,'vegas','fremont' union all
select 8,880,1,'mexican','black','1/9/2001','1/10/2001',8,8,'dells','chicago'
select * from @SourceTable
DECLARE @TargetTable TABLE
(
[order]INT,
Student INT,
Teacher INT,
student_type VARCHAR(100),
color VARCHAR(100),
begin_date DATETIME,
end_date DATETIME,
bookvalue INT,
accvalue INT,
begin_placeVARCHAR(100),
end_place VARCHAR(100))
INSERT INTO @TargetTable
select 1,123,1,'asian','brown','1/1/2001','1/5/2001',10,10,'chicago','dells' union all
select 2,420,1,'african','black','1/5/2001','1/6/2001',5,5,'buffalo','seattle' union all
select 3,678,1,'indian','brown','1/6/2001','1/8/2001',13,13,'maine', 'fremont' union all
select 4,880,1,'mexican','black','1/9/2001','1/10/2001',8,8,'dells','chicago'
select * from @TargetTable
hello
March 7, 2012 at 5:38 pm
Attachment included, thank you
hello
March 7, 2012 at 6:40 pm
You really need to explain how the source is supposed to be transformed into the destination. I have looked at your spreadsheet and the data and I can't see how you get from A to B.
March 7, 2012 at 6:58 pm
Ok, thank you for taking the time to answer.
Logic flows based on "Student Type" and "Color",
there are 4 types of possible "Student Type" and "Color" combinations.
1. asianbrown
2. african black
3. indianbrown
4. mexican black
So first row has --"asian" "brown" combination, search all rows until the same combination is found , Row 4 has "asian" "brown",
so final first row will have -- student 123, aggregation of bookvalue from row 1 to row 4, accvalue from row 1 to 4, begin_place of row1 and end_place of row 4 and
So rows 1, 2 , 3 and 4 become just 1 row
Next row 5
"african" "black" another combination, there are no more below row 5 - so keep the full row
next is row 6
"indian""brown", row 7 has the same value, so consolidate them into 1
next is row 8
stays the same
hello
March 8, 2012 at 12:07 am
A nice piece of homework....
So the way to solve this would be to group by picking the min and max order and aggregating (summing )bookvalue and accvalue. Then with the min /max's returned from that do a subquery to find the rows for the begin and end places.
I *could* write the query for you , but where's the value in that for me or (more importantly) you 😉 .
March 8, 2012 at 4:32 am
actually, i tried but its not that simple as you say, thank you for the reponse.
grouping does not produce correct results in this case.
hello
March 8, 2012 at 4:59 am
Show us the code for how far you got and we may be able to provide pointers to take you the rest of the way
March 8, 2012 at 5:10 am
Ok, i will have to re-write it for this sample data. I will do it but meanwhile anyother takers
hello
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply