CREATE PROCEDURE GetProductWageTable @YM VARCHAR(7) --�����ʽ '2026-08' ,@GLIDENAME VARCHAR(300)='' ,@StaffName VARCHAR(300)='' AS BEGIN SET NOCOUNT ON; DECLARE @StartDate DATE, @EndDate DATE; DECLARE @Cols NVARCHAR(MAX), @Sql NVARCHAR(MAX); SET @StartDate = CAST(@YM + '-01' AS DATE); SET @EndDate = EOMONTH(@StartDate); -- ����ÿ��CASE�У�[1],[2]...[31] ���� WITH DaysCTE AS( SELECT 1 AS d UNION ALL SELECT d+1 FROM DaysCTE WHERE d<31 ) SELECT @Cols = ISNULL(@Cols+',','') + N'ISNULL(SUM(CASE WHEN DayNo=' + CAST(d AS VARCHAR(2)) + N' THEN DayQty END),0) AS ' + QUOTENAME(d) FROM DaysCTE WHERE d <= DAY(@EndDate) ORDER BY d OPTION(MAXRECURSION 31); SET @Sql = N' SELECT StaffName, GlideName, C_NAME, crop, BuyName, C_Color, conno, Price, ' + @Cols + ', ISNULL(SUM(DayQty),0) AS TotalQty, -- ���²������� ISNULL(SUM(DayMoney),0) AS TotalMoney -- ���½����� FROM ( SELECT A.StaffName, A.GlideName, e.C_NAME, e.crop, f.conno, e.C_Color, f.BuyName, A.price, DAY(A.gotime) AS DayNo, A.qty AS DayQty, A.money AS DayMoney FROM Trade_Plan_Glide_OutPut A inner join Trade_Plan_Card e on a.pcid=e.pcid LEFT JOIN Bs_Contract_main f on f.conmid=e.mainid WHERE A.gotime >= @StartDate AND A.gotime < DATEADD(MONTH,1,@StartDate) AND A.Valid = ''Y'' AND (@GLIDENAME = '''' OR A.GlideName = @GLIDENAME) AND (@StaffName = '''' OR A.StaffName = @StaffName) ) AS T GROUP BY StaffName,GlideName,C_NAME,crop,BuyName,C_Color,conno,Price ORDER BY StaffName,GlideName,BuyName,conno,C_NAME,crop,Price; '; EXEC sp_executesql @Sql ,N'@StartDate DATE, @GLIDENAME VARCHAR(300), @StaffName VARCHAR(300)' ,@StartDate=@StartDate ,@GLIDENAME=@GLIDENAME ,@StaffName=@StaffName; END