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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need help with a query		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

future_is_me
Starting Member

USA
14 Posts
Posted - 11/27/2012 :  17:56:55  Show Profile  Reply with Quote
I have a table with the following format

Date Col1 Col2 Col3 col4 col5 Status
1/1/12 1 22 18 10 3 Rollback
1/2/12 28 10 15 13 34 Rollback
1/3/12 10 37 7 29 16 Rollover
1/4/12 36 11 27 25 19 Rollover
1/5/12 27 24 21 3 11 Rollover
1/6/12 31 28 11 20 34 Rollover
1/7/12 33 36 16 24 1 Rollover
1/8/12 17 1 30 21 34 Rollback
1/9/12 19 25 13 24 30 Rollback
1/10/12 19 33 8 31 36 Rollover
1/11/12 14 1 15 32 36 Rollover
1/12/12 26 7 31 23 17 Rollover
1/13/12 39 6 26 22 16 Rollover
1/14/12 14 1 39 11 36 Rollover
1/15/12 8 18 28 35 19 Rollover
1/16/12 4 11 17 9 8 Rollover
1/17/12 8 32 7 29 19 Rollover
1/18/12 17 7 16 25 33 Rollover
1/19/12 33 23 29 4 13 Rollback
1/20/12 13 27 9 38 17 Rollover
1/21/12 12 17 23 33 1 Rollover
1/22/12 33 31 17 12 38 Rollover
1/23/12 23 29 33 37 34 Rollover
1/24/12 26 23 34 35 12 Rollback
1/25/12 10 38 16 23 9 Rollover
1/26/12 29 13 30 27 22 Rollover
1/27/12 37 23 9 12 27 Rollback

I am looking for a script that checks repetition of column values between a rollover and a rollover followed by a rollback.

For example:
In row 3 the status column has value rollover. So I have to check for the immediate rollover after a rollback which has occured in row number 7. So between row 3 and row 7 i have to identify which numbers in col1 col2 col3 col4 col5 columns have appeared more than once with their dates and output those results to a different table.

In this case
36 appeared on 1/4/2012 and 1/7/2012
11 appeared on 1/4/2012, 1/5/12 and 1/6/2012
27 appeared on 1/4/2012 and 1/5/12

so on

so my end result has to be

tableA

Date Numbers_appeared
1/4/12 36,11,27
1/5/12 11,27
1/6/12 11
1/7/12 36




Is this possible? Experts..Thanks a bunch for your help on this.

namman
Constraint Violating Yak Guru

USA
285 Posts
Posted - 11/28/2012 :  13:15:08  Show Profile  Reply with Quote
Do you have a solution yet?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts
Posted - 11/28/2012 :  14:03:38  Show Profile  Reply with Quote
If you haven't gotten a solution yet:

I took a look at this, and your data does not seem to agree with your description. "So I have to check for the immediate rollover after a rollback which has occured in row number 7." The immediate rollover after a rollback seems to be on line 10


1/1/12	1	22	18	10	3	Rollback	1
1/2/12	28	10	15	13	34	Rollback	2
1/3/12	10	37	7	29	16	Rollover	3
1/4/12	36	11	27	25	19	Rollover	4
1/5/12	27	24	21	3	11	Rollover	5
1/6/12	31	28	11	20	34	Rollover	6
1/7/12	33	36	16	24	1	Rollover	7
1/8/12	17	1	30	21	34	Rollback	8
1/9/12	19	25	13	24	30	Rollback	9 <-- Rollback
1/10/12	19	33	8	31	36	Rollover	10<-- Rollover
1/11/12	14	1	15	32	36	Rollover	11
Or did you mean immediate rollover BEFORE a rollback?
Go to Top of Page

future_is_me
Starting Member

USA
14 Posts
Posted - 12/03/2012 :  10:03:42  Show Profile  Reply with Quote
No Problem. I was able to figure it out and script. Thanks
Go to Top of Page

namman
Constraint Violating Yak Guru

USA
285 Posts
Posted - 12/03/2012 :  13:30:10  Show Profile  Reply with Quote
If you figure out, you should share with others. It may help other people now or later.

Here is the place to share. People spend time to help you so when you have a solution, you should post here to help other.

Beside that, your solution may not be efficient or even incorrect. Posting solution is helping you too.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000