Rows to Columns DateDiff - 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 2000 Forums

Transact-SQL (2000)

Rows to Columns DateDiff

Author

Topic

@NeedHelp
Starting Member

3 Posts

Posted - 2008-03-18 : 16:00:47

Hello,

I am a newbie. I have the following table:

Tix# Created Transaction Modified
0001 2006-12-29 Close 2007-03-14
0001 2006-12-29 Reopen 2007-03-14
0001 2006-12-29 Close 2007-03-14
0001 2006-12-29 Reopen 2007-04-02
0001 2006-12-29 Close 2007-04-18
0002 2008-01-09 Close 2008-01-29
0002 2008-01-09 Reopen 2008-02-07
0002 2008-01-09 Close 2008-02-08

What I want to get:
Tix# |Created |Close 1 |Open 2 |Close 2 |Open 3| Close 3
0001 |2006-12-29 |2007-03-14 |2007-03-14 |2007-03-14 |2007-04-02 |2007-04-18
0002 |2008-01-09 |2008-01-29 |2008-02-07 |2008-02-08 ||

0001 Total Days Open:91
0002 Total Days Open:21

I have tried multiple self-joins and subqueries but keep coming back to the issue that the number of rows is unknown for each tix. That is , a tix can be open an infinite number of times and the SQL must accommodate all rows.

Perhaps a stored procedure can do it but I am not too familiar with writing one.

Thanks in advance.

bbasir
Yak Posting Veteran

76 Posts

Posted - 2008-03-18 : 16:44:25

You can solve this issue using a cross tab query or pivot table. you will need to pivot the rows that you want to see as columns and then get the count of the date column, in order to get the summary data for those.

@NeedHelp
Starting Member

3 Posts

Posted - 2008-03-18 : 18:14:29

Thank you for your quick reply.

Its SQL 2000 so there is no Pivot fct.

I have tried pivoting the rows to get columns using Min and Max summary fcts. It works for Tix 0002 but with Tix 0001 there are 3 Close dates. Min and Max gets me the first and last close dates but not the middle close date. And what if there are more than 3 closed dates?

@NeedHelp
Starting Member

3 Posts

Posted - 2008-03-19 : 16:35:37

Here is the code to produce the table and my attempt at pivoting.
Notice that the 0002 works but 0001 is missing the middle set of Reopen and Close records.

SET NOCOUNT ON
CREATE TABLE #tix (tixNo varchar(10), created datetime, trans varchar(10), mod datetime)

INSERT #tix VALUES ('0001','2006-12-29 12:39:43.333', 'Close', '2007-03-14 19:35:11.217')
INSERT #tix VALUES ('0001','2006-12-29 12:39:43.333','Reopen', '2007-03-14 19:36:21.697')
INSERT #tix VALUES ('0001','2006-12-29 12:39:43.333','Close', '2007-04-02 13:46:37.597')
INSERT #tix VALUES ('0001','2006-12-29 12:39:43.333','Reopen', '2007-04-18 18:13:20.300')
INSERT #tix VALUES ('0001','2006-12-29 12:39:43.333','Close', '2007-04-18 00:00:00.000')
INSERT #tix VALUES ('0002','2008-01-09 16:42:44.243','Close', '2008-01-29 23:03:10.770')
INSERT #tix VALUES ('0002','2008-01-09 16:42:44.243','Reopen', '2008-02-07 15:02:17.373')
INSERT #tix VALUES ('0002','2008-01-09 16:42:44.243','Close', '2008-02-07 15:03:16.817')

GO

SELECT t.tixNO,
Min(t.Created) AS Open_First,
Min(CASE t.TRANS WHEN 'Close' THEN t.MOD ELSE null END) AS Close_First,
Min(CASE t.TRANS WHEN 'Reopen' THEN t.Mod ELSE null END) AS Open2,
Max(CASE t.TRANS WHEN 'Close' THEN t.Mod ELSE null END) AS Close_Last

From #tix t

GROUP BY t.tixNO
DROP TABLE #tix

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources