Constraining Join Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Constraining Join Results

Author

Topic

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-19 : 05:41:24

I am designing a very simple view to be used in an SQL report. The report will show the name and version of a system and the name and version of a sub system that is assigned to that system.

In my sample data the system has 11 versions in the database. Each time an attribute of the system is changed (name, description etc a new version is created).

The sub system has 5 versions. Again each time an attribute of the sub system is changed a new version of the sub system is created.

The "System" and the "Sub System" are joined by way of a "Link" table which includes the ID of both the system and sub system.

My issue is that when I join all three tables, it creates 55 entries, 1 entry for each combination of system and subsystem.

What I want to be able to do is to see what version of the subsystem was assigned to the system for each version of the system, and what system the sub system was assigned to for each of its versions. So in ttotal only 16 entries.

Below is the basic SQL I have written so far - it includes two tables that allow me to show the date each version of the system and subsystem were created.

SELECT dbo.System.Version, dbo.Link.Version, dbo.SubSystem.Version AS,
dbo.System.name AS System, dbo.SubSystem.name AS SubSystem, dbo.LatestChanges.AddedDate AS SystemAddDate,
LatestChanges_1.AddedDate AS SubSystemAddDate
FROM dbo.Link INNER JOIN
dbo.SubSystem ON dbo.Link.treatment_dbid = dbo.SubSystem.dbid INNER JOIN
dbo.System ON dbo.Link.system _dbid = dbo.System.dbid INNER JOIN
dbo.LatestChanges ON dbo.System.BCID = dbo.LatestChanges.ID AND dbo.System.Version = dbo.LatestChanges.Version INNER JOIN
dbo.LatestChanges AS LatestChanges_1 ON dbo.SubSystem.BCID = LatestChanges_1.ID AND dbo.SubSystem.Version = LatestChanges_1.Version

Any help would be greatly appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 08:48:56

do you've version number stored in system subsystem tables?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-19 : 09:16:51

Each system and subsystem is versioned when any changes to these are made.

The version number is stored in system table and subsystem tables respectively.

The date the change was made is stored in the LatestChanges view whihc can be used to get the date/time a change was made and who made the change amongst other things.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-19 : 14:18:44

quote:
Originally posted by sprotson

Each system and subsystem is versioned when any changes to these are made.

The version number is stored in system table and subsystem tables respectively.

The date the change was made is stored in the LatestChanges view whihc can be used to get the date/time a change was made and who made the change amongst other things.

will version number be continuos in both system and subsystem? if yes, then its a matter of including it in join condition to get corresponding system subsystem information

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-19 : 19:49:02

The versions are separate because the system and subsystem can change independently.

So the system could be at version 2 but the subsystem may be at version 5

The date and time if each version is also recorded

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-20 : 14:55:30

so you want always latest version before current version for each system in your output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-20 : 17:54:30

The system and sub systems are each versioned based on the date the system or sub system was changed. This date/time info is captured. The system and sub system can change independently of each other.

A system has a sub system assigned to it.

I want to be able to show for each version of the system what the version of the sub system was at the point that version of the system was created. Ideally I would also like to show for each version of the sub system, the version of teh system it was assigned to when it was updated also.

So lets so say we have 5 versions of the system (1-5), with the versions created on the following dates

1 01/01/2012
2 02/01/2012
3 03/01/2012
4 05/01/2012
5 06/01/2012

And 3 versions of the subsystem created on the following dates

1 01/01/2012
2 04/01/2012
3 07/01/2012

The report would show the following

System Version SubSystem Version Date
1 1 01/01/2012
2 1 02/01/2012
3 1 03/01/2012
3 2 04/01/2012
4 2 05/01/2012
5 2 06/01/2012
5 3 07/01/2012

NB: the tables record the date/time in one field rather than just the date (think it is to the nearest second), so can't just match the dates.

Hope that makes sense. If it doesn't I may be able to provide more info in an email - i can be reached at gking1@o2.co.uk

Thanks for all your responses so far

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-21 : 13:12:41

sorry i didnt understand how you associate subsystems to systems. for example for 1 and 2 you link them only to one subsystem (1) whereas for 3 you've 2 subsystems ( 1 & 2). Can you explain how you determine this?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-22 : 04:56:49

I think you may be misunderstanding the sample data, probably because of the way it is formatted in my forum response. I also did not include the name of the system or subsystem in the sample data.

