SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Query Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

future_is_me
Starting Member

USA
14 Posts

Posted - 01/02/2013 :  17:26:39  Show Profile  Reply with Quote
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.

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/02/2013 :  20:28:03  Show Profile  Reply with Quote
You have already posted this before. AS Sunitabeck said, can you post what you have tried and share so we can help it out?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180994
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000