작업 하다가 대리점별 지역 주소를 정렬하는 부분에 사용한 쿼리
지역 코드가 여러개의 혼합으로 되어 있어서 그걸로 안되고 해서
주소의 내용 일부를 order by 해서 사용하도록 했다.
SELECT * FROM dbo.Agency order by case left(ConAddr,2)
when '서울' then 1
when '경기' then 2
when '인천' then 3
when '강원' then 4
when '대전' then 5
when '충남' then 6
when '충분' then 7
when '부산' then 8
when '울산' then 9
when '경남' then 10
when '대구' then 11
when '경북' then 12
when '광주' then 13
when '전남' then 14
when '전북' then 15
when '제주' then 16
end
-- 구성 내용
10초에 한번씩 입력 되는 해당 데이터의 내용을 5분 단위로 끊어서 새로운 테이블로 통계를 낸다.
값이 일정하지도 않을수 있고, 값의 주기가 조금씩 틀릴수도 있다.
우선 테스트를 통해서 더 보완 해야 할듯 하다. (커서를 몰라서 이렇게 걍 작성 했다.)
-- 배울점
1. 시간 값의 구간을 5분 단위로 구분
USE [GPMS]
GO
/****** 개체: StoredProcedure [dbo].[SP_TBL_CPU_MIN_INPUT] 스크립트 날짜: 02/19/2009 16:59:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 손병욱
-- Create date: 2009-02-12
-- Description:
-- =============================================
CREATE PROCEDURE [dbo].[SP_TBL_CPU_MIN_INPUT]
@HOSTNAME VARCHAR(50)
AS
DECLARE @FIRSTTIME DATETIME -- 테이블의 제일 적은 시간값
DECLARE @SNEWTIME VARCHAR(30) -- 적은 시간값의 5분 단위 구분값(임시)
DECLARE @STIME DATETIME -- 저장할 시작 시간 범위값
DECLARE @ETIME DATETIME -- 저장할 마지막 시간 범위값
DECLARE @BET_COUNT INT -- 범위 시간에 포함된 아이템의 개수
-- 제일 위에 있는 시간 값을 가져온다.
SELECT top 1 @FIRSTTIME = regDate FROM tbl_rep_cpu_input order by regDate asc
-- 가져온 시간 값을 사용해서 분 단위로 구분한다.
SET @SNEWTIME = DBO.GETSTIMEPER5MINTODATETIME(@FIRSTTIME)
-- 구분된 임시 문자열을 시간값으로 변환(저장할 시간 시간 범위값)
SET @STIME = DBO.CHANGEDATETIME(@SNEWTIME)
-- 마지막 범위값을 구한다.
SET @ETIME = DATEADD(MI, 5, @STIME)
--
--PRINT @SNEWTIME
--PRINT @STIME
--PRINT @ETIME
-- 범위 안에 포함된 개수를 구한다.
SELECT @BET_COUNT = COUNT(*) FROM DBO.TBL_REP_CPU_INPUT WHERE hostName=@HOSTNAME and regDate >= @STIME and regDate < @ETIME
--
--PRINT @BET_COUNT
IF @BET_COUNT > 0
BEGIN -- @BET_COUNT > 0
-- 범위 값을 저장하기 위해서
-- 범위의 시작 값을 키로 사용하게 된다.
-- 이때 이 값이 이미 존재 하는지를 판단해야 한다.
DECLARE @FINDKEY INT
SELECT @FINDKEY = COUNT(*) FROM DBO.TBL_REP_CPU_MIN WHERE regDate=@STIME
--
--PRINT @FINDKEY
-- value의 총 합을 구한다.
DECLARE @SUMVALUE INT
SELECT @SUMVALUE = SUM(value) FROM DBO.TBL_REP_CPU_INPUT WHERE hostName=@HOSTNAME and regDate >= @STIME and regDate < @ETIME
--
--PRINT @SUMVALUE
-- @FINDKEY의 값이 0 이면 하나도 존재 하지 않는 것이기 때문에 INSERT를 한다.
IF @FINDKEY = 0
INSERT INTO [GPMS].[dbo].[TBL_REP_CPU_MIN]
([hostName]
,[value]
,[valueSum]
,[valueCount]
,regDate)
VALUES
(@HOSTNAME
,0
,@SUMVALUE
,@BET_COUNT
,@STIME)
ELSE
--PRINT '여기야'
UPDATE DBO.TBL_REP_CPU_MIN
SET [valueSum] = [valueSum] + @SUMVALUE
,[valueCount] = [valueCount] + @BET_COUNT
WHERE hostName=@HOSTNAME and regDate=@STIME
END --@BET_COUNT > 0
-- 추가한 후에 해당 데이터는 삭제한다.(테스트로 템프로 옮기고 삭제)
INSERT TBL_REP_CPU_INPUT_TMP
SELECT hostName, value, regDate, flag FROM TBL_REP_CPU_INPUT WHERE hostName=@HOSTNAME and regDate >= @STIME and regDate < @ETIME
DELETE FROM DBO.TBL_REP_CPU_INPUT WHERE hostName=@HOSTNAME and regDate >= @STIME and regDate < @ETIME
USE [GPMS]
GO
/****** 개체: UserDefinedFunction [dbo].[CHANGEDATETIME] 스크립트 날짜: 02/19/2009 16:57:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- 날짜변환하는부분을함수로작성
CREATE FUNCTION [dbo].[CHANGEDATETIME] (
@sdate varchar(30)
)RETURNS DATETIME
AS
BEGIN
DECLARE @RETURNVALUE DATETIME
BEGIN
SELECT @RETURNVALUE=CASE WHEN CHARINDEX('오후',@sdate)>0
THEN DATEADD(HH,12,CONVERT (DATETIME, REPLACE(@sdate,'오후','')))
ELSE CONVERT (DATETIME,REPLACE(@sdate,'오전','') )
END
END
RETURN(@RETURNVALUE)
END
-- 결과
문자열을 시간 값으로 변경해 준다.
하지만 문자열이 시간값과 같은 포멧으로 구성되어 있어야 한다.
ex) 2009-02-19 14:25:00
