Solved

Better way to query two sets of criteria on same information - SQL Views

Posted on 2013-05-21
18
361 Views
Last Modified: 2013-05-22
Hi,

I have a table that I want to pull information out of twice and link that information together.

I currently have two views setup, then a third based on those 2 views with the inner join defined.

For example:-

View 1

Status = CUR
Firstname = Ted
Lastname = Baker
DOB = 10/10/10
ArrearsNET

View 2
Satus = FOR
Firstname = Ted
Lastname = Baker
DOB = 10/10/10
BalanceOutstanding
ArrearsOfficer

View 3
Firstname
Lastname
DOB
ArrearsNET
BalanceOutstanding
ArrearsOfficer
WHERE View1.Firstname = View2.Firstname AND View1.Lastname = View2.Lastname AND View1.DOB = View2.DOB

This seems obviously a rather inefficient way of doing things, but unfortunately I'm no expert.

The data I want is basically a list of people who were former customers with Arrears, who are also current customers.  The data is in the same table and I can only define the two by establishing two instances of the customers Firstname/Lastname/DOB in the table.  Put one entry will be 'FOR' and one entry will be 'CUR'.

Any help, hints or tips on how I can simplify or make this more efficient would be greatly appreaciated as the View3 runs very slow and sometimes timesout (alot of information in there, and I do actually link to other tables in each view and I pull back more fields than I have given in the example, but tried to simplify it for this question's purpose).

Thanks in Advance.
0
Comment
Question by:deborahhowson00
  • 9
  • 7
  • 2
18 Comments
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39183896
How do you identify that a customer is both a former and a current customer? That doesn't become clear from your queries.
0
 

Author Comment

by:deborahhowson00
ID: 39183914
by the Firstname, Lastname and DOB as shown in View3.

Basically Ted Baker dob 10/10/10 MAY be a former and a current customer, but with different customer ID's!  He could have re-registered under a new/different address.  The point of the query is to find people who are current customers but who have probably also been former customers and owe us money!  Thanks
0
 

Author Comment

by:deborahhowson00
ID: 39183942
Sorry, in addition to that View1 has a STATUS = 'CUR' and View2 has a STATUS = 'FOR' as shown in  my example.  Regards.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39183946
Try this:

Select		a.customer_ID as 'ID_1'
		,	b.customer_ID as 'ID_2'
		,	a.firstname
		,	a.lastname
		,	a.DOB
		,	a.ArrearsNET
		,	b.BalanceOutstanding
		,	b.ArrearsOfficer
From	yourtable a 
		inner join yourtable b
			on a.firstname = b.firstname
			and a.lastname = b.lastname
			and a.DOB = b.DOB
order by	a.lastname
		,	a.firstname
		,	a.DOB

Open in new window


Replace yourtable with your original table name in the database. I added the customer ID's from both tables so you can ID the customers better.
Pretty sure you already know this, but joining tables based on text fields is seriously bad practice, especially when it comes to names which are prone do different/misspelling. If I were you I'd clean up your database, and make sure that the same customer entries all have the same customer ID, or a new ID field which identifies the duplicate entries in case your customer ID is your primary key.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39183947
Try this:

Select		a.customer_ID as 'ID_1'
		,	b.customer_ID as 'ID_2'
		,	a.firstname
		,	a.lastname
		,	a.DOB
		,	a.ArrearsNET
		,	b.BalanceOutstanding
		,	b.ArrearsOfficer
From	yourtable a 
		inner join yourtable b
			on a.firstname = b.firstname
			and a.lastname = b.lastname
			and a.DOB = b.DOB
order by	a.lastname
		,	a.firstname
		,	a.DOB

Open in new window


Replace yourtable with your original table name in the database. I added the customer ID's from both tables so you can ID the customers better.
Pretty sure you already know this, but joining tables based on text fields is seriously bad practice, especially when it comes to names which are prone do different/misspelling. If I were you I'd clean up your database, and make sure that the same customer entries all have the same customer ID, or a new ID field which identifies the duplicate entries in case your customer ID is your primary key.
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39183948
Try this:

Select		a.customer_ID as 'ID_1'
		,	b.customer_ID as 'ID_2'
		,	a.firstname
		,	a.lastname
		,	a.DOB
		,	a.ArrearsNET
		,	b.BalanceOutstanding
		,	b.ArrearsOfficer
From	yourtable a 
		inner join yourtable b
			on a.firstname = b.firstname
			and a.lastname = b.lastname
			and a.DOB = b.DOB
order by	a.lastname
		,	a.firstname
		,	a.DOB

Open in new window


Replace yourtable with your original table name in the database. I added the customer ID's from both tables so you can ID the customers better.
Pretty sure you already know this, but joining tables based on text fields is seriously bad practice, especially when it comes to names which are prone do different/misspelling. If I were you I'd clean up your database, and make sure that the same customer entries all have the same customer ID, or a new ID field which identifies the duplicate entries in case your customer ID is your primary key.
0
 

Author Comment

by:deborahhowson00
ID: 39183971
Hi thanks.  The data I have given is just a very simplified example, I cannot really give more details for confidentiality purposes.  However, I work for a Housing Organisation and we are somewhat 'obliged' to take on new 'customers' as I have called them... regardless of whether they have been previous 'customers' (for example if they had maybe become homeless), and regardless of whether they have outstanding debt with us.

I cannot allocate the same 'CustomerID' to these people as it derived from the address that they are resident at (actually a Tenancy Reference).   If someone leaves one of our houses leaving a debt behind, we can rehouse them at some point further in time (and may be obliged to do so) but they will get a new 'Tenancy Reference' (my CustomerID in the example).  The ONLY way I can establish out of the literally hundreds of thousands of rows of data if someone was a previous tenant, is based on their firstname, lastname and DOB.  And yes, I unfortnately know that is very bad practice but it is the one instance where I just have no choice.

In the query you have give, how does it define the two different 'FOR' and 'CUR' status'?

My final info basically gives me a Tenant, tells me how much they owed from their previous tenancy, what the previous tenancy reference was, what their current tenancy reference is, and other things such as outstanding balance when they were 'for' tenants and current balance of their 'cur' tenancy.

Hope this explains better.


Thanks!
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39184045
No points please.  The one thing that is missing from the previous query is to disregard the relationship to the current record.  So try something like:

Select a.customer_ID as 'ID_Current'
,      b.customer_ID as 'ID_For'
,      a.firstname
,      a.lastname
,      a.DOB
,      a.ArrearsNET
,      b.BalanceOutstanding
,      b.ArrearsOfficer
From      yourtable a
            inner join yourtable b
                  on a.firstname = b.firstname
                  and a.lastname = b.lastname
                  and a.DOB = b.DOB
WHERE a.Customer_ID <> b.Customer_ID
order by      a.lastname
            ,      a.firstname
            ,      a.DOB

OR, if the Customer_ID field is a counter (autonumber) field, you could try a WHERE clause that looks like:

WHERE a.Customer_ID > b.Customer_ID
0
 

Author Comment

by:deborahhowson00
ID: 39184298
Fyed I think yours is looking a bit like what I'm wanting and giving me ideas.  The customerID's have nothing to do with it, a person will have two totally different ID's from when they were Former Tenants to Current Tenants.  The only way to match the former and current record is by their Forename, Surname and DOB matching.
I need the 'where' clause to select former tenants first in table a though where Status = 'For',  and then repeated in Table b where Status = 'Cur'.

To try and simplify again using your a and b example.

I've attached a bit of of code to try and help.  First I need to pull data from MYTABLE.a where arrears are >0 (so tells me that person is in debt) and Status = FOR.  Then I need to pull data from MYTABLE.b where Status = 'CUR' and the Forename, Surname and DOB match MYTABLE.a Forename, Surname and DOB so I know we can transfer the debt.  I have to know they are also CUR as well as FOR Tenants, because if they're just former then I cannot transfer the debt because I have nowhere on the system to transfer to!

Hope this helps, thanks again!

And yes I know the customerID's should ideally be the same, however we are a huge organisation with an equally large customer service desk and inevitably people are duplicated.  This query is to find people who have come back to one of our properties but are in debt to us from previously, but have for 'whatever reason' been given a different customerID when they returned.  I'm sorry but I can tell each new 'temp' we get every week till I'm blue in the face to check for a person on the system before creating another, but life just doesn't work that way... hence why we have IT to 'pick up the mess'! :)
work.xls
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39184371
Deborah,

I'm trying to figure out how you determined the value for your [Status] field?

If you don't include the WHERE clause line that excludes where the two customer_ID values are equal, you would get multiple records for the same individual; you don't want to do that.  So, try:

Select a.customerID as 'ID_Current'
,      b.customerID as 'ID_For'
,      a.firstname
,      a.lastname
,      a.DOB
,      a.ArrearsNET
,      b.CurrentBalance
,      a.InArrears
,      b.Address as Old_Address
,      a.Address as New_Address
From      yourtable a
            inner join yourtable b
                  on a.firstname = b.firstname
                  and a.lastname = b.lastname
                  and a.DOB = b.DOB
WHERE a.Customer_ID <> b.Customer_ID
AND b.Status = 'FOR'
AND b.InArrears = 'Yes'
order by     a.lastname
            ,      a.firstname
            ,      a.DOB
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39184379
Hi Deborah,

If the "status" column in your attached data is an existing column in your database table (NOT the views you were describing) then you can add in the Where clause:
where a.customer_ID <> b.customer_ID
and a.status = 'FOR' and b.status = 'CUR'

Open in new window

.

The point that Fyed made about checking that the customer ID's are not equal is valid and a sloppy oversight from my part, because otherwise you will end up with duplicate records.

About the point on creating unique ID's, I'm not trying to critisize, like I said it's probably something you already know. I know from experience it's hard to get people to check these things thoroughly. One little trick that our own system utilizes is to check for duplicates based on the first and last names entered before the record is written into the database. If the name combo already exists a pop up appears asking whether this is the same person as the existing customer. I don't know if your system is customizable, but this might be something worth checking to save you this headache in the long run.

