分別有三種不同的點數(DETATM3,DETATM4,DETATM5),然後這三種點數都符合期初+新增-減少=期末,只是當我執行select from [dbo].會員相關異動點數明細表 上一筆的日期(DETADAT)是13號,但是我在SELECT中的新增跟減少和剩餘中 宣告的@date 都要減2,這雖然是對的...
但是如果是執行20180816,20180813,這兩個日期 就都錯了。因為20180816上一筆是20180815,差了1天,而20180813的上一筆日期是20180809差了4天,因為會員點數不可能會有固定的日期做增加或減少,他可能開心哪天消費,點數就給哪天,下面是賈斯汀4筆不同日期的資料內容
每個欄位分別為:DETECNO是依當天哪個日期 再依照不同時間異動之會員尾數遞增的序號,DETNAME為姓名,DETIDNO為會員身分証,DETPENO為會員編號。
以下是我的程式碼
ALTER FUNCTION [dbo].[會員相關異動點數明細表]
(
@input nvarchar(20),@date char(8)
)
RETURNS @T TABLE
(
會員編號 char(12),
身分證 char(10),
姓名 nvarchar(20),
期初消費點數 numeric(10,2),
期初互聯點數 numeric(10,2),
期初專案點數 numeric(10,2),
增加消費點數 numeric(10,2),
增加互聯點數 numeric(10,2),
增加專案點數 numeric(10,2),
減少消費點數 numeric(10,2),
減少互聯點數 numeric(10,2),
減少專案點數 numeric(10,2),
剩餘消費點數 numeric(10,2),
剩餘互聯點數 numeric(10,2),
剩餘專案點數 numeric(10,2)
)
as
begin
declare @now char(8) =convert(char(20),getdate(),112)
if @input is not null
insert @T
(會員編號,
身分證,
姓名,
期初消費點數,
期初互聯點數,
期初專案點數,
增加消費點數,
增加互聯點數,
增加專案點數,
減少消費點數,
減少互聯點數,
減少專案點數,
剩餘消費點數,
剩餘互聯點數,
剩餘專案點數
)
select
MEMPENO,
MEMIDNO,
MEMNAME,
/期初/
sum(case when DETADAT=@date then DETAMT3 else 0 end),
sum(case when DETADAT=@date then DETAMT4 else 0 end),
sum(case when DETADAT=@date then DETAMT5 else 0 end),
/增加/
sum(case when DETADAT=@date then DETAMT3 else 0 end)-(sum(case when DETADAT=@date-2 then DETAMT3 else 0 end)),
sum(case when DETADAT=@date then DETAMT4 else 0 end)-(sum(case when DETADAT=@date-2 then DETAMT4 else 0 end)),
sum(case when DETADAT=@date then DETAMT5 else 0 end)-(sum(case when DETADAT=@date-2 then DETAMT5 else 0 end)),
/減少/
(sum(case when DETADAT=@date-2 then DETAMT3 else 0 end))-(sum(case when DETADAT=@date then DETAMT3 else 0 end)),
(sum(case when DETADAT=@date-2 then DETAMT4 else 0 end))-(sum(case when DETADAT=@date then DETAMT4 else 0 end)),
(sum(case when DETADAT=@date-2 then DETAMT5 else 0 end))-(sum(case when DETADAT=@date then DETAMT5 else 0 end)),
/剩餘/ sum(case when DETADAT=@date then DETAMT3 else 0 end)+sum(case when DETADAT=@date then DETAMT3 else 0 end)-(sum(case when DETADAT=@date-2 then DETAMT3 else 0 end))+(sum(case when DETADAT=@date-2 then DETAMT3 else 0 end))-(sum(case when DETADAT=@date then DETAMT3 else 0 end)),
sum(case when DETADAT=@date then DETAMT4 else 0 end)+sum(case when DETADAT=@date then DETAMT3 else 0 end)-(sum(case when DETADAT=@date-2 then DETAMT3 else 0 end))+(sum(case when DETADAT=@date-2 then DETAMT3 else 0 end))-(sum(case when DETADAT=@date then DETAMT3 else 0 end)),
sum(case when DETADAT=@date then DETAMT5 else 0 end)++sum(case when DETADAT=@date then DETAMT3 else 0 end)-(sum(case when DETADAT=@date-2 then DETAMT3 else 0 end))+(sum(case when DETADAT=@date-2 then DETAMT3 else 0 end))-(sum(case when DETADAT=@date then DETAMT3 else 0 end))
from
POINTS_S as s left join MEMBER as m on s.PSPENO=m.MEMPENO
left join DETAIL as d on s.PSPENO=d.DETPENO
where
(MEMPENO=@input or ( MEMNAME like '%'+@input+'%') or MEMIDNO=@input ) and DETADAT BETWEEN '20180401' AND @now
group by
MEMPENO,
MEMIDNO,
MEMNAME,
PSBON1,
PSBON2,
PSBON3
RETURN
END
SELECT
dat,
(SELECT Max(dat) FROM table WHERE dat < A.dat) AS last_date
FROM table A
SELECT
a.dat,
Max(b.dat) AS LastDate
FROM
Table a LEFT JOIN Table b ON a.dat > b.dat
GROUP BY a.dat
謝謝大大的語法分享,確實能抓到前一筆資料的日期,只是我要放入函式去做點數異動運算,只是條件要指定前一筆的日期(SELECT A.DETAMT3 FROM DETAIL a LEFT JOIN DETAIL b ON a.DETADAT > b.DETADAT WHERE A.DETADAT='20180813' GROUP BY A.DETAMT3) 他才能做運算,這樣又回到原來的問題,我們不會預知要查哪一天的點數異動@@