Create view with number range for summary levels 1

[BSman]

In SQL Server 2000 I'm trying to create a view that will give me a range of record references for summary levels. I can build views that will give me the starting number for each range, but the ending number would be lower than the starting number for the next range and I can't figure out how to get that.

Summary Level 2 Group View:
SummaryGrp LocOrder
22XXX 19
23XXX 24
31XXX 29

Actual locations view:
Loc LocOrder
22132 20
29352 21
39485 22
23155 25
23156 26
33243 27
31443 30
31554 31

At minimum, I need each SummaryGrp to display the highest LocOrder number of the actual locations that are part of that SummaryGrp, but I can't figure any way to make a view that will figure that number, even one that would essentially show in the Summary Groups Level 2 view the number of the next record minus 1.

Any suggestions? I can't modify the table of the source data, since it's from another SQL database that is the company master reference.

Thanks,
Bob

 

[k01]

Is this what you want to get?

SELECT
  LEFT(LOC,2) + 'xxx' AS SummaryGrp
 ,MAX(LocOrder)       AS LocOrder
FROM
 TEST1
GROUP BY
 LEFT(LOC,2) + 'xxx'

koichi

http://aboutabi.blogspot.com/

 

[BSman]

No, it's nothing as simple as that. Unfortunately, the Loc number doesn't have a reliable hierarchy meaning (such as every Loc starting with the same first 3 digits is part of one group). I need to end up with the following result (based on the sample data above):

Summary Level 2 Group View:
SummaryGrp LocOrder Highest LocOrder
22XXX 19 22
23XXX 24 27
31XXX 29 31

This means that all Locs with a LocOrder number between 19 and 22 belong to SummaryGrp 22XXX, LocOrder numbers between 24 and 27 belong to SummaryGrp 23XXX, and so on.

In effect, the HighestLocOrder number is the maximum value of LocOrder in the Actual Locations view where LocOrder is less than the next SummaryGrp in the sorted Summary Level 2 Group View. So Highest LocOrder for SummaryGrp 22XXX is the Highest LocOrder for Locations where the LocOrder number is less than the LocOrder for the next SummaryGrp, 23XXX.

Bob

 

[PDreyer]

You’ll have to explain your result needed. How do you get the starting number?

select Loc/1000 SummaryGrp, min(LocOrder) LocOrder, max(LocOrder) HighestLocOrder
from (select
22132, 20 union all select
29352, 21 union all select
39485, 22 union all select
23155, 25 union all select
23156, 26 union all select
33243, 27 union all select
31443, 30 union all select
31554, 31 
)locations(Loc,LocOrder)
group by Loc/1000
order by 2

SummaryGrp  LocOrder    HighestLocOrder 
----------- ----------- --------------- 
         22          20              20 
         29          21              21 Why do you include this with Grp 22
         39          22              22 Why do you include this with Grp 22
         23          25              26 
         33          27              27 Why do you include this with Grp 31
         31          30              31

 

[BSman]

The LocNbr has NO meaning. You're thinking that a number that starts with "33" should be in a SummaryGrp that starts with "33". Oh that it were that simple (then I wouldn't have to ask the question). The LocNbrs could just as easily be A, B, C, D, E, F, and so on. They are just character strings that happen to normally use all digits.

Looking at the sample data I provided, if you want to get precise, the starting LocOrder for 22XXX is really 20, since that's the lowest LocOrder number that is associated with an actual location.

In reality, I'm starting with a data table which contains all of the data I've listed in the two views. There is another column that basically identifies whether or not a specific record is an actual location or a summary level. So I'm trying to extract the ranges from one table that looks something like this sample data:

Original table:

LocNbr LocOrder IsLocation
22XXX 19 N
22132 20 Y
29352 21 Y
39485 22 Y
23XXX 24 N
23155 25 Y
23156 26 Y
33243 27 Y
31XXX 29 N
31443 30 Y
31554 31 Y

Bob

 

[k01]

How about this?

Test data creation:

CREATE TABLE [dbo].[TEST1](
  [Loc] [varchar](50) NULL,
  [LocOrder] [int] NULL,
  [IsLocation] [char](1) NULL
) ON [PRIMARY]
GO

INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('22XXX','19','N')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('22132','20','Y')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('29352','21','Y')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('39485','22','Y')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('23XXX','24','N')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('23155','25','Y')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('23156','26','Y')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('33243','27','Y')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('31XXX','29','N')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('31443','30','Y')
INSERT INTO [TEST1] ([Loc],[LocOrder],[IsLocation])  VALUES ('31554','31','Y')

Summary creation:

SELECT 
   Loc AS SummaryGrp
  ,LocOrder
  ,(SELECT MAX(LocOrder) FROM TEST1 T3 WHERE LocOrder < (SELECT MIN(LocOrder) FROM TEST1 T2 WHERE IsLocation = 'N' AND T1.LocOrder < T2.LocOrder)) AS HighestLocOrder
FROM 
  TEST1 T1
WHERE
  IsLocation = 'N'

koichi

http://aboutabi.blogspot.com/

 

[BSman]

I haven't tried it yet, but your SQL statement looks like it should work....however, I didn't list all of the data. There are actually multiple summary levels, so there might be a level that would include the range of LocNbr from 20 to 27. There is another column that indicates the type of level, and your suggestion still might work if I also incorporate that column. However, I believe that the resulting view is going to be sufficiently complex that it might take longer to run that would be desireable. As soon as I get a chance I'll try it and let you know the result, but I've come to the conclusion that it would probably be better to bite the bullet and include some processing code in the loading process that would populate some additional group level columns. The code would basically go through the data by locnbr order, keep track of each grouping level, and write the data to the grouping level columns for each current record.

Thanks,
Bob

 

[ESquared]

LocNbr obviously has SOME meaning or you wouldn't be pulling out certain digits of it and doing manipulations with them and how they relate to the same digits in other rows.

The thing about correlated subqueries is that they are so easy to write because they follow almost exactly natural English.

SELECT
   G.SummaryGrp,
   G.LocOrder,
   HighestLocOrder = (
      SELECT Max(LocOrder)
      FROM Locations
      WHERE LocOrder < (
         SELECT Min(LocOrder)
         FROM SummaryGrps
         WHERE SummaryGrp > G.SummaryGrp
      )
   )
FROM
   SummaryGrps G

Now let's try to convert that to some other query types, and you can compare their performance.

DECLARE @SummarySeq TABLE (
   Seq int identity(1,1),
   SummaryGrp varchar(5) PRIMARY KEY CLUSTERED
)

INSERT @SummarySeq (SummaryGrp)
SELECT SummaryGrp
FROM SummaryGrps
ORDER BY SummaryGrp

SELECT
   G.SummaryGrp,
   G.LocOrder,
   HighestLocOrder = Max(L.LocOrder)
FROM
   SummaryGrps G
   INNER JOIN @SummarySeq S1 ON G.SummaryGrp = S1.SummaryGrp
   LEFT JOIN @SummarySeq S2 ON S1.Seq + 1 = S2.Seq
   INNER JOIN Locations L ON L.LocNbr >= G.SummaryGrp AND L.LocNbr < Coalesce(S2.SummaryGrp, 'MaxValueForLastLocNbr')
GROUP BY
   G.SummaryGrp,
   G.LocOrder

I haven't tested this, but something like it should work. (Sorry, I don't have time to really create a test environment to make sure it's 100% right).

I wrote most of this post yesterday before k01 posted and our correlated subqueries are almost identical.

BSman,

Please give us some sample data that is EXACTLY how you have it, and then see what happens! To present an incomplete problem and then say that the complete answer will be too complex seems a little... I don't know... not considering fully the time people are giving to try to help out?

No one has actually had a chance to provide you with an answer to the real problem.

 

[BSman]

Thanks Koichi. I used your suggestion as a basis for a more complex SQL query that had additional criteria and actually provided the exact results I was looking for.

Of course, the advantage with the view is that the raw data can be updated from the database where it is maintained just by a straight import replace. I am concerned about speed using the views, so I may end up biting the bullet and writing a process that will compute the grouping level information for each group level for every time the data is imported. But first I will use the design of this view to create a version where each location includes the various grouping levels associated with it. It may just take too long to run as opposed to having all of the grouping levels information in the table, but we'll see.

Bob

 

[ESquared]

If performance of the correlated subquery becomes an issue (which it can with large result sets) I encourage you to try some derived-table and step methods like I outlined above.