Query figuring start date from complete date 1

[LadyLin]

I need help analyzing if this can be done.
I have a table of ops, each having a resource,
each resource takes so many days to perform.
I have a resource table that shows how many days it takes to complete 1 op, 2 ops, 3 ops etc. of each resource.
I have a completion date for the ops.
I want to figure backwards, when each op needs to complete.
[tt]
My tables:
Completion date 12/30/2009

_____________
Ops resource
_____________
1 soft
2 soft
3 vendor
4 soft
5 soft
6 soft
7 heattreat
8 vendor
9 hard
10 hard
11 hard
_____________________
Resources
_____________________
Resource #ops Days
soft 1 2
soft 2 4
soft 3 10
vendor 1 7
heattreat 1 5
hard 1 2
hard 2 5
hard 3 9

The results I want

Op Resource Completion date
________________________
11 hard 12/30/09
10 hard 12/26/09
9 hard 12/23/09
8 vendor 12/21/09
7 heattreat 12/14/09
6 soft 12/9/09
5 soft 12/3/09
4 soft 12/1/09
3 vendor 11/29/09
2 soft 11/22/09
1 soft 11/20/09

11/18/09 would be the start date.
Any thoughts would be appreciated.
[/tt]

 

[bborissov]

And how do you calculate this?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LadyLin]

What I think needs to be done is to sequence through the operations, adding 1 to a counter until the resource changes. This gives the # of ops done at the resource, then join to a table that gives the # of days at the maximum. Then either total up the # of days and subtract to find the start date, or go backwards, op by op.
____________
for the example
[tt]
_____________
Ops resource
_____________
1 soft 1 op soft 2 days
2 soft 2 ops soft 4 days 4 days soft
3 vendor 1 op vendor 7 days 7 days vendor
4 soft 1 op soft 2 days
5 soft 2 ops soft 4 days
6 soft 3 ops soft 10 days 10 days soft
7 heattreat 1 op heattreat 5 days 5 days heattreat
8 vendor 1 op vendor 7 days 7 days vendor
9 hard 1 op hard 2 days
10 hard 2 ops hard 5 days
11 hard 3 ops hard 9 days 9 days hard
42 days total

so completion date 12/30/09 minus 42 days
= start date 11/20/09 [/tt]

 

[drlex]

Boris,
As I read it, the time taken on op.s is dependent on the sequence, so whilst one soft op. takes 2 days, three consecutive ones take 10 days, rather than 3 x 2 days.
Thus calculating the overall time needs to consider the order of identical op. types rather than a simple join on op. duration.

soi là, soi carré

 

[bborissov]

If your data always look like this:

----- Preparing test data, you don't need this
DECLARE @Test TABLE (Id int, op varchar(20), OpsNo int, DaysTaken Int)
INSERT INTO @Test VALUES(1,'soft',1,2)
INSERT INTO @Test VALUES(2,'soft',2,4)
INSERT INTO @Test VALUES(3,'vendor',1,7)
INSERT INTO @Test VALUES(4,'soft',1,2)
INSERT INTO @Test VALUES(5,'soft',2,4)
INSERT INTO @Test VALUES(6,'soft',3,10)
INSERT INTO @Test VALUES(7,'heattreat',1,5)
INSERT INTO @Test VALUES(8,'vendor',1,7)
INSERT INTO @Test VALUES(9,'hard',1,2)
INSERT INTO @Test VALUES(10,'hard',2,5)
INSERT INTO @Test VALUES(11,'hard',3,9)
---- End preparing


--- That is StartDate, if you set it other way, you don't need this also
DECLARE @StartDate datetime
SET @StartDate = '20091230'
---- End


DECLARE @EndDate datetime


--- Just change @Test to your actual table name
SELECT @EndDate = @StartDate - SUM(tst.DaysTaken)
FROM @Test Tst 
LEFT JOIN @Test tst1 ON Tst1.Id = tst.Id+1 AND Tst.op = tst1.op 
WHERE tst1.Id IS NULL



SELECT @EndDate

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LadyLin]

The resource table always looks the same, the first table will change, meaning the # of ops,the order and resources needed will change. (The table is the list of operations in a workorder.) I will have an end date for the workorder.
I am going to try your solution. Counting the # of consecutive ops might pose me a challenge unless it is taken care of in your select statment. I'm trying to understand the logic but I'm not quite there yet. Thank you.

 

[bborissov]

The logic is simple:
Get the days only from these records where OP is changed.
As I understand you keep the whole number of days in the last of the OP records, isn't it?

I asked about is always the data same, because of ID!
That is crucial for the query. If your IDs are not in sequence order, then you'll get nothing or worse you'll get a totally wrong result.




Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LadyLin]

Boris,
I think I may have misled you with my example.

drlex interpreted it better - he wrote--

"the time taken on op.s is dependent on the sequence, so whilst one soft op. takes 2 days, three consecutive ones take 10 days, rather than 3 x 2 days.
Thus calculating the overall time needs to consider the order of identical op. types rather than a simple join on op. duration. "
I have 2 tables, one with the seq of ops & resources.
The other with resources & # of consecutive ops & days

I want to combine them, so I can do the date calculation.

 

[bborissov]

You really lost me here
Ok I can (or I think I can how yo get this:

4    soft        1 op soft        2 days
5    soft        2 ops soft       4 days
6    soft        3 ops soft      10 days  10 days soft

2+4+4 = 10 (maybe)

But how you get this

9    hard        1 op hard        2 days
10   hard        2 ops hard       5 days
11   hard        3 ops hard       9 days   9 days hard

????
2+5+2 ?????????????????
Why the calculations are not the same?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LadyLin]

I'm sorry this is so confusing,I'll try again.
The problem is that there is a seq. of ops
[tt]
Ops (I'm leaving out the #s)
_____________

soft
soft 2 consecutive soft ops = 4 days
vendor 1 vendor op = 7 days
soft
soft
soft 3 consecutive soft ops = 10 days
heattreat 1 heat treat = 5 days
vendor 1 vendor op = 7 days
hard
hard
hard 3 consecutive hard ops = 9 days

So I can only get the # of days after knowing how many consecutive ops of the same resource. They add up to 42 days. Now I can calculate start date from completion date - 42.
Does that help?
[/tt]

 

[bborissov]

OK, but HOW do you know how many days take conseq. Ops?
What if "soft" op takes 4 conseq. ops? How many days are here?
How do you know that?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LadyLin]

I will keep that in another table.
See 1st post Resources table. I shortened it for posting purposes.
[tt]
_____________________
Resources
_____________________
Resource #ops Days
soft 1 2
soft 2 4
soft 3 10
soft 4 10
vendor 1 7
vendor 2 10
heattreat 1 5
hard 1 2
hard 2 5
hard 3 9
hard 4 13
etc

so I would join ops - soft/3 to resources soft/3 to get 10 days for 3 consecutive soft ops.
[/tt]

 

[bborissov]

HURRAY!!!
Now I get it
BTW What version of SQL Server you use?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LadyLin]

I have SQL 2005. I hope you can come up with something.
I have to call it a day. I'll check in the morning. Thanks for staying with this. I've been trying to come up with a solution for days.

 

[bborissov]

Is this what you want?

[COLOR=green]----- Preparing test data, you don't need this
[/color][COLOR=blue]DECLARE[/color] @Test [COLOR=blue]TABLE[/color] (Id [COLOR=blue]int[/color], op [COLOR=blue]varchar[/color](20))
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](1,[COLOR=red]'soft'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](2,[COLOR=red]'soft'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](3,[COLOR=red]'vendor'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](4,[COLOR=red]'soft'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](5,[COLOR=red]'soft'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](6,[COLOR=red]'soft'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](7,[COLOR=red]'heattreat'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](8,[COLOR=red]'vendor'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](9,[COLOR=red]'hard'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](10,[COLOR=red]'hard'[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Test [COLOR=blue]VALUES[/color](11,[COLOR=red]'hard'[/color])



[COLOR=blue]DECLARE[/color] @Resources [COLOR=blue]TABLE[/color] (op [COLOR=blue]varchar[/color](20), OpNo [COLOR=blue]int[/color], DaysTaken [COLOR=blue]int[/color])
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'soft'[/color],1,2)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'soft'[/color],2,4)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'soft'[/color],3,10)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'soft'[/color],4,10)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'vendor'[/color],1,7)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'vendor'[/color],2,10)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'heattreat'[/color],1,5)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'hard'[/color],1,2)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'hard'[/color],2,5)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'hard'[/color],3,9)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @Resources [COLOR=blue]VALUES[/color]([COLOR=red]'hard'[/color],4,13)

[COLOR=green]---- End preparing
[/color]

[COLOR=blue]SELECT[/color] TestFinal.Op, 
       (Id - [COLOR=#FF00FF]ISNULL[/color](MaxId,0)) [COLOR=blue]AS[/color] OperNumbers,
       Rsrs.DaysTaken
[COLOR=blue]FROM[/color] (
[COLOR=blue]SELECT[/color] TestMe.*, 
      ([COLOR=blue]SELECT[/color] [COLOR=#FF00FF]MAX[/color](Id)
             [COLOR=blue]FROM[/color] ([COLOR=blue]SELECT[/color] Tst.*
                          [COLOR=blue]FROM[/color] @Test Tst
                   [COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Test tst1 [COLOR=blue]ON[/color] Tst1.Id = tst.Id+1 AND Tst.op = tst1.op
                   [COLOR=blue]WHERE[/color] tst1.Id [COLOR=blue]IS[/color] NULL AND 
                         Tst.Id < TestMe.Id) tsts) [COLOR=blue]AS[/color] MaxId
[COLOR=blue]FROM[/color] @Test TestMe
[COLOR=#FF00FF]LEFT[/color] [COLOR=blue]JOIN[/color] @Test tst1 [COLOR=blue]ON[/color] Tst1.Id = TestMe.Id+1 AND TestMe.op = tst1.op
[COLOR=blue]WHERE[/color] tst1.Id [COLOR=blue]IS[/color] NULL
) TestFinal
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] @Resources Rsrs [COLOR=blue]ON[/color]
      TestFinal.op = Rsrs.Op AND Rsrs.OpNo = (TestFinal.Id - [COLOR=#FF00FF]ISNULL[/color](TestFinal.MaxId,0))

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LadyLin]

Yes, that's what I was looking for, now I'll just work in my date calculations and work to keep the order of operations. Thank you so much, I would never have come up with the solution.

 

[bborissov]

Just remember that the ID sequence is crucial here
They always should be in sequence, if you have gaps the query will not return what you want.
If you are using SQL Server 2005 or 2008 and you may have gaps in sequence then you could use ROW_NUMBER() function to get what you want, but that means just another derived table

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[LadyLin]

Yes, I picked up on that,I found the row_number function and am using it to sequence the ops. Thanks again for your help. I still have a lot of work to do but at least I have the necessary info.