title: Why is my T-SQL Query returning inconsistent output?
body: <p>I am using SQL Server 2014 and I have the following T-SQL query:</p>
  
  <pre><code>USE MyDatabase
  GO
  
  SELECT 
      a.ReservationStayID
      ,c.PMSConfirmationNumber
      ,c.[PropertyCode]
      ,a.StayDate
      ,c.[MTH]
      ,1 AS 'RN'
      ,a.PackagePlanCode
      ,c.[Market Segment Code]
      ,c.[Status]
      ,c.[CurrencyCode]
      ,a.RateAmount
      ,SUM(a.RateAmount) OVER (PARTITION BY a.ReservationStayID) AS 'CUM_Rate'
      ,d.[Exchange Rate]
      ,((a.RateAmount * d.[Exchange Rate]) / 1.15) AS 'Package Revenue Excl VAT'
      ,c.[Tour Operator]
      ,c.[Group Booking ID]
      ,c.[Source of Business]
      ,c.[Booking Origin (1)]
      ,c.[Market FINAL]
      ,ISNULL(ay.[KeyAccountName], 'NA') AS 'Key A/c'
      ,c.[CreatedOn_RSD]
  FROM 
      ReservationStayDate a
  INNER JOIN 
      [RESERVATIONLIST(2)] c ON c.[ReservationStayID] = a.ReservationStayID
                             AND c.[MTH] = datename(m, StayDate) + ' ' + cast(datepart(yyyy, StayDate) AS VARCHAR)
  INNER JOIN 
      [PKGREVENUE] d ON d.[ReservationStayID] = a.ReservationStayID
                     AND d.[StayDate] = a.StayDate
  ORDER BY 
      a.ReservationStayID;
  </code></pre>
  
  <p>The query runs fine but the output is inconsistent! At times, I get 698,017 rows, and then if I run the query again immediately, I may get 698,020. Another attempt showed 698,025.</p>
  
  <p>This is driving me crazy as my database has not been updated. The SAME query is being run at, say, 1 minute interval and each run is providing an inconsistent output in terms of the number of records! What could be causing this behavior?</p>
  
  <p>I don't know if this additional information will be of any help:</p>
  
  <p>When I the ran the query "as is" above, it gave me 4 records for ReservationStayID = 147469.</p>
  
  <p>I then added the following line to the above query as a filter:</p>
  
  <pre><code>WHERE a.ReservationStayID = 147469
  </code></pre>
  
  <p>and surprisingly, I got only 2 rows!</p>
  
  <p><code>[ReservationList(2)]</code> and <code>[PKGREVENUE]</code> are views.</p>
  
  <p>Here are the 2 Views which are invloved:</p>
  
  <p>View 1:</p>
  
  <pre><code>CREATE VIEW [RESERVATIONLIST(2)] AS
  
  SELECT 
     x.[ReservationStayID],
     b.PropertyCode,
     c.CreatedOn,
     c.CreatedBy,
     c.UpdatedBy,
     c.UpdatedOn,
     xy.Rooms AS [Room Inventory], --added
     (xy.Rooms*[DaysInMonth]) AS [RNAvailable], --added
     (x.[Nights Spent]/(xy.Rooms*[DaysInMonth])) AS [Occupancy],
     c.PMSConfirmationNumber,
     a.ArrivalDate AS [Arrival Date],
     a.DepartureDate AS [Departure Date],
     (a.ArrivalDate - CONVERT(Varchar(10),(CAST(x.CreatedOn as DATE)),(101))) AS 'Booking Lead Time',
      a.FirstName + ' ' + a.LastName AS 'Name',
      j.ProfileID,
      j.EmailAddress AS 'Email',
      b.MarketSegmentCode AS 'Market Segment Code',
      a.DateOfBirth AS 'Date of Birth',
      b.ReservationStatus AS 'Status',
      j.Nationality AS 'Nationality',
      k.[Country of Residence],
      ISNULL(g3.CountryGroup2, 'Not Specified') AS 'Country of Residence 2', 
       c.ReasonForStayCode AS 'Reason For Stay',
       b.RateplanCode,
       x.[Rate Plan RSD] AS 'Rate Plan Code',
       x.[Room Type RSD] AS 'Room Type',
       i.RoomType3 AS 'Room Type 3', -- this code converts the Room Type as per Room Type codes used in the Budget
       al.NonRoombundleID,
       k3.MpDescription AS 'Meal Plan Description',
       ISNULL(k3.MpCode,'RO') AS 'Meal Plan Code',
       x.[Adult RSD] AS 'Adult',
       x.[Child RSD] AS 'Child',
       b.GuestCount AS 'Total Guest',
       x.[Nights Spent] AS 'Room Nights',
       x.[MTH],
       x.[DaysInMonth], --added
       x.[Rate] AS 'Room Rate WITH VAT',
       c.CurrencyCode, 
       y.[Pkg Rev (with VAT)],
       y.[Pkg Rev (excl VAT)], 
      x.CreatedOn AS [CreatedOn_RSD],
      CONVERT(Varchar(10),(CAST(x.CreatedOn as DATE)),(101)) as [DATE CREATED ON],
      datename(m,x.CreatedOn) + ' ' + cast(datepart(yyyy,x.CreatedOn) as varchar) as [Created On (MTH)],
      x.[DateOfArrival],
      x.[DateOfDeparture],
      e.TravelAgencyTypeCode AS 'Source of Business',
      c.TAProfileID,
      c.PropGroupBookingID AS 'Group Booking ID', 
      e.Name AS 'Tour Operator', 
      g.CountryGroup AS 'Market', 
      c.TAProfileID2, 
      e2.Name AS 'Booking Origin (1)',
     g2.CountryGroup AS 'Booking Origin (2)', 
  
     (CASE
       WHEN e.TravelAgencyTypeCode = 'DMC' 
       THEN g2.CountryGroup 
       ELSE g.CountryGroup 
        END) AS 'Market (DMC Classified)',
  
     (CASE
       WHEN e.TravelAgencyTypeCode = 'DMC' THEN g2.CountryGroup
      WHEN c.TAProfileID = '316' AND c.CurrencyCode = 'MUR' THEN 'DB Local'
      WHEN c.TAProfileID = '316' THEN 'DB International'
      ELSE g.CountryGroup
      END) AS 'Market FINAL'
  
     FROM GuestNameInfo a
  
    JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID
    LEFT JOIN ReservationStay c ON c.ReservationStayID =   b.ReservationStayID
    LEFT JOIN TravelAgency e ON e.TravelAgencyID = c.TAProfileID 
   LEFT JOIN Market g ON e.CountryCode = g.CountryCode
   LEFT JOIN TravelAgency e2 ON e2.TravelAgencyID = c.TAProfileID2
   LEFT JOIN Market g2 ON e2.CountryCode = g2.CountryCode
  
   LEFT JOIN CtyRes h ON h.ReservationStayID = a.ReservationStayID
   LEFT JOIN Market g3 ON g3.CountryCode = h.CountryCode
  
   LEFT JOIN Profile j ON j.ProfileID = c.ProfileID
   LEFT JOIN HotelInventory xy ON xy.PropertyCode = b.PropertyCode 
  
  
  
   LEFT JOIN
   (
      SELECT 
      min(CountryCode) AS [Country of Residence]
     , min(ProfileID) AS [Profile ID]
     ,min(PostalAddressID) AS [Postal Address ID]
  
     FROM PostalAddress
     GROUP BY CountryCode,ProfileID,PostalAddressID
    ) k ON k.[Postal Address ID] = c.PostalAddressID
  
  
   LEFT JOIN
    (
     SELECT 
       ReservationStayID,
       datename(m,StayDate) + ' ' + cast(datepart(yyyy,StayDate) as varchar) as [MTH],
       datediff(day, dateadd(day, 1-day(StayDate), StayDate),
            dateadd(month, 1, dateadd(day, 1-day(StayDate), StayDate))) AS [DaysInMonth],
       min(adultcount) as 'Adult RSD',
       min(childcount) as 'Child RSD',
       min(RoomTypeCode) AS 'Room Type RSD',
       min(PackagePlanCode) AS 'Rate Plan RSD',
       count(*) AS [Nights Spent],
       avg(RateAmount) as [Rate],
       min(CreatedOn) as CreatedOn,
       min(StayDate) as [DateOfArrival],
       max(StayDate) as [DateOfDeparture]
      FROM ReservationStayDate
      GROUP BY ReservationStayID, datename(m,StayDate) + ' ' +  cast(datepart(yyyy,StayDate) as varchar), datediff(day, dateadd(day, 1-day(StayDate), StayDate),
            dateadd(month, 1, dateadd(day, 1-day(StayDate), StayDate)))
   ) x ON x.ReservationStayID = b.ReservationStayID
  
   LEFT JOIN RoomCat i ON b.PropertyCode = i.Property AND [Room Type RSD] = i.RoomType
  
  
     LEFT JOIN
  
      (SELECT datename(m,StayDate) + ' ' + cast(datepart(yyyy,StayDate) as varchar) AS [MTH_PKGREV],
         [ReservationStayId], SUM([Package Revenue with VAT]) AS 'Pkg Rev (with VAT)',
         SUM([Package Revenue excl VAT]) AS 'Pkg Rev (excl VAT)'
  
     FROM PKGREVENUE
  
     GROUP BY datename(m,StayDate) + ' ' + cast(datepart(yyyy,StayDate) as varchar),[ReservationStayId]
  
  
     ) y ON y.[ReservationStayId] = b.ReservationStayID AND [MTH] = datename(m,[MTH_PKGREV]) + ' ' + cast(datepart(yyyy,[MTH_PKGREV]) as varchar)
  
  
    LEFT JOIN
     (
    SELECT ReservationStayID, MTH, NonRoombundleID
  
    FROM NONROOMBUNDLEID
  
    group by ReservationStayID, MTH, NonRoombundleID
  
    )al ON al.ReservationStayID = x.ReservationStayID AND al.[MTH] = x.[MTH]
  
    LEFT JOIN NonRoomBundle k2 ON K2.NonRoomBundleID = al.NonRoombundleID
  
    LEFT JOIN MealPlan k3 ON k3.MpDescription = k2.Description
  
  
    WHERE a.PrimaryGuest = '+'
  </code></pre>
  
  <p>View 2:</p>
  
  <pre><code>CREATE VIEW [PKGREVENUE] AS
  
  SELECT
    ReservationStayDate.ReservationStayID AS [ReservationStayId]
    ,ReservationStay.PMSConfirmationNumber AS [PmsConfirmationNumber]
    ,ReservationStayDate.StayDate AS [StayDate]
    ,ReservationStayDate.RateAmount AS [RateAmount]
    ,ReservationStay.CurrencyCode AS [CurrencyCode]
    ,CAST(ROUND(ISNULL((1/CA.SellRate),1),2) as numeric (36,2)) AS 'Exchange Rate' -- since MUR is not in the Exchange Rate Table, this replaces all NULL values for MUR by 1.00
  
    ,CAST(ROUND(ReservationStayDate.RateAmount * ISNULL((1/CA.SellRate),1),0) as numeric (36,0)) AS 'Package Revenue with VAT'
  
    ,(CAST(ROUND(ReservationStayDate.RateAmount * ISNULL((1/CA.SellRate),1),0)/1.15 as numeric (36,0))) AS 'Package Revenue excl VAT'
  
  FROM
    ReservationStayDate
    INNER JOIN ReservationStay ON ReservationStay.ReservationStayID = ReservationStayDate.ReservationStayID
  
    OUTER APPLY
    (
       SELECT TOP(1) ExchangeRate.SellRate
       FROM ExchangeRate
       WHERE
         ExchangeRate.ToCurrencyCode = ReservationStay.CurrencyCode
         AND ExchangeRate.EffectiveDate &lt;= ReservationStayDate.StayDate
        ORDER BY ExchangeRate.EffectiveDate DESC
    ) AS CA
  </code></pre>