I have a T-SQL script most of which is a select statement. Running this query returns data only sometimes. When it does it is correct. When it doesn't it completes fairly quickly but returns no rows. This behaviour happens in both
SQL server 2005 and SQL server 2000 with clients: SQL Analyzer, SSMS 2005, SSMS 2008 R" and .NET Native SQL client. The script follows. Any ideas ?
declare @CampaignID int
set @CampaignID = 4
--set a table with reg/site/pos of completed data
create table #DataFilter
(RegionID int
,SiteID int
,PositionId int
)
create index ixDataFilter on #DataFilter
(RegionID asc
,SiteID asc
,PositionID asc
)
insert #DataFilter
select DISTINCT RegionID, SiteID, PositionID
from PDAData_Data D
join PDAData_Config C
on D.selectID = C.selectID
where C.CampaignID = @CampaignID
select distinct
S.RegionID
,S.SiteID
,S.Customer
,C.[Customer Name]
,G.CustomerGroupDesc
,ME.PositionID
,ME.MachineNameCode
,PH.MinNo
,PH.MachineName
,ME.MachineType
,ME.CollectionFrequency
,X.Collector
,U.Name --Collector name
,' '
,' '
,US.Name
from Site S
left join tblMachineExtractNon90Day ME
on S.RegionID = ME.RegionID
and S.SiteID = ME.SiteID
join #DataFilter
on S.RegionID = #DataFilter.RegionID
and S.SiteID = #DataFilter.SiteID
and ME.PositionID = #DataFilter.PositionID
left join tblPositionHistory PH
on S.RegionID = PH.RegionID
and S.SiteID = PH.SiteID
and ME.PositionID = PH.PositionID
and ME.MachineNameCode = PH.MachineNameID
and isnull(PH.RemovalDate,getdate()) >= getdate()
left join tblCustomer C
on C.CustomerID = S.Customer
left join CustomerGroup G
on G.GroupAmedisLabel = C.[Customer Amedis Code]
left join CollectionPlanSite X
on X.RegionID = S.regionID
and X.SiteID = S.SiteID
and X.customer <> 0
and X.CollectionDate between dateadd(d,-ME.CollectionFrequency,getdate()) and getdate()
left join CompanyUsers U
on U.RegionID = S.RegionID
and U.UserID = X.Collector
left join CompanyUsers US
on US.UniqueUserID = U.ReportsTo
where S.regionID between 127 and 154
--and ME.RegionID is not null
and PH.RegionID is not null
and ME.MachineNameCode <> 0
drop table #dataFilter