In the sample data there is only 1 system and 1 subsystem. The report is showing which version of this system and subsystme are associated on the date the system or sub system changes.

Let me explain each line of the sample data as it would appear in the report. There are 3 columns, System version, subsystem version and date of change (either to system or the subsystem).

Line 1 - 1/1/12. System version 1 is associated to system version 1. Both were created on the same date at the same time.
Line 2 - 2/1/12. The system has changed so is now at version 2. It is still associated with subsystem version 1.
Line 3 - 3/1/12. The system has changed again so it is now at version 3. It is still associated with subsystem version 1.
Line 4 - 4/1/12. The sub system has changed to version 2. It is associated with version 3 of the system.
Line 5 - 5/1/12. The system has changed again so it is now at version 4. It is still associated with subsystem version 2.
Line 6 - 6/1/12. The system has changed again so it is now at version 5. It is still associated with subsystem version 2.
Line 7 - 7/1/12. The sub system has changed to version 3. It is associated with version 5 of the system.

Does that make things clearer?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-22 : 13:27:41

so what suggests the limits for the dates?
if its from use output (say @StartDate and @EndDate) you can do like


SELECT s.SysVersion,ss.SubSysVersion,f.[Date]
FROM dbo.CalendarTable(@StartDate,@EndDate,0,0) f
CROSS APPLY (SELECT MAX(SystemVersion) AS SysVersion
             FROM SystemTable
             WHETE [Date] <= f.[Date]
            )s
CROSS APPLY (SELECT MAX(SubSystemVersion) AS SubSysVersion
             FROM SubSystemTable
             WHETE [Date] <= f.[Date]
            )ss

dbo.CalendarTable function can be found in below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-23 : 04:36:27

The report would run based on a sytart date and end date.

If I understand your SQl correctly, the function returns a list of dates and for each date, the max version of the system and sub system is returned. Is that correct?

If so, the problem I see is that both the system and sub system can change many times on the same date. The precise date/time that a new version is created in recorded for both the system and sub system.

What I need to be able to do is see what version of the subsystem is assigned to the system at each change point (new version) and what system the sub system is assigned to at it's change point (new version)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 11:41:25

the problem I see is that both the system and sub system can change many times on the same date

In that case are you capturing time part also for change? In case of multiples which value you need to return for that day? or is it like you need to capture only change of system timings and get corresponding subsystem value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-23 : 12:11:42

Yes both the system and subsystem can change many times on the same date.

The system data is held in a table, the subsystem data is held in a table and another table connects the system and subsystem tables by way of a system ID and subsystem ID.

So if the system changes to a new version, I want to be able to return the version of the subsyetm at that date/time.

Ideally I also want to be able to determine the version of the system if a change is made to the subsystem that is assigned to that system.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 12:14:56

sorry you sample data doesnt match your explanation

you say

So if the system changes to a new version, I want to be able to return the version of the subsyetm at that date/time

but you've even cases where system value showing no change also captured in output

see

3 1 03/01/2012
3 2 04/01/2012

i cant see any change in system value here (both 3) then why is both records captured?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-23 : 12:21:08

That data refers to line 3 and 4 of the sample data.

Line 3 - 3/1/12. The system has changed again so it is now at version 3. It is still associated with subsystem version 1.
Line 4 - 4/1/12. The sub system has changed to version 2. It is associated with version 3 of the system.

Version 3 of the system is showing in the report twice as this is the ideal scenarion where by a change to the susbsystem and therefore the creation of a new version of the subsystem would return the version of the system it is assigned to.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 12:26:07

quote:
Originally posted by sprotson

That data refers to line 3 and 4 of the sample data.

Line 3 - 3/1/12. The system has changed again so it is now at version 3. It is still associated with subsystem version 1.
Line 4 - 4/1/12. The sub system has changed to version 2. It is associated with version 3 of the system.

Version 3 of the system is showing in the report twice as this is the ideal scenarion where by a change to the susbsystem and therefore the creation of a new version of the subsystem would return the version of the system it is assigned to.

so does that mean you need to track change of both? ie system and subsystem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-23 : 12:42:09

Yes, they each have their own table which contains the data for each system and each subsystem.

The two tables are joined in the report by way of a third table "Link", whihc contains the ID of each system and ID of each subsystem.

