Function that contain WITH table and parameters

Question

I have 2 tables:

It's regular table that contain information about calendar dates
It's WITH comon table that contains other calendar dates with parameters that user can pick by himself.

My task is to create view that will show those two tables in one table.

First table is regular it's named CalendarDates so it's simple. It contains normal columns like: day, month, quarter, year, formatedfulldate etc. Second table:

DECLARE @Year INT
DECLARE @Month INT
DECLARE @FullDate DATE = DATEFROMPARTS(@Year, @Month, 1);

WITH FullDate
AS (
    SELECT cast(DATEADD(yy, DATEDIFF(yy, 0, @FullDate), 0) AS DATE) AS FirstDayInCurrentYear
        ,cast(DATEADD(dd, - 1, DATEADD(yy, DATEDIFF(yy, 0, @FullDate) + 1, 0)) AS DATE) AS LastDayInCurrentYear
        ,cast(DATEADD(month, DATEDIFF(month, 0, @FullDate), 0) AS DATE) AS FirstDayInCurrentMonth
        ,cast(EOMONTH(@FullDate) AS DATE) AS LastDayInCurrentMonth
    )

Select * from FullDate

I can Join them doing:

Select * FROM FullDate
JOIN CalendarDates cal ON @FullDate = cal.formatedfulldate

But before this I have to do second table query with declaring parameters.

I need to create one view that will merge two tables and show resault depend from user paramters: @Year INT and @Month INT

I know that View can not contain parameters so I tried with function. It's blocked me on that part:

DECLARE @FullDate DATE = DATEFROMPARTS(@Year, @Month, 1);

There is code of function:

CREATE FUNCTION vv_FullDate
(
@Year INT ,@Month INT, @FullDate DATE = DATEFROMPARTS(@Year, @Month, 1)
)
returns table
AS RETURN

WITH FullDate
AS (
    SELECT cast(DATEADD(yy, DATEDIFF(yy, 0, @FullDate), 0) AS DATE) AS FirstDayInCurrentYear
        ,cast(DATEADD(dd, - 1, DATEADD(yy, DATEDIFF(yy, 0, @FullDate) + 1, 0)) AS DATE) AS LastDayInCurrentYear
        ,cast(DATEADD(month, DATEDIFF(month, 0, @FullDate), 0) AS DATE) AS FirstDayInCurrentMonth
        ,cast(EOMONTH(@FullDate) AS DATE) AS LastDayInCurrentMonth
    )

If anyone can help with this or know better way please let me know. Thanks

Marc Guillot · Accepted Answer · 2021-07-29 15:15:46Z

1

This is the proper syntax to use WITH (a Common Table Expression, CTE) to calculate FullDate and base the result on it.

CREATE FUNCTION vv_FullDate(@Year INT, @Month INT)
RETURNS TABLE
AS 
RETURN
(
  WITH CTE AS (
    SELECT DATEFROMPARTS(@Year, @Month, 1) as FullDate
  )
  SELECT cast(DATEADD(yy, DATEDIFF(yy, 0, FullDate), 0) AS DATE) AS FirstDayInCurrentYear
        ,cast(DATEADD(dd, - 1, DATEADD(yy, DATEDIFF(yy, 0, FullDate) + 1, 0)) AS DATE) AS LastDayInCurrentYear
        ,cast(DATEADD(month, DATEDIFF(month, 0, FullDate), 0) AS DATE) AS FirstDayInCurrentMonth
        ,cast(EOMONTH(FullDate) AS DATE) AS LastDayInCurrentMonth
  FROM CTE  
)

Another solution would be to create a Multi-statement Table-valued function instead of this Inline Table-valued function, but I don't personally think it's worth the bother when a CTE solves it easily.

edited Jul 29, 2021 at 15:15

answered Jul 29, 2021 at 15:03

Marc Guillot

6,5431 gold badge23 silver badges54 bronze badges

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

Comments

Cetin Basoz · Accepted Answer · 2021-07-29 15:43:52Z

You really don't need that @fullDate as a variable. And also you could simplify it a bit:

CREATE FUNCTION vv_FullDate(@Year INT, @Month INT)
RETURNS TABLE
AS
  RETURN WITH
           FullDate AS (
                         SELECT
                           CAST(DATEFROMPARTS(@Year, 1, 1) AS DATE)               AS FirstDayInCurrentYear
                         , CAST(DATEFROMPARTS(@Year, 12, 31) AS DATE)             AS LastDayInCurrentYear
                         , CAST(DATEFROMPARTS(@Year, @Month, 1) AS DATE)          AS FirstDayInCurrentMonth
                         , CAST(EOMONTH(DATEFROMPARTS(@Year, @Month, 1)) AS DATE) AS LastDayInCurrentMonth
                         , CAST(DATEFROMPARTS(@Year, @Month, 1) AS DATE)          AS myDate
                       )
  SELECT *
  FROM   FullDate
         JOIN CalendarDates cal ON FullDate.myDate=cal.formatedfulldate;

Collectives™ on Stack Overflow

Function that contain WITH table and parameters

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related