Combining multiple views - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Combining multiple views

Author

Topic

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-12-13 : 11:14:21

I have a series of views that all compare data in two databases.

Each view uses <>'something' in the filter column.

When the view is run they should produce no results because i am hoping that the data in the two databases are the same. Each view compares something different in the data base. e.g. dates, money, code.

How can i combine these views so i can run them all together and produce a list of the incorrect comparisons.

And help would be much appreciated

jggtz
Starting Member

32 Posts

Posted - 2010-12-14 : 10:44:29

How are you executing those views actually ?
In the SQL Server Management?
Inside an application?

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-12-16 : 09:21:53

I am running the views through SQL server Management Studio. Here are 2 of the views that I am trying to run together.

SELECT TOP (100) PERCENT Ctrt_ContractPublicRef, [LAS Commdt], [NAS Commdt], [NAS Benefit], CommencementDate
FROM dbo.CommencementDate
WHERE (CommencementDate <> [LAS Commdt])
ORDER BY Ctrt_ContractPublicRef

SELECT TOP (100) PERCENT JohnTest.dbo.pm_prem.pm_policy, JohnTest.dbo.pm_prem.pm_totgr, SUM(dbo.Ctrt_CostElement.MonetaryAmount)
AS NAS_Premium
FROM JohnTest.dbo.pm_prem INNER JOIN
dbo.Ctrt_Contract ON
JohnTest.dbo.pm_prem.pm_policy COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Ctrt_Contract.Ctrt_ContractPublicRef INNER JOIN
dbo.Ctrt_CostElement ON dbo.Ctrt_Contract.Ctrt_ContractPublicRef = dbo.Ctrt_CostElement.Ctrt_ContractPublicRef AND
JohnTest.dbo.pm_prem.pm_policy COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Ctrt_CostElement.Ctrt_ContractPublicRef
GROUP BY JohnTest.dbo.pm_prem.pm_totgr, JohnTest.dbo.pm_prem.pm_benefit, JohnTest.dbo.pm_prem.pm_policy,
LEFT(dbo.Ctrt_Contract.Ctrt_ContractPublicRef, 1)
HAVING (JohnTest.dbo.pm_prem.pm_benefit = ' ') AND (NOT (LEFT(dbo.Ctrt_Contract.Ctrt_ContractPublicRef, 1) = 'C')) AND
(SUM(dbo.Ctrt_CostElement.MonetaryAmount) <> JohnTest.dbo.pm_prem.pm_totgr)
ORDER BY JohnTest.dbo.pm_prem.pm_policy

I want to be able to run these on the same view and then it display all results together. Or even just let me know how many results there are in each view. Is this possible? Eventually i want to be able to run 20-30 views together in this way.

JohnMcLaughlin
Starting Member

28 Posts

Posted - 2010-12-17 : 06:29:24

If i was able to change the output of the views just to display a count of indifferences rather than details would this be easier. Is there a way of combining these views so i can see a count of each view?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources