[MS-SQL] 공통코드에 따른 시간 체크 스크립트

떠돌이개발자Jan 18, 2023

다음 스크립트는 현재시간 기준으로 7시부터 22시까지 15분 단위로 시간를 체크하여

어느 시간 영역에 포함되는지 확인 후 리턴값을 반환하는 스크립트 입니다.

DECLARE @P_TIME_CD		VARCHAR(10) = ''

SET @P_TIME_CD = (
SELECT CASE WHEN CONVERT(time,GETDATE()) <= CONVERT(time, '07:00') OR CONVERT(time,GETDATE()) < CONVERT(time, '07:15') THEN '10'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '07:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '07:30') THEN '12'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '07:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '07:45') THEN '14'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '07:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '08:00') THEN '16'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '08:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '08:15') THEN '18'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '08:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '08:30') THEN '20'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '08:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '08:45') THEN '22'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '08:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '09:00') THEN '24'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '09:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '09:15') THEN '26'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '09:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '09:30') THEN '28'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '09:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '09:45') THEN '30'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '09:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '10:00') THEN '32'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '10:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '10:15') THEN '34'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '10:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '10:30') THEN '36'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '10:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '10:45') THEN '38'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '10:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '11:00') THEN '40'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '11:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '11:15') THEN '42'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '11:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '11:30') THEN '44'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '11:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '11:45') THEN '46'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '11:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '12:00') THEN '48'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '12:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '12:15') THEN '50'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '12:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '12:30') THEN '52'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '12:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '12:45') THEN '54'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '12:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '13:00') THEN '56'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '13:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '13:15') THEN '58'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '13:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '13:30') THEN '60'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '13:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '13:45') THEN '62'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '13:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '14:00') THEN '64'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '14:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '14:15') THEN '66'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '14:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '14:30') THEN '68'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '14:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '14:45') THEN '70'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '14:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '15:00') THEN '72'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '15:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '15:15') THEN '74'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '15:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '15:30') THEN '76'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '15:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '15:45') THEN '78'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '15:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '16:00') THEN '80'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '16:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '16:15') THEN '82'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '16:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '16:30') THEN '84'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '16:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '16:45') THEN '86'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '16:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '17:00') THEN '88'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '17:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '17:15') THEN '90'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '17:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '17:30') THEN '92'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '17:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '17:45') THEN '94'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '17:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '18:00') THEN '96'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '18:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '18:15') THEN '98'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '18:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '18:30') THEN '100'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '18:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '18:45') THEN '102'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '18:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '19:00') THEN '104'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '19:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '19:15') THEN '106'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '19:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '19:30') THEN '108'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '19:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '19:45') THEN '110'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '19:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '20:00') THEN '112'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '20:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '20:15') THEN '114'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '20:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '20:30') THEN '116'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '20:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '20:45') THEN '118'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '20:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '21:00') THEN '120'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '21:00') AND CONVERT(time,GETDATE()) < CONVERT(time, '21:15') THEN '122'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '21:15') AND CONVERT(time,GETDATE()) < CONVERT(time, '21:30') THEN '124'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '21:30') AND CONVERT(time,GETDATE()) < CONVERT(time, '21:45') THEN '126'

	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '21:45') AND CONVERT(time,GETDATE()) < CONVERT(time, '22:00') THEN '128'
	WHEN CONVERT(time,GETDATE()) >= CONVERT(time, '22:00') AND CONVERT(time,GETDATE()) <= CONVERT(time, '24:00') THEN '130'
	ELSE '' END		
)

공통코드 값으로 단위시간별 코드값을 관리하고 있고,

저장시에 시간을 체크해서 공통코드값에 맞게 설정해줘야할 때 사용합니다.