Need help with a query - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help with a query

Author

Topic

future_is_me
Starting Member

14 Posts

Posted - 2012-11-27 : 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

285 Posts

Posted - 2012-11-28 : 13:15:08

Do you have a solution yet?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-28 : 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

14 Posts

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

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

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-12-03 : 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources