Contents

WITH Dumy
AS
(
        SELECT 0 Seq
        UNION ALL
        SELECT Seq + 1 FROM Dumy
        WHERE Seq + 1 <= 10
)
SELECT Seq FROM Dumy
OPTION (MAXRECURSION 0);

WITH Dumy
AS
(
        SELECT CONVERT(datetime, '20000101') DT 
        UNION ALL
        SELECT DT + 1 FROM Dumy
        WHERE DT + 1 < '20010101'
)
SELECT 
	DT DT_dtime
,	CONVERT(char(8), DT, 112) DT_char8
,	CONVERT(char(10), DT, 121) DT_char10
,	CONVERT(char(8), DT, 112) BeginDT
,	CONVERT(char(8), DT+1, 112) EndDT
,	CONVERT(datetime, CONVERT(char(8), DT, 112)) BeginDT_dt
,	CONVERT(datetime, CONVERT(char(8), DT+1, 112)) EndDT_dt
FROM Dumy
OPTION (MAXRECURSION 0);

declare 
	@begin char(8)
,	@end char(8);

set @begin = '20100101';
set @end = '20100115';

with dummy
as
(
        select convert(datetime, @begin) dt 
        union all
        select dt + 1 from dummy
        where dt + 1 <= @end
)
select dt
from dummy
option (maxrecursion 0);

create table #temp
(
	begin_dt datetime
,	end_dt datetime
)

insert #temp
select '2010-01-19 00:00:00.000', '2010-01-19 10:51:06.640'
union all
select '2010-01-18 10:51:06.640', '2010-01-18 11:51:06.640'
union all
select '2010-01-18 10:51:06.640', '2010-01-19 10:51:06.640'

--현재일이 20100118 라면.. 
select * from #temp
where 1=1
and begin_dt < '20100119'
and end_dt >= '20100118'

SET DATEFIRST 1; --월요일부터 시작, 값은 1
WITH Dumy
AS
(
        SELECT CONVERT(datetime, '20000103') DT 
        UNION ALL
        SELECT DT + 1 FROM Dumy
        WHERE DT + 1 < GETDATE()+1
), WW
AS
(
	SELECT 
		CONVERT(char(8), DT , 112) BeginDT_WK
	,	CONVERT(char(8), DATEADD(dd, 6, DT), 112) EndDT_WK
	FROM Dumy
	WHERE DATEPART(weekday, DT) IN (1)
	--AND GETDATE()-1 BETWEEN CONVERT(char(8), DT , 112)
	--AND CONVERT(char(8), DATEADD(dd, 6, DT), 112)
), MM
AS
(
	SELECT
		CONVERT(char(8), DT , 112) BeginDT_MM
	,	CONVERT(char(8), DATEADD(mm, 1, DT)-1, 112) EndDT_MM
	FROM Dumy
	WHERE DATEPART(dd, DT) IN (1)
)
SELECT 
	CONVERT(char(8), DT, 112) CurrDT
,	BeginDT_WK
,	EndDT_WK
,	BeginDT_MM
,	EndDT_MM
FROM Dumy A
	INNER JOIN WW B
		ON A.DT BETWEEN BeginDT_WK AND EndDT_WK
	INNER JOIN MM C
		ON A.DT BETWEEN BeginDT_MM AND EndDT_MM
OPTION (MAXRECURSION 0);

set nocount on
set statistics io off
declare
	@bdt char(8)
,	@edt char(8)

set @bdt = '20100101'

while (@bdt <= '20100128')
begin
	set @edt = convert(char(8), dateadd(dd, 1, @bdt), 112)

	print @bdt + ', ' + @edt
	set @bdt = @edt
end

declare @tname varchar(255); 

declare cur cursor for
--select

open cur;
fetch next from cur into @tname;
while @@FETCH_STATUS not in (-1, -2)
begin

	fetch next from cur into @tname;
end

close cur;
deallocate cur;

;with sale
as
(
	select 
		accountkey
	,	sum(saleamt) amt 
	,	log(sum(saleamt)) log_amt 
	from cji_eis.PubSales.Fact_Sales 
	where datekey between 20090601 and 20090630
	and gamekey = 20
	and saleamt > 0
	group by
		accountkey
), rs
as
(
	select
		row_number() over(order by convert(int, log_amt)) 번호
	,	sum(amt) 구매금액
	,	count(*) 구매계정수
	,	min(amt) 구매금액구간시작
	,	max(amt) 구매금액구간종료
	,	max(amt) - max(amt) / 2 중앙값
	,	avg(amt) 평균구매금액
	,	stdev(amt) 구매금액표준편차
	,	convert(int, log_amt) [log(구매금액)]
	,	case
			when convert(int, log_amt) in (5,6,7,8) then 'A'
			when convert(int, log_amt) in (9,10) then 'B'
			when convert(int, log_amt) in (11,12,13) then 'C'
			when convert(int, log_amt) in (14, 15,16) then 'D'
		end 등급
	from sale
	group by
		convert(int, log_amt)
)
select *
,	구매금액 / sum(구매금액*1.0) over() * 100 구매금액비율
,	구매계정수 / sum(구매계정수*1.0) over() * 100 구매계정비율
from rs
order by 1	

select
	a.name job_name
,	a.date_modified
,	b.step_id
,	b.step_name
,	b.subsystem
,	substring(right('000000' + convert(varchar, b.last_run_duration),6),1,2) + ':' +
	substring(right('000000' + convert(varchar, b.last_run_duration),6),3,2) + ':' +
	substring(right('000000' + convert(varchar, b.last_run_duration),6),5,2) last_run_duration
,	b.command
from msdb.dbo.sysjobs a
	inner join msdb.dbo.sysjobsteps b
		on a.job_id = b.job_id
where a.enabled = 1

--활성화된 Job만..(한번만 실행, 비활성화된 작업 제외)
select
	a.name job_name
,	a.date_modified
,	b.step_id
,	b.step_name
,	b.subsystem
,	substring(right('000000' + convert(varchar, b.last_run_duration),6),1,2) + ':' +
	substring(right('000000' + convert(varchar, b.last_run_duration),6),3,2) + ':' +
	substring(right('000000' + convert(varchar, b.last_run_duration),6),5,2) last_run_duration
,	b.command
from msdb.dbo.sysjobs a
	inner join msdb.dbo.sysjobsteps b
		on a.job_id = b.job_id
	inner join msdb.dbo.sysjobschedules c
		on a.job_id = c.job_id
where a.enabled = 1
and c.next_run_date >= convert(char(8), getdate(), 112)
order by last_run_duration desc

ALTER TABLE [Common].[Fact_ConnectHistory]  
WITH CHECK 
ADD  CONSTRAINT [FK_Fact_ConnectHistory_Dim_Date] FOREIGN KEY([DateKey]) REFERENCES [Common].[Dim_Date] ([DateKey])

ALTER TABLE [Common].[Fact_ConnectHistory]  
WITH NOCHECK 
ADD  CONSTRAINT [FK_Fact_ConnectHistory_Dim_Date] FOREIGN KEY([DateKey]) REFERENCES [Common].[Dim_Date] ([DateKey])

DECLARE
	@BeginDT datetime
,	@LoginDT datetime
SET @BeginDT = '20100307'
SET @LoginDT = '20100423'

--검색시작일이 '20100307'일 때, 주의 시작일이 '20100307'라면..
--'20100423'에 해당하는 주는..
SELECT 
	CONVERT(char(8), DATEADD(dd, DATEDIFF(dd, CONVERT(char(8), @BeginDT, 112), @LoginDT)%7*-1, @LoginDT), 112) + '~' +
	CONVERT(char(8), DATEADD(dd, DATEDIFF(dd, CONVERT(char(8), @BeginDT, 112), @LoginDT)%7*-1, @LoginDT)+6, 112)

--결과
--20100418~20100424

select 
	convert(tinyint, num) num
,	bin
,	str_bin
into dbo.binary_map
from (
	select 0 num, 0x00 bin ,'00' str_bin union all
	select 1, 0x01 bin ,'01' str_bin union all
	select 2, 0x02 bin ,'02' str_bin union all
	select 3, 0x03 bin ,'03' str_bin union all
	select 4, 0x04 bin ,'04' str_bin union all
	select 5, 0x05 bin ,'05' str_bin union all
	select 6, 0x06 bin ,'06' str_bin union all
	select 7, 0x07 bin ,'07' str_bin union all
	select 8, 0x08 bin ,'08' str_bin union all
	select 9, 0x09 bin ,'09' str_bin union all
	select 10, 0x0A bin ,'0A' str_bin union all
	select 11, 0x0B bin ,'0B' str_bin union all
	select 12, 0x0C bin ,'0C' str_bin union all
	select 13, 0x0D bin ,'0D' str_bin union all
	select 14, 0x0E bin ,'0E' str_bin union all
	select 15, 0x0F bin ,'0F' str_bin union all
	select 16, 0x10 bin ,'10' str_bin union all
	select 17, 0x11 bin ,'11' str_bin union all
	select 18, 0x12 bin ,'12' str_bin union all
	select 19, 0x13 bin ,'13' str_bin union all
	select 20, 0x14 bin ,'14' str_bin union all
	select 21, 0x15 bin ,'15' str_bin union all
	select 22, 0x16 bin ,'16' str_bin union all
	select 23, 0x17 bin ,'17' str_bin union all
	select 24, 0x18 bin ,'18' str_bin union all
	select 25, 0x19 bin ,'19' str_bin union all
	select 26, 0x1A bin ,'1A' str_bin union all
	select 27, 0x1B bin ,'1B' str_bin union all
	select 28, 0x1C bin ,'1C' str_bin union all
	select 29, 0x1D bin ,'1D' str_bin union all
	select 30, 0x1E bin ,'1E' str_bin union all
	select 31, 0x1F bin ,'1F' str_bin union all
	select 32, 0x20 bin ,'20' str_bin union all
	select 33, 0x21 bin ,'21' str_bin union all
	select 34, 0x22 bin ,'22' str_bin union all
	select 35, 0x23 bin ,'23' str_bin union all
	select 36, 0x24 bin ,'24' str_bin union all
	select 37, 0x25 bin ,'25' str_bin union all
	select 38, 0x26 bin ,'26' str_bin union all
	select 39, 0x27 bin ,'27' str_bin union all
	select 40, 0x28 bin ,'28' str_bin union all
	select 41, 0x29 bin ,'29' str_bin union all
	select 42, 0x2A bin ,'2A' str_bin union all
	select 43, 0x2B bin ,'2B' str_bin union all
	select 44, 0x2C bin ,'2C' str_bin union all
	select 45, 0x2D bin ,'2D' str_bin union all
	select 46, 0x2E bin ,'2E' str_bin union all
	select 47, 0x2F bin ,'2F' str_bin union all
	select 48, 0x30 bin ,'30' str_bin union all
	select 49, 0x31 bin ,'31' str_bin union all
	select 50, 0x32 bin ,'32' str_bin union all
	select 51, 0x33 bin ,'33' str_bin union all
	select 52, 0x34 bin ,'34' str_bin union all
	select 53, 0x35 bin ,'35' str_bin union all
	select 54, 0x36 bin ,'36' str_bin union all
	select 55, 0x37 bin ,'37' str_bin union all
	select 56, 0x38 bin ,'38' str_bin union all
	select 57, 0x39 bin ,'39' str_bin union all
	select 58, 0x3A bin ,'3A' str_bin union all
	select 59, 0x3B bin ,'3B' str_bin union all
	select 60, 0x3C bin ,'3C' str_bin union all
	select 61, 0x3D bin ,'3D' str_bin union all
	select 62, 0x3E bin ,'3E' str_bin union all
	select 63, 0x3F bin ,'3F' str_bin union all
	select 64, 0x40 bin ,'40' str_bin union all
	select 65, 0x41 bin ,'41' str_bin union all
	select 66, 0x42 bin ,'42' str_bin union all
	select 67, 0x43 bin ,'43' str_bin union all
	select 68, 0x44 bin ,'44' str_bin union all
	select 69, 0x45 bin ,'45' str_bin union all
	select 70, 0x46 bin ,'46' str_bin union all
	select 71, 0x47 bin ,'47' str_bin union all
	select 72, 0x48 bin ,'48' str_bin union all
	select 73, 0x49 bin ,'49' str_bin union all
	select 74, 0x4A bin ,'4A' str_bin union all
	select 75, 0x4B bin ,'4B' str_bin union all
	select 76, 0x4C bin ,'4C' str_bin union all
	select 77, 0x4D bin ,'4D' str_bin union all
	select 78, 0x4E bin ,'4E' str_bin union all
	select 79, 0x4F bin ,'4F' str_bin union all
	select 80, 0x50 bin ,'50' str_bin union all
	select 81, 0x51 bin ,'51' str_bin union all
	select 82, 0x52 bin ,'52' str_bin union all
	select 83, 0x53 bin ,'53' str_bin union all
	select 84, 0x54 bin ,'54' str_bin union all
	select 85, 0x55 bin ,'55' str_bin union all
	select 86, 0x56 bin ,'56' str_bin union all
	select 87, 0x57 bin ,'57' str_bin union all
	select 88, 0x58 bin ,'58' str_bin union all
	select 89, 0x59 bin ,'59' str_bin union all
	select 90, 0x5A bin ,'5A' str_bin union all
	select 91, 0x5B bin ,'5B' str_bin union all
	select 92, 0x5C bin ,'5C' str_bin union all
	select 93, 0x5D bin ,'5D' str_bin union all
	select 94, 0x5E bin ,'5E' str_bin union all
	select 95, 0x5F bin ,'5F' str_bin union all
	select 96, 0x60 bin ,'60' str_bin union all
	select 97, 0x61 bin ,'61' str_bin union all
	select 98, 0x62 bin ,'62' str_bin union all
	select 99, 0x63 bin ,'63' str_bin union all
	select 100, 0x64 bin ,'64' str_bin union all
	select 101, 0x65 bin ,'65' str_bin union all
	select 102, 0x66 bin ,'66' str_bin union all
	select 103, 0x67 bin ,'67' str_bin union all
	select 104, 0x68 bin ,'68' str_bin union all
	select 105, 0x69 bin ,'69' str_bin union all
	select 106, 0x6A bin ,'6A' str_bin union all
	select 107, 0x6B bin ,'6B' str_bin union all
	select 108, 0x6C bin ,'6C' str_bin union all
	select 109, 0x6D bin ,'6D' str_bin union all
	select 110, 0x6E bin ,'6E' str_bin union all
	select 111, 0x6F bin ,'6F' str_bin union all
	select 112, 0x70 bin ,'70' str_bin union all
	select 113, 0x71 bin ,'71' str_bin union all
	select 114, 0x72 bin ,'72' str_bin union all
	select 115, 0x73 bin ,'73' str_bin union all
	select 116, 0x74 bin ,'74' str_bin union all
	select 117, 0x75 bin ,'75' str_bin union all
	select 118, 0x76 bin ,'76' str_bin union all
	select 119, 0x77 bin ,'77' str_bin union all
	select 120, 0x78 bin ,'78' str_bin union all
	select 121, 0x79 bin ,'79' str_bin union all
	select 122, 0x7A bin ,'7A' str_bin union all
	select 123, 0x7B bin ,'7B' str_bin union all
	select 124, 0x7C bin ,'7C' str_bin union all
	select 125, 0x7D bin ,'7D' str_bin union all
	select 126, 0x7E bin ,'7E' str_bin union all
	select 127, 0x7F bin ,'7F' str_bin union all
	select 128, 0x80 bin ,'80' str_bin union all
	select 129, 0x81 bin ,'81' str_bin union all
	select 130, 0x82 bin ,'82' str_bin union all
	select 131, 0x83 bin ,'83' str_bin union all
	select 132, 0x84 bin ,'84' str_bin union all
	select 133, 0x85 bin ,'85' str_bin union all
	select 134, 0x86 bin ,'86' str_bin union all
	select 135, 0x87 bin ,'87' str_bin union all
	select 136, 0x88 bin ,'88' str_bin union all
	select 137, 0x89 bin ,'89' str_bin union all
	select 138, 0x8A bin ,'8A' str_bin union all
	select 139, 0x8B bin ,'8B' str_bin union all
	select 140, 0x8C bin ,'8C' str_bin union all
	select 141, 0x8D bin ,'8D' str_bin union all
	select 142, 0x8E bin ,'8E' str_bin union all
	select 143, 0x8F bin ,'8F' str_bin union all
	select 144, 0x90 bin ,'90' str_bin union all
	select 145, 0x91 bin ,'91' str_bin union all
	select 146, 0x92 bin ,'92' str_bin union all
	select 147, 0x93 bin ,'93' str_bin union all
	select 148, 0x94 bin ,'94' str_bin union all
	select 149, 0x95 bin ,'95' str_bin union all
	select 150, 0x96 bin ,'96' str_bin union all
	select 151, 0x97 bin ,'97' str_bin union all
	select 152, 0x98 bin ,'98' str_bin union all
	select 153, 0x99 bin ,'99' str_bin union all
	select 154, 0x9A bin ,'9A' str_bin union all
	select 155, 0x9B bin ,'9B' str_bin union all
	select 156, 0x9C bin ,'9C' str_bin union all
	select 157, 0x9D bin ,'9D' str_bin union all
	select 158, 0x9E bin ,'9E' str_bin union all
	select 159, 0x9F bin ,'9F' str_bin union all
	select 160, 0xA0 bin ,'A0' str_bin union all
	select 161, 0xA1 bin ,'A1' str_bin union all
	select 162, 0xA2 bin ,'A2' str_bin union all
	select 163, 0xA3 bin ,'A3' str_bin union all
	select 164, 0xA4 bin ,'A4' str_bin union all
	select 165, 0xA5 bin ,'A5' str_bin union all
	select 166, 0xA6 bin ,'A6' str_bin union all
	select 167, 0xA7 bin ,'A7' str_bin union all
	select 168, 0xA8 bin ,'A8' str_bin union all
	select 169, 0xA9 bin ,'A9' str_bin union all
	select 170, 0xAA bin ,'AA' str_bin union all
	select 171, 0xAB bin ,'AB' str_bin union all
	select 172, 0xAC bin ,'AC' str_bin union all
	select 173, 0xAD bin ,'AD' str_bin union all
	select 174, 0xAE bin ,'AE' str_bin union all
	select 175, 0xAF bin ,'AF' str_bin union all
	select 176, 0xB0 bin ,'B0' str_bin union all
	select 177, 0xB1 bin ,'B1' str_bin union all
	select 178, 0xB2 bin ,'B2' str_bin union all
	select 179, 0xB3 bin ,'B3' str_bin union all
	select 180, 0xB4 bin ,'B4' str_bin union all
	select 181, 0xB5 bin ,'B5' str_bin union all
	select 182, 0xB6 bin ,'B6' str_bin union all
	select 183, 0xB7 bin ,'B7' str_bin union all
	select 184, 0xB8 bin ,'B8' str_bin union all
	select 185, 0xB9 bin ,'B9' str_bin union all
	select 186, 0xBA bin ,'BA' str_bin union all
	select 187, 0xBB bin ,'BB' str_bin union all
	select 188, 0xBC bin ,'BC' str_bin union all
	select 189, 0xBD bin ,'BD' str_bin union all
	select 190, 0xBE bin ,'BE' str_bin union all
	select 191, 0xBF bin ,'BF' str_bin union all
	select 192, 0xC0 bin ,'C0' str_bin union all
	select 193, 0xC1 bin ,'C1' str_bin union all
	select 194, 0xC2 bin ,'C2' str_bin union all
	select 195, 0xC3 bin ,'C3' str_bin union all
	select 196, 0xC4 bin ,'C4' str_bin union all
	select 197, 0xC5 bin ,'C5' str_bin union all
	select 198, 0xC6 bin ,'C6' str_bin union all
	select 199, 0xC7 bin ,'C7' str_bin union all
	select 200, 0xC8 bin ,'C8' str_bin union all
	select 201, 0xC9 bin ,'C9' str_bin union all
	select 202, 0xCA bin ,'CA' str_bin union all
	select 203, 0xCB bin ,'CB' str_bin union all
	select 204, 0xCC bin ,'CC' str_bin union all
	select 205, 0xCD bin ,'CD' str_bin union all
	select 206, 0xCE bin ,'CE' str_bin union all
	select 207, 0xCF bin ,'CF' str_bin union all
	select 208, 0xD0 bin ,'D0' str_bin union all
	select 209, 0xD1 bin ,'D1' str_bin union all
	select 210, 0xD2 bin ,'D2' str_bin union all
	select 211, 0xD3 bin ,'D3' str_bin union all
	select 212, 0xD4 bin ,'D4' str_bin union all
	select 213, 0xD5 bin ,'D5' str_bin union all
	select 214, 0xD6 bin ,'D6' str_bin union all
	select 215, 0xD7 bin ,'D7' str_bin union all
	select 216, 0xD8 bin ,'D8' str_bin union all
	select 217, 0xD9 bin ,'D9' str_bin union all
	select 218, 0xDA bin ,'DA' str_bin union all
	select 219, 0xDB bin ,'DB' str_bin union all
	select 220, 0xDC bin ,'DC' str_bin union all
	select 221, 0xDD bin ,'DD' str_bin union all
	select 222, 0xDE bin ,'DE' str_bin union all
	select 223, 0xDF bin ,'DF' str_bin union all
	select 224, 0xE0 bin ,'E0' str_bin union all
	select 225, 0xE1 bin ,'E1' str_bin union all
	select 226, 0xE2 bin ,'E2' str_bin union all
	select 227, 0xE3 bin ,'E3' str_bin union all
	select 228, 0xE4 bin ,'E4' str_bin union all
	select 229, 0xE5 bin ,'E5' str_bin union all
	select 230, 0xE6 bin ,'E6' str_bin union all
	select 231, 0xE7 bin ,'E7' str_bin union all
	select 232, 0xE8 bin ,'E8' str_bin union all
	select 233, 0xE9 bin ,'E9' str_bin union all
	select 234, 0xEA bin ,'EA' str_bin union all
	select 235, 0xEB bin ,'EB' str_bin union all
	select 236, 0xEC bin ,'EC' str_bin union all
	select 237, 0xED bin ,'ED' str_bin union all
	select 238, 0xEE bin ,'EE' str_bin union all
	select 239, 0xEF bin ,'EF' str_bin union all
	select 240, 0xF0 bin ,'F0' str_bin union all
	select 241, 0xF1 bin ,'F1' str_bin union all
	select 242, 0xF2 bin ,'F2' str_bin union all
	select 243, 0xF3 bin ,'F3' str_bin union all
	select 244, 0xF4 bin ,'F4' str_bin union all
	select 245, 0xF5 bin ,'F5' str_bin union all
	select 246, 0xF6 bin ,'F6' str_bin union all
	select 247, 0xF7 bin ,'F7' str_bin union all
	select 248, 0xF8 bin ,'F8' str_bin union all
	select 249, 0xF9 bin ,'F9' str_bin union all
	select 250, 0xFA bin ,'FA' str_bin union all
	select 251, 0xFB bin ,'FB' str_bin union all
	select 252, 0xFC bin ,'FC' str_bin union all
	select 253, 0xFD bin ,'FD' str_bin union all
	select 254, 0xFE bin ,'FE' str_bin union all
	select 255, 0xFF bin ,'FF' str_bin
) t

create function dbo.binary_to_string (@bin varbinary(16))
returns varchar(32)
as
begin
	declare @var varchar(32)
	set @var = ''

	select
		@var = @var + str_bin
	from dbo.binary_map a
		inner join (
			select 
				substring(@bin, num+1,1) bin
			from dbo.binary_map
			where num <= datalength(@bin)-1
		) b
		on a.bin = b.bin

	return @var
end
go

select dbo.binary_to_string(0x002421A4B8EB)
/*
002421A4B8EB
*/