구매 고객별 등급 나누기?

select

		select C.custId,
			case when sum(sales) >= 10000 then 'A'
				 when sum(sales) >= 5000 then 'B'
				 when sum(sales) >= 10 then 'C'
				 else 'D'
			end as grade

		from customersTbl C
		left join salesTbl S ON C.custId = S.custId
		group by c.custId

 

 

 

테이블 update

update customersTbl
set 
customersTbl.grade = S.grade
from customersTbl as C,
	(
		select C.custId,
			case when sum(sales) >= 10000 then 'A'
				 when sum(sales) >= 5000 then 'B'
				 when sum(sales) >= 10 then 'C'
				 else 'D'
			end as grade

		from customersTbl C
		left join salesTbl S ON C.custId = S.custId
		group by c.custId
	)
	as S
where c.custId = s.custId

 

 

 

함수사용( 날짜 입력받고 테이블리턴)

create function ufn_userGrade(@bDate date)
	returns @retTable table
	(
		userId char(8),
		userName nvarchar(10),
		grade nvarchar(5)
	)
as
begin
	declare @rowCnt int;
	select @rowCnt = count(*) from customersTbl where birthDate >= @bDate

	if @rowCnt <= 0
	begin 
		insert into @retTable values('없음','없음','없음')
		return;
	end

	insert into @retTable
		select C.custId, C.custName,
			case
				when ( sum(sales) >= 5000 ) then N'최우수고객'
				when ( sum(sales) >= 3000 ) then N'우수고객'
				when ( sum(sales) >= 1 ) then N'일반고객'
				else N'휴먼고객'
			end
		from salesTbl S
		right outer join customersTbl C ON S.custId = C.custId
		where birthDate >= @bDate
		group by C.custId, C.custName
	return;
end


--실행
select * from dbo.ufn_userGrade('1970-01-01')