为什么我的T-SQL查询返回不一致的输出? [英] Why is my T-SQL Query returning inconsistent output?

问题描述

我正在使用SQL Server 2014，并且具有以下T-SQL查询:

I am using SQL Server 2014 and I have the following T-SQL query:

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;

查询运行正常，但输出不一致！有时，我得到698,017行，然后如果立即再次运行查询，我可能会得到698,020.另一尝试显示698,025.

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.

这使我发疯，因为我的数据库尚未更新. SAME查询以例如1分钟的间隔运行，并且每次运行在记录数量方面都提供不一致的输出！可能是什么原因导致了这种行为?

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?

我不知道这些附加信息是否有帮助:

I don't know if this additional information will be of any help:

当我运行上面的查询"as is"时，它给了我4条ReservationStayID = 147469的记录.

When I the ran the query "as is" above, it gave me 4 records for ReservationStayID = 147469.

然后我将以下行添加到上述查询中作为过滤器:

I then added the following line to the above query as a filter:

WHERE a.ReservationStayID = 147469

而且令人惊讶的是，我只有2行！

and surprisingly, I got only 2 rows!

[ReservationList(2)]和[PKGREVENUE]是视图.

以下是受人欢迎的2个视图:

Here are the 2 Views which are invloved:

查看1:

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 = '+'

查看2:

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 <= ReservationStayDate.StayDate
      ORDER BY ExchangeRate.EffectiveDate DESC
  ) AS CA

推荐答案

感谢所有为我的问题提供帮助的人.我什至得到了负面的，我也不知道为什么！ 无论如何，我想在这里提供有关如何解决该问题的更新.我对一些人建议的确定性和非确定性功能进行了深入研究.但是，我无法在查询和视图中找到任何导致主查询行为异常并给出不一致结果的函数.

Thanks everyone who provided help to my problem. I even got a negative and I don't know why! Anyway, I would like to provide an update here as to how I solved the problem. I had a deep look into the deterministic and non-deterministic functions as suggested by some of you. However, I could not locate any such functions in my query and views that were causing the main query to misbehave and give inconsistent results.

我在主查询中尝试了GROUP BY子句(尽管我不需要该子句).瞧，问题解决了！！现在，我的查询每次尝试都给我相同数量的记录.至于为什么GROUP BY子句解决了这个问题，我绝对不知道.只是想在这里与社区分享.也许有人可以阐明GROUP BY子句的神奇力量！ :-)

I gave the GROUP BY clause a try in my main query (although I did not need that clause). And behold, the problem was solved!! Now, my query is giving me the same number of records at each attempt. As to why did the GROUP BY clause solve the problem, I have absolutely no idea. Just wanted to share this with the community here. May be someone can shed some light on the magical powers of the GROUP BY clause! :-)

这篇关于为什么我的T-SQL查询返回不一致的输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！