I need to understand the following MS SQL statement ( SELECT FROM VALUES)

user_11648271 0
Viewed 130

I am pretty new to MS SQL but I am having to work with it a lot now. I need to understand what is going on here:

    BEGIN TRANSACTION loadHalfdayAbsences;
    INSERT INTO @halfDayAbsences
    ([AbsencePart], 
     [AbsenceId], 
     [DeleteDate], 
     [LastChangeDate]
    )
           SELECT CASE ap.AbsencePart
                      WHEN 1
                      THEN a.AbsenceStart
                      WHEN 3
                      THEN a.AbsenceEnd
                      ELSE CASE
                               WHEN a.AbsenceStartHalfDay = 1
                               THEN DATEADD(DAY, 1, a.AbsenceStart)
                               ELSE a.AbsenceStart
                           END
                  END AS newEnd,
                  CASE ap.AbsencePart
                      WHEN 1
                      THEN 0.50
                      WHEN 2
                      THEN 1.00
                      WHEN 3
                      THEN 0.50
                  END AS newDuration, 
                  [ap].[AbsencePart], 
                  [a].[AbsenceId], 
                  [a].[EmployeeId], 
           FROM
           (
               SELECT AbsencePart
               FROM(VALUES(1), (2), (3)) AS t(AbsencePart)
           ) AS ap
           INNER JOIN dwh.Absence AS a ON 1 = 1
           WHERE AbsenceType IN
           (
               SELECT AbsenceType
               FROM @AbsenceType4TimeTac
           )

Particularly:

           FROM
           (
               SELECT AbsencePart
               FROM(VALUES(1), (2), (3)) AS t(AbsencePart)
           ) AS ap
           INNER JOIN dwh.Absence AS a ON 1 = 1
           WHERE AbsenceType IN
           (
               SELECT AbsenceType
               FROM @AbsenceType4TimeTac
           )
  • What does this part do?
  • What do the VALUES do here for me?
  • What happens to the data?
  • Is it being selected, but only fields where there is either a 1, a 2 or a 3 in there? Or what is going on with VALUES?

Thanks for any input in advance :)

sql-server
2 Answers

The portion

FROM (VALUES(1), (2), (3)) AS t(AbsencePart)

just is an inline table consisting of three values, 1 through 3, in a column called AbsensePart. You could have also used the following syntax:

FROM
(
    SELECT 1 AS AbsencePart UNION ALL
    SELECT 2 UNION ALL
    SELECT 3
) t
user_18632290

That is known as a Table Value Constructor. It is used instead of things like temp tables when you have a list of known values you want in a table. You can read more about them here.

user_38131160
Related