November 14, 2018 at 6:52 am
So, I have been asked to provide a way to compare daily data that comes in to a given date. We arbitrarily established 01/11/2018 as the static date as the reference date.
Table 1 is the historical data table, and Table 2 is updated with new values. I am guessing I need to create a third Table to store the results in so that they are searchable later.
My question is how do I compare the SSI1, SSI2, SSI3 column values from Table 2 to Table 1 where the WEA column will contain the identical data in both tables?
Thanks,
Pertinent table structures
Table 1 is structured like this;USE [nms_rt]
GO
/****** Object: Table [dbo].[RT_Group_Coverage] Script Date: 11/14/2018 07:29:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RT_Group_Coverage](
[WEA] [dbo].[ATCS_Address] NOT NULL,
[Date_Time] [datetime] NOT NULL,
[Base1] [char](10) NULL,
[Base2] [char](10) NULL,
[Base3] [char](10) NULL,
[SSI1] [int] NULL,
[SSI2] [int] NULL,
[SSI3] [int] NULL,
[Lock1] [bit] NULL,
[Lock2] [bit] NULL,
[Lock3] [bit] NULL,
[Pref_base] [char](10) NULL,
[dbu] [bit] NULL,
[alarm] [bit] NULL
) ON [PRIMARY]
GO
Table 2 is structured like this;USE [nms_rt]
GO
/****** Object: Table [dbo].[RT_Group_Status] Script Date: 11/14/2018 07:30:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RT_Group_Status](
[WEA] [dbo].[ATCS_Address] NOT NULL,
[Date_Time] [datetime] NOT NULL,
[Base1] [char](10) NULL,
[Base2] [char](10) NULL,
[Base3] [char](10) NULL,
[SSI1] [int] NULL,
[SSI2] [int] NULL,
[SSI3] [int] NULL,
[Lock1] [bit] NULL,
[Lock2] [bit] NULL,
[Lock3] [bit] NULL,
[Pref_base] [char](10) NULL,
[dbu] [bit] NULL,
[alarm] [bit] NULL,
CONSTRAINT [PK_RT_Group_Status] PRIMARY KEY CLUSTERED
(
[WEA] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
November 14, 2018 at 7:20 am
You can do this with EXCEPT, you want to know all the values from the table you know has all the required data definitely which are not in the other table so you're looking at something like
SELECT Column1, Column2 FROM originTable
EXCEPT
SELECT Column1, Column2 from othertable
if this isn't enough but you want to check both sides you turn the Query around, too.
SELECT Column1, Column2 FROM othertable
EXCEPT
SELECT Column1, Column2 from originTable
I've created a stored procedure here in the scripts section a while ago, it's a bit ugly at times to use (and has undergone some improvements which are not here yet) but essentially it adds a UNION ALL. Which is good however you have to work a bit harder to make the UNION ALL statement identifiable in a way that you know from which query the row is returned.
November 14, 2018 at 7:50 am
Love what you posted. Should I use the following to compare against specific referenced records?;SELECT DISTINCT WEA, SSI1, SSI2, SSI3 FROM RT_Group_Coverage
EXCEPT
SELECT DISTINCT WEA, SSI1, SSI2, SSI3 FROM RT_Group_Status
ORDER BY WEA
If I wanted to compare it to a specific Date, how would I modify the above?
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
November 14, 2018 at 7:50 am
I will also check out the script you posted.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
November 14, 2018 at 8:34 am
DinoRS - Wednesday, November 14, 2018 7:20 AMYou can do this with EXCEPT, you want to know all the values from the table you know has all the required data definitely which are not in the other table so you're looking at something like
SELECT Column1, Column2 FROM originTable
EXCEPT
SELECT Column1, Column2 from othertableif this isn't enough but you want to check both sides you turn the Query around, too.
SELECT Column1, Column2 FROM othertable
EXCEPT
SELECT Column1, Column2 from originTableI've created a stored procedure here in the scripts section a while ago, it's a bit ugly at times to use (and has undergone some improvements which are not here yet) but essentially it adds a UNION ALL. Which is good however you have to work a bit harder to make the UNION ALL statement identifiable in a way that you know from which query the row is returned.
OK, after analyzing the results of the query, this gives me what is missing from one table to the other.
I am looking for what is DIFFERENT from each column and row., so
if row one in table one is;
220590010000 53 35
and row one in table 2 is;
220590010000 45 25
I need the difference of the values of each column. Also Table 1 would be the static date and table two would be the updated daily values.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
November 14, 2018 at 8:48 am
SELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3]
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = b.WEA
AND ((b.SSI1 <> a.SSI1 OR b.SSI1 IS NULL AND a.SSI1 IS NOT NULL OR b.SSI1 IS NOT NULL AND a.SSI1 IS NULL)
OR (b.SSI2 <> a.SSI2 OR (b.SSI2 IS NULL AND a.SSI2 IS NOT NULL) OR (b.SSI2 IS NOT NULL AND a.SSI2 IS NULL))
OR (b.SSI3 <> a.SSI3 OR (b.SSI3 IS NULL AND a.SSI3 IS NOT NULL) OR (b.SSI3 IS NOT NULL AND a.SSI3 IS NULL)))
November 14, 2018 at 9:03 am
you can specify the WHERE Clause in both SELECT Statements to limit Date_Time like in a normal Statement e.g. WHERE Date_Time = ' '
if you want both directions (as in the rows from both sides compared)
things might look something like this
SELECT Column1, Column2 FROM originTable
EXCEPT
SELECT Column1, Column2 from othertable
UNION ALL
SELECT Column1, Column2 FROM othertable
EXCEPT
SELECT Column1, Column2 from originTable
GROUP BY Column1
ORDER BY Column1
The important bit here would be to group by preferably the value that should be the same in both cases, I'd guess in your case it would be ATCS_Address
but it will not tell you that way which row is from which table, you'll still have to check that manually.
SELECT * FROM Table where Column = 'dateofinterest'
UNION ALL
SELECT * FROM OtherTable where Column = 'dateofinterest'
EXCEPT doesn't exactly like adding something like SELECT 'source Table' AS Tablename, * from Table as a reference to the origin (SQL would consider any row with 'source' different to any column with 'target') but there are ways to work around this I guess.
November 14, 2018 at 9:59 am
Jonathan AC Roberts - Wednesday, November 14, 2018 8:48 AMSELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3]
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = b.WEA
AND ((b.SSI1 <> a.SSI1 OR b.SSI1 IS NULL AND a.SSI1 IS NOT NULL OR b.SSI1 IS NOT NULL AND a.SSI1 IS NULL)
OR (b.SSI2 <> a.SSI2 OR (b.SSI2 IS NULL AND a.SSI2 IS NOT NULL) OR (b.SSI2 IS NOT NULL AND a.SSI2 IS NULL))
OR (b.SSI3 <> a.SSI3 OR (b.SSI3 IS NULL AND a.SSI3 IS NOT NULL) OR (b.SSI3 IS NOT NULL AND a.SSI3 IS NULL)))
This is working partially. It looks like it is not matching the WEA column of the daily table to the WEA of the static table. It also comes back with an astronomical number of result rows. the static table only has 3779 distinct entries. The Daily can have multiple entries, but not nearly 3 Million in this case. it should be around 300K entries for the past 24 hours.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
November 14, 2018 at 10:33 am
brian.cook - Wednesday, November 14, 2018 9:59 AMJonathan AC Roberts - Wednesday, November 14, 2018 8:48 AMSELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3]
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = b.WEA
AND ((b.SSI1 <> a.SSI1 OR b.SSI1 IS NULL AND a.SSI1 IS NOT NULL OR b.SSI1 IS NOT NULL AND a.SSI1 IS NULL)
OR (b.SSI2 <> a.SSI2 OR (b.SSI2 IS NULL AND a.SSI2 IS NOT NULL) OR (b.SSI2 IS NOT NULL AND a.SSI2 IS NULL))
OR (b.SSI3 <> a.SSI3 OR (b.SSI3 IS NULL AND a.SSI3 IS NOT NULL) OR (b.SSI3 IS NOT NULL AND a.SSI3 IS NULL)))This is working partially. It looks like it is not matching the WEA column of the daily table to the WEA of the static table. It also comes back with an astronomical number of result rows. the static table only has 3779 distinct entries. The Daily can have multiple entries, but not nearly 3 Million in this case. it should be around 300K entries for the past 24 hours.
Thanks,
Ah!ON b.WEA = b.WEA
should be ON b.WEA = a.WEA
November 14, 2018 at 11:01 am
Jonathan AC Roberts - Wednesday, November 14, 2018 10:33 AMbrian.cook - Wednesday, November 14, 2018 9:59 AMJonathan AC Roberts - Wednesday, November 14, 2018 8:48 AMSELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3]
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = b.WEA
AND ((b.SSI1 <> a.SSI1 OR b.SSI1 IS NULL AND a.SSI1 IS NOT NULL OR b.SSI1 IS NOT NULL AND a.SSI1 IS NULL)
OR (b.SSI2 <> a.SSI2 OR (b.SSI2 IS NULL AND a.SSI2 IS NOT NULL) OR (b.SSI2 IS NOT NULL AND a.SSI2 IS NULL))
OR (b.SSI3 <> a.SSI3 OR (b.SSI3 IS NULL AND a.SSI3 IS NOT NULL) OR (b.SSI3 IS NOT NULL AND a.SSI3 IS NULL)))This is working partially. It looks like it is not matching the WEA column of the daily table to the WEA of the static table. It also comes back with an astronomical number of result rows. the static table only has 3779 distinct entries. The Daily can have multiple entries, but not nearly 3 Million in this case. it should be around 300K entries for the past 24 hours.
Thanks,
Ah!
ON b.WEA = b.WEA
should beON b.WEA = a.WEA
That's better! Thanks!!!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
November 14, 2018 at 11:06 am
brian.cook - Wednesday, November 14, 2018 11:01 AMJonathan AC Roberts - Wednesday, November 14, 2018 10:33 AMbrian.cook - Wednesday, November 14, 2018 9:59 AMJonathan AC Roberts - Wednesday, November 14, 2018 8:48 AMSELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3]
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = b.WEA
AND ((b.SSI1 <> a.SSI1 OR b.SSI1 IS NULL AND a.SSI1 IS NOT NULL OR b.SSI1 IS NOT NULL AND a.SSI1 IS NULL)
OR (b.SSI2 <> a.SSI2 OR (b.SSI2 IS NULL AND a.SSI2 IS NOT NULL) OR (b.SSI2 IS NOT NULL AND a.SSI2 IS NULL))
OR (b.SSI3 <> a.SSI3 OR (b.SSI3 IS NULL AND a.SSI3 IS NOT NULL) OR (b.SSI3 IS NOT NULL AND a.SSI3 IS NULL)))This is working partially. It looks like it is not matching the WEA column of the daily table to the WEA of the static table. It also comes back with an astronomical number of result rows. the static table only has 3779 distinct entries. The Daily can have multiple entries, but not nearly 3 Million in this case. it should be around 300K entries for the past 24 hours.
Thanks,
Ah!
ON b.WEA = b.WEA
should beON b.WEA = a.WEAThat's better! Thanks!!!
If you don't have NULLs it is a much simpler SQL statement:SELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3]
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = a.WEA
AND (b.SSI1 <> a.SSI1
OR b.SSI2 <> a.SSI2
OR b.SSI3 <> a.SSI3)
November 14, 2018 at 12:07 pm
Jonathan AC Roberts - Wednesday, November 14, 2018 11:06 AMbrian.cook - Wednesday, November 14, 2018 11:01 AMJonathan AC Roberts - Wednesday, November 14, 2018 10:33 AMbrian.cook - Wednesday, November 14, 2018 9:59 AMJonathan AC Roberts - Wednesday, November 14, 2018 8:48 AMSELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3]
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = b.WEA
AND ((b.SSI1 <> a.SSI1 OR b.SSI1 IS NULL AND a.SSI1 IS NOT NULL OR b.SSI1 IS NOT NULL AND a.SSI1 IS NULL)
OR (b.SSI2 <> a.SSI2 OR (b.SSI2 IS NULL AND a.SSI2 IS NOT NULL) OR (b.SSI2 IS NOT NULL AND a.SSI2 IS NULL))
OR (b.SSI3 <> a.SSI3 OR (b.SSI3 IS NULL AND a.SSI3 IS NOT NULL) OR (b.SSI3 IS NOT NULL AND a.SSI3 IS NULL)))This is working partially. It looks like it is not matching the WEA column of the daily table to the WEA of the static table. It also comes back with an astronomical number of result rows. the static table only has 3779 distinct entries. The Daily can have multiple entries, but not nearly 3 Million in this case. it should be around 300K entries for the past 24 hours.
Thanks,
Ah!
ON b.WEA = b.WEA
should beON b.WEA = a.WEAThat's better! Thanks!!!
If you don't have NULLs it is a much simpler SQL statement:
SELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3]
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = a.WEA
AND (b.SSI1 <> a.SSI1
OR b.SSI2 <> a.SSI2
OR (b.SSI3 <> a.SSI3)
If you don't mind a follow up question...
If I wanted to add two more columns, and then put the resulting numerical difference in those columns from the values of the reference table, how could I add that?
So in example;
220590010000 2018-11-14 02:30:23.050 53 35 53 37 0 2
220760020000 2018-11-14 00:24:20.293 57 24 56 23 -1 -1
etc.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
November 14, 2018 at 12:48 pm
Difference between what? The SSI1, SSI2, and SSI3 columns?
If they're dates, use DATEDIFF(), otherwise just subtract.
November 14, 2018 at 1:04 pm
SELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3],
ISNULL(a.[SSI1],0)-ISNULL(b.[SSI1],0) SSI1Difference,
ISNULL(a.[SSI2],0)-ISNULL(b.[SSI2],0) SSI2Difference,
ISNULL(a.[SSI3],0)-ISNULL(b.[SSI3],0) SSI3Difference
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = a.WEA
AND ((b.SSI1 <> a.SSI1 OR b.SSI1 IS NULL AND a.SSI1 IS NOT NULL OR b.SSI1 IS NOT NULL AND a.SSI1 IS NULL)
OR (b.SSI2 <> a.SSI2 OR (b.SSI2 IS NULL AND a.SSI2 IS NOT NULL) OR (b.SSI2 IS NOT NULL AND a.SSI2 IS NULL))
OR (b.SSI3 <> a.SSI3 OR (b.SSI3 IS NULL AND a.SSI3 IS NOT NULL) OR (b.SSI3 IS NOT NULL AND a.SSI3 IS NULL)))
November 14, 2018 at 3:00 pm
Jonathan AC Roberts - Wednesday, November 14, 2018 1:04 PMSELECT b.WEA,
b.[Date_Time],
a.[SSI1] [SSI1Reference],
a.[SSI2] [SSI2Reference],
a.[SSI3] [SSI3Reference],
b.[SSI1],
b.[SSI2],
b.[SSI3],
ISNULL(a.[SSI1],0)-ISNULL(b.[SSI1],0) SSI1Difference,
ISNULL(a.[SSI2],0)-ISNULL(b.[SSI2],0) SSI2Difference,
ISNULL(a.[SSI3],0)-ISNULL(b.[SSI3],0) SSI3Difference
FROM [dbo].[RT_Group_Coverage] a
INNER JOIN [dbo].[RT_Group_Status] b
ON b.WEA = b.WEA
AND ((b.SSI1 <> a.SSI1 OR b.SSI1 IS NULL AND a.SSI1 IS NOT NULL OR b.SSI1 IS NOT NULL AND a.SSI1 IS NULL)
OR (b.SSI2 <> a.SSI2 OR (b.SSI2 IS NULL AND a.SSI2 IS NOT NULL) OR (b.SSI2 IS NOT NULL AND a.SSI2 IS NULL))
OR (b.SSI3 <> a.SSI3 OR (b.SSI3 IS NULL AND a.SSI3 IS NOT NULL) OR (b.SSI3 IS NOT NULL AND a.SSI3 IS NULL)))
Yes, the SSI difference. My apologies for not being clear.
Thank you very much for the help and lessons!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply