'MSSQL'에 해당되는 글 1건

  1. 2008.11.12 Cursor 사용 예제 by 잠수콩

Cursor 사용 예제

쿼리_SQL 2008.11.12 11:16


SET NOCOUNT ON

Declare curGet_ViewSO1_PaperLog_Query CURSOR
FOR

 select
  count(*) as count_all, 
  convert(varchar(10), regdate, 121) as q_date,
  datepart(hh, regdate) as q_time,
  isnull(sum(cost),0) as countpay,
  mrd
 from
  SO1_Portal_Log
 where
  convert(varchar(10),regdate,121) = convert(varchar(10), dateadd(d,-1,getdate()), 121)
  and mrd in ('3800', '3802', '480', '4800', '6800', '390', '3806')
  and phone not in (select phone_mdn from kocn_phone)
 group by mrd, convert(varchar(10), regdate, 121), datepart(hh, regdate)
 order by convert(varchar(10), regdate, 121), datepart(hh, regdate)

FOR READ ONLY


OPEN curGet_ViewSO1_PaperLog_Query

Declare @count_all int
 , @q_date varchar(20)
 , @q_time varchar(20)
 , @countpay int
 , @mrd varchar(20)


Fetch next From curGet_ViewSO1_PaperLog_Query into @count_all, @q_date, @q_time, @countpay, @mrd

While @@Fetch_status=0

Begin

 insert into So1_ALL_Paper_Times_Log (wdate, wtime, cost, cnt, regdate, winc)
  values ( replace(@q_date, '-', '') , right('0'+@q_time,2), @countpay, @count_all, getdate(), @mrd)
 
 Fetch next From curGet_ViewSO1_PaperLog_Query into @count_all, @q_date, @q_time, @countpay, @mrd
end

close curGet_ViewSO1_PaperLog_Query

deallocate curGet_ViewSO1_PaperLog_Query

SET NOCOUNT OFF

go

저작자 표시 비영리 변경 금지
신고

'쿼리_SQL' 카테고리의 다른 글

ms-sql UPDATE 문을 제대로 써보자!  (0) 2008.11.13
MS-SQL 랭킹(순위) 매기기  (0) 2008.11.13
ms-sql 2000 SQL인젝션방지용 쿼리문  (0) 2008.11.13
Cursor 사용 예제  (0) 2008.11.12
MS-SQL 2000 보안 쿼리문  (0) 2008.11.12
tempDB 로그 삭제  (0) 2008.11.12
Posted by 잠수콩