Best regards,

Kvwielink
0
 

Author Comment

by:deborahhowson00
ID: 39186751
I don't need to specify anything about the customerID.  The fact that we're pulling a MYTABLE.a with a Status of FOR then a MYTABLE.b with a Status of CUR ensures that you don't get matching customer ID's.  If the customerID's were the same then i wouldn't need to be doing this report because like I said, it's to catch those that have been setup twice under different ID's.

Anyway, I get the principle, create table a and b from the same data and link the two.  I have put that in to practice and can get the test results on a very simple version based on just the one table..  However I am struggling with how to nest/subquery my other tables from the two original views.  I have attached the original SQL views.  Arrears_Bal would be say need to be my TableA and Arrears_Cats would be need to be my TableB then join them together on the Firstname/Lastname/DOB (which I currently do as shown in View3).  I am once again am stuck.

Any chance of showing me how I would do this?

Thanks.
views.txt
0
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 500 total points
ID: 39186793
Hi Deborah,

Valid point about the customer ID.
I think this query will replace your 3 views with a single one:
Create VIEW Arrears_full

as

SELECT		d.TenancyRef as 'TenancyRefFormer'
		,	d.CorrespondanceName1 as 'CorresponanceName1Former'
		,	d.TenancyStatus as 'TenancyStatusFormer'
		,	d.TenancyCat as 'TenancyCatFormer'
		,	c.PersonRef as 'PersonRefFormer'
		,	d.PlaceRef  as 'PlaceRefFormer'
		,	d.ArrearsStage as 'ArrearsStageFormer'
		,	d.ArrearsDescription as 'ArrearsDescriptionFormer'
		,	b.Forenames
		,	b.Surname
		,	b.DateofBirth
		,	d.CurrentBalance as 'CurrentBalanceFormer'
		,	d.TenancyEnd as 'TenancyEndFormer'
		,	e.MGTArea
		,	e.FormerArrearsOfficerLookup
		,	e.FormerArrearsOfficer
		,	h.ArrearsNetHB
		,	h.TenancyRef as 'TenancyRefCurrent'
		,	h.CorrespondanceName1 as 'CorrespondanceName1Current'
		,	h.TenancyStatus as 'TenancyStatusCurrent'
		,	h.TenancyCat as 'TenancyCatCurrent'
		,	g.PersonRef as 'PersonRefCurrent'
		,	h.PlaceRef as 'PlaceRefCurrent'
		,	h.ArrearsStage as 'ArrearsStageCurrent'
		,	h.ArrearsDescription as 'ArrearsDescriptionCurrent'
		,	g.OnTenancy
		,	i.FullAddress
FROM	dbo.vwPersonCurrent a
		inner join dbo.vwPersonCurrent b
			on a.forenames = b.forenames
			and a.surname = b.surname
			and a.dateofbirth = b.dateofbirth
		INNER JOIN dbo.vwTenancyPersonCurrent c
			ON a.PersonRef = c.PersonRef 
		INNER JOIN dbo.vwTenancyPersonCurrent g
			on b.PersonRef = g.PersonRef
		INNER JOIN dbo.DimTenancy d
			ON c.TenancySystemRef = d.TenancySystemRef
		INNER JOIN dbo.DimTenancy h
			on g.TenancySystemRef = h.TenancySystemRef
		INNER JOIN dbo.vwLocationCurrent e
			ON d.PlaceRef = e.PlaceRef
		INNER JOIN dbo.vwPlaceCurrent f
			ON d.PlaceRef = f.PlaceRef 
		INNER jOIN dbo.vwPlaceCurrent i
			on h.PlaceRef = i.PlaceRef
WHERE	(c.OnTenancy = 1)
AND		(d.TenancyStatus <> N'TER') 
AND		(h.TenancyStatus <> N'TER')
AND		(d.TenancyStatus <> N'CUR')
AND		(h.TenancyStatus <> N'FOR') 
AND		(d.TenancyCat <> N'OTH') 
AND		(h.TenancyCat <> N'OTH')
AND		(d.CurrentBalance <> 0) 
AND		(d.Curr = 1)
AND		(h.Curr = 1)

Open in new window


I've renamed many of the columns with either ...FORMER or ...CURRENT to make it more clear from which instance of the table they are pulled. Otherwise you will have duplicate column names in your final result set.

Rgds,

Kvwielink
0
 

Author Comment

by:deborahhowson00
ID: 39186805
Hi that's great Kvwielink and certainly makes it alot clearer to me how to incorporate more than one table.  The sytax seems spot on as I get no errors, however the query does time out just like my View3 did.  Probably because I'm having to do the query on Firstname/Lastname/DOB.  This may have all been to no avail, however I have learnt a great deal so it certainly hasn't been a waste.

Thank you for all your assistance, points will be awarded, cheers!
0
 

Author Closing Comment

by:deborahhowson00
ID: 39186812
Fantastic, thank you for your time!
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39186937
Hi Deborah,

If it's still timing out you might want to try a different approach:


Create procedure uSP_Arrears

as

set nocount on

--create temp table for customers which are both former and current

Create table	#Arrears
(	personrefformer		int
,	personrefcurrent		int
,	forenames				nvarchar(100)
,	surname					nvarchar(100)
,	dateofbirth				datetime
)

--Insert customer identities into temp table

Insert into #arrears
(	personrefformer
,	personrefcurrent
,	forenames
,	surname
,	dateofbirth
)
Select		a.personref
		,	b.personref
		,	a.forenames
		,	a.surname
		,	a.dateofbirth
FROM	dbo.vwPersonCurrent a
		inner join dbo.vwPersonCurrent b
			on a.forenames = b.forenames
			and a.surname = b.surname
			and a.dateofbirth = b.dateofbirth
			
--Select details for selected customers only
			
Select		d.TenancyRef as 'TenancyRefFormer'
		,	d.CorrespondanceName1 as 'CorresponanceName1Former'
		,	d.TenancyStatus as 'TenancyStatusFormer'
		,	d.TenancyCat as 'TenancyCatFormer'
		,	c.PersonRef as 'PersonRefFormer'
		,	d.PlaceRef  as 'PlaceRefFormer'
		,	d.ArrearsStage as 'ArrearsStageFormer'
		,	d.ArrearsDescription as 'ArrearsDescriptionFormer'
		,	a.Forenames
		,	a.Surname
		,	a.DateofBirth
		,	d.CurrentBalance as 'CurrentBalanceFormer'
		,	d.TenancyEnd as 'TenancyEndFormer'
		,	e.MGTArea
		,	e.FormerArrearsOfficerLookup
		,	e.FormerArrearsOfficer
		,	h.ArrearsNetHB
		,	h.TenancyRef as 'TenancyRefCurrent'
		,	h.CorrespondanceName1 as 'CorrespondanceName1Current'
		,	h.TenancyStatus as 'TenancyStatusCurrent'
		,	h.TenancyCat as 'TenancyCatCurrent'
		,	g.PersonRef as 'PersonRefCurrent'
		,	h.PlaceRef as 'PlaceRefCurrent'
		,	h.ArrearsStage as 'ArrearsStageCurrent'
		,	h.ArrearsDescription as 'ArrearsDescriptionCurrent'
		,	g.OnTenancy
		,	i.FullAddress
From	#arrears a
		INNER JOIN dbo.vwTenancyPersonCurrent c
			ON a.PersonRefformer = c.PersonRef 
		INNER JOIN dbo.vwTenancyPersonCurrent g
			on a.PersonRefcurrent = g.PersonRef
		INNER JOIN dbo.DimTenancy d
			ON c.TenancySystemRef = d.TenancySystemRef
		INNER JOIN dbo.DimTenancy h
			on g.TenancySystemRef = h.TenancySystemRef
		INNER JOIN dbo.vwLocationCurrent e
			ON d.PlaceRef = e.PlaceRef
		INNER JOIN dbo.vwPlaceCurrent f
			ON d.PlaceRef = f.PlaceRef 
		INNER jOIN dbo.vwPlaceCurrent i
			on h.PlaceRef = i.PlaceRef
WHERE	(c.OnTenancy = 1)
AND		(d.TenancyStatus <> N'TER') 
AND		(h.TenancyStatus <> N'TER')
AND		(d.TenancyStatus <> N'CUR')
AND		(h.TenancyStatus <> N'FOR') 
AND		(d.TenancyCat <> N'OTH') 
AND		(h.TenancyCat <> N'OTH')
AND		(d.CurrentBalance <> 0) 
AND		(d.Curr = 1)
AND		(h.Curr = 1)

--Drop temp table

Drop table #arrears

Set nocount off

Open in new window


Stored procedures are generally faster than views or individual select statements. Also, by creating a temporary table first and inserting first only the PersonRef's of those individuals we're interested in, I'm hoping that linking only this subset to the rest of the tables would result in less data being read.
You can create this procedure on your database, then execute the following command:

Exec uSP_Arrears

Open in new window


This will execute the procedure and hopefully give you the results you want much faster. Out of curiosity, how many records are there in your main table?

Rgds,

Kvwielink
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39186938
Hi Deborah,

If it's still timing out you might want to try a different approach:


Create procedure uSP_Arrears

as

set nocount on

--create temp table for customers which are both former and current

Create table	#Arrears
(	personrefformer		int
,	personrefcurrent		int
,	forenames				nvarchar(100)
,	surname					nvarchar(100)
,	dateofbirth				datetime
)

--Insert customer identities into temp table

Insert into #arrears
(	personrefformer
,	personrefcurrent
,	forenames
,	surname
,	dateofbirth
)
Select		a.personref
		,	b.personref
		,	a.forenames
		,	a.surname
		,	a.dateofbirth
