文章作者 jieh
於 2007/3/21 下午 12:13:00
|
SQL 動態語法完整範例 - 撐著用繳款資訊更新 Ver 1.1
| drop table ICMS30Bak.dbo.JobBak_V2_繳款記錄 select * into ICMS30Bak.dbo.JobBak_V2_繳款記錄 from confer.dbo.V2_繳款記錄
-- 從債協專用更新最新繳款記錄 - vSQL1
insert into confer.dbo.V2_繳款記錄 (cust_idn) select distinct cust_idn from confer.dbo.V2_繳款記錄_債協專用 where cust_idn not in (select cust_idn from confer.dbo.V2_繳款記錄)
declare @vSQL1 nvarchar(1000), @vSQL2 nvarchar(1000) declare @ColumnName varchar(50), @RunDate datetime, @MaxDate datetime, @DateStr char(8)
-- 取得 Table 中以年月設計的欄位最大值 (考慮未來維護的人只知道增加欄位而未修改程式) select @MaxDate = convert(char(8), convert(int, max(substring(col.name, 8, 4)) + '25') + 19110000) from sysobjects obj join syscolumns col on obj.id = col.id where obj.name = 'v2_繳款記錄' and col.name like 'INCOME_%'
set @vSQL1 = 'update V2_繳款記錄 set FIRST_PAY_DATE = V1.FIRST_PAY_DATE' set @vSQL2 = 'update V2_繳款記錄 set first_in_date = dateadd(day, 15, first_pay_date) where (0=1'
set @RunDate = @MaxDate while @RunDate >= '2006-02-01' begin
set @ColumnName = 'INCOME_' + convert(char(4), (datepart(year, @RunDate) - 1911) * 100 + datepart(month, @RunDate)) set @vSQL1 = @vSQL1 + ', ' + @ColumnName + ' = V1.' + @ColumnName set @vSQL2 = @vSQL2 + ' or upper(' + @ColumnName + ') = ''Y'''
set @RunDate = dateadd(month, -1, @RunDate) continue end
set @vSQL1 = @vSQL1 + ' from V2_繳款記錄 V2 join V2_繳款記錄_債協專用 V1 on V1.CUST_IDN = V2.CUST_IDN' print @vSQL1 execute sp_executesql @vSQL1
update V2_繳款記錄 set last_in_date = null, first_in_date = null, times = 0 set @vSQL2 = @vSQL2 + ')' print @vSQL2 execute sp_executesql @vSQL2
set @RunDate = @MaxDate while @RunDate > '2006-02-01' begin set @DateStr = convert(char(8), @RunDate, 112)
set @vSQL1 = 'update V2_繳款記錄 set times = times + 1,' + ' last_in_date = case when last_in_date is null then ''' + @DateStr + ''' else last_in_date end' + ' from V2_繳款記錄 where income_' + convert(char(4), convert(int, substring(@DateStr, 1, 6)) - 191100) + ' = ''Y''' print @vSQL1 execute sp_executesql @vSQL1
set @RunDate = dateadd(month, -1, @RunDate) continue end
|
(轉貼時請加註來源) 引用自:藍色小惡魔《用 SQL 語法動態組合 SQL》 http://www.imp.idv.tw/play/forum/viewthread?thread=743 <hr><script type="text/javascript" src="http://adcenter.conn.tw/oeya_ad.php?key=7971"></script> 這種操死 User 的作法當然不是長久之計 但是也不能操死自己 所以只好玩 SQL 填補需求裂口
xuite, pixnet
2016/7/27:71 2016/7/27:18
----------------------------------------
支持小惡魔 BTC : 19tn3RnCuwZVukXAwyhDWZD4uBgUZoGJPx LTC : LTFa17pSvvoe3aU5jbmfcmEpo1xuGa9XeA 知識跟八卦一樣,越多人知道越有價值;知識最好的備份方法,散播! 藍色小惡魔(林永傑): 臉書
|