ALTER PROCEDURE [AgentSETAlbumBestSeller]
AS
DECLARE @GoodVerify INT,
@SpecialCD INT
SET @GoodVerify = 2
--베스트셀러
SET @SpecialCD = 1
-- 월요일이면
if datepart(dw, getdate()) =2
begin
begin tran
-- 스페셜상품 테이블에서 베스트셀러 상품들만 삭제한다.
DELETE SpecialProduct WHERE SpecialCD=1 and ProdClassCD=2
-- 지난 일주일간 판매량 집계, 음반
SELECT 1 as SpecialCD, 2 as ProdClassCD, M.Class1CD as ProdClass1CD, M.Class2CD as ProdClass2CD, M.Class3CD as ProdClass3CD, M.AlbumCD as ProductCD,
M.PoINT INTO #Temp2 from (
SELECT AAC.AlbumCD as AlbumCD, AAC.AlbumClass1CD as Class1CD, AAC.AlbumClass2CD as Class2CD, AAC.AlbumClass3CD as Class3CD, B.BestPoINT as PoINT
from AlbumAlbumClass as AAC inner join (
SELECT GoodCD, count(*) as BestPoINT
from sql2.logics.dbo.OrderGood
WHERE OrderDate > getdate()-6 and GoodVerify=@GoodVerify and MallVerify=1
group by GoodCD) as B
on AAC.AlbumCD=B.GoodCD ) as M
--베스트 포인트 업데이트
UPDATE Album SET BestSPoINT = T.PoINT
from Album as B inner join #Temp2 as T
on B.AlbumCD = T.ProductCD
INSERT INTO SpecialProduct (SpecialCD, ProdClassCD, ProdClass1CD, ProdClass2CD, ProdClass3CD, ProductCD, Regdate, TransDate, DBMemberID)
SELECT top 100 SpecialCD, ProdClassCD, ProdClass1CD, ProdClass2CD, ProdClass3CD, ProductCD, getdate(), null, 'sqlagent' from #Temp2 order by PoINT desc
--group by SpecialCD, ProdClassCD, ProdClass1CD, ProdClass2CD, ProdClass3CD, ProductCD order by PoINT desc
if @@Error <> 0
begin
rollback tran
RETURN 0
end
commit tran
end
