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)
 Help with a Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

future_is_me
Starting Member

USA
14 Posts
Posted - 12/03/2012 :  12:30:13  Show Profile  Reply with Quote
I have a table with the following DDL

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

select CallsAttempted as CA,CallsPlaced as CP,CallsCompleted as CC, No_Response as NR, VM, Status as ST from callslog

Date CA CP CC NR VM ST
2012-12-02 66 22 18 10 3 Forward
2012-12-01 28 10 15 13 34 Forward
2012-11-30 10 37 7 29 16 Complete
2012-11-29 36 11 27 25 19 Complete
2012-11-28 27 24 21 3 11 Complete
2012-11-27 31 28 11 20 34 Complete
2012-11-26 133 66 16 24 1 Complete
2012-11-25 17 1 30 21 34 Forward
2012-11-24 19 25 13 24 30 Forward
2012-11-23 19 33 8 31 36 Complete
2012-11-22 14 1 15 32 36 Complete
2012-11-21 26 7 31 23 17 Complete
2012-11-20 39 6 26 22 16 Complete
2012-11-19 14 1 39 11 36 Complete
2012-11-18 8 18 28 35 19 Complete
2012-11-17 4 11 17 9 8 Complete
2012-11-16 8 32 7 29 19 Complete
2012-11-15 17 7 16 25 33 Complete
2012-11-14 33 23 29 4 13 Forward
2012-11-13 13 27 9 38 17 Complete
2012-11-12 12 17 23 33 1 Complete
2012-11-11 33 31 17 12 38 Complete
2012-11-10 23 29 33 37 34 Complete
2012-11-09 26 23 34 35 12 Forward
2012-11-08 10 38 16 23 9 Complete
2012-11-07 29 13 30 27 22 Complete
2012-11-06 37 23 9 12 27 Forward


All I am looking for is a script that gives an output in the following format irrespective of status column. column CA has 66 on date 2012-12-02 and the last time 66 appeared was on 2012-11-26 in column CP and the difference in the dates is 5. Similarly CP has 22 on 2012-12-02 and the last time 22 appeared was on 2012-11-20 in column NR and the difference in the dates is 11 and so on for every column in every row.
So the output appears as below

Date Column
2012-12-02 5,11,12,0,3
2012-12-01 3,0,7,6,3

and so on

Is this possible?

visakh16
Very Important crosS Applying yaK Herder

India
47069 Posts
Posted - 12/03/2012 :  12:48:12  Show Profile  Reply with Quote
so do you mean you've to look for similar values in all the successive columns for each column?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

future_is_me
Starting Member

USA
14 Posts
Posted - 12/03/2012 :  13:59:32  Show Profile  Reply with Quote
Thanks for the reply visakh16. Yes look for similar values in the successive columns until the column value has appeared and identify the difference between the last time it appeared till the current date excluding the last time and current dates.

Thanks Again
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/03/2012 :  14:40:13  Show Profile  Reply with Quote
Here is one brute force way of doing it. You can probably simplify it by unpivoting the value columns
CREATE TABLE #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
)

INSERT INTO #CallsLog  VALUES 
('2012-12-02','66','22','18','10','3','Forward'),
('2012-12-01','28','10','15','13','34','Forward'),
('2012-11-30','10','37','7','29','16','Complete'),
('2012-11-29','36','11','27','25','19','Complete'),
('2012-11-28','27','24','21','3','11','Complete'),
('2012-11-27','31','28','11','20','34','Complete'),
('2012-11-26','133','66','16','24','1','Complete'),
('2012-11-25','17','1','30','21','34','Forward'),
('2012-11-24','19','25','13','24','30','Forward'),
('2012-11-23','19','33','8','31','36','Complete'),
('2012-11-22','14','1','15','32','36','Complete'),
('2012-11-21','26','7','31','23','17','Complete'),
('2012-11-20','39','6','26','22','16','Complete'),
('2012-11-19','14','1','39','11','36','Complete'),
('2012-11-18','8','18','28','35','19','Complete'),
('2012-11-17','4','11','17','9','8','Complete'),
('2012-11-16','8','32','7','29','19','Complete'),
('2012-11-15','17','7','16','25','33','Complete'),
('2012-11-14','33','23','29','4','13','Forward'),
('2012-11-13','13','27','9','38','17','Complete'),
('2012-11-12','12','17','23','33','1','Complete'),
('2012-11-11','33','31','17','12','38','Complete'),
('2012-11-10','23','29','33','37','34','Complete'),
('2012-11-09','26','23','34','35','12','Forward'),
('2012-11-08','10','38','16','23','9','Complete'),
('2012-11-07','29','13','30','27','22','Complete'),
('2012-11-06','37','23','9','12','27','Forward')


SELECT
	a.Date,
	DATEDIFF(dd,b.caDate,a.Date) AS CaDays,
	DATEDIFF(dd,c.cpDate,a.Date) AS CpDays
FROM
	#CallsLog a
	OUTER APPLY
	(
		SELECT TOP (1) x.Date AS caDate
		FROM #CallsLog x
		WHERE 
			a.[callsattempted] IN 
			( 
				x.[callsattempted]
				, x.[callsplaced]
				, x.[callscompleted]
				, x.[No_Response]
				, x.[VM]
			)
			AND x.Date < a.Date
		ORDER BY
			x.Date DESC
	) b
	OUTER APPLY
	(
		SELECT TOP (1) x.Date AS CpDate
		FROM #CallsLog x
		WHERE 
			a.[callsplaced] IN 
			( 
				x.[callsattempted]
				, x.[callsplaced]
				, x.[callscompleted]
				, x.[No_Response]
				, x.[VM]
			)
			AND x.Date < a.Date
		ORDER BY
			x.Date DESC
	) c


DROP TABLE #CallsLog
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