June 11, 2021 at 3:09 pm
Jeffrey, here is a sample of the be_xref.oa table;
base_equip_addressoffice_app_addressx_pbaseregionportpollxprimarypath_typedesignator
22009121220169126912111691201110NULL
2201003120016003600310160030110NULL
2201108222016108610820161081110NULL
2201108322016108610830161082110NULL
2201169122016169616910161690110NULL
2201172222016172617220161721110NULL
2201173222016173617320161731110NULL
Here is a sample of the atcs_group table;
Group_AddressSubregionLine_Addressleft_neighbor_groupright_neighbor_groupATCS_GroupNameStateDescriptionMilepostLongitudeLatitude
220030010000NULL220030000000NULLNULL1Hauser B House West MainIDNULLNULLNULLNULL
220030020000NULL220030000000NULLNULL2Hauser B House West BlueIDNULLNULLNULLNULL
220030030000NULL220030000000NULLNULL3Downing West MainIDNULLNULLNULLNULL
220030040000NULL220030000000NULLNULL4Downing West BlueIDNULLNULLNULLNULL
220030050000NULL220030000000NULLNULL5Downing East MainIDNULLNULLNULLNULL
220030060000NULL220030000000NULLNULL6Downing East BlueIDNULLNULLNULLNULL
220030070000NULL220030000000NULLNULL7Hauser East MainIDNULLNULLNULLNULL
220030080000NULL220030000000NULLNULL8Hauser East BlueIDNULLNULLNULLNULL
220040100000NULL220040000000NULLNULL10Dalhart NorthTXNULLNULLNULLNULL
220040110000NULL220040000000NULLNULL11Dalhart UPRRXNULLNULLNULLNULLNULL
220040120000NULL220040000000NULLNULL12CP 4154TXNULLNULLNULLNULL
220590010000NULL220590000000NULLNULL1Rhea WestTXNULLNULLNULLNULL
220590020000NULL220590000000NULLNULL2Rhea EastTXNULLNULLNULLNULL
220590030000NULL220590000000NULLNULL3Dickworsham WestTXNULLNULLNULLNULL
220590040000NULL220590000000NULLNULL4Dickworsham EastTXNULLNULLNULLNULL
What you are saying makes sense when you put it into context.
The eight character string consists of that base id value, i.e. 601.1.01
Here is the sample from the Base_Equipment
Base_Equip_AddressSubregionEquipment_TypeNodeDeviceOffice_App_AddressPort_NumberPoll_AddressNameStateDescriptionHW_VersionSW_VersionDate_In_ServiceLongitudeLatitudecov_region
22009121NULL2150912199999999901Browning West IPMTCNA1007-1.2.3 IP x.x.x.xNULLNULL1900-01-01 00:00:00.000NULLNULL0
22010031NULL21513199999999901Sig Eng Gen Primary IPKSCNA1000 IP x.x.x.xNULLNULL1900-01-01 00:00:00.000NULLNULL0
22011082NULL2151108299999999901Hazen East MP 63.54 IPNDCNA1009-1.1.0 IP x.x.x.xNULLNULL2016-06-13 00:00:00.000NULLNULL0
22011083NULL2151108399999999901West Tower MP 552.61 IPTXCNA1007-1.2.3 IP x.x.x.xNULLNULL2018-08-27 00:00:00.000NULLNULL0
22011691NULL2151169199999999901Oakland North IPNECNA1007-1.2.3 IP x.x.x.xNULLNULL2012-01-05 00:00:00.000NULLNULL0
22011722NULL2151172299999999901Garretson South IPMNCNA1007-1.2.3 IP x.x.x.xNULLNULL2012-04-24 00:00:00.000NULLNULL0
22011732NULL2151173299999999901Merrill North IPMNCNA1007-1.2.3 IP x.x.x.xNULLNULL2012-01-31 00:00:00.000NULLNULL0
22011733NULL2181173399999999901106.82 RCPS IPMNCNA1000 IP x.x.x.xNULLNULL1900-01-01 00:00:00.000NULLNULL0
22011771NULL2151177199999999901Hauser B House West IPIDCNA1007-1.2.3 IP x.x.x.xNULLNULL2017-08-24 00:00:00.000NULLNULL0
22011891NULL2151189199999999901MP 219.31 IPIACNA1009-1.1.0 IP x.x.x.xNULLNULL2015-12-14 00:00:00.000NULLNULL0
Let me know if you need something else.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 3:35 pm
Shifting gears back to the function that was used, I've added a smidgen of additional bullet-proofing.
CREATE OR ALTER FUNCTION dbo.ufnStringToPbase
(@String CHAR(8))
/*******************************************************************
Purpose:
Given an 8 character string of precisely 8 digits containing
information for the Line, Port, Poll, and other values, extract the
Line, Port, and Poll information and convert to a prepended PBase
value using the following logic.
Given: 12345678
||| | ||<--- Poll
||| |<------ Port
|||<-------- Line
Added: 6<----------- Leader/Offset
Returns: 6123578
To summarize, positions 6 and 4 of the string are not used and the
string is prepended with a Leader/Offset of "6".
Programmer Notes:
1. If any of the characters in the original string are non-numeric
digits, the function will return a NULL.
---------------------------------------------------------------------
Usage Examples:
--===== Basic syntax
SELECT dbo.ufnStringToPbase(@String)
;
--===== Usage with table
SELECT dbo.ufnStringToPbase(SomeStringCol)
FROM dbo.SomeTable
;
---------------------------------------------------------------------
Revision History:
Rev 00 - Unknown - Author Unknown
Rev 01 - 11 Jun 2021 - Jeff Moden
- Full redaction, documentation, and unit test of the code.
Rev 02 - 11 Jun 2021 - Jeff Moden
- Modify the code to return a NULL if any non-numeric digits
are present, which also forces a NULL to be returned for
"short" values of less than 8 digits because the input
parameter is a CHAR(8). The COLLATE is necessary because
some collations recognize superscripted digits and some
fractional representations as being the same as the 0-9
digits. The COLLATE will also make it a bit faster.
********************************************************************/
RETURNS INT AS
BEGIN
RETURN (
SELECT 6000000
+ CONVERT(INT,STUFF(STUFF(@string,6,1,''),4,1,''))
WHERE @String NOT LIKE '%[^0-9]%' COLLATE Latin1_General_BIN
);
END
;
GO
--Jeff Moden
Change is inevitable... Change for the better is not.
June 11, 2021 at 4:27 pm
the fourth and sixth digit is a decimal symbol ".", would that affect this?
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 5:03 pm
If you could put that data into temp tables - with create statement and insert statements - it would help. We could then take that sample data and use it to generate a working solution. This also helps because it tells us what data types are used for each column - for example, what is the data type of the Group_Address column in the atcs_group table?
For this - we really only need the columns that are related to the issue - we don't need every column in the table.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 11, 2021 at 5:24 pm
You also need to provide samples of the [base1], [base2] and [base3] values. Those are the values that are passed into the function - to return the base equipment address.
The lookup is based on the region, port and poll - and identifies a potential problem. Can there be the same combination of region|port|poll for separate base addresses, or is that a unique combination? If unique combination - is it enforced by a constraint?
Is the base value always formatted as {region}.{port}.{poll} - with region = 4 digits, port = 1 digit and poll = 2 digits?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 11, 2021 at 5:28 pm
Here is the ATCS_Group Table;
USE [nms_cfg4]
GO
/****** Object: Table [dbo].[ATCS_Group] Script Date: 06/11/2021 12:23:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ATCS_Group](
[Group_Address] [float] NOT NULL,
[Subregion] [int] NULL,
[Line_Address] [float] NOT NULL,
[left_neighbor_group] [float] NULL,
[right_neighbor_group] [float] NULL,
[ATCS_Group] [int] NOT NULL,
[Name] [varchar](50) NULL,
[State] [varchar](3) NULL,
[Description] [varchar](255) NULL,
[Milepost] [varchar](12) NULL,
[Longitude] [varchar](20) NULL,
[Latitude] [varchar](20) NULL,
CONSTRAINT [ATCS_Group_PK] PRIMARY KEY CLUSTERED
(
[Group_Address] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ATCS_Group] WITH CHECK ADD CONSTRAINT [ATCS_Line_ATCS_Group_FK1] FOREIGN KEY([Line_Address])
REFERENCES [dbo].[ATCS_Line] ([Line_Address])
GO
ALTER TABLE [dbo].[ATCS_Group] CHECK CONSTRAINT [ATCS_Line_ATCS_Group_FK1]
GO
ALTER TABLE [dbo].[ATCS_Group] WITH CHECK ADD CONSTRAINT [Subregion_ATCS_Group_FK1] FOREIGN KEY([Subregion])
REFERENCES [dbo].[Subregion] ([Subregion])
GO
ALTER TABLE [dbo].[ATCS_Group] CHECK CONSTRAINT [Subregion_ATCS_Group_FK1]
GO
Here is the be_xref.oa;
USE [nms_cfg4]
GO
/****** Object: Table [dbo].[be_xref_oa] Script Date: 06/11/2021 12:25:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[be_xref_oa](
[base_equip_address] [float] NOT NULL,
[office_app_address] [float] NOT NULL,
[x_pbase] [int] NOT NULL,
[region] [int] NOT NULL,
[port] [tinyint] NOT NULL,
[poll] [tinyint] NOT NULL,
[xprimary] [bit] NOT NULL,
[path_type] [tinyint] NOT NULL,
[designator] [nvarchar](24) NULL,
CONSTRAINT [PK_be_xref_oa] PRIMARY KEY CLUSTERED
(
[base_equip_address] ASC,
[office_app_address] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[be_xref_oa] WITH CHECK ADD CONSTRAINT [FK_be_xref_oa_Base_Equipment] FOREIGN KEY([base_equip_address])
REFERENCES [dbo].[Base_Equipment] ([Base_Equip_Address])
GO
ALTER TABLE [dbo].[be_xref_oa] CHECK CONSTRAINT [FK_be_xref_oa_Base_Equipment]
GO
ALTER TABLE [dbo].[be_xref_oa] WITH CHECK ADD CONSTRAINT [FK_be_xref_oa_Office_Applications] FOREIGN KEY([office_app_address])
REFERENCES [dbo].[Office_Applications] ([Off_App_Address])
GO
ALTER TABLE [dbo].[be_xref_oa] CHECK CONSTRAINT [FK_be_xref_oa_Office_Applications]
GO
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 5:30 pm
Is the base value always formatted as {region}.{port}.{poll} - with region = 4 digits, port = 1 digit and poll = 2 digits?
Yes, the first digit of the Region is always a 6, so 6xxx.x.xx
the sample is in the be_xref.oa earlier in the chain.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 5:37 pm
So if the base value is 601.1.01 - the lookup will be for region 6601, port 1 poll 01?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 11, 2021 at 5:38 pm
Correct.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
June 11, 2021 at 6:18 pm
Okay - here is something for you to review:
Declare @xref Table (base_equip_address int, office_app_address int, x_pbase int, region int, port int, poll int);
Insert Into @xref (base_equip_address, office_app_address, x_pbase, region, port, poll)
Values (22009121, 22016912, 6912111, 6912, 0, 11)
, (22010031, 20016003, 6003101, 6003, 0, 1)
, (22011082, 22016108, 6108201, 6108, 1, 1)
, (22011083, 22016108, 6108301, 6108, 2, 1)
, (22011691, 22016169, 6169101, 6169, 0, 1)
, (22011722, 22016172, 6172201, 6172, 1, 1)
, (22011732, 22016173, 6173201, 6173, 1, 1);
Declare @base_equipment Table (base_equip_address int, Name varchar(100), State char(2));
Insert Into @base_equipment (base_equip_address, Name, [State])
Values (22009121, 'Browning West IP', 'MT')
, (22010031, 'Sig Eng Gen Primary IP', 'KS')
, (22011082, 'Hazen East MP 63.54 IP', 'ND')
, (22011083, 'West Tower MP 552.61 IP', 'TX')
, (22011691, 'Oakland North IP', 'NE')
, (22011722, 'Garretson South IP', 'MN')
, (22011732, 'Merrill North IP', 'MN')
, (22011733, 'RCPS IP', 'MN')
, (22011771, 'Hauser B House West IP', 'ID')
, (22011891, 'MP 219.31 IP', 'IA');
Declare @base_examples Table (base1 char(8), base2 char(8), base3 char(8));
Insert Into @base_examples (base1, base2, base3)
Values ('912.0.11', '003.0.01', '108.1.01')
, ('108.2.01', '169.0.01', '172.1.01')
, ('173.1.01', Null, Null);
Select *
From @base_examples ex
Outer Apply (Select BasePrimary = concat(be.Name, ', ', be.[State], ' (', ex.base1, ')')
From @base_equipment be
Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
Where xr.region = concat('6', substring(ex.base1, 1, 3))
And xr.port = substring(ex.base1, 5, 1)
And xr.poll = substring(ex.base1, 7, 2)
) b1
Outer Apply (Select BaseSecondary = concat(be.Name, ', ', be.[State], ' (', ex.base2, ')')
From @base_equipment be
Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
Where xr.region = concat('6', substring(ex.base2, 1, 3))
And xr.port = substring(ex.base2, 5, 1)
And xr.poll = substring(ex.base2, 7, 2)
) b2
Outer Apply (Select BaseTertiary = concat(be.Name, ', ', be.[State], ' (', ex.base3, ')')
From @base_equipment be
Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
Where xr.region = concat('6', substring(ex.base3, 1, 3))
And xr.port = substring(ex.base3, 5, 1)
And xr.poll = substring(ex.base3, 7, 2)
) b3
;
You could create a function to accomplish this - but there really isn't any need. If you will always have values for all 3 base values you can change to CROSS APPLY instead of OUTER APPLY.
With that said - looking at the sample data you provided I am seeing invalid values. The x_pbase value does not match the region, port, and poll values. I would expect the x_pbase value to be 6912011 for region 6912 port 0 poll 11 but you have that value as 6912111.
The lookup you have is looking for a x_pbase = {function} - but that function isn't returning the correct value unless your base id values are different. If your base id values are actually something like 6912.1.11 for region 6912 port 0 poll 11 - then it would be even easier:
Select *
From @base_examples ex
Outer Apply (Select BasePrimary = concat(be.Name, ', ', be.[State], ' (', ex.base1, ')')
From @base_equipment be
Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
Where xr.x_pbase = concat('6', replace(ex.base1, '.', ''))
) b1
Outer Apply (Select BaseSecondary = concat(be.Name, ', ', be.[State], ' (', ex.base2, ')')
From @base_equipment be
Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
Where xr.x_pbase = concat('6', replace(ex.base2, '.', ''))
) b2
Outer Apply (Select BaseTertiary = concat(be.Name, ', ', be.[State], ' (', ex.base3, ')')
From @base_equipment be
Inner Join @xref xr On xr.base_equip_address = be.base_equip_address
Where xr.x_pbase = concat('6', replace(ex.base3, '.', ''))
) b3
;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 12, 2021 at 9:35 am
Jeff,
You are correct in what you say below here. the leading 6 is dropped from the final product as it identifies that as a base. So the resulting values of 912.1.11 is the ID for the base and how the lookup comes back if you are searching the db that way.
The function is designed to take the numeric value and put a plain text on it so that "joe user" knows what it is without having access to the database, and is built into a report for them to see what the historic values of the bases Signal Strength over a given period of days. The Stored Procedure pulls all that data, and the function converts the base information.
I think your first suggestion in this last message will work. Thank you for you help!
With that said - looking at the sample data you provided I am seeing invalid values. The x_pbase value does not match the region, port, and poll values. I would expect the x_pbase value to be 6912011 for region 6912 port 0 poll 11 but you have that value as 6912111.
The lookup you have is looking for a x_pbase = {function} - but that function isn't returning the correct value unless your base id values are different. If your base id values are actually something like 6912.1.11 for region 6912 port 0 poll 11 - then it would be even easier:
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply