SQL Server Forums - Need help with a query

Home | Weblogs | Forums | SQL Server Links

Active Forum Topics | Popular Articles | All Articles by Tag | SQL Server Books | About

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

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

Topic

future_is_me
Starting Member

USA
14 Posts

Posted - 11/27/2012 : 17:56:55

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

Do you have a solution yet?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/28/2012 : 14:03:38

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?

future_is_me
Starting Member

USA
14 Posts

Posted - 12/03/2012 : 10:03:42

No Problem. I was able to figure it out and script. Thanks

namman
Constraint Violating Yak Guru

USA
285 Posts

Posted - 12/03/2012 : 13:30:10

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.

Topic

Reply to Topic

Printer Friendly

Jump To:

SQL Server Forums

This page was generated in 0.03 seconds.