ADDING DATES

[sereleg]

I will appreciate any help with the following:
ID------PNO---ACTIONID--DAY----MONTH----YEAR
144 756 1 26 4 2005
145 756 2 27 4 2005
146 756 3 29 4 2005
147 756 4 30 4 2005
148 756 5 31 4 2005
149 756 6 1 5 2005
150 444 1 2 5 2005
151 444 2 4 5 2005
152 444 3 9 5 2006
153 444 4 11 5 2006
154 444 5 17 5 2006
155 444 6 22 5 2006


I need to show the duration takes to go from
an action ID to the next one. For example
I have two PNO one is 756 and the other 444
well for 756 I should get something like this:
PNO=756
ActionID= 1 took 1 day (note: 27/04/2005 - 28/04/2005)
ActionID= 2 took 2 days (note: 29/04/2005-27/04/2005)

Please if you have some idea let me know I will appreciate it.
Thanks

 

[Catadmin]

ActionID= 1 took 1 day (note: 27/04/2005 - 28/04/2005)
ActionID= 2 took 2 days (note: 29/04/2005-27/04/2005)

I'm not seeing how you got the time totals from the data you posted. You only show (as far as I can see) 1 date per ActionID. If that's the case, there is no way we can tell you how to get a "how long" without another date to compare.

MOF, I don't even see a 28/04/2005 date up in your data. And the ActionID2 in your quote is showing a reverse date setup (like the process ended 2 days before it started).

Could you explain better? Thanks,




Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[sereleg]

First, I will need to convert those three [day (nvarchar), month(nvarchar),yeaR(nvarchar)] fields as a regular Date format so I can used in my comparition.

Second I'll need to compare the dates from one actionid with the next(There are 6 actionID).


EXAMPLE:
If I want to get how long it takes from actionid=1 to get into actionid=2, I will have to substract the date for actionID=2 minus The dates for ActionID=1.

Another example will be:
If I want to know how long it takes to process ActionID=2 I'll have to substract the date for ActionID=3 minus the dates for actionID=2

Thanks for any help.

 

[vongrunt]

Assuming that (PNO, ACTIONID) produce unique ordered sequence (no dupes, desired sort), write this function first:

create function fn_dateSerial(@y smallint, @m tinyint, @d tinyint)
returns datetime
begin
	return DATEADD(dd, @d-1, DATEADD(mm, @m-1, DATEADD(yy, @y-1900, 0)))
end
go

... and then run this (blah is your table name):

select A.*,
datediff( dd, dbo.fn_dateSerial(A.YEAR, A.MONTH, A.DAY), dbo.fn_dateSerial(B.YEAR, B.MONTH, B.DAY)) as DAYCOUNT
from blah A
inner join 
(	select X.PNO, X.ACTIONID, min(Y.ACTIONID) as NEXTACTIONID
	from blah X
	left outer join blah Y on X.PNO=Y.PNO and X.ACTIONID<Y.ACTIONID
	group by X.PNO, X.ACTIONID
) AB on A.PNO=AB.PNO and A.ACTIONID=AB.ACTIONID
left outer join blah B on B.PNO=AB.PNO and B.ACTIONID=AB.NEXTACTIONID
order by A.ID

And btw. since when April has 31 days?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[sereleg]

Thank you for your help vongrunt, your solution was exactly what I was looking for.

Thanks so much