0

Everyone, the table relationship diagram for the database is the below one

enter image description here

In case the full DDL for the database is requested, I can provide it (although it will probably exceed the character limit of Stackexchange).

Question 1) The task is to query a 'Duration' column that shows the duration between the ArrivalDate and ReturnDate in days. If a trip is canceled, the value in that column should say 'Canceled' accordingly. I have researched a lot and could not find a solution without sub-querying to show the 'Duration' in the format e.g. '6 days'.

I have managed to achieve it the below way - by converting the DATEDIFF() first and then using the result as a sub-query (please disregard the ELSE condition and it does not set the field name to 'Canceled')

SELECT [Id], Firstname + ' ' + ISNULL(MiddleName,'') +  ' ' + LastName AS [Full Name], [From], 
            CASE 
                WHEN CanceledOrNot IS NOT NULL THEN 'Canceled'
                ELSE CONCAT(Duration, ' ', 'days')
            END AS Duration
FROM (
    SELECT Trips.[Id] AS [Id], Accounts.FirstName, Accounts.LastName, Accounts.MiddleName AS Middlename, Cities.[Name] AS [From], Hotels.CityId, Hotels.[Id] AS HotelId,
                 CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) AS [Duration], Trips.CancelDate AS CanceledOrNot
            FROM Accounts
                    LEFT JOIN Cities
                    ON Cities.Id = Accounts.CityId
                    JOIN AccountsTrips
                    ON AccountsTrips.AccountId = Accounts.[Id]
                    JOIN Trips
                    ON Trips.[Id] = AccountsTrips.TripId
                    JOIN Rooms
                    ON Rooms.[Id] = Trips.[RoomId]
                    JOIN Hotels
                    ON Rooms.HotelId = Hotels.[Id] ) AS ToAddDays

Every time I try to add the string directly after the converted DATEDIFF() in the CAST statement. without using a sub-query, an error appears. Why is it so - the DATEDIFF() output is converted to varchar(30) which should be fine for concatenation with the 'days' string? Here is the error-causing query:

SELECT Trips.[Id], Accounts.FirstName, Accounts.LastName, Cities.[Name] AS [From], Hotels.CityId, 
                (CASE 
                    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' ' + 'days')
                    --CONCAT(CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS VARBINARY(MAX)), 'days')
                    --((CONVERT(VARCHAR(100), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate))),
                    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS int)
                END) AS [Duration]
            FROM Accounts
                    LEFT JOIN Cities
                    ON Cities.Id = Accounts.CityId
                    JOIN AccountsTrips
                    ON AccountsTrips.AccountId = Accounts.[Id]
                    JOIN Trips
                    ON Trips.[Id] = AccountsTrips.TripId
                    JOIN Rooms
                    ON Rooms.[Id] = Trips.[RoomId]
                    JOIN Hotels
                    ON Rooms.HotelId = Hotels.[Id]

The corresponding error is:

Conversion failed when converting the nvarchar value '4 days' to data type int.

I have tried multiple viations, with CONCAT_WS, FORMAT but without success. Is the only way the sub-query approach?

Question 2) May I ask you as well how is it possible to SELECT and show in an additional column 'To' that includes the City of the Hotel?

The correct result set should look like this (please disregard the missing [Full Name] column in my query - this is not a problem as concatenation or simple addition works as the datatypes of FirstName and LastName are identical.

Thanks for your support!

enter image description here

1
  • Please add your DDL, some example data and your existing queries into a db<>fiddle and post the link as this will make it easier for people to assist you. – HandyD Jun 22 '20 at 3:28
1

This expression is explicitly returning two different data types (nvarchar and int):

(CASE 
    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' ' + 'days')
    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS int)
END) AS [Duration]

When CancelDate is not null, the value with be of type nvarchar(max). Otherwise, it will be of type int.

A column only has one data type, which means SQL Server has to choose between those two and make every row the same type.

int has a higher data type precedence than nvarchar, so SQL Server will implicitly convert the nvarchar values from the first part of your CASE statement to ints.

It fails to convert the strings produced by the first part to int (because they contain letters and spaces), resulting in the error message in your question.

One way to resolve the problem would be to explicitly convert the number to a string, if you want to show both numbers and strings in this column:

(CASE 
    WHEN Trips.CancelDate IS NOT NULL THEN (CONVERT(NVARCHAR(MAX), DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate)) + ' days')
    ELSE CAST(DATEDIFF(day, Trips.ArrivalDate, Trips.ReturnDate) AS nvarchar(max))
END) AS [Duration]

As a side note:

  • I used nvarchar(max) for consistency with the first part of your query. You should consider how many days are really supported, and reduce the size of the output column
    • You have 5 characters of fixed length (" days"), so I would expect 7 or 8 characters max would do the trick (for 2 or 3 digit durations - unless your tracking 3+ year trips)
    • the final data type size can affect things like query memory grants, which will have an impact on performance and concurrency
  • You probably only need varchar, as it doesn't seem your expression would ever produce unicode data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.