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 2012 Forums
 Transact-SQL (2012)
 QUery Help

Author  Topic 

future_is_me
Starting Member

14 Posts

Posted - 2013-01-02 : 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.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-01-02 : 17:30:18
For future reference, please do not cross post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=181811
Go to Top of Page
   

- Advertisement -