|
kamii47
Constraint Violating Yak Guru
337 Posts |
 Posted - 12/04/2012 : 02:06:31
|
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)
|
|
Any improvement possible in the given query
