草庐IT

关于 SQL Server：SQL Server – 查询按计数拆分时间(重叠办公室)

codeneng 2023-03-28 原文

SQL Server - Query to split time by count (overlapping offices)

我正在寻找一些关于我应该采取的查询方法的建议。我有一张表 (EMP)，其中存储了今年的员工详细信息和工作时间(每周 40 小时)。另外 2 个表存储员工所属的主要和次要办公室。由于员工可以在办公室之间移动，因此这些都与日期一起存储。

我希望返回员工在办公室期间的工作小时数。如果员工的主要办公室与次要办公室重叠，则小时数应仅按重叠期间的重叠办公室数量进行划分。

我在下面附上了示例 DDL。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
-- Employee Table with hours for year 2014

CREATE TABLE [dbo].[EMP](
    [EMP_ID] [INT] NOT NULL,
    [EMP_NAME] [VARCHAR](255) NULL,
    [EMP_FYHOURS] [FLOAT] NULL,
 CONSTRAINT [PK_EMP] PRIMARY KEY CLUSTERED
(
    [EMP_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

-- Employees and their primary offices

CREATE TABLE [dbo].[OFFICEPRIMARY](
    [OFFICEPRIMARY_ID] [INT] NOT NULL,
    [OFFICEPRIMARY_NAME] [VARCHAR](255) NULL,
    [OFFICEPRIMARY_EMP_ID] [INT] NOT NULL,
    [OFFICEPRIMARY_START] [datetime] NULL,
    [OFFICEPRIMARY_END] [datetime] NULL,
 CONSTRAINT [PK_OFFICEPRIMARY] PRIMARY KEY CLUSTERED
(
    [OFFICEPRIMARY_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[OFFICEPRIMARY]  WITH CHECK ADD  CONSTRAINT [FK_OFFICEPRIMARY_FK1] FOREIGN KEY([OFFICEPRIMARY_EMP_ID])
REFERENCES [dbo].[EMP] ([EMP_ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[OFFICEPRIMARY] CHECK CONSTRAINT [FK_OFFICEPRIMARY_FK1]
GO

-- Employees and their secondary offices

CREATE TABLE [dbo].[OFFICESECONDARY](
    [OFFICESECONDARY_ID] [INT] NOT NULL,
    [OFFICESECONDARY_NAME] [VARCHAR](255) NULL,
    [OFFICESECONDARY_EMP_ID] [INT] NOT NULL,
    [OFFICESECONDARY_START] [datetime] NULL,
    [OFFICESECONDARY_END] [datetime] NULL,
 CONSTRAINT [PK_OFFICESECONDARY] PRIMARY KEY CLUSTERED
(
    [OFFICESECONDARY_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[OFFICESECONDARY]  WITH CHECK ADD  CONSTRAINT [FK_OFFICESECONDARY_FK1] FOREIGN KEY([OFFICESECONDARY_EMP_ID])
REFERENCES [dbo].[EMP] ([EMP_ID])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[OFFICESECONDARY] CHECK CONSTRAINT [FK_OFFICESECONDARY_FK1]
GO

-- Insert sample data

INSERT INTO EMP (EMP_ID, EMP_NAME, EMP_FYHOURS)
VALUES (1, 'John Smith', 2080);

INSERT INTO EMP (EMP_ID, EMP_NAME, EMP_FYHOURS)
VALUES (2, 'Jane Doe', 2080);

GO

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (1, 'London', 1, '2014-01-01', '2014-05-31')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (2, 'Berlin', 1, '2014-06-01', '2014-08-31')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (3, 'New York', 1, '2014-09-01', '2014-12-31')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (4, 'New York', 2, '2014-01-01', '2014-04-15')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (5, 'Paris', 2, '2014-04-16', '2014-09-30')

INSERT INTO OFFICEPRIMARY (OFFICEPRIMARY_ID, OFFICEPRIMARY_NAME, OFFICEPRIMARY_EMP_ID, OFFICEPRIMARY_START, OFFICEPRIMARY_END)
VALUES (6, 'London', 2, '2014-10-01', '2014-12-31')

GO

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (1, 'Paris', 1, '2014-01-01', '2014-03-31')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (2, 'Lyon', 1, '2014-04-01', '2014-05-15')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (3, 'Berlin', 1, '2014-05-16', '2014-09-30')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (4, 'Chicago', 1, '2014-10-01', '2015-02-22')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (5, 'Chicago', 2, '2013-11-21', '2014-04-10')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (6, 'Berlin', 2, '2014-04-11', '2014-09-16')

INSERT INTO OFFICESECONDARY (OFFICESECONDARY_ID, OFFICESECONDARY_NAME, OFFICESECONDARY_EMP_ID, OFFICESECONDARY_START, OFFICESECONDARY_END)
VALUES (7, 'Amsterdam', 2, '2014-09-17', '2015-03-31')

GO

感谢您的指点。我调整了您的查询，使其呈现主要和次要办公室的联合。

剩下的就是计算办公室之间重叠时段的工作时间。例如，

约翰·史密斯，纽约，2014 年 1 月 4 日，2014 年 10 月 8 日

约翰·史密斯，伦敦，2014 年 1 月 8 日，2014 年 12 月 31 日

对于 2014 年 1 月 8 日至 2014 年 8 月 10 日这两个办公室之间的重叠时间段，我预计工作时间将平均分配。如果有 3 个重叠的办公室，那么它将被分成 3 路。

1
2
3
4
5
6
7
8
SELECT 'Primary' AS Office, e.EMP_NAME, op.OFFICEPRIMARY_NAME, op.OFFICEPRIMARY_START, op.OFFICEPRIMARY_END, datediff(wk,OFFICEPRIMARY_START,OFFICEPRIMARY_END) * 40 AS HoursWorkedPrimary
FROM EMP e
INNER JOIN OFFICEPRIMARY op ON op.OFFICEPRIMARY_EMP_ID = e.EMP_ID
UNION ALL
SELECT 'Secondary' AS Office, e.EMP_NAME, os.OFFICESECONDARY_NAME, os.OFFICESECONDARY_START, os.OFFICESECONDARY_END, datediff(wk,OFFICESECONDARY_START,OFFICESECONDARY_END) * 40 AS HoursWorkedSecondary
FROM EMP e
INNER JOIN OFFICESECONDARY os ON os.OFFICESECONDARY_EMP_ID = e.EMP_ID
ORDER BY e.EMP_NAME

如果我理解正确，您希望看到的最终结果是每个员工和办公室的总工作小时数？

我想出了这个：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
-- generate date table
DECLARE @MinDate datetime, @MaxDate datetime
SET @MinDate = (SELECT MIN(d) FROM (SELECT d = OFFICEPRIMARY_START FROM dbo.OFFICEPRIMARY UNION SELECT OFFICESECONDARY_START FROM dbo.OFFICESECONDARY) a)
SET @MaxDate = (SELECT MAX(d) FROM (SELECT d = OFFICEPRIMARY_END FROM dbo.OFFICEPRIMARY UNION SELECT OFFICESECONDARY_END FROM dbo.OFFICESECONDARY) a)

SELECT
    d = DATEADD(DAY, NUMBER, @MinDate)
INTO
    #tmp_dates
FROM
    (SELECT DISTINCT NUMBER FROM master.dbo.spt_values WHERE name IS NULL) n
WHERE
    DATEADD(DAY, NUMBER, @MinDate) < @MaxDate


;WITH CTE AS
(
SELECT  
    d.d
    ,o.OfficeType
    ,o.OfficeID
    ,o.OfficeName
    ,o.EmpID
    ,EmpName = e.EMP_NAME
    ,HoursWorked = 8 / (COUNT(1) OVER (PARTITION BY EmpID, d))
FROM
    (
        SELECT
            OfficeType = 1
            ,OfficeID = op.OFFICEPRIMARY_ID
            ,OfficeName = op.OFFICEPRIMARY_NAME
            ,EmpID = op.OFFICEPRIMARY_EMP_ID
            ,StartDate = op.OFFICEPRIMARY_START
            ,EndDate = op.OFFICEPRIMARY_END
        FROM
            dbo.OFFICEPRIMARY op

        UNION

        SELECT
            OfficeType = 2
            ,OfficeID = os.OFFICESECONDARY_ID
            ,OfficeName = os.OFFICESECONDARY_NAME
            ,EmpID = os.OFFICESECONDARY_EMP_ID
            ,StartDate = os.OFFICESECONDARY_START
            ,EndDate = os.OFFICESECONDARY_END
        FROM
            dbo.OFFICESECONDARY os
    ) o

INNER JOIN
    dbo.EMP e ON e.EMP_ID = o.EmpID
INNER JOIN
    #tmp_dates d ON o.StartDate<=d.d AND o.EndDate>=d.d
)

SELECT
    EmpID
    ,EmpName
    ,OfficeType
    ,OfficeName
    ,TotalHoursWorked = SUM(HoursWorked)
FROM
    CTE
GROUP BY
    EmpID
    ,EmpName
    ,OfficeType
    ,OfficeID
    ,OfficeName
ORDER BY
    EmpID
    ,OfficeName

我首先生成一个临时表，其中包含最小日期和最大日期之间的日期。

然后我合并两个办公室表(为什么你有 2 个表？)，我得到一个 CTE，它返回关于员工、日期、办公室和在这个办公室工作的小时数的数据(8 除以员工所在办公室的数量)这一天上班)。

然后我将这些数据相加，得到按员工和办公室分组的小时总和。

也许有一个更简单的解决方案。这是我想到的第一个解决方案。

下面的链接应该可以帮助您指出正确的方向，以确定日期如何重叠。

计算日期范围内可能重叠的排除天数

这应该会给你一个良好的开端：

1
2
3
4
5
6
7
8
SELECT datediff(wk,OFFICEPRIMARY_START,OFFICEPRIMARY_END) * 40 AS HoursWorkedPrimary
,datediff(wk,OFFICESECONDARY_START,OFFICESECONDARY_END) * 40 AS HoursWorkedSecondary
,EMP_NAME
,OFFICEPRIMARY_NAME,OFFICEPRIMARY_START,OFFICEPRIMARY_END
,OFFICESECONDARY_NAME,OFFICESECONDARY_START,OFFICESECONDARY_END
FROM [EMP]
INNER JOIN OFFICEPRIMARY AS op ON op.OFFICEPRIMARY_EMP_ID = EMP.EMP_ID
INNER JOIN OFFICESECONDARY AS os ON os.OFFICESECONDARY_EMP_ID = EMP.EMP_ID

  • 感谢您的指点。我调整了您的查询，使其呈现主要和次要办公室的联合。

ServerSQLspanclassbrdatabasesqlsql-servertsql

有关关于 SQL Server：SQL Server – 查询按计数拆分时间(重叠办公室)的更多相关文章

  1. ruby - ECONNRESET (Whois::ConnectionError) - 尝试在 Ruby 中查询 Whois 时出错 - 2

    我正在用Ruby编写一个简单的程序来检查域列表是否被占用。基本上它循环遍历列表，并使用以下函数进行检查。require'rubygems'require'whois'defcheck_domain(domain)c=Whois::Client.newc.query("google.com").available?end程序不断出错(即使我在google.com中进行硬编码)，并打印以下消息。鉴于该程序非常简单，我已经没有什么想法了-有什么建议吗？/Library/Ruby/Gems/1.8/gems/whois-2.0.2/lib/whois/server/adapters/base.

  2. ruby - 如何在 Ruby 中拆分参数字符串 Bash 样式？ - 2

    我正在为一个项目制作一个简单的shell，我希望像在Bash中一样解析参数字符串。foobar"helloworld"fooz应该变成:["foo","bar","helloworld","fooz"]等等。到目前为止，我一直在使用CSV::parse_line，将列分隔符设置为""和.compact输出。问题是我现在必须选择是要支持单引号还是双引号。CSV不支持超过一个分隔符。Python有一个名为shlex的模块:>>>shlex.split("Test'helloworld'foo")['Test','helloworld','foo']>>>shlex.split('Test"

  3. ruby-on-rails - 在 Rails 和 ActiveRecord 中查询时忽略某些字段 - 2

    我知道我可以指定某些字段来使用pluck查询数据库。ids=Item.where('due_at但是我想知道，是否有一种方法可以指定我想避免从数据库查询的某些字段。某种反拔？posts=Post.where(published:true).do_not_lookup(:enormous_field) 最佳答案 Model#attribute_names应该返回列/属性数组。您可以排除其中一些并传递给pluck或select方法。像这样:posts=Post.where(published:true).select(Post.attr

  4. ruby-on-rails - Ruby 检查日期时间是否为 iso8601 并保存 - 2

    我需要检查DateTime是否采用有效的ISO8601格式。喜欢:#iso8601?我检查了ruby​​是否有特定方法，但没有找到。目前我正在使用date.iso8601==date来检查这个。有什么好的方法吗？编辑解释我的环境，并改变问题的范围。因此，我的项目将使用jsapiFullCalendar，这就是我需要iso8601字符串格式的原因。我想知道更好或正确的方法是什么，以正确的格式将日期保存在数据库中，或者让ActiveRecord完成它们的工作并在我需要时间信息时对其进行操作。 最佳答案 我不太明白你的问题。我假设您想检查

  5. ruby-on-rails - 将 Ruby 中的日期/时间格式化为 YYYY-MM-DD HH :MM:SS - 2

    这个问题在这里已经有了答案:Railsformattingdate(4个答案)关闭4年前。我想格式化Time.Now函数以显示YYYY-MM-DDHH:MM:SS而不是:“2018-03-0909:47:19+0000”该函数需要放在时间中.现在功能。require‘roo’require‘roo-xls’require‘byebug’file_name=ARGV.first||“Template.xlsx”excel_file=Roo::Spreadsheet.open(“./#{file_name}“,extension::xlsx)xml=Nokogiri::XML::Build

  6. ruby - 查找字符串中的内容类型(数字、日期、时间、字符串等) - 2

    我正在尝试解析一个CSV文件并使用SQL命令自动为其创建一个表。CSV中的第一行给出了列标题。但我需要推断每个列的类型。Ruby中是否有任何函数可以找到每个字段中内容的类型。例如，CSV行:"12012","Test","1233.22","12:21:22","10/10/2009"应该产生像这样的类型['integer','string','float','time','date']谢谢! 最佳答案 require'time'defto_something(str)if(num=Integer(str)rescueFloat(s

  7. ruby-on-rails - Ruby on Rails 计数器缓存错误 - 2

    尝试在我的RoR应用程序中实现计数器缓存列时出现错误Unknownkey(s):counter_cache。我在这个问题中实现了模型关联:Modelassociationquestion这是我的迁移:classAddVideoVotesCountToVideos0Video.reset_column_informationVideo.find(:all).eachdo|p|p.update_attributes:videos_votes_count,p.video_votes.lengthendenddefself.downremove_column:videos,:video_vot

  8. ruby - 使用多个数组创建计数 - 2

    我正在尝试按0-9和a-z的顺序创建数字和字母列表。我有一组值value_array=['0','1','2','3','4','5','6','7','8','9','a','b'，'光盘'，'e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','','u','v','w','x','y','z']和一个组合列表的数组，按顺序，这些数字可以产生x个字符，比方说三个list_array=[]和一个当前字母和数字组合的数组(在将它插入列表数组之前我会把它变成一个字符串，]current_combo['0','0','0']

  9. sql - 查询忽略时间戳日期的时间范围 - 2

    我正在尝试查询我的Rails数据库(Postgres)中的购买表，我想查询时间范围。例如，我想知道在所有日期的下午2点到3点之间进行了多少次购买。此表中有一个created_at列，但我不知道如何在不搜索特定日期的情况下完成此操作。我试过:Purchases.where("created_atBETWEEN?and?",Time.now-1.hour,Time.now)但这最终只会搜索今天与那些时间的日期。 最佳答案 您需要使用PostgreSQL'sdate_part/extractfunction从created_at中提取小时

  10. ruby - 在没有基准或时间的情况下用 Ruby 测量用户时间或系统时间 - 2

    因为我现在正在做一些时间测量，我想知道是否可以在不使用Benchmark类或命令行实用程序time的情况下测量用户时间或系统时间。使用Time类只显示挂钟时间，而不显示系统和用户时间，但是我正在寻找具有相同灵active的解决方案，例如time=TimeUtility.now#somecodeuser,system,real=TimeUtility.now-time原因是我有点不喜欢Benchmark，因为它不能只返回数字(编辑:我错了-它可以。请参阅下面的答案。)。当然，我可以解析输出，但感觉不对。*NIX系统的time实用程序也应该可以解决我的问题，但我想知道是否已经在Ruby中实

随机推荐