The version number and date/time of the change is kept in two tables, "Latest Changes System" and "latest Changes Subsystem".

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-23 : 12:46:16

quote:
Originally posted by sprotson

Yes, they each have their own table which contains the data for each system and each subsystem.

The two tables are joined in the report by way of a third table "Link", whihc contains the ID of each system and ID of each subsystem.

The version number and date/time of the change is kept in two tables, "Latest Changes System" and "latest Changes Subsystem".

sounds like this


SELECT s.Version AS SysVersion,ss.SubSysVersion, s.[date]
FROM System s
CROSS APPLY (SELECT MAX(Version) AS SubSysVersion
             FROM SubSystem ss1
             INNER JOIN Link l
             ON l.SubsystemID = ss1.SubSystemID
             WHERE ss1.[date] <= s.[date]
             AND l.SystemID = s.SystemID
            )ss

UNION

SELECT s.SysVersion, ss.Version, s.[date]
FROM SubSystem ss
CROSS APPLY (SELECT MAX(Version) AS SysVersion
             FROM System s1
             INNER JOIN Link l
             ON l.systemID = s1.SystemID
             WHERE s1.[date] <= ss.[date]
             AND l.SubSystemID = ss.SubSystemID
            )s
ORDER BY [date]

make sure you replace columns with correct names in your actual query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-24 : 05:12:45

Thanks very much for the help.

I have not been able to adjust your code to the correct columns or table names as yet. My issue is that the date when the system and sub system is updated is contained in another 2 tables. I think I mentioned this in a previous post.

Below is the code that I have used to join all 5 tables together. Can you suggest how I can modift this as per your previous post to get the desired result from the CROSS APPLY and UNION?

SELECT dbo.CallingListCME.BCID AS SystemID, dbo.CallingListCME.BCVersion AS SystemVersion, dbo.ConfigurationItemLatest.AddedDate AS SystemDate,
dbo.TreatmentCME.BCID AS SubSystemID, dbo.TreatmentCME.BCVersion, ConfigurationItemLatest_1.AddedDate
FROM dbo.CallingListCME LEFT OUTER JOIN
dbo.ConfigurationItemLatest ON dbo.CallingListCME.BCID = dbo.ConfigurationItemLatest.ID AND
dbo.CallingListCME.BCVersion = dbo.ConfigurationItemLatest.Version INNER JOIN
dbo.CallListTrtmCME ON dbo.CallingListCME.dbid = dbo.CallListTrtmCME.calling_list_dbid INNER JOIN
dbo.ConfigurationItemLatest AS ConfigurationItemLatest_1 RIGHT OUTER JOIN
dbo.TreatmentCME ON ConfigurationItemLatest_1.ID = dbo.TreatmentCME.BCID AND ConfigurationItemLatest_1.Version = dbo.TreatmentCME.BCVersion ON
dbo.CallListTrtmCME.treatment_dbid = dbo.TreatmentCME.dbid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:33:02

i cant analyse your query and suggest . can you post sample data from 5 tables so that its easier for me to give the query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sprotson
Yak Posting Veteran

75 Posts

Posted - 2012-04-25 : 05:13:09

Thanks

Below is date from 4 of the 5 tables. The 5th is not included as it only contains the link between the system and subsystem.

The first set of data below contains the data from the Treatment(ID, Version, DBID, Name)and ConfigurationItemLatest1(date)tables. This has 5 versions of the subsystem.

Sub System ID Sub System version Sub System DBID Sub System name Sub System Date
295 1 101 Treatment 1 10/5/2010 11:31:34 AM
295 2 101 Treatment 1 10/8/2010 05:35:42 AM
295 3 101 Treatment 1 10/13/2010 02:40:02 PM
295 4 101 Treatment 1 2/8/2011 11:19:13 AM
295 5 101 Treatment 1 5/23/2011 09:41:37 AM

The next set of data below contains the data from the CallingList(ID, Version, DBID, Name)and ConfigurationItemLatest(date)tables. This has 11 version of the system.

