我想請問一下各位前輩如上圖
我的欄位的名稱是 201909 201910 201911 201912 202001 202002 202003
四月份的時候希望可以自動的變成
201910 201911 201912 202001 202002 202003 202004
也就是說每換一個月 前面的就會被擠掉 最後面的變成了當月的日期
只要到月份就好了
我爬了許都文 都找不到相關的訊息,昨天搞了一天到現在還沒弄好
麻煩前輩救救我吧
SELECT 區域, 客戶名稱, 客戶代號, 價格條件, 備註, TOTAL
FROM
(SELECT C.MA015 AS 區域, C.MA002 AS 客戶名稱, C.MA001 AS 客戶代號, C.MA030 AS 價格條件, C.MA049 AS 備註, SUM(A.TA041 + A.TA042 - A.TA044) AS TOTAL, LEFT(A.TA003, 6) AS Expr2
FROM dbo.ACRTA AS A INNER JOIN dbo.COPMA AS C ON A.TA004 = C.MA001
WHERE (C.MA001 NOT LIKE 'S%') AND (A.TA027 = 'N') AND (A.TA025 = 'Y') AND (A.TA001 IN ('611', '612', '613', '615', '617', '620', '614')) AND (DATEDIFF(m, A.TA003, GETDATE()) = 1)
GROUP BY C.MA001, C.MA002, C.MA030, C.MA049, C.MA015, A.TA003, DATEDIFF(m, A.TA003, GETDATE())
HAVING (SUM(A.TA041 + A.TA042 - A.TA044) <> 0)) AS M
這是我SQL的語法 我只是先做其中的一個月,SUM(A.TA041 + A.TA042 - A.TA044) AS TOTAL但 TOTAL的欄位希望能是年月,請問我要怎麼做呢?
我試過了
DECLARE @M0 varchar(7)
SET @M0 = CONVERT(varchar(10),dateadd(month,-1,getdate()),111)
SET @M0 = left(@M0,7)
exec sp_rename 'M.TOTAL' , 'M0'
這幾行是我爬行試的
但是還是不行
麻煩一下各位先進前輩教教我
或者是用 ASP的gridview可以做的到也可以
已邀請的邦友 {{ invite_list.length }}/5
我想你應該要把日期放到另一個 Table
然後關聯 Primary key
而不是修改欄位名稱
例如
SELECT MA015,TA004,MA002,
SUM(CASE WHEN YM=(CONVERT(VARCHAR(6),DATEADD(MONTH,-6,GETDATE()),112))
THEN TOTAL ELSE 0 END) AS '201909',
SUM(CASE WHEN YM=(CONVERT(VARCHAR(6),DATEADD(MONTH,-5,GETDATE()),112))
THEN TOTAL ELSE 0 END) AS '201910',
SUM(CASE WHEN YM=(CONVERT(VARCHAR(6),DATEADD(MONTH,-4,GETDATE()),112))
THEN TOTAL ELSE 0 END) AS '201911',
SUM(CASE WHEN YM=(CONVERT(VARCHAR(6),DATEADD(MONTH,-3,GETDATE()),112))
THEN TOTAL ELSE 0 END) AS '201912',
SUM(CASE WHEN YM=(CONVERT(VARCHAR(6),DATEADD(MONTH,-2,GETDATE()),112))
THEN TOTAL ELSE 0 END) AS '202001',
SUM(CASE WHEN YM=(CONVERT(VARCHAR(6),DATEADD(MONTH,-1,GETDATE()),112))
THEN TOTAL ELSE 0 END) AS '202002',
SUM(CASE WHEN YM=(CONVERT(VARCHAR(6),DATEADD(MONTH,0,GETDATE()),112))
THEN TOTAL ELSE 0 END) AS '202003'
FROM (
SELECT TA004 ,SUBSTRING(TA003,1,6) AS 'YM',SUM(TA041+TA042-TA058) AS TOTAL
FROM ACRTA
WHERE 1=1
AND TA003 >=CONVERT(VARCHAR(6),DATEADD(MONTH,-6,GETDATE()),112)+'01'
AND TA003 < CONVERT(VARCHAR(6),DATEADD(MONTH,+1,GETDATE()),112)+'01'
GROUP BY TA004,SUBSTRING(TA003,1,6)
) AS TEMP
LEFT JOIN COPMA ON MA001=TA004
GROUP BY TA004,MA015,MA002
ORDER BY TA004,MA015,MA002
AS '201909',AS '201910',......AS '202003' 自行修改
把上面的 SQL 改成 stored procedure , 傳參數就可以了
CREATE PROCEDURE [dbo].[Test]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQLCommandAll nvarchar(max)
DECLARE @YM01 nvarchar(max)
DECLARE @YM02 nvarchar(max)
DECLARE @YM03 nvarchar(max)
DECLARE @YM04 nvarchar(max)
DECLARE @YM05 nvarchar(max)
DECLARE @YM06 nvarchar(max)
DECLARE @YM07 nvarchar(max)
SET @YM01 = (CONVERT(VARCHAR(6),DATEADD(MONTH,-6,GETDATE()),112))
SET @YM02 = (CONVERT(VARCHAR(6),DATEADD(MONTH,-5,GETDATE()),112))
SET @YM03 = (CONVERT(VARCHAR(6),DATEADD(MONTH,-4,GETDATE()),112))
SET @YM04 = (CONVERT(VARCHAR(6),DATEADD(MONTH,-3,GETDATE()),112))
SET @YM05 = (CONVERT(VARCHAR(6),DATEADD(MONTH,-2,GETDATE()),112))
SET @YM06 = (CONVERT(VARCHAR(6),DATEADD(MONTH,-1,GETDATE()),112))
SET @YM07 = (CONVERT(VARCHAR(6),DATEADD(MONTH,0,GETDATE()),112))
--
SET @SQLCommandAll = 'SELECT MA015,TA004,MA002,
SUM(CASE WHEN YM='+@YM01+'
THEN TOTAL ELSE 0 END) AS '''+@YM01+''','+
'SUM(CASE WHEN YM='+@YM02+'
THEN TOTAL ELSE 0 END) AS '''+@YM02+''','+
'SUM(CASE WHEN YM='+@YM03+'
THEN TOTAL ELSE 0 END) AS '''+@YM03+''','+
'SUM(CASE WHEN YM='+@YM04+'
THEN TOTAL ELSE 0 END) AS '''+@YM04+''','+
'SUM(CASE WHEN YM='+@YM05+'
THEN TOTAL ELSE 0 END) AS '''+@YM05+''','+
'SUM(CASE WHEN YM='+@YM06+'
THEN TOTAL ELSE 0 END) AS '''+@YM06+''','+
'SUM(CASE WHEN YM='+@YM07+'
THEN TOTAL ELSE 0 END) AS '''+@YM07+'''
FROM (
SELECT TA004 ,SUBSTRING(TA003,1,6) AS ''YM'',SUM(TA041+TA042-TA058) AS TOTAL
FROM ACRTA
WHERE 1=1
AND TA003 >=CONVERT(VARCHAR(6),DATEADD(MONTH,-6,GETDATE()),112)+''01''
AND TA003 <CONVERT(VARCHAR(6),DATEADD(MONTH,+1,GETDATE()),112)+''01''
GROUP BY TA004,SUBSTRING(TA003,1,6)
) AS TEMP
LEFT JOIN COPMA ON MA001=TA004
GROUP BY TA004,MA015,MA002
ORDER BY TA004,MA015,MA002'
--
EXEC sp_executesql @SQLCommandAll;
--PRINT @SQLCommandAll;
END
GO
每個月一號執行
EXEC [dbo].[Test]
謝謝您
iThome鐵人賽
看影片追技術