SQL Server Video
133 page views
5474 video views
2024, July 21, Sunday

#023 How to Create Calendar Table in SQL Server

Apresentamos nesse vídeo como criar uma tabela Calendário no SQL Server para garantir a integridade de cálculo de inteligência temporal do seus relatórios, pois disponibiliza todos os dias do período entre os registros existentes no banco de dados.

We'll get to know the techniques:

1. CREATE TABLE, CALCULATED COLUMN:

You can create a table with calculated columns that derive their value from expressions.

CREATE TABLE ExampleCalculation (
  ID INT PRIMARY KEY,
  Value1 INT,
  Value2 INT,
  Sum AS (Value1 + Value2)
);

2. Get part of the date (

DATEPART, DATENAME, YEAR, MONTH, DAY):

To get specific parts of a date, you can use the DATEPART, DATENAME, YEAR, MONTH, and DAY functions.

Select 
  DATEPART(YEAR, GETDATE()) AS Year,
  DATENAME(MONTH, GETDATE()) AS SameName,
  MONTH(GETDATE()) AS SameNumber,
  DAY(GETDATE()) AS Day;

3. Combine Column Values (

CONCAT):

The CONCAT function combines column values into a single string.

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM PeopleTable;

4. Get minimum and maximum date of selected tables (MIN, MAX):

Use the MIN and MAX functions to get the minimum and maximum values of a column.
SELECT MIN(Date) AS
```
Minimum Date, MAX(Date) AS MaximumDate
FROM DateTable;
```

5. Combine All Table Records (

UNION ALL):

Combine results from two queries using UNION ALL.

SELECT Column FROM Table1
UNION ALL
SELECT Column FROM Table2;

6. Date hierarchy with Common Table Expression (

CTE):

Use a CTE to create a hierarchy of dates.

WITH AS DateHierarchy (
  SELECT 
      Date
      YEAR(Date) AS YEAR,
      MONTH(Date) AS Month
  FROM DateTable
)
SELECT * FROM HierarchyDate;

7. Insert Records into Calendar Table (INSERT):

Insert records into a calendar table.

INSERT INTO Calendar (Date, DayWeek, Month, Year)
VALUES 
  ('2023-01-01', 'Sunday', 'January', 2023),
  ('2023-01-02', 'Monday', 'January', 2023),
  ...;

These examples are illustrative and may need to be tailored to meet the specific needs of your database environment and schema.