求助MS SQL SERVER高手 ..表與表之間欄位與欄位的時間相加

leechintux

leechintux
個人積分：76分
文章編號：58730250

76分

樓主

2015-12-29 21:49

先上一下圖..

這張表code為

SELECT convert(int,replace(substring(ControlP,1,6),' ','')) AS 'NEWControlP' ,*

FROM Equipment

裡面各欄的數值來源於我的equipment資料庫裡，

ControlP欄為保養日期，

Frequency欄為多久時間後要再保養，值會有1年,2年,3年,5年,每季,NULL,跟一年(同1年),

NEWControlP表要為下次保養的時間(還沒寫好)，

如列1 ControlP=20160302 Frequency=1 所以NEWControlP該為20170302為答案; +2就為20180302

每季就為20160602..碰到超過12月要進位2017,ControlP為NULL則依然保持NULL..依此類推

但每列的時間與保養年分都可能不同，

然後因為Frequency要轉換為單純數字所以我把它另外製作了另一replacetable資料庫裡，圖內的replacename欄為Frequency轉換後的顯示，結果如下圖

求助MS SQL SERVER高手 ..表與表之間欄位與欄位的時間相加

該圖code為

select replacetable.replacename,replacetable.field,*
from Equipment left join replacetable on (Frequency=Name)

請問要最後的顯示結果需要列出欄 ControlP，加總過時間後的NEWControlP，及Frequency 的三欄資料，

請問有高手能夠稍微解說加上解答嗎?

因為很久沒接觸到SQL語言，需要幫忙講解消化一下....萬分拜託，跪求幫助。

2015-12-29 21:49 發佈

文章關鍵字 sq MS SQL SERVER 高手欄位

Kenny_Din

Kenny_Din
個人積分：7023分
文章編號：58730559

7023分

2樓

2015-12-29 22:14

select ControlP,Frequency,
case Frequency
when '每季' then DATE_ADD(STR_TO_DATE(ControlP,'%Y%m/%d'),INTERVAL 4 MONTH)
when '1年' then DATE_ADD(STR_TO_DATE(ControlP,'%Y%m/%d'),INTERVAL 12 MONTH)
when '2年' then DATE_ADD(STR_TO_DATE(ControlP,'%Y%m/%d'),INTERVAL 24 MONTH)
when '3年' then DATE_ADD(STR_TO_DATE(ControlP,'%Y%m/%d'),INTERVAL 36 MONTH)
when '4年' then DATE_ADD(STR_TO_DATE(ControlP,'%Y%m/%d'),INTERVAL 48 MONTH)
when '5年' then DATE_ADD(STR_TO_DATE(ControlP,'%Y%m/%d'),INTERVAL 60 MONTH)
else null
end NEWControlP
from Equipment

這樣?

leechintux

leechintux
個人積分：76分
文章編號：58731103

76分

樓主

2015-12-29 22:57

Kenny_Din wrote:
select ControlP...(恕刪)

感謝你的回覆...可是跑起來好像有問題，

他說STR_TO_DATE 不是可辨識的內建函數名稱

還有NEWConctrolP處的語法不正確~"~

rone2101

rone2101
個人積分：1699分
文章編號：58732247

1699分

4樓

2015-12-30 0:59

leechintux wrote:
先上一下圖..這張...(恕刪)

首先你先了解一下字串和日期間的轉換公式和日期的加減

1. String(yyyyMMdd) to Datetime
SELECT convert(datetime, '20161023', 112) -- ISO yyyymmdd
-- 2016-10-23 00:00:00.000

2. DateTime to String (yyyyMMdd)
SELECT CONVERT(char(8), getdate(), 112) -- ISO yyyymmdd
-- 20151230

3. 日期加減 DATEADD (datepart , number , date )
datepart : year/month/day 或 yy/mm/dd 三種值
number 是你要加上的數量
date 是某個日期

4. CASE ("欄位名")
WHEN "條件1" THEN "結果1"
WHEN "條件2" THEN "結果2"
...
[ELSE "結果N"]
END

5. 簡化一下, 把要加上去的都是月, 即 datepart 的值是 month或mm

簡化後的語意 SELECT NEWControlP=CONVERT(char(8), DATEADD (mm, 要加上的月份數, convert(datetime, ControlP, 112))), 112) FROM Equipment

要加上的月份數 SQL 是
CASE Frequency
WHEN '每季' THEN 3
WHEN '1年' THEN 12
WHEN '一年' THEN 12
WHEN '2年' THEN 24
WHEN '二年' THEN 24
WHEN '3年' THEN 36
WHEN '三年' THEN 36
WHEN '5年' THEN 60
WHEN '五年' THEN 60
WHEN ... -- 其他條件
ELSE 0 -- 避免加錯
END

當然囉, 你要用 Join 的方式取得 number 數量也可以...

6. 因為有 NULL 的情況, 所以上面的公式還要加上一層 CASE ControlP WHEN NULL THEN NULL ELSE 第5點公式 END

7. 把數量和單位擠進相同的 Frequency 欄位不是一個好做法...

大體想法可以分成 11月以下和 12月以上,

11月以下 : X 也就是 X月
12月以上 : Y/12 (商數) + Y (餘數), 也就是 n年m月

