Thursday, March 8, 2012

Assign Set Numbers

I have a raw data set like so:

OrdNumberEventDateEventCodeRoomNum7900059-15/1/07 6:41 AMPB31007900059-15/1/07 6:49 AMPL31007900059-15/1/07 8:09 AMPB31007900059-15/1/07 8:16 AMPL31007900059-15/1/07 8:53 AMPB31007900059-15/1/07 9:02 AMPL31007900059-15/1/07 10:04 AMPB31007900059-15/1/07 10:16 AMPL31007900059-15/1/07 12:20 PMPB31007900059-15/1/07 12:37 PMPL31007900059-15/1/07 1:59 PMPB31007900059-15/1/07 2:13 PMPL31007900059-15/2/07 6:49 AMPB31007900059-15/2/07 6:59 AMPL3100

I need to identify where the time stamps for PB and PL are sets. They all have the same OrdNumber and RoomNum and yet I need to capture the time difference for each set of PB and PL. I will use the accumulative time from these sets for this RoomNum in a report.

I have an idea that should work, but can't get the syntax right. If I were to keep these in ascending order and then assign a number for each event code separately I should have the same number assigned for each code which would serve as a set number. So, if I were to create a variable +1 that would look at "PB" and then reset when the OrderNum changes. Then do the same for the 'PL'.

Could I do that step as an Update to a designated "Set" field from a temp table in my code?

My end result should have the number 1 in the first two I have in bold type, then 2, etc. I have very little experience at this level and could use some help.

Thank you.

You can do exactly the same with subqueries and creating uniqueids with ROW_NUMBER(), try this:

Code Snippet

select subPB.OrdNumber, subPB.RoomNum, datediff(minute, subPB.eventdate, subPL.eventdate) as TimeElapsed
from
(select OrdNumber,EventDate,EventCode,RoomNum, ROW_NUMBER() over ( order by Eventdate)as id
from tbltest2 where Eventcode='PB'
) subPB,
(select OrdNumber,EventDate,EventCode,RoomNum, Row_number() over( order by Eventdate)as id
from tbltest2 where Eventcode='PL'
) subPL
where subPB.ordnumber=subPL.ordnumber and subPB.roomnum = subPL.roomnum
and subPB.id=subPL.id

|||

I can't get this to work because it is not accepting row_number() and 'Over'. I am working in Embarcardero Sybase Rapid SQL if that helps.

|||

do you know what your underlying DataBase is? The code I posted is for SQL Server 2005.

If it's some other database, you need to figure how to generate ids for that DB instead of using "ROW_Number() over"

|||

How about this query..

Code Snippet

CreateTable #data(

[OrdNumber]Varchar(100),

[EventDate]dateTime,

[EventCode]Varchar(100),

[RoomNum]int

);

InsertInto #dataValues('7900059-1','5/1/07 6:41 AM','PB','3100');

InsertInto #dataValues('7900059-1','5/1/07 6:49 AM','PL','3100');

InsertInto #dataValues('7900059-1','5/1/07 8:09 AM','PB','3100');

InsertInto #dataValues('7900059-1','5/1/07 8:16 AM','PL','3100');

InsertInto #dataValues('7900059-1','5/1/07 8:53 AM','PB','3100');

InsertInto #dataValues('7900059-1','5/1/07 9:02 AM','PL','3100');

InsertInto #dataValues('7900059-1','5/1/07 10:04 AM','PB','3100');

InsertInto #dataValues('7900059-1','5/1/07 10:16 AM','PL','3100');

InsertInto #dataValues('7900059-1','5/1/07 12:20 PM','PB','3100');

InsertInto #dataValues('7900059-1','5/1/07 12:37 PM','PL','3100');

InsertInto #dataValues('7900059-1','5/1/07 1:59 PM','PB','3100');

InsertInto #dataValues('7900059-1','5/1/07 2:13 PM','PL','3100');

InsertInto #dataValues('7900059-1','5/2/07 6:49 AM','PB','3100');

InsertInto #dataValues('7900059-1','5/2/07 6:59 AM','PL','3100');

Select*,Identity(int,1,1) RowIdInto #PBFrom #DataWhere [EventCode]='PB'ORDERBY 2

Select*,Identity(int,1,1) RowIdInto #PLFrom #DataWhere [EventCode]='PL'ORDERBY 2

Select

*

from

#PB PB

Join #PL PL

On

PB.RowId=PL.RowId

|||

Hi Manivannan.D.Sekaran,

This method is not reliable.

The behavior of the IDENTITY function when used with SELECT INTO or INSERT .. SELECT queries that contain an ORDER BY clause

http://support.microsoft.com/kb/273586

AMB

|||

Thank you very much AMB.. Its really surprise for me (I learnt new thing)

Then, How about this approach..

Code Snippet

Create Table #data (

[OrdNumber] Varchar(100) ,

[EventDate] dateTime ,

[EventCode] Varchar(100) ,

[RoomNum] int

);

Insert Into #data Values('7900059-1','5/1/07 6:41 AM','PB','3100');

Insert Into #data Values('7900059-1','5/1/07 6:49 AM','PL','3100');

Insert Into #data Values('7900059-1','5/1/07 8:09 AM','PB','3100');

Insert Into #data Values('7900059-1','5/1/07 8:16 AM','PL','3100');

Insert Into #data Values('7900059-1','5/1/07 8:53 AM','PB','3100');

Insert Into #data Values('7900059-1','5/1/07 9:02 AM','PL','3100');

Insert Into #data Values('7900059-1','5/1/07 10:04 AM','PB','3100');

Insert Into #data Values('7900059-1','5/1/07 10:16 AM','PL','3100');

Insert Into #data Values('7900059-1','5/1/07 12:20 PM','PB','3100');

Insert Into #data Values('7900059-1','5/1/07 12:37 PM','PL','3100');

Insert Into #data Values('7900059-1','5/1/07 1:59 PM','PB','3100');

Insert Into #data Values('7900059-1','5/1/07 2:13 PM','PL','3100');

Insert Into #data Values('7900059-1','5/2/07 6:49 AM','PB','3100');

Insert Into #data Values('7900059-1','5/2/07 6:59 AM','PL','3100');

Select * Into #PB From #Data Where [EventCode]='PB' ORDER BY 2

Select * Into #PL From #Data Where [EventCode]='PL' ORDER BY 2

Alter table #PB Add RowId int Identity(1,1);

Alter table #PL Add RowId int Identity(1,1);

Select

*

from

#PB PB

Join #PL PL

On

PB.RowId=PL.RowId

|||

I am using Sybase Embarcardero Rapid SQL 7.4

I think there is and identity function and I am trying to figure that out

|||

Hi Manivannan.D.Sekaran,

Sure it could work. what about giving a try to:

create table #PB (

RowId int not null unique clustered,

...

)

create table #PL (

RowId int not null unique clustered,

...

)

insert into #PB (c1, ..., cn)

select c1, ..., cn

...

order by [EventDate]

insert into #PL (c1, ..., cn)

select c1, ..., cn

...

order by [EventDate]

...

AMB

No comments:

Post a Comment