November 19, 2018 at 1:23 pm
I wanted to post my finished product of the query you guys helped me with. I appreciate the help greatly.
USE nms_rt
GO
--Clear the RT_Group_Diff DataTable
DELETE FROM RT_Group_Diff
GO
--Repopulete the RT_Group DataTable with current Data
INSERT INTO RT_Group_Diff(WEA, Date_Time, PriBase_Ref, PriSSI_Ref, PriBase, Pri_SSI, SecBase_Ref, SecSSI_Ref, SecBase, Sec_SSI)
SELECT
t1.WEA
, t2.Date_Time
, t1.Base1
, t1.SSI1
, t2.Base1
, t2.SSI1
, t1.Base2
, t1.SSI2
, t2.Base2
, t2.SSI2
FROM
RT_Group_Average AS t1
JOIN RT_Group_Status AS t2
ON t1.WEA = t2.WEA
--Begin select of info to construct the Line/Group of the query
SELECT
--Specify the Date Convertion, as just DATE
(SELECT CONVERT(varchar,getDate(),110) AS Date_Time) AS Date,
master.dbo.ufnGetLG(WEA) AS 'Line/Group',
(SELECT [Name] + ',' + [State]
FROM [nms_cfg4].[dbo].[ATCS_Group] WHERE Group_Address = (WEA / 10000 *10000 )) AS Wayside,
--, wea / 10000 * 10000
--Convert the Primary Reference CktID to Name/State
(SELECT [Name] + ',' + [State]
FROM [nms_cfg4].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = (SELECT TOP 1 Base_Equip_Address
FROM [nms_cfg4].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([PriBase_Ref]))) + ' ( ' + [PriBase_Ref] + ')' AS 'Primary',
PriSSI_Ref,
--Convert the Current Primary CktID to Name/State
(SELECT [Name] + ',' + [State]
FROM [nms_cfg4].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = (SELECT TOP 1 Base_Equip_Address
FROM [nms_cfg4].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([PriBase]))) + ' ( ' + [PriBase] + ')' AS 'Current_Primary',
Pri_SSI,
--Determine the Diff of the Primary Reference SSI and the Current Primary SSI and populate the Pri_Diff Column
ISNULL([Pri_SSI],0)-ISNULL([PriSSI_Ref],0) Pri_Diff,
--Convert the Secondary Reference CktID to Name/State
(SELECT [Name] + ',' + [State]
FROM [nms_cfg4].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = (SELECT TOP 1 Base_Equip_Address
FROM [nms_cfg4].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([SecBase_Ref]))) + ' ( ' + [SecBase_Ref] + ')' AS 'Secondary',
SecSSI_Ref,
--Convert the Current Secondary CktID to Name/State
(SELECT [Name] + ',' + [State]
FROM [nms_cfg4].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = (SELECT TOP 1 Base_Equip_Address
FROM [nms_cfg4].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([SecBase]))) + ' ( ' + [SecBase] + ')' AS 'Current_Secondary',
Sec_SSI,
--Determine the Diff of the Secondary Reference SSI and the Current Secondary SSI and populate the Sec_Diff Column
ISNULL([Sec_SSI],0)-ISNULL([SecSSI_Ref],0) Sec_DIff
FROM RT_Group_Diff
ORDER BY WEA
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
November 19, 2018 at 2:22 pm
A few comments on your code.
Do not as a standard store user functions on master -- master.dbo.ufnStringToPbase should be on any db other than system db's
Do not hardcode database names - use synonyms instead - table names should only be referencing 2 naming parts.
Give alias to all your tables, and always declare the columns using the alias
As a matter of standard either use square brackets around everything or around none (except the mandatory ones).
Mixing is bad - and using the brackets just makes it harder to read in any case.
November 20, 2018 at 6:09 am
frederico_fonseca - Monday, November 19, 2018 2:22 PMA few comments on your code.Do not as a standard store user functions on master -- master.dbo.ufnStringToPbase should be on any db other than system db's
Do not hardcode database names - use synonyms instead - table names should only be referencing 2 naming parts.
Give alias to all your tables, and always declare the columns using the aliasAs a matter of standard either use square brackets around everything or around none (except the mandatory ones).
Mixing is bad - and using the brackets just makes it harder to read in any case.
Thank you Frederico. I will move the Functions out of the Master db.
The second item, I am not sure i understand. Could you give me an example of what you are meaning?
I will review the aliasing of tables, and it makes sense.
Bracketing, I will update my code and remove.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
November 20, 2018 at 6:33 am
brian.cook - Tuesday, November 20, 2018 6:09 AMfrederico_fonseca - Monday, November 19, 2018 2:22 PMA few comments on your code.Do not as a standard store user functions on master -- master.dbo.ufnStringToPbase should be on any db other than system db's
Do not hardcode database names - use synonyms instead - table names should only be referencing 2 naming parts.
Give alias to all your tables, and always declare the columns using the aliasAs a matter of standard either use square brackets around everything or around none (except the mandatory ones).
Mixing is bad - and using the brackets just makes it harder to read in any case.Thank you Frederico. I will move the Functions out of the Master db.
The second item, I am not sure i understand. Could you give me an example of what you are meaning?
I will review the aliasing of tables, and it makes sense.
Bracketing, I will update my code and remove.
I think Frederico means you shouldn't have [nms_cfg4].[dbo].[Base_Equipment]. i.e. the database name in the query. As you are inserting into a table on database nms_rt from a query on nms_cfg4 you will need to have a three part name either in the insert table or the select tables unless you use synonyms. He is suggesting you create synonyms for the tables on nms_cfg4 e.g.:
CREATE SYNONYM dbo.[cfg_Base_Equipment] FOR [nms_cfg4].[dbo].[Base_Equipment]
Then the select query can select from dbo.[cfg_Base_Equipment] instead of [nms_cfg4].[dbo].[Base_Equipment]
November 20, 2018 at 6:38 am
Jonathan AC Roberts - Tuesday, November 20, 2018 6:33 AMbrian.cook - Tuesday, November 20, 2018 6:09 AMfrederico_fonseca - Monday, November 19, 2018 2:22 PMA few comments on your code.Do not as a standard store user functions on master -- master.dbo.ufnStringToPbase should be on any db other than system db's
Do not hardcode database names - use synonyms instead - table names should only be referencing 2 naming parts.
Give alias to all your tables, and always declare the columns using the aliasAs a matter of standard either use square brackets around everything or around none (except the mandatory ones).
Mixing is bad - and using the brackets just makes it harder to read in any case.Thank you Frederico. I will move the Functions out of the Master db.
The second item, I am not sure i understand. Could you give me an example of what you are meaning?
I will review the aliasing of tables, and it makes sense.
Bracketing, I will update my code and remove.I think Frederico means you shouldn't have [nms_cfg4].[dbo].[Base_Equipment]. i.e. the database name in the query. As you are inserting into a table on database nms_rt from a query on nms_cfg4 you will need to have a three part name either in the insert table or the select tables unless you use synonyms. He is suggesting you create synonyms for the tables on nms_cfg4 e.g.:
CREATE SYNONYM dbo.[cfg_Base_Equipment] ON [nms_cfg4].[dbo].[Base_Equipment]
Then the select query can select from dbo.[cfg_Base_Equipment] instead of [nms_cfg4].[dbo].[Base_Equipment]
Ah! Okay. I understand now. Thank you for the clarification. That would Simplify the query some too.
Thanks guys!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply