|
|
我有一个设计不良的表，我没有设计该表，无法修复/更改0 K9 I5 \' G+ d
该表，因为有写入该表的第三方黑莓应用程序。最重要的是，有
1 V/ R: i! h: I" T+ \8 J4 O一个开始记录和一个停止记录，用于跟踪没有连接的事件或5 _$ F* a3 O2 c( }2 r
验证是否存在匹配项的事件。黑莓应用程序不执行任何操作将这些
+ M% j$ `. a: V. m9 Z记录绑定在一起。我试着在其自身上创建一个连接，并
- q9 e. B+ {, Q: n" z, c2 z以开始和结束以完全外部联接来创建临时表。该7 Q" B8 Q4 Y, D! a* L9 l
问题是，我有重复条目被该条目将被标记为
% k# r- d. r6 y' r# U没有伴侣。现有数据的行在开始和结束
# l" E$ G8 S) l: H& D6 Y( O& G记录上都没有配对。我已经搜索了答案，发现了一些很接近的答案，
2 J; ^; U) w/ y9 p- a& p) g* r7 V9 s已经带我走了这么远。我知道这是一篇很长的帖子，对此感到抱歉。
+ J3 e( l. @' }+ w有一个单一的令人惊讶地具有主键表。没有1 c. T+ F w6 y5 z) H
枢轴/相交表。结构是
( o5 t! \& m1 H3 M* `2 ]9 UID (int PK)% E/ j! o% C, z. n( I
activityType varchar
, K$ `8 }# e5 H7 [: ObeginEnd varchar ('begin' or 'end')* g, Q O" V+ `4 S; ^6 Q5 k
businessKey varchar nullable! v& `0 w) A% ~: Z' `- n
date DATETIME1 B. M0 ]* d. y7 r( o6 c1 S# h
technician varchar' N- P7 j- W. [9 W z
表中也包含以下几列，但它们可以为空，并且对查询不重要。$ w) O O, L; h1 n2 u
dateSubmitted DATETIME& v1 }; V# r+ A( p7 Z/ `
gpsLatitude float% \; B' _7 |8 S7 X4 J
gpsLongitude float
( q/ @( _6 T# O8 b) x+ o unote varchar& h6 \& a( }4 t1 A5 i0 D- d
odometer int
8 A6 M9 I& ]& R
, o6 J& y4 ?. q2 R( vThe query that I have now that still leaves dupes: Showing and sorting ID and
9 r& m+ u6 L. }' E% oEndID are for debugging only
V# N- ?, w7 N L" JDECLARE @DateFrom DATETIME
+ |8 Q, J& F A3 K/ g4 k& KDECLARE @DateTo DATETIME
/ n/ y" k# R( YSET @DateFrom='20101101'
' _1 K4 P7 Z1 q# hSET @DateTo='20101102'2 _0 z( f6 i' t' L: A) h4 G
DECLARE @Incomplete VARCHAR(15)6 P1 S3 Z* o% k
SET @Incomplete = 'Incomplete'8 P! ?: M$ p; A3 W( C ?! P
DECLARE @StartEvents TABLE
! {1 P/ Q$ o, S$ f) D# q% c(
5 B6 P7 u. Y8 K[id] [numeric](19, 0) NOT NULL,
. \% s7 b& E- y3 O' N[activityType] [varchar](255) NOT NULL,% M1 T Y0 M7 I7 b4 S8 R
[beginEnd] [varchar](255) NULL,
2 i; o, N& S. N' x[businessKey] [varchar](255) NULL,
5 u* h V9 Q. A* M[date] [datetime] NOT NULL,
3 S: D& u# C3 v[dateSubmitted] [datetime] NULL,
! S* P: P6 z; j& E: S[gpsLatitude] [float] NULL, w/ y9 P) M' m7 _ [7 m
[gpsLongitude] [float] NULL,
: J. \5 ?) F6 n, ^; V% Z[note] [varchar](255) NULL,
+ H* Y# o0 j- T, H9 n. s9 k7 c[odometer] [int] NULL,
# }9 B9 I6 o! |& a[technician] [varchar](255) NOT NULL
+ u0 }& E& ^5 B1 w)4 J, ]' s; ]1 g: o: j$ I
INSERT @StartEvents
( f% X+ r6 r8 g; k4 r& A8 A( o( _ ([ID],[activityType],[beginEnd],[businessKey],[date],[dateSubmitted],[gpsLatitude]
3 X4 J9 U: r" g3 t! m ,[gpsLongitude],[note],[odometer],[technician])
. `: d( [6 ` ySELECT *3 u* M0 B6 J7 O2 k, T: q
FROM dbo.TimeEntry7 a' |& H. q c" l3 I3 [
WHERE
2 L6 H7 l# O/ }! V/ S[date] between @DateFrom AND @DateTo
6 l' E! b2 d1 k& i" ]( K( UAND beginEnd = 'Begin'; q/ \+ d- c) ~. y" A5 ^0 U+ c
--AND [technician] = 'FRED'
5 o" E8 V8 ]7 I+ B; pORDER by technician
7 n4 x9 b& |/ }: }! y& D8 t------------------------------------------------------------4 ]9 v2 X. d4 n
DECLARE @EndEvents TABLE
: O: _# D$ Y3 m' E(0 V9 d) N; o2 b9 c3 |6 o- q' U
[id] [numeric](19, 0) NOT NULL,
4 n' n* W: `. L- E. u1 s8 `7 L[activityType] [varchar](255) NOT NULL,
: S0 {7 o% N- p1 h& k% j[beginEnd] [varchar](255) NULL,# b& ~6 \- e3 b
[businessKey] [varchar](255) NULL,/ e$ ~- z5 m* I& |8 w
[date] [datetime] NOT NULL,2 U% o! c# F# @2 @" ?) V
[dateSubmitted] [datetime] NULL,& b# }) `6 R# \5 C9 k
[gpsLatitude] [float] NULL,# y7 l- o) e" o& d
[gpsLongitude] [float] NULL,
" h7 q" D) _# t& ?; g- E[note] [varchar](255) NULL,: o3 n, G- R( [8 {6 D) I
[odometer] [int] NULL,
7 {% W( R! f, L: J$ {[technician] [varchar](255) NOT NULL
}( R# _) S& J)
+ E6 Z) E2 z9 i& pINSERT @EndEvents D# z3 ]/ B* _" v4 N
([ID],[activityType],[beginEnd],[businessKey],[date],[dateSubmitted],[gpsLatitude]- I9 |* g% G6 q/ r6 T% F
,[gpsLongitude],[note],[odometer],[technician])( F" I( ~5 b( `! ^6 \ J
SELECT *5 I5 e$ ~; H9 }8 u) w" t
FROM dbo.TimeEntry
0 a( ?# R8 U+ Y9 H7 t$ [WHERE ) f; n& F" P' E: `
[date] between @DateFrom AND @DateTo AND4 P8 p9 T0 P. T4 a' \
beginEnd = 'End'
, {9 H% l3 M& I$ F1 g4 ^--AND [technician] = 'FRED'1 R/ U! h& x$ a. ?3 d
ORDER by technician
+ C9 C) J5 V. w5 a; g5 R-- And then a conventional SELECT
0 L4 F; P( S4 D8 X# ?SELECT & z+ J" W+ v# x2 y$ h. W' i
StartEvents.id
5 v3 @- L* p" U, Q/ F,EndEvents.id AS EndID: V+ L2 O2 Y8 c$ K; Q p7 i4 o$ y) w4 [
,COALESCE(2 ?$ G! w9 M# j& o6 I& j. ?! a
StartEvents.activityType ,EndEvents.activityType ,'Not Available'5 \$ T' `7 ~; q
) AS ActivityType
b3 J/ _- F1 H5 ^& [--,StartEvents.beginEnd as [Begin] 5 v- l, g3 x6 v5 ~2 u# [% q
--,EndEvents.beginEnd AS [End]
) H& P, N: V5 [& ~- l8 E3 S% J2 Q,COALESCE (
' J3 x0 j; O$ ? convert(VARCHAR(12), StartEvents.[date], 103),
4 O5 E$ S, P. u1 m$ N h convert(VARCHAR(12), EndEvents.[date], 103), @Incomplete2 l. E$ P h! Q; V$ S% Q6 `
) as [Event Date] . \6 Z- T0 V/ @/ F! a1 Q8 Q# b
,COALESCE (
( L0 d2 b$ K3 [& _8 e convert(VARCHAR(12), EndEvents.[date], 103), @Incomplete
3 V. D' i- d3 d7 ]8 m8 y5 `* C7 O! w ) as [End Date] ' ]$ J/ a& X' e; w
,COALESCE(
6 x1 u- x& b4 ? CONVERT(VARCHAR(5) , StartEvents.dateSubmitted , 108) , @Incomplete
( ~- J5 D% W$ M8 d& d# d ) AS StartTime5 b- K% J: A+ E* c7 R j
,COALESCE(- { i/ \3 t, E Z
CONVERT(VARCHAR(5) , EndEvents.dateSubmitted , 108) , @Incomplete
: r3 ~$ s9 s1 n7 ~0 i ) AS EndTime
9 @3 w5 T, a$ I+ n7 Z/ U,COALESCE(
/ f0 P' S* i4 y( }2 ~ StartEvents.note, EndEvents.note, ''$ l; b* V4 \8 i& _. |0 u, N
) as [Note]
3 Q3 V( j8 k3 L" Z,COALESCE(7 i6 z/ s" _0 p2 Q1 ^
StartEvents.technician,EndEvents.technician,'Not Available'1 W. u C% r9 p, @0 ~2 s1 {
) AS Technician+ I" Q8 i- H Q; a1 w- o6 W
FROM 3 b# o* h- ]# J3 q& \
@StartEvents As StartEvents+ i2 t `, b! G1 |" P' M) l& y
FULL OUTER JOIN
( {% J2 m( m1 }. r0 r4 M$ Q J@EndEvents AS EndEvents ON
0 h1 l0 `/ c' V0 ]StartEvents.technician = EndEvents.technician AND
4 H8 J8 r! q! B0 X9 _( }* |StartEvents.businessKey = EndEvents.businessKey AND & l3 m3 K# w' {2 M2 H: Z
StartEvents.activityType = EndEvents.activityType
8 B5 u4 G S7 _5 n( t1 o ^AND convert(VARCHAR(12), StartEvents.[date], 103) = convert(VARCHAR(12), EndEvents.[date], 103)
! U/ }0 a( h/ u2 m-- WHERE 0 [- f6 g, f: j7 j4 v
--StartEvents.[date] between @DateFrom AND @DateTo OR
9 Y7 ^& ?4 ^: z6 d1 ^+ e+ b0 Z --StartEvents.[dateSubmitted] between @DateFrom AND @DateTo + b: z% z+ u. Y, H) N
ORDER BY
" U& j6 b2 g0 y! g7 N, J StartEvents.Technician,3 v0 n0 i/ d$ N* w5 F3 D, `
ID,ENDID
& h% e3 w" l9 g3 j. r% O2 t7 L. e$ K
DATA:
* X$ X2 M* ?' ]& k( \. w& B$ uid,activityType,beginEnd,businessKey,date,dateSubmitted,gpsLatitude,gpsLongitude,note,odometer,technician
9 g1 t' w1 i: s" U! X) G& [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. l/ N, Y2 a$ [+ F; Z" S$ I0 q( {4 H
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
' b* ?# r M' ^% i' w23571,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
, B1 T3 w* g* y7 P23572,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& g+ T/ @1 c" k7 p* Z/ B2 A
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.com3 F' T* @) S+ t. v7 W" z; L, J
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
/ _( A- f9 C0 i( ~ a: j& I- o23575,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# W3 m1 S6 M' J6 M T/ X* s& S6 E
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
4 d! A3 J8 q9 a8 f( q9 I23577,Travel,End,00100228137,2010-11-01 09:45:51.000,2010-11-01 09:42:39.230,34.0918,-77.9002,,28771,barthur@fubar.com3 ]0 a3 C' T* u p6 c( @8 I2 w
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# t. S" ^' \' K0 l) |/ O
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
9 }6 v9 r5 G1 h23580,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
& I! u$ K# e6 _9 q2 ^8 ]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
2 p3 M- ^; C# y) ~) }; I3 n, D23582,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.com6 t8 n3 h ]9 w3 M+ T
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
0 b! D4 h1 W7 Y% a! h, f9 Y+ M( J" A; W n23584,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
4 W' {+ k# @! j) ^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.com3 y+ U4 G' O. Q9 e" \7 K0 e2 {& F
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 P7 G1 u# V1 i: _& ?( l# M
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
, y1 s0 H5 c$ U23588,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
$ z6 j! x9 w! o! j' J v, ^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
1 u6 D1 J" b0 A0 \5 D23590,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
6 ?/ m/ T+ v/ a. }( O8 Q! W23591,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
) l2 o2 V+ D+ t# G/ v! v9 x6 e* O23592,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+ L/ _3 l, ?4 Z5 y1 D4 ]
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' r! _+ d. F% Q+ X
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
; l1 V) n/ Z# [5 z" H# t! u6 I23595,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
1 P) Q c+ I, q a: u8 y- |$ t, O. Q23596,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
8 a3 J/ [/ S; r) G23597,Travel,Begin,00100227980,2010-11-01 13:15:14.000,2010-11-01 13:59:15.927,34.2743,-77.8668,,28774,barthur@fubar.com1 F+ @" m- A$ I$ z( v6 h
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
! t) Z, G$ a/ d/ e! ?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
# \, b: G% A7 H. d6 I% |# U23600,Travel,End,00100227980,2010-11-01 15:15:58.000,2010-11-01 15:15:40.403,35.3414,-78.0325,,28880,barthur@fubar.com3 n% @# d; y+ m) N
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
+ g* A2 i- V6 j0 o+ V% g1 [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% z$ r2 [# R* U- d
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' K7 T6 ` \4 V) n# w) l6 c: @
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
1 x( D& Z, g: Y# b23605,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$ x2 X. z7 [+ e# p
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# B" ~' r' G: ]8 t# M# `
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
" j; g- T5 S$ N" B& e23608,OnSite,Begin,00100228007,2010-11-01 16:30:48.000,2010-11-01 18:38:32.700,35.3941,-77.994,,28880,barthur@fubar.com7 C9 x. b; g D6 I: j, |2 i. }
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.com8 L3 @5 c- `4 |% n
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
0 @# N% U& P5 b/ c23611,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! H5 Z9 K, B2 D: {9 y7 ^3 j* E2 b; ^
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- Z! {' N" v7 w% `/ A' |5 Q7 j( l
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- H# x8 o+ H1 P% i# B
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) p, U0 ?' R9 q' d$ k& J2 q' E' \9 a
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.com7 k t6 H7 U; M( f
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.com4 [1 k& J" g1 d( h$ I: |: ]
在此图像中，您看到突出显示的行显示了6个结束时间，
( n1 v2 c- t6 W并且开始时间相同。记录14和15显示2开始且没有结束。
% d C! ~ M$ w) S6 p! K
4 R6 k+ ^) k5 |! Z5 H/ V解决方案:# z3 [) {! B$ l" y/ | n
' N0 j5 h0 E4 X! y" b
. ]* I e* Q3 B# e' Y" f: [" R) f( v
' D8 ?: ^0 g( J 这是滥用row_number函数的方法。请检查注释以获取一些解释。
* z+ R, A I% I- b4 Z0 D3 G t;with Seq as ( L2 ~' a# r9 L" k
-- Create a master sequence of events' N, }! E$ |0 Q# C6 E1 X% m
-- Trust the date column to be accurate (don't match a Begin to an earlier End)/ U4 G' P% p' V: a! m. `, s. Z
select id, activitytype, beginend) [( v: ?) {$ b Y0 B" @
, coalesce(businesskey, '') as businesskey -- Needed to match nulls as equal
5 J7 X3 e! V) d& @- L8 n , [date], technician, note
! P2 h) @. U! c" w) Y5 w+ U+ j1 k , row_number() over (partition by technician, businesskey, activitytype order by [date], beginend, id) as rownumber
0 i& w1 C' u/ E' _ from TimeEntry2 A6 X* Y0 c* w$ b. Z& @
)
# ?& G! g! Y1 Pselect b.id as BeginID
$ e+ D: }5 j4 K/ b; R% K5 t , e.id as EndID5 b |) v8 u% B/ R/ O
, coalesce(b.technician, e.technician) as Technician' W w% f8 D9 m4 g
, coalesce(b.businesskey, e.businesskey) as BusinessKey
2 V+ j0 q: g" Z; l4 Z , coalesce(b.activitytype, e.activitytype) as ActivityType+ L$ }# l5 E! C6 e: j- t
, coalesce(convert(char(10), b.[date], 103), 'Incomplete') as BeginDate G" e% |9 D2 B6 E2 P0 M/ {+ {$ N
, coalesce(convert(char(10), e.[date], 103), 'Incomplete') as EndDate$ X G3 z. j |; Q4 z
, coalesce(convert(char(5), b.[date], 108), 'Incomplete') as BeginTime
6 K! R9 {/ l3 q/ l. E) J( i , coalesce(convert(char(5), e.[date], 108), 'Incomplete') as EndTime+ T& ~2 F& l# p9 i" H( L
, b.note as BeginNote
1 L& c+ ]7 N n& r; Z* K$ c , e.note as EndNote
0 ]( K! V; L4 vfrom (select * from Seq where beginend = 'Begin') b -- Get all Begins
9 _, V$ M: ~! A W3 |- _ full outer join (select * from Seq where beginend = 'End') e -- Get all Ends
7 M3 I* g) d- v+ X on b.technician = e.technician) X# @1 Q1 B$ c3 ~9 ], h
and b.businesskey = e.businesskey
: j0 W7 n1 B9 x- o and b.activitytype = e.activitytype
E- H% \, n6 j' c and b.rownumber = e.rownumber - 1 -- Match a Begin with only the very next End of that type* U6 {8 R! a* i. K+ B: i: \
order by coalesce(b.[date], e.[date])
: k1 n, W+ H: I% D2 U7 a2 j , coalesce(b.id, e.id)
: d6 r) y7 T& W" G% ~ , coalesce(b.technician, e.technician)
* U: ]8 Y' v, n" H# o1 g , coalesce(b.businesskey, e.businesskey)5 x$ r! O I: n, E& X! e
, coalesce(b.activitytype, e.activitytype)
" _0 L5 Q) z; s5 ^8 n' GAnd the SQL Fiddle should anyone want the; z( M1 _/ N" i+ `" t4 w5 W
DDL or demo. |
|
技术问答
319 人阅读
|
0 人回复
|
2023-09-13