FROM	dbo.vwPersonCurrent a
		inner join dbo.vwPersonCurrent b
			on a.forenames = b.forenames
			and a.surname = b.surname
			and a.dateofbirth = b.dateofbirth
			
--Select details for selected customers only
			
Select		d.TenancyRef as 'TenancyRefFormer'
		,	d.CorrespondanceName1 as 'CorresponanceName1Former'
		,	d.TenancyStatus as 'TenancyStatusFormer'
		,	d.TenancyCat as 'TenancyCatFormer'
		,	c.PersonRef as 'PersonRefFormer'
		,	d.PlaceRef  as 'PlaceRefFormer'
		,	d.ArrearsStage as 'ArrearsStageFormer'
		,	d.ArrearsDescription as 'ArrearsDescriptionFormer'
		,	a.Forenames
		,	a.Surname
		,	a.DateofBirth
		,	d.CurrentBalance as 'CurrentBalanceFormer'
		,	d.TenancyEnd as 'TenancyEndFormer'
		,	e.MGTArea
		,	e.FormerArrearsOfficerLookup
		,	e.FormerArrearsOfficer
		,	h.ArrearsNetHB
		,	h.TenancyRef as 'TenancyRefCurrent'
		,	h.CorrespondanceName1 as 'CorrespondanceName1Current'
		,	h.TenancyStatus as 'TenancyStatusCurrent'
		,	h.TenancyCat as 'TenancyCatCurrent'
		,	g.PersonRef as 'PersonRefCurrent'
		,	h.PlaceRef as 'PlaceRefCurrent'
		,	h.ArrearsStage as 'ArrearsStageCurrent'
		,	h.ArrearsDescription as 'ArrearsDescriptionCurrent'
		,	g.OnTenancy
		,	i.FullAddress
From	#arrears a
		INNER JOIN dbo.vwTenancyPersonCurrent c
			ON a.PersonRefformer = c.PersonRef 
		INNER JOIN dbo.vwTenancyPersonCurrent g
			on a.PersonRefcurrent = g.PersonRef
		INNER JOIN dbo.DimTenancy d
			ON c.TenancySystemRef = d.TenancySystemRef
		INNER JOIN dbo.DimTenancy h
			on g.TenancySystemRef = h.TenancySystemRef
		INNER JOIN dbo.vwLocationCurrent e
			ON d.PlaceRef = e.PlaceRef
		INNER JOIN dbo.vwPlaceCurrent f
			ON d.PlaceRef = f.PlaceRef 
		INNER jOIN dbo.vwPlaceCurrent i
			on h.PlaceRef = i.PlaceRef
WHERE	(c.OnTenancy = 1)
AND		(d.TenancyStatus <> N'TER') 
AND		(h.TenancyStatus <> N'TER')
AND		(d.TenancyStatus <> N'CUR')
AND		(h.TenancyStatus <> N'FOR') 
AND		(d.TenancyCat <> N'OTH') 
AND		(h.TenancyCat <> N'OTH')
AND		(d.CurrentBalance <> 0) 
AND		(d.Curr = 1)
AND		(h.Curr = 1)

--Drop temp table

Drop table #arrears

Set nocount off

Open in new window


Stored procedures are generally faster than views or individual select statements. Also, by creating a temporary table first and inserting first only the PersonRef's of those individuals we're interested in, I'm hoping that linking only this subset to the rest of the tables would result in less data being read.
You can create this procedure on your database, then execute the following command:

Exec uSP_Arrears

Open in new window


This will execute the procedure and hopefully give you the results you want much faster. Out of curiosity, how many records are there in your main table?

Rgds,

Kvwielink
0
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 39186939
Hi Deborah,

If it's still timing out you might want to try a different approach:


Create procedure uSP_Arrears

as

set nocount on

--create temp table for customers which are both former and current

Create table	#Arrears
(	personrefformer		int
,	personrefcurrent		int
,	forenames				nvarchar(100)
,	surname					nvarchar(100)
,	dateofbirth				datetime
)

