Using an IF statement in an INSERT INTO Statement

Question

I'm trying to insert values into a Billing table. For the BillingAmount, I would like that to change depending on what time the guest checks out. If the guest checks out before noon, then I would like the BillingAmount to just be the QuotedRate multiplied by how many nights they stayed. If the guest checks out after 1:00PM, then I would like the a 50% surcharge to be added. For the check out time, I'm just using the current system time. Simply put, I'm trying to use an IF statement in an INSERT INTO query. However, I keep getting syntax errors around the IF statement. How would I format this to get rid of any syntax errors? Any help would be appreciated.

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

Here you go! This is your 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 is down, so I created your tables and ran the SQL Locally. See below.

Here are the individual SQL scripts. You might wanna modify it for your IDENTITY columns etc.

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 

I used FolioID = 24 because that's what I see as the value in your INSERT statement. But this approach can be used for any FolioID

The following condition will give you the correct calculation based on the time of the day. You should also have a default clause "just in case", which is the ELSE part below.

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

I used the condition in SQL Server without SELECT-ing from a table to ensure my CASE statement works, see below.