leechintux

leechintux
個人積分：76分
文章編號：58732673

76分

樓主

2015-12-30 2:37

rone2101 wrote:
首先你先了解一下 ...(恕刪)

謝謝大大...大致上看得懂，可是when then這部分可以寫的詳細一點嗎?

DATEADD是可以寫出加在我ControlP上的日期沒錯...

可是每行的日期與加上的時間都不同

這部分是要用when then，但是不太明白要如何去表達出來...

達哥~

達哥~
個人積分：36分
文章編號：58734342

36分

6樓

2015-12-30 9:45

看了一下~我覺得 NEWControlP 是不需要的欄位，

有記錄保養日期的話~下次保養時間用保養頻率就算的出來!

replacetable 也非必要，只要將 frequency 切成兩個欄位

[頻率] [時間單位] 即可或是頻率全用月來算會好很多!

取名真是難

取名真是難
個人積分：271分
文章編號：58737556

271分

7樓

2015-12-30 13:26

你把frequency改成月為單位
把controlp變成日期，然後add date不就有new controlp

new controlp = add_date(str_to_date(controlp), frequency, 'Month')

rone2101

rone2101
個人積分：1699分
文章編號：58739970

1699分

8樓

2015-12-30 16:37

leechintux wrote:
謝謝大大...大致...(恕刪)

你的 SQL 語言要認真複習一下喔...

CASE Frequency --表示依據 Frequency 欄位進行動態判斷
WHEN '條件' THEN 月份(數值型態) --表示符合那個條件就動態給上應該加上的月份數量
ELSE 0 --統一特殊情況也是數值型態, 避免 DATEADD() 函式產生語法上的錯誤
END

--過濾 ControlP IS NULL 的資料，若連 NULL 的資料都出現，參考第7點的說明...
SELECT NEWControlP=CONVERT(char(8), DATEADD (mm, CASE Frequency WHEN '每季' THEN 3 WHEN '1年' THEN 12 WHEN '一年' THEN 12 WHEN '2年' THEN 24 WHEN '二年' THEN 24 WHEN '3年' THEN 36 WHEN '三年' THEN 36 WHEN '5年' THEN 60 WHEN '五年' THEN 60 ELSE 0 END , CONVERT(datetime, ControlP, 112)), 112), Frequency, ControlP FROM Equipment WHERE ControlP IS NOT NULL

說明
1. SELECT 語法
　　--SELECT 新欄位名稱=公式產生的結果, 原欄位名稱1, 原欄位名稱2 FROM 資料表名稱 WHERE 篩選條件
　　SELECT NEWControlP=公式產生的結果, Frequency, ControlP FROM Equipment WHERE ControlP IS NOT NULL

2. 上述1 公式產生的結果
　　--CONVERT() 是轉換變數型態、DATEADD() 則是日期加減公式,傳回日期型態資料

2.1 第一個 CONERT() 是為了將日期型態轉成字串型態 yyyyMMdd (112 就是指定資料是yyyyMMdd格式)
　　--CONVERT(char(8), 加上月份後的日期, 112)
　　CONVERT(char(8), DATEADD(...略...), 112)

2.2 DATEADD()的部分
　　--DATEADD(mm, 月份數量, 某個日期)
　　DATEADD (mm, CASE Frequency WHEN '每季' THEN 3 WHEN '1年' THEN 12 WHEN '一年' THEN 12 WHEN '2年' THEN 24 WHEN '二年' THEN 24 WHEN '3年' THEN 36 WHEN '三年' THEN 36 WHEN '5年' THEN 60 WHEN '五年' THEN 60 ELSE 0 END , CONVERT(datetime, ControlP, 112))

　　月份數量, 即 CASE ...END 的部分

　　某個日期, 即第二個 CONVERT() 是為了將字串型態 yyyyMMdd 轉成日期型態
　　--CONVERT(datetime, 欄位變數, 112)
　　CONVERT(datetime, ControlP, 112)

不知這樣拆解過, 會不會比較容易懂？

leechintux

leechintux
個人積分：76分
文章編號：58741694

76分

樓主

2015-12-30 19:23

達哥~ wrote:
看了一下~我覺得 NEWControlP...(恕刪)

這是我上司丟給我的資料...原則上要再盡量不改值跟欄位各項東西的情況下，用現有的所有東西寫出他想要的東西..

最後我還是參考上面when then的語法寫出答案來了...

我也是覺得把時間單位分開，可以很好解決問題...

謝謝你的教導

leechintux

leechintux
個人積分：76分
文章編號：58741752

76分

樓主

2015-12-30 19:29

rone2101 wrote:
你的 SQL 語言...(恕刪)

我的SQL語法重新剛接觸48小時~"~ ..當初還沒要求我要接觸這塊，所以大學的記憶早就已經忘了一乾二淨了..

老闆又隨便丟了這個題目給我，要我不能卡太久在這上面，這些東西對我來說其實幾乎等於新的了...

說實話我也卡了一天最後整合上面的敘述才得出答案....

我現在很努力地在複習了，但是還是有很多不懂的問題，身旁的人也不會，只好上來求助版上的高手。

最後謝謝你的詳細解釋，對我的幫助真的很大很大，謝謝!!