Help with a Query - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Help with a Query

Author

Topic

future_is_me
Starting Member

14 Posts

Posted - 2012-12-03 : 12:30:13

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

52326 Posts

Posted - 2012-12-03 : 12:48:12

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/

future_is_me
Starting Member

14 Posts

Posted - 2012-12-03 : 13:59:32

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-03 : 14:40:13

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources