problems with data presentation

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

Transact-SQL (2005)

problems with data presentation

Author

Topic

LLatinsky
Starting Member

38 Posts

Posted - 2010-09-24 : 17:39:42

Hello, the following code will create a table with data of interest.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[housingTmp]') AND type in (N'U'))
DROP TABLE [dbo].[housingTmp]

select * into dbo.housingTmp
from (select
'Bob' as EmpID, '2010-05-27 22:34:12.000' AssignDatetime, '2010-06-01 16:55:37.000'
EndAssignDatetime, 'TX' StateID
union all select
'Bob', '2010-06-01 16:55:37.000', '2010-06-02 11:29:25.000', 'TX'
union all select
'Bob', '2010-06-02 11:29:25.000', '2010-06-02 12:54:46.000', 'TX'
union all select
'Bob', '2010-06-02 12:54:46.000', '2010-06-02 13:09:19.000', 'OK'
union all select
'Bob', '2010-06-02 13:09:19.000', '2010-06-04 01:05:47.000', 'TX'
union all select
'Bob', '2010-06-04 01:05:47.000', '2010-06-04 01:09:40.000', 'TX'
union all select
'Bob', '2010-06-04 01:09:40.000', '2010-06-04 02:14:56.000', 'TX'
union all select
'Bob', '2010-06-04 02:14:56.000', '2010-06-06 05:37:31.000', 'TX'
union all select
'Bob', '2010-06-06 05:37:31.000', '2010-06-08 07:56:07.000', 'TX'
union all select
'Bob', '2010-06-08 07:56:07.000', '2010-06-09 14:11:47.000', 'TX'
union all select
'Bob', '2010-06-09 14:11:47.000', '2010-06-12 13:36:09.000', 'MI'
union all select
'Bob', '2010-06-12 13:36:09.000', '2010-06-12 13:37:00.000', 'MI'
union all select
'Bob', '2010-06-12 13:37:00.000', '2010-07-11 03:08:55.000', 'MI'
union all select
'Paul' , '2010-06-16 20:23:28.000', '2010-06-17 11:10:39.000', 'TX'
union all select
'Paul' , '2010-06-17 11:10:39.000', '2010-06-17 13:15:32.000', 'MI'
union all select
'Paul' , '2010-06-17 13:15:32.000', '2010-06-17 13:19:15.000', 'TX'
union all select
'Paul' , '2010-06-17 13:19:15.000', '2010-06-18 15:53:45.000', 'TX'
union all select
'Paul' , '2010-06-18 15:53:45.000', '2010-06-19 10:44:43.000', 'MI'
union all select
'Paul' , '2010-06-19 10:44:43.000', '2010-06-22 15:35:28.000', 'MI'
union all select
'Paul' , '2010-06-22 15:35:28.000', '2010-06-23 15:48:49.000', 'MI'
union all select
'Paul' , '2010-06-23 15:48:49.000', '2010-06-23 15:58:45.000', 'MI'
union all select
'Paul' , '2010-06-23 15:58:45.000', '2010-06-24 13:34:28.303', 'MI' )a

select*from housingTmp order by Empid, assigndatetime

need to display this data as distinct contiguius assignments per employee per state like so:

StateID EmpID AssignDatetime EndAssignDatetime

TX Paul 2010-06-16 20:23:28.000 2010-06-17 11:10:39.000
MI Paul 2010-06-17 11:10:39.000 2010-06-17 13:15:32.000
TX Paul 2010-06-17 13:15:32.000 2010-06-18 15:53:45.000
MI Paul 2010-06-18 15:53:45.000 2010-06-24 13:34:28.303

TX Bob 2010-05-27 22:34:12.000 2010-06-02 12:54:46.000
OK Bob 2010-06-02 12:54:46.000 2010-06-02 13:09:19.000
TX Bob 2010-06-02 13:09:19.000 2010-06-09 14:11:47.000
MI Bob 2010-06-09 14:11:47.000 2010-07-11 03:08:55.000

Is it possible to write a T-sql batch that would help compress
some of the records into 1 contiguous record as in situations shown above. I was trying to use the fact
that for every employee every following record starts when the previous record ends, but failed miserably.
Does anybody know what can be done in a situation like this?
thank you

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-09-24 : 19:04:16

Try something like this:


SELECT H.StateID,
       H.EmpID,
       MIN(H.AssignDatetime) AS AssignDatetime,
       MAX(H.EndAssignDatetime) AS EndAssignDatetime
  FROM (SELECT H.StateID, H.EmpID, H.AssignDatetime, H.EndAssignDatetime,
               ROW_NUMBER() OVER(PARTITION BY H.EmpID
                                     ORDER BY H.AssignDatetime) -
               ROW_NUMBER() OVER(PARTITION BY H.StateID, H.EmpID
                                     ORDER BY H.AssignDatetime) AS grp
          FROM housingTmp AS H) AS H
 GROUP BY H.StateID, H.EmpID, grp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-25 : 00:51:24

[code]
CREATE CLUSTERED INDEX IDX_Clsut ON dbo.housingTmp(EmpID,StateID,AssignDatetime,EndAssignDatetime)

DECLARE @EmpID varchar(100),@StateID varchar(100),@AssignDatetime datetime,@EndAssignDatetime datetime
SELECT TOP 1 @EmpID=EmpID,
@StateID =StateID,
@AssignDatetime =AssignDatetime,
@EndAssignDatetime=EndAssignDatetime
FROM dbo.housingTmp

UPDATE t
SET @AssignDatetime=t.AssignDatetime =CASE WHEN EmpID=@EmpID AND StateID=@StateID AND AssignDatetime=@EndAssignDatetime THEN @AssignDatetime ELSE AssignDatetime END,
@EmpID=EmpID,
@StateID =StateID,
@EndAssignDatetime=EndAssignDatetime
FROM dbo.housingTmp t

SELECT EmpID,StateID,AssignDatetime,MAX(EndAssignDatetime)AS EndAssignDatetime
FROM dbo.housingTmp
GROUP BY EmpID,StateID,AssignDatetime
ORDER BY EmpID,AssignDatetime

output
-------------------------------------
EmpID StateID AssignDatetime EndAssignDatetime
Bob TX 2010-05-27 22:34:12.000 2010-06-02 12:54:46.000
Bob OK 2010-06-02 12:54:46.000 2010-06-02 13:09:19.000
Bob TX 2010-06-02 13:09:19.000 2010-06-09 14:11:47.000
Bob MI 2010-06-09 14:11:47.000 2010-07-11 03:08:55.000
Paul TX 2010-06-16 20:23:28.000 2010-06-17 11:10:39.000
Paul MI 2010-06-17 11:10:39.000 2010-06-17 13:15:32.000
Paul TX 2010-06-17 13:15:32.000 2010-06-18 15:53:45.000
Paul MI 2010-06-18 15:53:45.000 2010-06-24 13:34:28.303

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

LLatinsky
Starting Member

38 Posts

Posted - 2010-09-27 : 16:26:38

thank both of you so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 13:22:00

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources