4

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());
Improve this question
2
  • 2
    Use CASE WHEN statement. But I think it is better if you do "logic" work in your business layer (.net) and use sql query only for saving/updating data Commented Jul 16, 2016 at 23:13
  • This isn't directly relevant to answers, but what should the guest be charged if they check out at 12:30pm? Commented Jul 17, 2016 at 8:19

2 Answers 2

Reset to default
6

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.

enter image description here

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.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Syntax is CASE WHEN ... END AS "Total Lodging". You can't do <TextLiteral> = <Expression> in a SELECT clause. Or ever.
Using single-quotes (')? I'd expect that to coerce the numeric value from the CASE to a string, then perform string-literal = string-value comparison, resulting in an unnamed boolean value.
Yes. single-quotes let you name the columns for SELECT statements. It's the same as doing SELECT QuotedRate * Nights AS 'Total Lodging'. I updated my answer to show this to you.
I learned something new. Thanks. You should explain that it is SQL Server specific syntax, and doing the same as CASE WHEN ... END AS "Total Lodging" would work for all RDBMS dialects, not just SQL Server.
Sure. Isn't it obvious, since OP has tagged his question as SQL-Server :)
|
-1 
INSERT INTO Billing (FolioID, BillingCategoryID, BillingDescription, BillingAmount, BillingItemQty, BillingItemDate)
VALUES(
    24
   , 1
   , 'Room'
   , CASE WHEN (CAST(GETDATE() as time) < '12:00 PM')  then 
        (
            SELECT QuotedRate * Nights AS "TotalLodging" FROM Folio
        ) 
    ELSE
        ( 
            SELECT (QuotedRate * Nights) + ((QuotedRate * Nights) * .50) AS "TotalLodging"
            FROM Folio
        )
    End
   , 1
   , GETDATE()
)
Improve this answer

1 Comment

it dont work if Folio has multiple rows, be carefull

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.