I have a raw data set like so:
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