How to retrieve data from a SplitTable and join it with an SQL function [dbo].fn_ListDayOfMonth

DotNetNext / SqlSugar

.Net aot ORM Fastest ORM Simple Easy VB.NET Sqlite orm Oracle ORM Mysql Orm 虚谷数据库 postgresql ORm SqlServer oRm 达梦 ORM 人大金仓 ORM 神通ORM C# ORM , C# ORM .NET ORM NET5 ORM .NET6 ORM ClickHouse orm QuestDb ,TDengine ORM,OceanBase orm,GaussDB orm ,Tidb orm Object/Relational Mapping

MIT License

5.19k stars 1.32k forks source link

FUNC

CREATE function dbo.fn_ListDayOfMonth
(
    @year int = 2023, @month int = 10
)
returns table
as
return 
WITH numbers
as
(
    Select 1 as value
    UNion ALL
    Select value + 1 from numbers
    where value + 1 <= Day(EOMONTH(datefromparts(@year,@month,1)))
)
SELECT convert(varchar(4), @year) + iif(@month<10,'0','') + convert(varchar(2),@month) as NamThang,  datefromparts(@year,@month,numbers.value) day , DATEPART(dw,datefromparts(@year,@month,numbers.value)) as T
FROM numbers

GO

Here's the T-SQL query:

DECLARE @Nam INT = 2023, @Thang INT = 10, @EmployeeId BIGINT = 1627608121918754816

SELECT DK.*
FROM [dbo].fn_ListDayOfMonth(@Nam, @Thang) AS P
JOIN [dbo].[CC_DangKy_20231001] AS DK ON P.NamThang = DK.NamThang AND DK.EmployeeId = @EmployeeId

Table:

DotNetNext / SqlSugar

How to retrieve data from a SplitTable and join it with an SQL function [dbo].fn_ListDayOfMonth #1203