Complex Sequel SQL server problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Complex Sequel SQL server problem

Author

Topic

JohnCash1
Starting Member

39 Posts

Posted - 2012-09-19 : 21:03:32

Hi,

Could someone let me know how best to solve this in sql server.

I have 5 fields, in column E, 1 means together, 2 means together.

In column E, when 1 starts, I want C ab to get B's 1b,
the second 1, C's ac should get A's 1a, and third 1, C's ad should
get A's 2b, fourth 1, C's ae should get A's 3a, fifth 1, C's af should get column D's LEB.

and then reset since in column E is now 2, when
2 first starts C's ab gets B's 1c, second 2 C's ae's gets
a's 1b, third 2 in column E C's ac gets column D's ced.

and the process repeats until the end of the records.

How can I do this with sequel? Thanks

Please note: put the final result into a new table.

A B C D E

1a 1b ab cae 1
2a 2b ac LBB 1
3a 3b ad LAA 1
4a 4b ae LBE 1
5a 5b af LEB 1
1b 1c ab bea 2
2b 2c ae aed 2
3b 3c ac ced 2

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-19 : 22:56:11

sorry, very confusing. Please post your table schema, sample data and the corresponding expected result

KH
[spoiler]Time is always against us[/spoiler]

JohnCash1
Starting Member

39 Posts

Posted - 2012-09-20 : 21:50:51

how to put table schema? thx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-20 : 21:51:59

quote:
Originally posted by JohnCash1

how to put table schema? thx

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

KH
[spoiler]Time is always against us[/spoiler]

JohnCash1
Starting Member

39 Posts

Posted - 2012-09-20 : 22:37:25

Hi,

http://postimage.org/image/recpd2nr3/

here is an image of the original and the result of how the information should look like. We have a hundreds of thousands of these records.

the hardest part would be to work with new information that keeps coming in and the information that are already done with don't work on the next time.

Please let me know how you could solve it.

Thanks

JohnCash1
Starting Member

39 Posts

Posted - 2012-09-20 : 22:44:19

Hi, this image is more clearer. I changed the header for the original to make it more clearer.

I just noticed your message, I created an image before you show me the table schema link. thanks

http://postimage.org/image/f0zv65y2n/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-21 : 02:37:34

what is the logic here ?

KH
[spoiler]Time is always against us[/spoiler]

JohnCash1
Starting Member

39 Posts

Posted - 2012-09-22 : 21:27:21

Ok. here is the logic.
I try to explain as best as I can, because is kind of complex.
in the original image, in the column id, we have 1 , 2 and 3.

the first 1, we take the first item on dte2 column.
the second 1, which is the last, we take the dte3 column that belong to it.

now we go to the 2 in the id column.
the first 2, we take the first item on dte2 column
the second 2, we take the first item on dte1 column that belong to it.
the second 2, we take the second item on the dte1 column that belong to it.
the third 2, we take the third item on the dte1 column that belong to it.
the fourth 2 which is the last 2, we take the last dte3 column that belong to it.

we do the same for id number 3.

but if id only appear once, then take the first item on dte2 column that belong to it.

Do you know if this can or how this can be done in sql server? and please note that there are millions of these records. Thanks

JohnCash1
Starting Member

39 Posts

Posted - 2012-09-22 : 21:37:04

quote:
now we go to the 2 in the id column.
the first 2, we take the first item on dte2 column
the second 2, we take the first item on dte1 column that belong to it.
the second 2, we take the second item on the dte1 column that belong to it.
the third 2, we take the third item on the dte1 column that belong to it.
the fourth 2 which is the last 2, we take the last dte3 column that belong to it.

sorry let me make correction to this quote.

now we go to the 2 in the id column.
the first 2, we take the first item on dte2 column
the second 2, we take the first item on dte1 column that belong to it.
the third 2, we take the second item on the dte1 column that belong to it.
the fourth 2 which is the last 2, we take the last dte3 column that belong to it.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-22 : 23:07:28

Couple of questions:

1. The desired output depends on some sort of ordering of the rows in the table. By definition, the rows in a table are an unordered collection. So you have to specify how to order it. In your case, would the ordering be id, nt?

2. Many of the solutions depend on the version of SQL Server you are using. The test data I created below assumes SQL 2008 or later. If you are using an earlier version of SQL Server, post the version number. You can run the query "select @@version" to get the SQL Server version number.

It would be easier for someone to respond if you give them the code to create the test data (which is what the link KH pointed to shows). For your question, I created the script for test data below:

--- SAMPLE INPUT DATA
CREATE TABLE #Original
(id INT, nt CHAR(1), dte1 CHAR(2), dte2 CHAR(2), dte3 CHAR(2));

INSERT INTO #Original VALUES
(1,'a','1a','2a','aa'),
(1,'b','1b','2b','ab'),

(2,'c','1c','2c','ac'),
(2,'d','1d','2d','ad'),
(2,'e','1e','2e','ae'),
(2,'f','1f','2f','af'),

(3,'g','1g','2g','ag'),
(3,'h','1h','2h','ah'),
(3,'i','1i','2i','ai');

--- DESIRED OUTPUT DATA
CREATE TABLE #Result
(id INT, nt CHAR(1), dte CHAR(2));
INSERT INTO #Result VALUES
(1,'a','2a'),
(1,'b','ab'),

(2,'c','2c'),
(2,'d','1c'),
(2,'e','1d'),
(2,'f','af'),

(3,'g','2g'),
(3,'h','1g'),
(3,'i','ai');

SELECT * FROM #Original;
SELECT * FROM #Result;

JohnCash1
Starting Member

39 Posts

Posted - 2012-09-23 : 10:01:04

yes the order is on the id column. I believe we are using management studio 2008.

What you did was to create the table and select from it. So do you know if there is an sql server code to get it from the original to the result? I think if you work with small amount of information and can get it to work with these you could work with large amount of information.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-23 : 10:15:29

Just a wild guess

; with cte as
(
	select	*, 
		rn 	= row_number() over (partition by id order by nt),
		cnt	= count(*) over (partition by id)
	from	#Original
)
select	c1.id, c1.nt,
	dte	= case	when 	c1.rn = 1 	
			then 	c1.dte2
			when	c1.rn = c1.cnt
			then	c1.dte3
			else	c2.dte1
			end
from	cte c1
	left join cte c2	on	c1.id	= c2.id
				and	c1.rn	= c2.rn + 1

KH
[spoiler]Time is always against us[/spoiler]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-23 : 10:33:14

quote:
Originally posted by JohnCash1

yes the order is on the id column. I believe we are using management studio 2008.

What you did was to create the table and select from it. So do you know if there is an sql server code to get it from the original to the result? I think if you work with small amount of information and can get it to work with these you could work with large amount of information.

Thanks

I was not trying to calculate the results. I was simply demonstrating what you should post so that someone can write a query to get you the solution. If I had to write a query, I would probably do it exactly the way that KH posted.

JohnCash1
Starting Member

39 Posts

Posted - 2012-09-24 : 00:13:41

Ok, not sure if that sql statement would be correct.

So you think something like that are do-able. some people think that can't be done with SQL statements. What do you think?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-09-24 : 01:54:11

quote:
Originally posted by JohnCash1

Ok, not sure if that sql statement would be correct.

We won't know also. You have to tell us is that what you want.

quote:
Originally posted by JohnCash1
So you think something like that are do-able. some people think that can't be done with SQL statements. What do you think?

Well, it depends on what is the requirement.

I actually don't really understand what is the logic required. You didn't really explain it clearly also. I just based on the sample data and expected result and guess.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources