Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
djorre
Yak Posting Veteran
94 Posts |
 Posted - 2012-04-11 : 08:01:15
|
Hello,I am struggling on this specific query, although it only uses 2 simple tables.There is a system with 2 unwinders, and they switch during runs. When unwinder 2 runs, unwinder 1 stands still and vice versa. The runs are in my first table (see screenshot).The other table contains the Roll that is mounted on the unwinder. A roll will always be mounted when an unwinder stands still. The setup is so that a Roll can be changed while the other unwinder runs. That is the second table.The required query is the blue one, and it contains a join of the runs and rolls mounted. I hope this is quite clear.I have no idea how to start this.Thanks in advance,djorre |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-04-11 : 09:32:49
|
Post the sample data with DDL statements and INSERT statement.The solution is simple, use double ROW_NUMBER() to keep the islands apart.
N 56°04'39.26"E 12°55'05.63" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-12 : 01:19:25
|
| [code]SELECT t1.Unwinder,t2.Roll,t2.[ON] AS Mount,MIN(t1.Start) AS FirstStart ,MAX(t1.Stop) AS LastStopFROM UnwinderTable t1CROSS APPLY (SELECT TOP 1 Roll,[ON] FROM UNwinderRoll WHERE Unwinder = t1.Unwinder AND [ON] <= t1.Start AND OFF > = t1.End ORDER BY [ON] DESC )t2OUTER APPLY (SELECT MAX(Start) AS Prev FROM Unwinder WHERE Start < t1.Start AND Unwinder <> t1.Unwinder )t3GROUP BY t1.Unwinder,t2.Roll,t2.[ON],t3.Prev[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
need help on this query