--Insert customer identities into temp table

Insert into #arrears
(	personrefformer
,	personrefcurrent
,	forenames
,	surname
,	dateofbirth
)
Select		a.personref
		,	b.personref
		,	a.forenames
		,	a.surname
		,	a.dateofbirth
FROM	dbo.vwPersonCurrent a
		inner join dbo.vwPersonCurrent b
			on a.forenames = b.forenames
			and a.surname = b.surname
			and a.dateofbirth = b.dateofbirth
			
--Select details for selected customers only
			
Select		d.TenancyRef as 'TenancyRefFormer'
		,	d.CorrespondanceName1 as 'CorresponanceName1Former'
		,	d.TenancyStatus as 'TenancyStatusFormer'
		,	d.TenancyCat as 'TenancyCatFormer'
		,	c.PersonRef as 'PersonRefFormer'
		,	d.PlaceRef  as 'PlaceRefFormer'
		,	d.ArrearsStage as 'ArrearsStageFormer'
		,	d.ArrearsDescription as 'ArrearsDescriptionFormer'
		,	a.Forenames
		,	a.Surname
		,	a.DateofBirth
		,	d.CurrentBalance as 'CurrentBalanceFormer'
		,	d.TenancyEnd as 'TenancyEndFormer'
		,	e.MGTArea
		,	e.FormerArrearsOfficerLookup
		,	e.FormerArrearsOfficer
		,	h.ArrearsNetHB
		,	h.TenancyRef as 'TenancyRefCurrent'
		,	h.CorrespondanceName1 as 'CorrespondanceName1Current'
		,	h.TenancyStatus as 'TenancyStatusCurrent'
		,	h.TenancyCat as 'TenancyCatCurrent'
		,	g.PersonRef as 'PersonRefCurrent'
		,	h.PlaceRef as 'PlaceRefCurrent'
		,	h.ArrearsStage as 'ArrearsStageCurrent'
		,	h.ArrearsDescription as 'ArrearsDescriptionCurrent'
		,	g.OnTenancy
		,	i.FullAddress
From	#arrears a
		INNER JOIN dbo.vwTenancyPersonCurrent c
			ON a.PersonRefformer = c.PersonRef 
		INNER JOIN dbo.vwTenancyPersonCurrent g
			on a.PersonRefcurrent = g.PersonRef
		INNER JOIN dbo.DimTenancy d
			ON c.TenancySystemRef = d.TenancySystemRef
		INNER JOIN dbo.DimTenancy h
			on g.TenancySystemRef = h.TenancySystemRef
		INNER JOIN dbo.vwLocationCurrent e
			ON d.PlaceRef = e.PlaceRef
		INNER JOIN dbo.vwPlaceCurrent f
			ON d.PlaceRef = f.PlaceRef 
		INNER jOIN dbo.vwPlaceCurrent i
			on h.PlaceRef = i.PlaceRef
WHERE	(c.OnTenancy = 1)
AND		(d.TenancyStatus <> N'TER') 
AND		(h.TenancyStatus <> N'TER')
AND		(d.TenancyStatus <> N'CUR')
AND		(h.TenancyStatus <> N'FOR') 
AND		(d.TenancyCat <> N'OTH') 
AND		(h.TenancyCat <> N'OTH')
AND		(d.CurrentBalance <> 0) 
AND		(d.Curr = 1)
AND		(h.Curr = 1)

--Drop temp table

Drop table #arrears

Set nocount off

Open in new window


Stored procedures are generally faster than views or individual select statements. Also, by creating a temporary table first and inserting first only the PersonRef's of those individuals we're interested in, I'm hoping that linking only this subset to the rest of the tables would result in less data being read.
You can create this procedure on your database, then execute the following command:

Exec uSP_Arrears

Open in new window


This will execute the procedure and hopefully give you the results you want much faster. Out of curiosity, how many records are there in your main table?

Rgds,

Kvwielink
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now