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 2012 Forums
 Transact-SQL (2012)
 QUery Help
 Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

future_is_me
Starting Member

USA
14 Posts

Posted - 01/02/2013 :  17:25:36  Show Profile
I have a table with the following format

CREATE TABLE [dbo].[CallsLog](
[Date] [datetime] NOT NULL,
[callsattempted] [int] NOT NULL,
[callsplaced] [int] NOT NULL,
[callscompleted] [int] NOT NULL,
[No_Response] [int] NOT NULL,
[VM] [int] NOT NULL,
[Status] [nvarchar](20) NULL
) ON [PRIMARY]

GO

Date CallsAttempted CallsPlaced CallsCompleted No Response VM Status
1/1/2012 100 22 18 10 3 Forward
1/2/2012 28 10 15 13 34 Forward
1/3/2012 10 37 7 29 16 Complete
1/4/2012 36 11 27 25 19 Complete
1/5/2012 27 24 21 3 11 Complete
1/6/2012 31 28 11 20 34 Complete
1/7/2012 33 36 16 24 1 Complete
1/8/2012 17 1 30 21 34 Forward
1/9/2012 19 25 13 24 30 Forward
1/10/2012 19 33 8 31 36 Complete
1/11/2012 14 1 15 32 36 Complete
1/12/2012 26 7 31 23 17 Complete
1/13/2012 39 6 26 22 16 Complete
1/14/2012 14 1 39 11 36 Complete
1/15/2012 8 18 28 35 19 Complete
1/16/2012 4 11 17 9 8 Complete
1/17/2012 8 32 7 29 19 Complete
1/18/2012 17 7 16 25 33 Complete
1/19/2012 33 23 29 4 13 Forward
1/20/2012 13 27 9 38 17 Complete
1/21/2012 12 17 23 33 1 Complete
1/22/2012 33 31 17 12 38 Complete
1/23/2012 23 29 33 37 34 Complete
1/24/2012 26 23 34 35 12 Forward
1/25/2012 10 38 16 23 9 Complete
1/26/2012 29 13 30 27 22 Complete
1/27/2012 37 23 9 12 27 Forward

I am looking for a script that checks repetition of column values between a "complete" and a "complete" followed by a "Forward"

For example:
In row 3 the status column has value complete. So I have to check for the immediate "complete" after a "forward" which has occured in row number 7. So between row 3 and row 7 i have to identify which numbers in Callsttempted,CallsPlaced,CallsCompleted,No Response,VM columns have appeared more than once with their dates and then 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
and 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.

robvolk
Most Valuable Yak

USA
15732 Posts

Posted - 01/02/2013 :  17:30:18  Show Profile  Visit robvolk's Homepage
For future reference, please do not cross post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181811
Go to Top of Page
  Previous Topic Topic Next Topic  
 Topic Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000