sql-server - 在 INSERT INTO 语句中使用 IF 语句

Question

我正在尝试将值插入到帐单表中。对于 BillingAmount，我希望根据客人退房的时间进行更改。如果客人在中午之前退房，那么我希望 BillingAmount 只是 QuotedRate 乘以他们入住的晚数。如果房客在下午 1:00 后退房，那么我想加收 50% 的附加费。对于退房时间，我只使用当前系统时间。简而言之，我试图在 INSERT INTO 查询中使用 IF 语句。但是，我不断收到有关 IF 语句的语法错误。我将如何格式化它以消除任何语法错误？任何帮助将不胜感激。

SET IDENTITY_INSERT Billing OFF
INSERT INTO Billing (FolioID, BillingCategoryID, BillingDescription, BillingAmount, BillingItemQty, BillingItemDate)
VALUES(24
       , 1
       , 'Room'
       , IF(CAST(GETDATE() as time) < '12:00 PM')
            SELECT QuotedRate * Nights AS "TotalLodging"
            FROM Folio
         ELSE IF(CAST(GETDATE() as time) > '1:00 PM')
            SELECT (QuotedRate * Nights) + ((QuotedRate * Nights) * .50) AS "TotalLodging"
            FROM Folio
       , 1
       , GETDATE());

Answer 1

给你!这是您的 SQL。

SET IDENTITY_INSERT Billing OFF

INSERT INTO Billing 
(
    FolioID
    , BillingCategoryID
    , BillingDescription
    , BillingAmount
    , BillingItemQty
    , BillingItemDate
)
SELECT
    24
    , 1
    , 'Room'
    ,  CASE 
        WHEN CAST(GETDATE() AS TIME) <= '12:00' THEN QuotedRate * Nights 
        WHEN CAST(GETDATE() AS TIME) >= '13:00' THEN (QuotedRate * Nights) + (QuotedRate * Nights * .50) 
        ELSE (QuotedRate * Nights) + (QuotedRate * Nights * .50) 
      END
    , 1
    , GETDATE()
FROM Folio
WHERE FolioID = 24

SQL Fiddle 已关闭，因此我创建了您的表并在本地运行了 SQL。见下文。

这是单独的 SQL 脚本。您可能想为您的 IDENTITY 列等修改它。

CREATE TABLE Billing (
    FolioID INT NOT NULL,
    BillingCategoryID INT NULL,
    BillingDescription VARCHAR(100) NOT NULL,
    BillingAmount DECIMAL(9,2) NULL,
    BillingItemQty NUMERIC(5,0) NULL,
    BillingItemDate DATETIME NULL
)

CREATE TABLE Folio
(
  FolioID INT NOT NULL,
  QuotedRate DECIMAL(6,2) NOT NULL,
  Nights INT NOT NULL
)

INSERT INTO Folio(FolioID, QuotedRate, Nights)
VALUES (24, 100.00, 5)

INSERT INTO Billing (
    FolioID 
    , BillingCategoryID
    , BillingDescription
    , BillingAmount
    , BillingItemQty
    , BillingItemDate
)
SELECT
    24
    , 1
    , 'Room'
    ,  CASE 
        WHEN CAST(GETDATE() AS TIME) <= '12:00' THEN QuotedRate * Nights 
        WHEN CAST(GETDATE() AS TIME) >= '13:00' THEN (QuotedRate * Nights) + (QuotedRate * Nights * .50) 
        ELSE (QuotedRate * Nights) + (QuotedRate * Nights * .50) 
      END
    , 1
    , GETDATE()
FROM Folio
WHERE FolioID = 24

SELECT * FROM Billing 

我使用了 FolioID = 24 因为这是我在您的 INSERT 语句中看到的值。但这种方法可用于任何 FolioID

以下条件将为您提供基于一天中时间的正确计算。您还应该有一个默认子句“以防万一”，即下面的 ELSE 部分。

SELECT 'Total Lodging' =
  CASE 
    WHEN CAST(GETDATE() AS TIME) <= '12:00' THEN QuotedRate * Nights 
    WHEN CAST(GETDATE() AS TIME) >= '13:00' THEN (QuotedRate * Nights) + ((QuotedRate * Nights) * .50) 
    ELSE (QuotedRate * Nights) + ((QuotedRate * Nights) * .50) -- Default rate
  END
FROM Folio

我在没有从表中进行 SELECT 操作的情况下使用 SQL Server 中的条件来确保我的 CASE 语句有效，请参见下文。