#083 Get NETWORKDAYS Function in DAX and Azure SQL

We'll get to know the techniques:

1. Count working days with DAX function (EVALUATE, ROW, NETWORKDAYS):The DAX NETWORKDAYS

• function can be used to count working days between two dates. Example:

  EVALUATE
  ROW("Weekdays", NETWORKDAYS([StartDate], [EndDate]))
  

2. Using dates in literal format:

• Dates can be represented verbatim in DAX. Example:

  ExampleDate = DATE(2023, 12, 31)
  

3. Join multiple rows in the same DAX (UNION) result:

• The UNION operator can be used to combine the results of two or more tables into a single table. Example:

  CombinedTable = Table1 UNION Table2
  

4. Azure SQL

• Example SQL query to calculate working days between two dates:

  SELECT COUNT(*)
  FROM Table
  WHERE DATEDIFF(day, StartDate, EndDate) - (DATEDIFF(week, StartDate, EndDate) * 2) > 0
  

5. Create User-Defined Function (SCALAR-VALUED

• Example of creating a user-defined function in SQL Server:

  CREATE FUNCTION MyFunction (@Parametro INT)
  RETURNS INT
  THE
  BEGIN
      -- Function logic here
  END
  

6. Create User-Defined Table Type (USER-DEFINED

• Example of creating a user-defined table data type in SQL Server:

  CREATE TYPE MyTableType AS TABLE
  ( 
      Column1 INT,
      Column2 VARCHAR(50)
  )
  

7. Convert comma-separated text

• The STRING_SPLIT function is used to split a string into lines based on a delimiter. Example:

  SELECT value FROM STRING_SPLIT('Text1,Text2,Text3', ',')
  

8. Declare variables in function (DECLARE, AS, BEGIN):In

• T-SQL, you can declare variables in a function as follows:

  DECLARE @MinhaVariavel INT
  SET @MinhaVariavel = 10
  

9. Conditional for value validation (IF, BEGIN, END):Using

• a conditional structure in SQL Server:

  IF @MinhaVariavel > 5
  BEGIN
      -- Code to run if condition is true
  END
  

10. Insert

• Example of Entering Values in a Data Type Variable table:

  INSERT INTO @MinhaVariavel (Column1, Column2) VALUES (1, Text)
  

11. Sum values with conditional between dates

• Example of conditional sum in SQL Server:

  SELECT SUM(CASE WHEN Data BETWEEN '2023-01-01' AND '2023-12-31' THEN ELSE 0 END value)
  FROM Table
  

12. Execute User-Defined Function (SELECT)

• Example of executing a user-defined function in SQL Server:

  SELECT dbo. My Function(5)
  

13. Table of constants and the use of the function (VALUES):

• The VALUES function is used to create a table of constants. Example:

  ConstantTable = VALUES(Table[Column])