久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

為工作日俱樂部排行

To club the rows for week days(為工作日俱樂部排行)
本文介紹了為工作日俱樂部排行的處理方法,對(duì)大家解決問題具有一定的參考價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)吧!

問題描述

我有如下數(shù)據(jù):

StartDate     EndDate    Duration 
----------
41890         41892       3
41898         41900       3
41906         41907       2
41910         41910       1

StartDate 和 EndDate 是日歷中任何日期的各自 ID 值.我想計(jì)算連續(xù)幾天的持續(xù)時(shí)間總和.在這里,我想包括周末的日子.例如.在上面的數(shù)據(jù)中,假設(shè) 41908 和 41909 是周末,那么我需要的結(jié)果集應(yīng)該如下所示.

StartDate and EndDate are respective ID values for any dates from calendar. I want to calculate the sum of duration for consecutive days. Here I want to include the days which are weekends. E.g. in the above data, let's say 41908 and 41909 are weekends, then my required result set should look like below.

我已經(jīng)有另一個(gè) proc 可以在下一個(gè)工作日返回我,即如果我在該 proc 中傳遞 41907 或 41908 或 41909 作為 DateID,它將在下一個(gè)工作日返回 41910.基本上我想檢查當(dāng)我傳遞上面的 EndDateID 時(shí)我的 proc 返回的 DateID 是否與上面數(shù)據(jù)中的下一個(gè) StartDateID 相同,然后這兩行都應(yīng)該被打成棍子.下面是我想要獲取的數(shù)據(jù).

I already have another proc that can return me the next working day, i.e. if I pass 41907 or 41908 or 41909 as DateID in that proc, it will return 41910 as the next working day. Basically I want to check if the DateID returned by my proc when I pass the above EndDateID is same as the next StartDateID from above data, then both the rows should be clubbed. Below is the data I want to get.

ID          StartDate     EndDate    Duration 
----------
278457        41890       41892       3
278457        41898       41900       3
278457        41906       41910       3

如果要求不明確,請(qǐng)告訴我,我可以進(jìn)一步解釋.

Please let me know in case the requirement is not clear, I can explain further.

我的日期表如下:

DateId        Date      Day
----------
41906      09-04-2014    Thursday
41907      09-05-2014    Friday
41908      09-06-2014    Saturdat
41909      09-07-2014    Sunday
41910      09-08-2014    Monday

這是用于設(shè)置的 SQL 代碼:

Here is the SQL Code for setup:

CREATE TABLE Table1
(
StartDate INT,
EndDate INT,
LeaveDuration INT
)

INSERT INTO Table1
VALUES(41890, 41892, 3),
(41898, 41900, 3),
(41906, 41907, 3),
(41910, 41910, 1)

CREATE TABLE DateTable
(
DateID INT,
Date DATETIME,
Day VARCHAR(20)
)

INSERT INTO DateTable
VALUES(41907, '09-05-2014', 'Friday'),
(41908, '09-06-2014', 'Saturday'),
(41909, '09-07-2014', 'Sunday'),
(41910, '09-08-2014', 'Monday'),
(41911, '09-09-2014', 'Tuesday')

推薦答案

這相當(dāng)復(fù)雜.這是使用窗口函數(shù)的方法.

This is rather complicated. Here is an approach using window functions.

首先用日期表枚舉沒有周末的日期(如果你想也可以去掉假期).然后,使用非等值聯(lián)接將期間擴(kuò)展為每行一天.

First, use the date table to enumerate the dates without weekends (you can also take out holidays if you want). Then, expand the periods into one day per row, by using a non-equijoin.

然后,您可以使用一種技巧來確定連續(xù)的日子.這個(gè)技巧是為每個(gè) id 生成一個(gè)序列號(hào),然后從日期的序列號(hào)中減去它.這是連續(xù)天數(shù)的常數(shù).最后一步只是一個(gè)聚合.

You can then use a trick to identify sequential days. This trick is to generate a sequential number for each id and subtract it from the sequential number for the dates. This is a constant for sequential days. The final step is simply an aggregation.

