SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Any improvement possible in the given query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kamii47
Constraint Violating Yak Guru

352 Posts

Posted - 12/04/2012 :  02:06:31  Show Profile  Reply with Quote
Declare @StartDate DATETIME
Declare @EndDate DATETIME

Set @StartDate = '2012/10/01'
Set @EndDate = '2012/10/31'

Select tt.MyTableID_PK
, tt.Firstname
, tt.Lastname
, tt.Contact1
, tt.Email
, tt.TotalFeeCharged
, Case
When ct.CaseTypeId = 1 Then 'Traffic'
Else 'Criminal'
End As 'CaseType'
, t.RecDate As LeadDate
, (Select Min(ttp1.RecDate)
From MyOtherDB.dbo.tblTicketsPayment ttp1
Where ttp1.MyTableID = tt.MyTableID_PK
And ttp1.PaymentVoid = 0
And ttp1.isRemoved = 0) As HireDate
From MyOtherDB.dbo.tblTickets tt
Inner Join MyOtherDB.dbo.CaseType ct
On ct.CaseTypeId = tt.CaseTypeId
Inner Join tblcontactus t
On ( ( ISNULL(tt.Contact1, '') = ISNULL(t.Phone, '')
And ISNULL(tt.Contact1, '') != ''
And ISNULL(t.Phone, '') != '' )
Or ( ISNULL(tt.Contact2, '') = ISNULL(t.Phone, '')
And ISNULL(tt.Contact2, '') != ''
And ISNULL(t.Phone, '') != '' )
Or ( ISNULL(tt.Contact3, '') = ISNULL(t.Phone, '')
And ISNULL(tt.Contact3, '') != ''
And ISNULL(t.Phone, '') != '' )
Or ( ISNULL(tt.Email, '') = ISNULL(t.EmailAddress, '')
And ISNULL(tt.Email, '') != ''
And ISNULL(t.EmailAddress, '') != '' ) )
And (Select Min(ttp.RecDate)
From MyOtherDB.dbo.tblTicketsPayment ttp
Where ttp.MyTableID = tt.MyTableID_PK
And ttp.PaymentVoid = 0
And ttp.isRemoved = 0) >= t.RecDate
And t.RecDate Between @StartDate And @EndDate
Where ISNULL(tt.Email, '') Not Like '%Test%'


It is the lehgacy app query which we have to maintain and database is sql server 2005

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/04/2012 :  03:02:09  Show Profile  Reply with Quote
the catch all type of query may result in bad plan and can cause performance issues in case of large datasets.
Can you explain your requirement in words?

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000