November 1, 2010 at 2:42 pm
ChangeIDrefOTSIDOttimeOldTypeOldlocalNewTypeNewlocalDateChangedDateClosed
77,39748,242Oct 29, 2010 11:50:00 AMDecision10/30/2010 6:00:00 AMParts16.00Oct 30, 2010 9:08:00 AMOct 31, 2010 10:50:00 AM
77,42248,242Oct 29, 2010 11:50:00 AMParts16.00Decision10/30/2010 1:00:00 PMOct 30, 2010 2:13:00 PMOct 31, 2010 10:50:00 AM
77,43248,242Oct 29, 2010 11:50:00 AMDecision10/30/2010 1:00:00 PMParts08.00Oct 30, 2010 3:53:00 PMOct 31, 2010 10:50:00 AM
77,47048,242Oct 29, 2010 11:50:00 AMParts08.00Solid10/31/2010 6:00:00 AMOct 30, 2010 11:20:00 PMOct 31, 2010 10:50:00 AM
77,35248,226Oct 29, 2010 3:00:00 AMDecision10/29/2010 6:00:00 PMDecision10/30/2010 4:00:00 AMOct 29, 2010 10:58:00 PMOct 31, 2010 4:20:00 PM
77,39448,226Oct 29, 2010 3:00:00 AMDecision10/30/2010 4:00:00 AMSolid10/30/2010 2:00:00 PMOct 30, 2010 9:03:00 AMOct 31, 2010 4:20:00 PM
77,44448,226Oct 29, 2010 3:00:00 AMSolid10/30/2010 2:00:00 PMSolid10/30/2010 8:00:00 PMOct 30, 2010 5:45:00 PMOct 31, 2010 4:20:00 PM
77,47248,226Oct 29, 2010 3:00:00 AMSolid10/30/2010 8:00:00 PMSolid10/31/2010 4:00:00 AMOct 31, 2010 12:03:00 AMOct 31, 2010 4:20:00 PM
77,48848,226Oct 29, 2010 3:00:00 AMSolid10/31/2010 4:00:00 AMSolid10/31/2010 10:00:00 AMOct 31, 2010 8:03:00 AMOct 31, 2010 4:20:00 PM
77,51348,226Oct 29, 2010 3:00:00 AMSolid10/31/2010 10:00:00 AMSolid10/31/2010 3:00:00 PMOct 31, 2010 3:03:00 PMOct 31, 2010 4:20:00 PM
77,51448,226Oct 29, 2010 3:00:00 AMSolid10/31/2010 3:00:00 PMSolid10/31/2010 11:45:00 AMOct 31, 2010 4:15:00 PMOct 31, 2010 4:20:00 PM
76,75447,874Oct 29, 2010 3:00:00 AMDecision10/22/2010 7:00:00 AMDecision10/22/2010 12:00:00 PMOct 22, 2010 11:05:00 AMOct 24, 2010 8:53:00 AM
76,76747,874Oct 21, 2010 3:50:00 PMDecision10/22/2010 12:00:00 PMSolid10/23/2010 4:00:00 AMOct 22, 2010 3:33:00 PMOct 24, 2010 8:53:00 AM
76,81247,874Oct 21, 2010 3:50:00 PMSolid10/23/2010 4:00:00 AMDecision10/23/2010 9:00:00 AMOct 23, 2010 7:08:00 AMOct 24, 2010 8:53:00 AM
76,83447,874Oct 21, 2010 3:50:00 PMDecision10/23/2010 9:00:00 AMSolid10/23/2010 3:00:00 PMOct 23, 2010 1:18:00 PMOct 24, 2010 8:53:00 AM
76,85747,874Oct 21, 2010 3:50:00 PMSolid10/23/2010 3:00:00 PMSolid10/23/2010 11:00:00 PMOct 23, 2010 5:38:00 PMOct 24, 2010 8:53:00 AM
76,87647,874Oct 21, 2010 3:50:00 PMSolid10/23/2010 11:00:00 PMSolid10/24/2010 4:00:00 AMOct 23, 2010 11:08:00 PMOct 24, 2010 8:53:00 AM
The above shown or the attached file is my table and I need some help regarding this table like
for each refotsid I need to find the total time with parts, solid and decision
lets take the first record in which oldtype is 'decision' and the newtype is 'parts' which means that till the 'DateChanged' time from the 'OTtime' the ID was in 'decision' and then it was changed to 'parts' so this is the decisiontime 'difference'
and then coming to the second the record the oldtype is 'parts' and the newtype is 'decision' so the 'difference' from the 'datechanged' when changed to 'parts' in the previous row to 'datechanged' when changed to 'decision' gives the 'partstime'
and so on...
please help me with this...
I have tried writing smoething like this but I am getting an error
select a.refeventid, a.oldtype, a.oldlocal, a.newtype, a.newlocal, a."timestamp",
datediff(minute, (select ("timestamp") from X where refeventid=b.refeventid
and "timestamp"< b."timestamp"), b."timestamp") different from X a
join X b
on a.refeventid = b.refeventid
error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
November 1, 2010 at 2:45 pm
The resulted output should be like this....
ChangeIDrefOTSIDOttimeOldTypeOldlocalNewTypeNewlocalDateChangedDateCloseddifference
77,39748,242Oct 29, 2010 11:50:00 AMDecision10/30/2010 6:00:00 AMParts16.00Oct 30, 2010 9:08:00 AMOct 31, 2010 10:50:00 AM1278 (Ottime-DateChanged)
77,42248,242Oct 29, 2010 11:50:00 AMParts16.00Decision10/30/2010 1:00:00 PMOct 30, 2010 2:13:00 PMOct 31, 2010 10:50:00 AM305 mins (Prev row DateChanged - DateChanged)
77,43248,242Oct 29, 2010 11:50:00 AMDecision10/30/2010 1:00:00 PMParts08.00Oct 30, 2010 3:53:00 PMOct 31, 2010 10:50:00 AM100 mins
77,47048,242Oct 29, 2010 11:50:00 AMParts08.00Solid10/31/2010 6:00:00 AMOct 30, 2010 11:20:00 PMOct 31, 2010 10:50:00 AM687 mins
77,35248,226Oct 29, 2010 3:00:00 AMDecision10/29/2010 6:00:00 PMDecision10/30/2010 4:00:00 AMOct 29, 2010 10:58:00 PMOct 31, 2010 4:20:00 PM478 mins (ottime-datechanged)
77,39448,226Oct 29, 2010 3:00:00 AMDecision10/30/2010 4:00:00 AMSolid10/30/2010 2:00:00 PMOct 30, 2010 9:03:00 AMOct 31, 2010 4:20:00 PM
77,44448,226Oct 29, 2010 3:00:00 AMSolid10/30/2010 2:00:00 PMSolid10/30/2010 8:00:00 PMOct 30, 2010 5:45:00 PMOct 31, 2010 4:20:00 PM
77,47248,226Oct 29, 2010 3:00:00 AMSolid10/30/2010 8:00:00 PMSolid10/31/2010 4:00:00 AMOct 31, 2010 12:03:00 AMOct 31, 2010 4:20:00 PM
77,48848,226Oct 29, 2010 3:00:00 AMSolid10/31/2010 4:00:00 AMSolid10/31/2010 10:00:00 AMOct 31, 2010 8:03:00 AMOct 31, 2010 4:20:00 PM
77,51348,226Oct 29, 2010 3:00:00 AMSolid10/31/2010 10:00:00 AMSolid10/31/2010 3:00:00 PMOct 31, 2010 3:03:00 PMOct 31, 2010 4:20:00 PM
77,51448,226Oct 29, 2010 3:00:00 AMSolid10/31/2010 3:00:00 PMSolid10/31/2010 11:45:00 AMOct 31, 2010 4:15:00 PMOct 31, 2010 4:20:00 PM
76,75447,874Oct 29, 2010 3:00:00 AMDecision10/22/2010 7:00:00 AMDecision10/22/2010 12:00:00 PMOct 22, 2010 11:05:00 AMOct 24, 2010 8:53:00 AM
76,76747,874Oct 21, 2010 3:50:00 PMDecision10/22/2010 12:00:00 PMSolid10/23/2010 4:00:00 AMOct 22, 2010 3:33:00 PMOct 24, 2010 8:53:00 AM
76,81247,874Oct 21, 2010 3:50:00 PMSolid10/23/2010 4:00:00 AMDecision10/23/2010 9:00:00 AMOct 23, 2010 7:08:00 AMOct 24, 2010 8:53:00 AM
76,83447,874Oct 21, 2010 3:50:00 PMDecision10/23/2010 9:00:00 AMSolid10/23/2010 3:00:00 PMOct 23, 2010 1:18:00 PMOct 24, 2010 8:53:00 AM
76,85747,874Oct 21, 2010 3:50:00 PMSolid10/23/2010 3:00:00 PMSolid10/23/2010 11:00:00 PMOct 23, 2010 5:38:00 PMOct 24, 2010 8:53:00 AM
76,87647,874Oct 21, 2010 3:50:00 PMSolid10/23/2010 11:00:00 PMSolid10/24/2010 4:00:00 AMOct 23, 2010 11:08:00 PMOct 24, 2010 8:53:00 AM
November 1, 2010 at 2:49 pm
Problem is this portion of your query, in regards to the error:
(select ("timestamp") from X where refeventid=b.refeventid
and "timestamp"< b."timestamp")
It needs to return a single entry for the function, and you're returning multiples. You'll need to analyze your data and figure out what you really want in there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 3:20 pm
I am unable to write the row number function and I could not know how to compare the two rows...
please help me with an idea what to use...
November 1, 2010 at 3:31 pm
Naidu,
Let's figure a few things out here. First, if you could take a look at the first link in my signature, and setup DDL and sample data that way, it would make things a lot easier.
Now, secondly, let's trim down the data you've got above to something a little more feasible to discuss:
ChangeID refOTSID Ottime OldType Oldlocal NewType Newlocal DateChanged DateClosed
77,397 48,242 Oct 29, 2010 11:50:00 AM Decision 10/30/2010 6:00:00 AM Parts 16.00 Oct 30, 2010 9:08:00 AM Oct 31, 2010 10:50:00 AM
77,422 48,242 Oct 29, 2010 11:50:00 AM Parts 16.00 Decision 10/30/2010 1:00:00 PM Oct 30, 2010 2:13:00 PM Oct 31, 2010 10:50:00 AM
77,432 48,242 Oct 29, 2010 11:50:00 AM Decision 10/30/2010 1:00:00 PM Parts 08.00 Oct 30, 2010 3:53:00 PM Oct 31, 2010 10:50:00 AM
77,470 48,242 Oct 29, 2010 11:50:00 AM Parts 08.00 Solid 10/31/2010 6:00:00 AM Oct 30, 2010 11:20:00 PM Oct 31, 2010 10:50:00 AM
We have four records for refOTSID. Ignoring Ottime since it repeats for this, From 10/30 at 6AM till 10/30 at 9:08 AM it's a parts entry. Then from 16.00 (useless)...
Alright, we'll skip a few rows since that's trash data.
3rd row. 10/30 1PM to 10/30 3:53PM it's a Parts entry, used to be Decision. then on row four... we've got trash data again (08.00 Solid)
The data is inconsistent. We're trying to puzzle out your requirements along with inconsistent data. This will not work. Please give us DDL, sample data that can all be run in a single query analyzer, and a sample result set of what you're expecting to see out of this, and perhaps we can help you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 3:44 pm
Thanks a lot for the reply Craig...
let me try DDL by looking at the first link but please give me some time...
November 1, 2010 at 5:25 pm
I tried create a table with the data in it...
create table mytable
(
changeid int,
refotsid int,
OTtime datetime,
Oldtype varchar(20),
Oldlocal varchar(50),
newtype varchar(20),
newlocal varchar(50),
DateChanged datetime,
DateClosed datetime
)
inserting the rows in it...
insert into mytable (Changeid, refotsid, OTtime, Oldtype, Oldlocal, newtype, newlocal, DateChanged, DateClosed)
select '77397', '48242','10/29/2010 11:50:00 AM','Decision', '10/30/2010 6:00:00 AM', 'Parts', '16.00', '10/30/2010 9:08:00 AM', '10/31/2010 10:50:00 AM'
union all
select '77422', '48242', '10/29/2010 11:50:00 AM', 'Parts','16.00', 'Decision', '10/30/2010 1:00:00 PM','10/30/2010 2:13:00 PM','10/31/2010 10:50:00 AM'
union all
select '77432', '48242', '10/29/2010 11:50:00 AM', 'Decision', '10/30/2010 1:00:00 PM', 'Parts', '08.00', '10/30/2010 3:53:00 PM', '10/31/2010 10:50:00 AM'
union all
select '77470', '48242', '10/29/2010 11:50:00 AM', 'Parts', '08.00', 'Solid', '10/31/2010 6:00:00 AM', '10/30/2010 11:20:00 PM', '10/31/2010 10:50:00 AM'
union all
select '77352', '48226', '10/29/2010 3:00:00 AM', 'Decision', '10/29/2010 6:00:00 PM', 'Decision', '10/30/2010 4:00:00 AM', '10/29/2010 10:58:00 PM', '10/31/2010 4:20:00 PM'
union all
select '77394', '48226', '10/29/2010 3:00:00 AM', 'Decision', '10/30/2010 4:00:00 AM', 'Solid', '10/30/2010 2:00:00 PM', '10/30/2010 9:03:00 AM', '10/31/2010 4:20:00 PM'
union all
select '77444', '48226', '10/29/2010 3:00:00 AM', 'Solid', '10/30/2010 2:00:00 PM', 'Solid', '10/30/2010 8:00:00 PM', '10/30/2010 5:45:00 PM', '10/31/2010 4:20:00 PM'
union all
select '77472', '48226', '10/29/2010 3:00:00 AM', 'Solid', '10/30/2010 8:00:00 PM', 'Solid', '10/31/2010 4:00:00 AM', '10/31/2010 12:03:00 AM', '10/31/2010 4:20:00 PM'
union all
select '77488', '48226', '10/29/2010 3:00:00 AM', 'Solid', '10/31/2010 4:00:00 AM', 'Solid', '10/31/2010 10:00:00 AM', '10/31/2010 8:03:00 AM', '10/31/2010 4:20:00 PM'
Now I need a new column XYZ in which
lets take for the same
refotsid = 48242 then for the first row I need XYZ as the dimediff(minute, DateChanged, OTtime)
where as from the second row I need XYZ as datediff(minute, DateChanged of prev row, DateChanged of present row)
and again for the next refotsid the same thing and so on...
hope this is clear and if not please let me know I will try something else....
Thanks in advance....
November 1, 2010 at 5:33 pm
Why does oldLocal have 16.00 and a real datetime value in it? Barring the rest, what are the non datetime values supposed to mean? You're overloading the column, obviously.
And what is dimediff()? I assume you meant datediff?
EDIT: For oldlocal and newlocal some rows have a nondate in one col, some in another, and some never have non-dates.
You're describing 'first row', 'second row'. We'll need business descriptions similar to: "When column x isdate() = 0 then determine the time between this row's oldlocal and the previous row's newlocal. The way to order the columns is by ID (or by new local, or whatever)."
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 5:42 pm
oldlocal or newlocal are supposed to be datetime but when its parts then need some parts to fix so they are not sure of the datetime and when coming to decision that is the time it should get ready...
and am sorry that is datediff()
Thanks in advace...
November 1, 2010 at 5:46 pm
I still need to have a better understanding of this before I can even ask what to do with the non-datetimes:
refotsid = 48242 then for the first row I need XYZ as the dimediff(minute, DateChanged, OTtime)
where as from the second row I need XYZ as datediff(minute, DateChanged of prev row, DateChanged of present row)
How do you know what happens to any specific row? What rules?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 5:55 pm
Craig Farrell (11/1/2010)
I still need to have a better understanding of this before I can even ask what to do with the non-datetimes:refotsid = 48242 then for the first row I need XYZ as the dimediff(minute, DateChanged, OTtime)
where as from the second row I need XYZ as datediff(minute, DateChanged of prev row, DateChanged of present row)
How do you know what happens to any specific row? What rules?
Sorry to bother you...
but for any refotsid for the first row XYZ should be a datediff(minute, DateChanged, OTtime)
and for the remaining rows in the same refotsid, XYZ should be datediff(minute, DateChanged of prev row, DateChange of current row)
DateChanged is always a datetime and coming to oldlocal and newlocal there is not going to be any operations on them.
November 1, 2010 at 6:00 pm
naidu4u (11/1/2010)
but for any refotsid for the first row XYZ should be a datediff(minute, DateChanged, OTtime)and for the remaining rows in the same refotsid, XYZ should be datediff(minute, DateChanged of prev row, DateChange of current row)
DateChanged is always a datetime and coming to oldlocal and newlocal there is not going to be any operations on them.
Ah hah! That's what I misunderstood from your first post. Okay, this makes a *lot* more sense now. So, the oldtype is the important one, right?
So, the first line would be decision from OTtime to DateChanged, then next row parts from Row-1.Datechanged to Row.Datechanged... etc. Correct?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 6:04 pm
yeah thank you...
thats right...
please help me how to achieve this...Actually I am from Cognos background so I dont have much familiarity with SQL and so I am trying to learn new things...
November 1, 2010 at 6:08 pm
There's a couple of different techniques and approaches to this and I don't want to give you an over-complicated one if unnecessary. You've posted in the 7.0/2k forums. Are you on SQL 2k? Or are you on 2k5/2k8? The difference is astounding, and I want to make sure.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 1, 2010 at 6:11 pm
Craig Farrell (11/1/2010)
There's a couple of different techniques and approaches to this and I don't want to give you an over-complicated one if unnecessary. You've posted in the 7.0/2k forums. Are you on SQL 2k?
Yeah I am using SQL 2000.
So, that was the reason rownumber() function wont work for me...
Thank you for helping...
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply