- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我有一个设计不佳的表,我没有设计它并且无法修复/更改,因为一个 3rd 方黑莓应用程序会写入它。关键是有一个开始记录和一个停止记录,用于在没有连接或验证匹配的情况下跟踪事件。黑莓应用程序不会将这些记录联系在一起。我试图在其自身上创建一个连接,并创建带有开始和结束的临时表,以完全外部连接它们。问题是我有重复的条目,应该将条目标记为没有伴侣。现有数据在开始和结束记录上都有没有配对的行。我已经搜索了 SO 的答案,我找到了一些接近的答案,这些答案使我走到了这一步。我知道这是一个很长的帖子,抱歉。
有一个单例令人惊讶地具有主键的表。没有枢轴/交集表。结构是
ID (int PK)
activityType varchar
beginEnd varchar ('begin' or 'end')
businessKey varchar nullable
date DATETIME
technician varchar
dateSubmitted DATETIME
gpsLatitude float
gpsLongitude float
note varchar
odometer int
DECLARE @DateFrom DATETIME
DECLARE @DateTo DATETIME
SET @DateFrom='20101101'
SET @DateTo='20101102'
DECLARE @Incomplete VARCHAR(15)
SET @Incomplete = 'Incomplete'
DECLARE @StartEvents TABLE
(
[id] [numeric](19, 0) NOT NULL,
[activityType] [varchar](255) NOT NULL,
[beginEnd] [varchar](255) NULL,
[businessKey] [varchar](255) NULL,
[date] [datetime] NOT NULL,
[dateSubmitted] [datetime] NULL,
[gpsLatitude] [float] NULL,
[gpsLongitude] [float] NULL,
[note] [varchar](255) NULL,
[odometer] [int] NULL,
[technician] [varchar](255) NOT NULL
)
INSERT @StartEvents
([ID],[activityType],[beginEnd],[businessKey],[date],[dateSubmitted],[gpsLatitude]
,[gpsLongitude],[note],[odometer],[technician])
SELECT *
FROM dbo.TimeEntry
WHERE
[date] between @DateFrom AND @DateTo
AND beginEnd = 'Begin'
--AND [technician] = 'FRED'
ORDER by technician
------------------------------------------------------------
DECLARE @EndEvents TABLE
(
[id] [numeric](19, 0) NOT NULL,
[activityType] [varchar](255) NOT NULL,
[beginEnd] [varchar](255) NULL,
[businessKey] [varchar](255) NULL,
[date] [datetime] NOT NULL,
[dateSubmitted] [datetime] NULL,
[gpsLatitude] [float] NULL,
[gpsLongitude] [float] NULL,
[note] [varchar](255) NULL,
[odometer] [int] NULL,
[technician] [varchar](255) NOT NULL
)
INSERT @EndEvents
([ID],[activityType],[beginEnd],[businessKey],[date],[dateSubmitted],[gpsLatitude]
,[gpsLongitude],[note],[odometer],[technician])
SELECT *
FROM dbo.TimeEntry
WHERE
[date] between @DateFrom AND @DateTo AND
beginEnd = 'End'
--AND [technician] = 'FRED'
ORDER by technician
-- And then a conventional SELECT
SELECT
StartEvents.id
,EndEvents.id AS EndID
,COALESCE(
StartEvents.activityType ,EndEvents.activityType ,'Not Available'
) AS ActivityType
--,StartEvents.beginEnd as [Begin]
--,EndEvents.beginEnd AS [End]
,COALESCE (
convert(VARCHAR(12), StartEvents.[date], 103),
convert(VARCHAR(12), EndEvents.[date], 103), @Incomplete
) as [Event Date]
,COALESCE (
convert(VARCHAR(12), EndEvents.[date], 103), @Incomplete
) as [End Date]
,COALESCE(
CONVERT(VARCHAR(5) , StartEvents.dateSubmitted , 108) , @Incomplete
) AS StartTime
,COALESCE(
CONVERT(VARCHAR(5) , EndEvents.dateSubmitted , 108) , @Incomplete
) AS EndTime
,COALESCE(
StartEvents.note, EndEvents.note, ''
) as [Note]
,COALESCE(
StartEvents.technician,EndEvents.technician,'Not Available'
) AS Technician
FROM
@StartEvents As StartEvents
FULL OUTER JOIN
@EndEvents AS EndEvents ON
StartEvents.technician = EndEvents.technician AND
StartEvents.businessKey = EndEvents.businessKey AND
StartEvents.activityType = EndEvents.activityType
AND convert(VARCHAR(12), StartEvents.[date], 103) = convert(VARCHAR(12), EndEvents.[date], 103)
-- WHERE
--StartEvents.[date] between @DateFrom AND @DateTo OR
--StartEvents.[dateSubmitted] between @DateFrom AND @DateTo
ORDER BY
StartEvents.Technician,
ID,ENDID
id,activityType,beginEnd,businessKey,date,dateSubmitted,gpsLatitude,gpsLongitude,note,odometer,technician
23569,Standby,Begin,,2010-11-01 08:00:13.000,2010-11-01 08:26:45.533,34.139,-77.895,#1140,28766,barthur@fubar.com
23570,Travel,Begin,00100228002,2010-11-01 07:00:44.000,2010-11-01 08:34:15.370,35.0634,-80.7668,,18706,creneau@fubar.com
23571,Standby,End,,2010-11-01 08:30:08.000,2010-11-01 08:35:20.463,34.0918,-77.9002,#1140,28766,barthur@fubar.com
23572,Travel,Begin,00100226488,2010-11-01 08:30:41.000,2010-11-01 08:36:56.420,34.0918,-77.9002,,28766,barthur@fubar.com
23573,Travel,End,00100226488,2010-11-01 08:45:00.000,2010-11-01 08:44:15.553,34.0918,-77.9002,,28768,barthur@fubar.com
23574,OnSite,Begin,00100226488,2010-11-01 08:45:41.000,2010-11-01 09:24:23.943,34.0918,-77.9002,,0,barthur@fubar.com
23575,OnSite,End,00100226488,2010-11-01 09:30:10.000,2010-11-01 09:33:19.953,34.0918,-77.9002,,28768,barthur@fubar.com
23576,Travel,Begin,00100228137,2010-11-01 09:30:20.000,2010-11-01 09:34:57.330,34.0918,-77.9002,,28768,barthur@fubar.com
23577,Travel,End,00100228137,2010-11-01 09:45:51.000,2010-11-01 09:42:39.230,34.0918,-77.9002,,28771,barthur@fubar.com
23578,Travel,Begin,00100228138,2010-11-01 09:00:23.000,2010-11-01 09:58:22.857,34.9827,-80.5365,,18749,creneau@fubar.com
23579,OnSite,Begin,00100228137,2010-11-01 09:45:47.000,2010-11-01 10:41:10.563,34.139,-77.895,,0,barthur@fubar.com
23580,OnSite,End,00100228137,2010-11-01 10:45:43.000,2010-11-01 11:09:14.393,34.139,-77.895,,28771,barthur@fubar.com
23581,OnSite,Begin,00100228142,2010-11-01 10:45:42.000,2010-11-01 11:29:26.447,34.139,-77.895,#1015,28771,barthur@fubar.com
23582,OnSite,End,00100228142,2010-11-01 11:15:18.000,2010-11-01 11:55:28.603,34.139,-77.895,#1015,28771,barthur@fubar.com
23583,Travel,Begin,,2010-11-01 11:15:06.000,2010-11-01 11:56:01.633,34.139,-77.895,"#1142 Fuel, #1154 Tickets",28771,barthur@fubar.com
23584,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:07:54.867,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com
23585,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:07:55.087,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com
23586,Break,Begin,,2010-11-01 12:00:26.000,2010-11-01 12:08:06.007,34.139,-77.895,#1153,28774,barthur@fubar.com
23587,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:08:06.040,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com
23588,Break,Begin,,2010-11-01 12:00:26.000,2010-11-01 12:08:06.070,34.139,-77.895,#1153,28774,barthur@fubar.com
23589,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:16:02.673,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com
23590,Travel,End,,2010-11-01 12:00:47.000,2010-11-01 12:16:14.220,34.139,-77.895,"#1154, #1142",28774,barthur@fubar.com
23591,Travel,Begin,00100228000,2010-11-01 11:45:19.000,2010-11-01 12:35:46.363,35.0634,-80.7668,,18760,creneau@fubar.com
23592,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:58:51.050,34.0918,-77.9002,,28774,barthur@fubar.com
23593,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:03.830,34.0918,-77.9002,,28774,barthur@fubar.com
23594,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:03.893,34.1594,-77.8929,,28774,barthur@fubar.com
23595,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:03.940,34.1594,-77.8929,,28774,barthur@fubar.com
23596,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.880,34.1594,-77.8929,,28774,barthur@fubar.com
23597,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.927,34.2743,-77.8668,,28774,barthur@fubar.com
23598,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.987,34.2743,-77.8668,,28774,barthur@fubar.com
23599,Travel,Begin,00100228166,2010-11-01 14:00:13.000,2010-11-01 14:29:45.320,35.0634,-80.7668,,18779,creneau@fubar.com
23600,Travel,End,00100227980,2010-11-01 15:15:58.000,2010-11-01 15:15:40.403,35.3414,-78.0325,,28880,barthur@fubar.com
23601,Travel,Begin,00100228205,2010-11-01 15:30:46.000,2010-11-01 15:41:41.810,35.0661,-80.8376,,18781,creneau@fubar.com
23602,OnSite,Begin,00100227980,2010-11-01 15:15:23.000,2010-11-01 15:59:45.203,35.3873,-77.9395,,28880,barthur@fubar.com
23603,OnSite,End,00100227980,2010-11-01 16:15:22.000,2010-11-01 16:06:09.150,35.3873,-77.9395,,28880,barthur@fubar.com
23604,Travel,Begin,00100228007,2010-11-01 16:15:15.000,2010-11-01 16:15:25.253,35.3873,-77.9395,,28880,barthur@fubar.com
23605,Travel,Begin,,2010-11-01 16:15:12.000,2010-11-01 16:20:49.933,35.0445,-80.8227,Return trip home,18785,creneau@fubar.com
23606,Travel,End,00100228007,2010-11-01 16:30:48.000,2010-11-01 16:26:43.360,35.3873,-77.9395,,28884,barthur@fubar.com
23607,Travel,End,,2010-11-01 17:30:14.000,2010-11-01 17:23:57.897,35.2724,-81.1577,Return trip home,18822,creneau@fubar.com
23608,OnSite,Begin,00100228007,2010-11-01 16:30:48.000,2010-11-01 18:38:32.700,35.3941,-77.994,,28880,barthur@fubar.com
23609,Travel,Begin,00100228209,2010-11-01 17:45:16.000,2010-11-01 18:39:05.683,35.3941,-77.994,,28884,barthur@fubar.com
23610,OnSite,End,00100228007,2010-11-01 17:45:52.000,2010-11-01 18:41:36.980,35.3941,-77.994,,28884,barthur@fubar.com
23611,OnSite,Begin,00100228209,2010-11-01 18:00:38.000,2010-11-01 18:42:12.763,35.3941,-77.994,,28888,barthur@fubar.com
23612,OnSite,End,00100228209,2010-11-01 18:30:44.000,2010-11-01 18:43:29.123,35.3941,-77.994,,28888,barthur@fubar.com
23613,Standby,Begin,,2010-11-01 18:30:58.000,2010-11-01 18:45:28.857,35.3941,-77.994,#1157 ergo,28888,barthur@fubar.com
23614,Standby,End,,2010-11-01 18:45:26.000,2010-11-01 18:46:01.167,35.3941,-77.994,#1157 ergo redo,28888,barthur@fubar.com
23615,Travel,Begin,,2010-11-01 18:45:24.000,2010-11-01 18:47:37.803,35.3941,-77.994,RTN,28888,barthur@fubar.com
23616,Travel,End,,2010-11-01 20:45:05.000,2010-11-01 20:34:39.433,34.139,-77.895,#1142 Fueled,28990,barthur@fubar.com
最佳答案
这是一种滥用 row_number 函数的方法。请检查评论以获取一些解释。
;with Seq as (
-- Create a master sequence of events
-- Trust the date column to be accurate (don't match a Begin to an earlier End)
select id, activitytype, beginend
, coalesce(businesskey, '') as businesskey -- Needed to match nulls as equal
, [date], technician, note
, row_number() over (partition by technician, businesskey, activitytype order by [date], beginend, id) as rownumber
from TimeEntry
)
select b.id as BeginID
, e.id as EndID
, coalesce(b.technician, e.technician) as Technician
, coalesce(b.businesskey, e.businesskey) as BusinessKey
, coalesce(b.activitytype, e.activitytype) as ActivityType
, coalesce(convert(char(10), b.[date], 103), 'Incomplete') as BeginDate
, coalesce(convert(char(10), e.[date], 103), 'Incomplete') as EndDate
, coalesce(convert(char(5), b.[date], 108), 'Incomplete') as BeginTime
, coalesce(convert(char(5), e.[date], 108), 'Incomplete') as EndTime
, b.note as BeginNote
, e.note as EndNote
from (select * from Seq where beginend = 'Begin') b -- Get all Begins
full outer join (select * from Seq where beginend = 'End') e -- Get all Ends
on b.technician = e.technician
and b.businesskey = e.businesskey
and b.activitytype = e.activitytype
and b.rownumber = e.rownumber - 1 -- Match a Begin with only the very next End of that type
order by coalesce(b.[date], e.[date])
, coalesce(b.id, e.id)
, coalesce(b.technician, e.technician)
, coalesce(b.businesskey, e.businesskey)
, coalesce(b.activitytype, e.activitytype)
关于sql - T-SQL 连接以获取配对和非配对的开始和停止记录,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/12963293/
SQL、PL-SQL 和 T-SQL 之间有什么区别? 谁能解释一下这三者之间的区别,并提供每一个的相关使用场景? 最佳答案 SQL 是一种对集合进行操作的查询语言。 它或多或少是标准化的,几乎所有关
这个问题已经有答案了: What is the difference between SQL, PL-SQL and T-SQL? (6 个回答) 已关闭 9 年前。 我对 SQL 的了解足以完成我的
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列,该列有一个默认约束为 GETDATE()。 使用 LINQ 时,我没有为 RegistrationDate 列提供任
我有一个可能属于以下类型的字符串 string expected result 15-th-rp 15 15/12-rp 12 15-12-th
很难说出这里问的是什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或言辞激烈,无法以目前的形式合理回答。如需帮助澄清此问题以便可以重新打开,visit the help center . 9年前关闭
我有一个存储过程(称为 sprocGetArticles),它从文章表中返回文章列表。这个存储过程没有任何参数。 用户可以对每篇文章发表评论,我将这些评论存储在由文章 ID 链接的评论表中。 有什么方
我目前正在做一个 *cough*Oracle*cough* 数据库主题。讲师介绍embedded SQL作为让其他语言(例如 C、C++)与(Oracle)数据库交互的方式。 我自己做了一些数据库工作
SQL Server 中 SQL 语句的最大长度是多少?这个长度是否取决于 SQL Server 的版本? 例如,在 DECLARE @SQLStatement NVARCHAR(MAX) = N'S
这个问题已经有答案了: Simple way to transpose columns and rows in SQL? (9 个回答) 已关闭 8 年前。 CallType
预先感谢您对此提供的任何帮助。 假设我有一个查询,可以比较跨年的数据,从某个任意年份开始,永无止境(进入 future ),每年同一时期直到最后一个完整的月份(其特点是一月数据永远不会显示至 2 月
我在数据库中有一个 USER 表。该表有一个 RegistrationDate 列,该列的默认约束为 GETDATE()。 使用 LINQ 时,我没有为 RegistrationDate 列提供任何数
下面是我试图用来检查存储过程是否不存在然后创建过程的 sql。它会抛出一个错误:Incorrect syntax near the keyword 'PROCEDURE' IF NOT EXISTS
我有一个同事声称动态 SQL 在许多情况下比静态 SQL 执行得更快,所以我经常看到 DSQL 到处都是。除了明显的缺点,比如在运行之前无法检测到错误并且更难阅读,这是否准确?当我问他为什么一直使用
来自 lobodava 的动态 SQL 查询是: declare @sql nvarchar(4000) = N';with cteColumnts (ORDINAL_POSITION, CO
使用 SQL Server 中的存储过程执行动态 SQL 命令的现实优点和缺点是什么 EXEC (@SQL) 对比 EXEC SP_EXECUTESQL @SQL ? 最佳答案 sp_executes
我有这个有效的 SQL 查询: select sum(dbos.Points) as Points, dboseasons.Year from dbo.StatLines dbos i
我正在调试一些构建成功运行的 SQL 命令的代码。 然而,在查询结束时,查询结果似乎被写入了一个文本文件。 完整的查询如下 echo SELECT DATE,DATETABLE,DATE,APPDAT
我有一些创建表的 .sql 文件(MS SQL 数据库): 表_1.sql: IF OBJECT_ID (N'my_schema.table1', N'U') IS NOT NULL DROP TAB
我写了下面的 SQL 存储过程,它一直给我错误@pid = SELECT MAX(... 整个过程是: Alter PROCEDURE insert_partyco @pname varchar(20
我在 SQL Server 2005 中有包含两列 Fruit 和 Color 的表,如下所示 Fruit Colour Apple Red Orange
我是一名优秀的程序员,十分优秀!