[SQL] 特別時差計法

本帖最後由 紫河馬 於 2013-6-3 17:07 編輯

要計2個日子之間既時差 例如 Interval (day) = Date_A - Date_B

Logic:
- 唔計 紅日 (星期日, 政府公布既銀行假)
- 唔計 星期六下晝 (Say 13:30)
- 如 A, B 都在假期 或六下晝, 而期間都是假期 >  Interval = 0 Day

Example One:  
   Date_A = 2013-03-04 09:00 (Mon)
   Date_B = 2013-03-02 09:00 (Sat AM)
   Interval = 0.5625 Day (即 只計到 2013-03-02 13:30 (SAT))

Example Two:  
   Date_A = 2013-03-03 09:00 (Sun)
   Date_B = 2013-03-01 09:00 (Fri)
   Interval = 1.1875 Day (即 只計到 2013-03-02 13:30 (SAT))

目前方法係起2個 Table

一個儲 紅日  (Bank_holidays 時間為零時零分)
一個儲 六下晝 (Halfdays    時間為13:30 )
  1. Interval =Datediff(minute,Date_B,Date_A)/1440.00
  2. - (Select count(Holiday) from Bank_holidays
  3.                         where Holiday between Date_A and Date_B)
  4. - (13.5/24.00)*(Select count(Halfday) from Halfdays
  5.                         where Halfday between Date_A and Date_B)
複製代碼
但有問題...
因為有Data 係紅日, between過左零時零分就減唔到
而且Logic #3 亦未解決
各位有咩意見??

可唔可以解釋一下
兩個example個day點得出來﹖
example one: 0.5625 Day = 13.5/24 ? 但 13.5 邊度來﹖ sat 9:00-13:30 + mon 0:00 - 09:00?
example two: 1.1875 Day = ? fri 9:00 到 sat 9:00 = 1 day, sat 9:00 - sat 13:30 = 4.5/24 = 0.1875 Day?

TOP

點解要比SQL計, 如果好大量DATA, 會出現好明顯既 QUERY QUEUE, 會一直慢惡性循環

真係要計, stuckoverflow 有人解決到
http://stackoverflow.com/questio ... to-exclude-weekends
  1. SET @start  = '2012-09-30';
  2. SET @end    = '2012-11-03';

  3. SELECT
  4.     @raw_days   := DATEDIFF(@end, @start)+1 'raw_days',
  5.     @full_weeks := FLOOR(@raw_days / 7) 'full_weeks',
  6.     @odd_days   := @raw_days - @full_weeks * 7 'odd_days',
  7.     @wday_start := DAYOFWEEK(@start) 'wday_start',
  8.     @wday_end   := DAYOFWEEK(@end) 'wday_end',
  9.     @weekend_intrusion  := @wday_start + @odd_days 'weekend_intrusion',
  10.     @extra_weekends     :=
  11.         IF(@wday_start = 1, IF(@odd_days = 0, 0, 1),
  12.             IF(@weekend_intrusion > 7, 2,
  13.                 IF(@weekend_intrusion > 6, 1, 0)
  14.             )
  15.         ) 'extra_weekends',
  16.     @total_weekends     := @full_weeks * 2 + @extra_weekends 'total_weekends',
  17.     @total_workdays     := @raw_days - @total_weekends 'total_workdays'
複製代碼

TOP

本帖最後由 紫河馬 於 2013-6-3 19:58 編輯

回復 2# dell
example one - Yes
Sat 09:00-13:30
Mon:00:00 - 09:00
星期日唔計

example two - Yes
同上

TOP

本帖最後由 紫河馬 於 2013-6-4 14:02 編輯

回復 3# ioptional

玩ETL, Raw Data 係人地既 ...
Raw Data Transform  後拎住個 interval 再玩aggregate report...

TOP

suggestion:
1) holiday: 因為係全日,只compare date,BETWEEN date(Date_B) and date(Date_A)
2) halfday 要 exclude holiday (by left join): 再分三part做,
a) > date(Date_B) and < date(Date_A)
b) = date(Date_B), 個時間再計
c) = date(Date_A), 個時間再計

TOP

初步諗法:

1. cal timestamp diff between start and end
2. subtract count of holidays > date(start) and < date(end) * 86400000
3. subtract count of halfdays > date(start) and < date(end) * 48600000
4. check date(start) and date(end) is holiday, if yes subtract them
5. check date(start) and date(end) is halfdays, if yes subtract the remaining

TOP

先union 左你個個紅日同六下晝個table, 再減得唔得

TOP

本帖最後由 紫河馬 於 2013-6-6 12:14 編輯

多謝各位ching~呢家試緊~!
我用SQL server既
玩野用左float 計日子...我知唔係準..不過想比較下速度...之後要寫過 date function version...
原code 太意粉, 唔貼, 主要講下點做,  :
如1999\意粉 請見諒~
首先union 2個 holiday Table:
  1. Select * into #temph  
  2. from
  3. (        Select dtype='HO', holiday ,floatday =cast(holiday as float)  from dss_db.dbo.holiday   
  4.         union all
  5.         Select dtype='HA',holiday ,floatday =cast(holiday as float)  from dss_db.dbo.half_day
  6. ) h
複製代碼
再left join "Raw" Table 去識別Start date/ End Date 有冇係假期/ 六下晝入面:
  1. from raw_table
  2. left join #temph start_h_table on
  3.   floor(cast(start_date as float))-floor(start_h_table.floatday)=0
  4. left join #temph end_h_table on
  5.   floor(cast(end_date as float))-floor(end_h_table.floatday)=0
複製代碼
之後就計假, 同減假, Code比較亂, 只貼start date example:

  1. Select

  2. Diff_d=cast(end_date as float) - cast(start_date as float),

  3. --Start date 星期六減鐘
  4. Sd_Sat = case when start_h_table.floatday- floor(start_h_table.floatday)<>0 and start_h_table.floatday > cast(start_date as float)  --SAT AM Cases then  0
  5.                           when start_h_table.floatday- floor(start_h_table.floatday)<>0 and start_h_table.floatday < cast(start_date as float) --SAT PM Cases
  6.                          then  (floor(start_h_table.floatday)+1)-cast(start_date as float) else 0                                      
  7. -- Sun Day  00:00 - Start time  (Head Sat PM)
  8.                         end,

  9. -- Start date 假期減鐘
  10. -- Next Day 00:00 - Start Time (Head)
  11. Sd_Ho =  case when start_h_table.floatday- floor(start_h_table.floatday)=0 then isnull(((floor(start_h_table.floatday)+1)-cast(start_date as float)),0)else 0 end,

  12. --Count Holiday
  13. HO_CNT = (Select count(holiday) from #temph
  14. where dtype ='HO' and cast(holiday as float) > cast(start_date as float) and cast( end_date  as float)>cast(holiday as float)  ),
  15. --Count Half_day
  16. HA_CNT = (Select count(holiday) from #temph
  17. where dtype ='HA'  and cast(holiday as float) > cast(start_date as float) and cast( end_date  as float)>cast(holiday as float))  
複製代碼
最後做減數, done.
-------------補完----------
改用datediff 唔用float 快3秒
Raw 80萬條, end date indexed

TOP

點解唔用 timestamp (p.s. 唔識 SQL server)

timestamp 可以整數加減, 計完可以除 (60*60*24) > 86400 轉日數
timestamp overflow 另計

仲有就係, 與其 select 哂全部 holiday / halfday 入去 temp
不如一開始直接 select cout & select 1 where exists
計數既話唔需要知實際邊日係假期

TOP