iT邦幫忙

0

SQL-每次新增資料的時間間隔不一定,要如何抓取上一筆的資料

分別有三種不同的點數(DETATM3,DETATM4,DETATM5),然後這三種點數都符合期初+新增-減少=期末,只是當我執行select from [dbo].會員相關異動點數明細表 上一筆的日期(DETADAT)是13號,但是我在SELECT中的新增跟減少和剩餘中 宣告的@date 都要減2,這雖然是對的...
https://ithelp.ithome.com.tw/upload/images/20180822/20111204BJMCRvEcql.png
但是如果是執行20180816,20180813,這兩個日期 就都錯了。因為20180816上一筆是20180815,差了1天,而20180813的上一筆日期是20180809差了4天,因為會員點數不可能會有固定的日期做增加或減少,他可能開心哪天消費,點數就給哪天,下面是賈斯汀4筆不同日期的資料內容
https://ithelp.ithome.com.tw/upload/images/20180822/20111204JwIZnd2Dwg.png
每個欄位分別為: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

小魚 iT邦高手 1 級 ‧ 2018-08-22 12:03:56 檢舉
看來我的中文不好...
題目都看不懂 @@
debbie5 iT邦新手 5 級 ‧ 2018-08-22 13:45:22 檢舉
T^T 恩..換一個方式說...執行function輸入的日期,要抓輸入日期的前一筆 日期的資料,但是每筆異動的日期跟上一筆異動間隔天數不一定,有可能這個人異動日期是昨天,有可能是上禮拜,要怎麼才能捉到正確的異動資料,如果我這樣說 能理解嗎><

1 個回答

1
pcw
iT邦研究生 3 級 ‧ 2018-08-22 12:28:50
最佳解答

方法一

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
看更多先前的回應...收起先前的回應...
debbie5 iT邦新手 5 級 ‧ 2018-08-22 16:16:33 檢舉

https://ithelp.ithome.com.tw/upload/images/20180822/20111204N48XfN28Ow.png
謝謝大大的語法分享,確實能抓到前一筆資料的日期,只是我要放入函式去做點數異動運算,只是條件要指定前一筆的日期(SELECT A.DETAMT3 FROM DETAIL a LEFT JOIN DETAIL b ON a.DETADAT > b.DETADAT WHERE A.DETADAT='20180813' GROUP BY A.DETAMT3) 他才能做運算,這樣又回到原來的問題,我們不會預知要查哪一天的點數異動@@

pcw iT邦研究生 3 級 ‧ 2018-08-22 16:51:04 檢舉

既然已經有當日日期與前日日期,接下來用where子句或join on去指定即可。

debbie5 iT邦新手 5 級 ‧ 2018-08-22 17:42:46 檢舉

謝謝大大 ,我剛剛有用出來囉~~~
謝謝你的語法點醒了我!我卡好多天了~~~萬歲~~~耶!~~~

pcw iT邦研究生 3 級 ‧ 2018-08-22 17:51:07 檢舉

/images/emoticon/emoticon12.gif

我要發表回答

立即登入回答