查詢結(jié)果如下:

with d as (
      select d.*, row_number() over (order by date) as seqnum
      from dates d
      where day not in ('Saturday', 'Sunday')
     )
select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)
from (select t.*, ds.seqnum, ds.date,
             (d.seqnum - row_number() over (partition by id order by ds.date) ) as grp
      from table t join
           d ds
           on ds.date between t.startdate and t.enddate
     ) t
group by t.id, grp;

以下是這個(gè) SQL Fiddle的版本:

The following is the version on this SQL Fiddle:

with d as (
      select d.*, row_number() over (order by date) as seqnum
      from datetable d
      where day not in ('Saturday', 'Sunday')
     )
select t.id, min(t.date) as startdate, max(t.date) as enddate, sum(duration)
from (select t.*, ds.seqnum, ds.date,
             (ds.seqnum - row_number() over (partition by id order by ds.date) ) as grp
      from (select t.*, 'abc' as id from table1 t) t join
           d ds
           on ds.dateid between t.startdate and t.enddate
     ) t
group by grp;

我相信這是有效的,但日期表中沒有包含所有日期.

I believe this is working, but the date table doesn't have all the dates in it.

這篇關(guān)于為工作日俱樂部排行的文章就介紹到這了,希望我們推薦的答案對(duì)大家有所幫助,也希望大家多多支持html5模板網(wǎng)!

【網(wǎng)站聲明】本站部分內(nèi)容來源于互聯(lián)網(wǎng),旨在幫助大家更快的解決問題,如果有圖片或者內(nèi)容侵犯了您的權(quán)益,請(qǐng)聯(lián)系我們刪除處理,感謝您的支持!

相關(guān)文檔推薦

Converting Every Child Tags in to a Single Column with multiple Delimiters -SQL Server (3)(將每個(gè)子標(biāo)記轉(zhuǎn)換為具有多個(gè)分隔符的單列-SQL Server (3))
How can I create a view from more than one table?(如何從多個(gè)表創(chuàng)建視圖?)
Create calculated value based on calculated value inside previous row(根據(jù)前一行內(nèi)的計(jì)算值創(chuàng)建計(jì)算值)
How do I stack the first two columns of a table into a single column, but also pair third column with the first column only?(如何將表格的前兩列堆疊成一列,但也僅將第三列與第一列配對(duì)?) - IT屋-程序員軟件開發(fā)技
Recursive t-sql query(遞歸 t-sql 查詢)
Convert Month Name to Date / Month Number (Combinations of Questions amp; Answers)(將月份名稱轉(zhuǎn)換為日期/月份編號(hào)(問題和答案的組合))
主站蜘蛛池模板: 亚洲国产一区在线 | 日韩成年人视频在线 | 一区在线播放 | 91精品国产自产精品男人的天堂 | 极品的亚洲 | 日本午夜一区二区三区 | 免费高清av | 成人免费在线观看 | 黄色亚洲 | 国产91丝袜在线播放 | 在线免费黄色小视频 | 亚洲精品乱码久久久久久按摩 | 国产中文区二幕区2012 | 天堂一区二区三区 | 中文字幕精品一区二区三区在线 | 精品一区二区三区91 | 欧美一区二区三区视频 | 亚洲一区 中文字幕 | 激情毛片 | 久热爱| 99久久精品视频免费 | 日本成人中文字幕 | 中文字幕在线免费视频 | 一级片av | 久久精品国产亚洲 | 黄色成人在线观看 | 国产一区二区在线视频 | 美女视频一区 | 99精品久久久久久久 | 欧美三级免费观看 | 一区二区三区在线电影 | 国产精品亚洲精品日韩已方 | 国产成人精品一区 | 中文字幕在线观看一区二区 | 久久亚洲一区二区三区四区 | 国产分类视频 | 国产欧美精品在线 | 中文字幕日韩欧美一区二区三区 | 成人在线观 | 一区二区三区四区在线视频 | 香蕉视频黄色 |