玩玩SQL~如何將欲扣的點數，逐筆計算需要使用的點數?

sql

純真的人 2022-03-14 17:44:28 ‧ 1145 瀏覽

分享至

在弄銷售的工作上用到的~這是銷售某個額度後獲的點數
該點數有使用期限(我設定一周內有效)，才能查詢可用點數來消耗~
看各位SQL會怎樣下達呢^^a

基本資料，假設小明3/1~3/14獲得點數如下，因點數期限只有3/8~3/14有效
小明要使用395點兌換贈品，那資料表如何表示相關各筆消耗?(從最早日期開始扣)

declare @Point int = '395'
declare @Tmp table(
	流水號 int
	,姓名 nvarchar(50)
	,點數日期 date
	,點數獲得 int
)

insert into @Tmp
values(1,'小明','2022/3/1',150)
,(2,'小明','2022/3/5',120)
,(3,'小明','2022/3/8',130)
,(4,'小明','2022/3/10',100)
,(5,'小明','2022/3/12',200)
,(6,'小明','2022/3/13',250)
,(7,'小明','2022/3/14',350)

如圖顯示

這是我的方式~

select 姓名
,點數日期
,點數獲得
,(
	case when @Point >= SumPoint
	then 點數獲得
	else 
		case when SumPoint - (@Point + 點數獲得) < 0
		then 點數獲得 - (SumPoint - @Point)
		else 0
		end
	end
) 點數消耗
from (
	select 姓名
	,點數日期
	,點數獲得
	,sum(點數獲得)over(order by 流水號) SumPoint
	from @Tmp
	where 姓名 = '小明'
	and dateadd(d,-7,Convert(date,GetDate())) < 點數日期
) k

果然rogeryao rogeryao比較SQL厲害

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

1 個回答

rogeryao

iT邦超人 8 級 ‧ 2022-03-14 19:06:28

最佳解答

create table Tmp (
	流水號 int
	,姓名 nvarchar(50)
	,點數日期 date
	,點數獲得 int
);

insert into Tmp
values(1,N'小明','2022/3/1',150)
,(2,N'小明','2022/3/5',120)
,(3,N'小明','2022/3/8',130)
,(4,N'小明','2022/3/10',100)
,(5,N'小明','2022/3/12',200)
,(6,N'小明','2022/3/13',250)
,(7,N'小明','2022/3/14',350);

select [姓名],[點數日期],[點數獲得],
case when pointA < 0 then [點數獲得] 
when pointA > [點數獲得] then 0 
else [點數獲得] - pointA end '點數消耗'
from (
select *,
sum([點數獲得]) over (partition by [姓名] order by [點數日期]) - 395 as pointA
from Tmp
where [點數日期] >='2022/3/8' and [點數日期] <='2022/3/14') as mm

Demo