Solved

SQL Select query

Posted on 2014-04-30
3
150 Views
Last Modified: 2014-04-30
I have a table such as the table below.   The BegMTypw identifies where a particular feature begins, EndMType identifies where a particular feature ends.  CasingID is the foreignkey to the Feature.  
ID      Coordinate      BegMType      EndMType      CasingID
1      0                      Casing                                             1
2      5                  
3      7                  
4      8                  
5      9                  
6      10                                               Casing                       1
7      20                  
8      30                  
9      35                  
10      50                       Casing                                             1
11      51                  
12      62                  
13      100                                               Casing                       1


My output should look like

BegM    EndM     CasingID
0            10            1
50           100          1

The coordinates will always be entered in order.  Any ideas on how I can get the output above?  I can't do min and max because the CasingID is not unique, this would yeild

BegM   EndM   CasingID
0             100        1

This result would be incorrect, I need the two records for beg and end.
0
Comment
Question by:yanci1179
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 5

Accepted Solution

by:
dannygonzalez09 earned 500 total points
ID: 40032386
Something like this?
SELECT * INTO #Test
FROM
(
SELECT Id = 1,CoOrdinate = 0, BegMType = 'Casing',EndMtype = '',CasingId = 1
UNION
SELECT Id = 2,CoOrdinate = 5, BegMType = '',EndMtype = '',CasingId = ''
UNION
SELECT Id = 3,CoOrdinate = 7, BegMType = '',EndMtype = '',CasingId = ''
UNION
SELECT Id = 4,CoOrdinate = 10, BegMType = '',EndMtype = 'Casing',CasingId = 1
UNION
SELECT Id = 5,CoOrdinate = 50, BegMType = 'Casing',EndMtype = '',CasingId = 1
UNION
SELECT Id = 6,CoOrdinate = 100, BegMType = '' ,EndMtype = 'Casing',CasingId = 1

)x

With BegCTE AS
(
SELECT CoOrdinate,CasingId,RANK() OVER (PARTITION BY BegMType ORDER BY Id) BegRnk FROM #Test
WHERE LEN(BegMType) > 1
)
,EndCTE AS
(
SELECT CoOrdinate,CasingId,RANK() OVER (PARTITION BY EndMType ORDER BY Id) EndRnk FROM #Test
WHERE LEN(EndMType) > 1
)

SELECT B.CoOrdinate,E.CoOrdinate,B.CasingId FROM BegCTE B
LEFT JOIN EndCTE E
ON B.BegRnk = E.EndRnk and B.CasingId = E.CasingId

Open in new window

0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 40032387
you could look at the rank() function.

http://technet.microsoft.com/en-us/library/ms176102.aspx

it's a CTE with a join

something like


;with cte as
(select *, rank() over (Partition By CasingID order by coordinate) myRank
  from table
  where isnull(BegMType, '') != ''  or isnull(EndMType, '') != ''
)  


select  B.Coordinate, E.Coordinate, B.CasingId
from cte B
join cte E on B.CasingID = E.CasingID and E.MyRank = B.MyRank +1 and isnull(B.BegMType, '') != ''
0
 

Author Closing Comment

by:yanci1179
ID: 40033216
Thanks!!
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question