I have a query that I am running and I just noticed that every time I run it, it is returning a different amount of records (usually less by 1 or 2 rows). My question is how can this be possible? I am running the same exact query (just pressing f5 on SQL server 2008) and the number of rows returned changes.
1 reason might be that someone is updating the tables right now in the database and that is the reason my query results are also changing but I am not aware of anyone doing any updates during regular work hours, as the updates are usually done after hours.
Would the tables being updated be the only possible reason for the query results to be changing or could it be something else?
If it is the fact that someone is running an update on the database, is there some code/command I can put in my query to at least make sure the query is looking at the same data when it starts running at the begging of execution as it is in the middle and ending of the query execution, so that the data is not changing on me at least during query execution, kind of like a LOCK?
Below is the query code:
declare @date as date set @date = '03-01-2012' select DATEPART(MONTH,@date) as Month_Opened,DATEPART(year,@date) as Year_Opened,isnull(t1.StateCode, t2.StateCode) StateCode,isnull(t1.CoverageCode, t2.CoverageCode) coveragecode,sum(t1.#_of_Claims)#_of_Claims,sum(t2.EarnedExposures) Earned_Exposures, isnull(t1.Multicar, t2.Multicar) Multicar, isnull(t1.DecpageTypeCode, t2.DecpageTypeCode) Policy_Type, isnull(t1.VehiclePoints, t2.VehiclePoints) VehiclePoints,isnull(t1.VehicleClassCode, t2.VehicleClassCode) VehicleClassCode,isnull(t1.ModelYear, t2.ModelYear) ModelYear, isnull(t1.RatingTier, t2.RatingTier) RatingTier,isnull(t1.PriorCoverage, t2.PriorCoverage)PriorCoverage from ( select table1.DecpageID,table1.PolicyID,table1.Risk,Year_Opened,Month_Opened,table1.StateCode,CoverageCode,COUNT(claims) #_of_Claims, Multicar, AgentCode,DecpageTypeCode,VehicleClassCode,ModelYear,RatingTier,PriorCoverage,VehiclePoints from ( select ClaimNumber,DecpageID,PolicyID,Risk,StateCode,YEAR(firstdateopened) as Year_Opened, MONTH(FirstDateOpened) as Month_Opened,CoverageCode,ClaimNumber claims from ( select StateCode,DecpageID,PolicyID,atable.Risk, atable.DateOpened,atable.ClaimID1,atable.ClaimID2,claimnumber, atable.claimant, atable.statuscode,CoverageCode,atable.CovExaminer, Loss_Paid,Expense_Paid, AccidentDate Loss_Date, reportdate Report_Date, dateclosed Date_Closed, claims.statuscode ClaimsTableStatusCode from ( select --MIN (h.TransactionDate) First_Date_Opened, DecpageID,PolicyID,Risk,DateOpened,AccidentDate,CovExaminer,agentcode, statecode, statclaimdate,c.claimid1,c.claimid2,c.claimant, sum(ClaimPayment) as Loss_Paid,SUM(laepayment) as Expense_Paid,claimnumber, reportdate, c.statuscode from statclaims as c inner join statclaimhistory as h on c.claimid1 = h.claimid1 and c.claimid2 = h.claimid2 and c.claimant = h.claimant where statclaimdate = '4/26/2012' --and StateCode = 3 and agentcode ='3002' and c.Claimant <> '99' and IsNull(CoverageCode,'') <> '' and c.StatusCode <> 'E' group by DateOpened,CovExaminer,AccidentDate,DecpageID,PolicyID,Risk, statclaimdate, claimnumber, reportdate, c.claimant, c.claimid1, c.claimid2, agentcode, statecode, c.statuscode ) as atable join isdata15sql.dbo.claims as claims on atable.claimid1 = claims.claimid1 and atable.claimid2 = claims.claimid2 and atable.claimant = claims.claimant where atable.DateOpened between '1-1-2011' and '4-12-2012'-- and CoverageCode = 'PIP' ) as t1 join (select MIN(transactiondate) FirstDateOpened,ClaimID1,ClaimID2 from StatClaimHistory where ClaimID1 in ('2011','2012') group by ClaimID1,ClaimID2 ) as t2 on t1.ClaimID1 = t2.ClaimID1 and t1.ClaimID2 = t2.ClaimID2 ) as table1 left join StatRiskDecpages s on table1.PolicyID= s.PolicyID and table1.DecpageID= s.DecpageID and table1.Risk = s.Risk where --CoverageCode = 'coll' and table1.StateCode = 9 and Year_Opened=DATEPART(year,@date) and Month_Opened=DATEPART(month,@date) and CoverageCode not in ('rental','towing','pu') group by Year_Opened,Month_Opened,CoverageCode,table1.StateCode,table1.DecpageID,table1.PolicyID,table1.Risk,Multicar,AgentCode, DecpageTypeCode,VehicleClassCode,ModelYear,RatingTier,PriorCoverage,VehiclePoints ) as t1 full outer join ( select --sum(EarnedPremium) as EarnedPremium, sum(EarnedExposure) as EarnedExposures,s.DecpageID,s.Risk,s.PolicyID,CoverageCode,StateCode, Multicar,AgentCode,DecpageTypeCode,Symbol,VehicleClassCode,ModelYear,RatingTier,PriorCoverage,VehiclePoints from StatRiskPrem_udf(@date,CONVERT(DATETIME, DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,CONVERT(DATETIME, DATEADD(M,+1,DATEADD(mm, DATEDIFF(m,0,@date),0)), 102)),0)), 102),'Y','Y','') as u inner join StatRiskDetails as d on u.PolicyID = d.PolicyID and u.Risk = d.Risk and u.InvDetailNum = d.InvDetailNum inner join StatRiskDecpages as s on d.PolicyID = s.PolicyID and d.DecpageID = s.DecpageID and d.Risk = s.Risk where StateCode = 9 and CoverageCode not in('towing','rental','pu') Group by StateCode,s.DecpageID,s.Risk,s.PolicyID,CoverageCode, Multicar,AgentCode,DecpageTypeCode,Symbol,VehicleClassCode,ModelYear,RatingTier,PriorCoverage, VehiclePoints ) as t2 on t1.PolicyID = t2.PolicyID and t1.DecpageID = t2.DecpageID and t1.Risk = t2.Risk and t1.CoverageCode = t2.CoverageCode WHERE NOT(#_of_Claims is null AND earnedexposures=0) group by isnull(t1.CoverageCode, t2.CoverageCode),isnull(t1.StateCode, t2.StateCode),isnull(t1.Multicar, t2.Multicar),isnull(t1.DecpageTypeCode, t2.DecpageTypeCode), isnull(t1.VehiclePoints, t2.VehiclePoints),isnull(t1.VehicleClassCode, t2.VehicleClassCode),isnull(t1.ModelYear, t2.ModelYear),isnull(t1.RatingTier, t2.RatingTier), isnull(t1.PriorCoverage, t2.PriorCoverage)