|
future_is_me
Starting Member
USA
14 Posts |
 Posted - 01/02/2013 : 17:25:36
|
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.
|
|
QUery Help
Topic Locked