System ID System Version System DBID System Name System Date
172 1 101 Calling List 1 10/5/2010 11:31:32 AM
172 2 101 Calling List 1 10/8/2010 05:35:42 AM
172 3 101 Calling List 1 10/13/2010 02:40:58 PM
172 4 101 Calling List 1 11/4/2010 10:28:54 AM
172 5 101 Calling List 1 2/3/2011 06:16:15 PM
172 6 101 Calling List 1 2/3/2011 07:01:23 PM
172 7 101 Calling List 1 2/8/2011 11:09:04 AM
172 8 101 Calling List 1 2/8/2011 11:20:06 AM
172 9 101 Calling List 1 5/23/2011 09:41:37 AM
172 10 101 Calling List 1 9/7/2011 01:59:14 PM
172 11 101 Calling List 1 11/7/2011 11:06:49 AM

The last set of data below is the result I get when I combine all 5 tables as per my previous SQL.55 results in total.

System Name System ID System Version System Date Sub System Name Sub System ID Sub System Version Sub System Date
Calling List 1 172 1 10/5/2010 11:31:32 AM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 1 10/5/2010 11:31:32 AM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 1 10/5/2010 11:31:32 AM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 1 10/5/2010 11:31:32 AM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 1 10/5/2010 11:31:32 AM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 2 10/8/2010 05:35:42 AM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 2 10/8/2010 05:35:42 AM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 2 10/8/2010 05:35:42 AM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 2 10/8/2010 05:35:42 AM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 2 10/8/2010 05:35:42 AM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 3 10/13/2010 02:40:58 PM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 3 10/13/2010 02:40:58 PM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 3 10/13/2010 02:40:58 PM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 3 10/13/2010 02:40:58 PM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 3 10/13/2010 02:40:58 PM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 4 11/4/2010 10:28:54 AM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 4 11/4/2010 10:28:54 AM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 4 11/4/2010 10:28:54 AM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 4 11/4/2010 10:28:54 AM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 4 11/4/2010 10:28:54 AM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 5 2/3/2011 06:16:15 PM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 5 2/3/2011 06:16:15 PM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 5 2/3/2011 06:16:15 PM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 5 2/3/2011 06:16:15 PM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 5 2/3/2011 06:16:15 PM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 6 2/3/2011 07:01:23 PM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 6 2/3/2011 07:01:23 PM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 6 2/3/2011 07:01:23 PM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 6 2/3/2011 07:01:23 PM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 6 2/3/2011 07:01:23 PM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 7 2/8/2011 11:09:04 AM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 7 2/8/2011 11:09:04 AM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 7 2/8/2011 11:09:04 AM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 7 2/8/2011 11:09:04 AM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 7 2/8/2011 11:09:04 AM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 8 2/8/2011 11:20:06 AM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 8 2/8/2011 11:20:06 AM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 8 2/8/2011 11:20:06 AM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 8 2/8/2011 11:20:06 AM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 8 2/8/2011 11:20:06 AM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 9 5/23/2011 09:41:37 AM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 9 5/23/2011 09:41:37 AM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 9 5/23/2011 09:41:37 AM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 9 5/23/2011 09:41:37 AM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 9 5/23/2011 09:41:37 AM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 10 9/7/2011 01:59:14 PM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 10 9/7/2011 01:59:14 PM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 10 9/7/2011 01:59:14 PM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 10 9/7/2011 01:59:14 PM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 10 9/7/2011 01:59:14 PM Treatment 1 295 5 5/23/2011 09:41:37 AM
Calling List 1 172 11 11/7/2011 11:06:49 AM Treatment 1 295 1 10/5/2010 11:31:34 AM
Calling List 1 172 11 11/7/2011 11:06:49 AM Treatment 1 295 2 10/8/2010 05:35:42 AM
Calling List 1 172 11 11/7/2011 11:06:49 AM Treatment 1 295 3 10/13/2010 02:40:02 PM
Calling List 1 172 11 11/7/2011 11:06:49 AM Treatment 1 295 4 2/8/2011 11:19:13 AM
Calling List 1 172 11 11/7/2011 11:06:49 AM Treatment 1 295 5 5/23/2011 09:41:37 AM

This result is giving me a line for every version of the system against every version of the sub system.

What I am looking for is an entry when a new version of the system is created showing what the sub system version is at that date and time, and an entry for every time a new version of the subsystem is created showing what version of the system is was assigned to at that date/time.

I believe this should be 16 entries in total - 11 system changes and 5 susbsystem changes.

If the formatting of the data is difficult to read, I could email you the data if you let me know what your email address is

Many Thanks

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources