|
我正在尝试重构ASP.Net网站中的某些代码,并且正在编写的存储过程有问题。
% M9 M' z. ?/ ]" O2 T1 U我想做的是获取一个日期范围,然后从表格中选择该范围内的所有数据,但如果不存在日期,我仍然需要选择一行。8 [. A2 @9 J0 c6 k- g
正如您在下面的代码中看到的那样,我的想法是创建一个临时表,并用日期范围内的所有日期填充该表,然后将其加入到我要选择的表中,但这是行不通的。我在这里做错什么了吗?tempDate列在此联接中始终为null,但是我已经检查了表,并且表中确实有数据。; L& }2 _+ w6 L0 G2 O. ]
-- Parameters2 ]* h* P: g! Z5 p) {( l* A
DECLARE @DutyDate datetime='2012-01-01 00:00:00'
N9 M, l2 v, E) D4 H- h) UDECLARE @InstructorID nvarchar(2) = N'29'( w/ U. j3 | V4 J$ e/ b
DECLARE @datesTBL TABLE (tempDate DATETIME)
9 k8 K( {/ D, H" w5 k' `3 t-- Variables
- }& S# U# R& ?/ c8 dDECLARE @StartDate DATETIME ( r3 b" }9 D# `8 {6 ]% V- Z6 f5 g
DECLARE @EndDate DATETIME; S- W4 o) _% P# H& W+ X) b
SELECT # U3 T' h' ~" d2 c! N. T7 s
@StartDate =StartDate, 8 F9 `- k! e/ _) O$ P
@EndDate = EndDate. g8 N) f& u9 `9 e, p5 v
FROM % T/ L- M' s5 ^( y# y. U
DutyPeriodTbl
7 L* q4 z- h8 F# e+ u5 wWHERE & a2 D" w. B/ v7 O1 O ~' L5 ]0 D( g
(StartDate = @DutyDate)
) p9 G# Z; m+ D+ p/ K7 J# g0 E: N# x/ t/ j! V# I, e. @
DECLARE @d DATETIME = @StartDate
, z @2 H! p2 u& e6 KWHILE @d<=@EndDate
, J) r/ a! D5 {7 G( M% l5 y6 gBEGIN0 r6 a9 e! L3 c: v m
INSERT INTO @datesTBL VALUES (CONVERT(DATETIME, @d, 102))
& `2 H' w# w }; n- u9 h SET @d=DATEADD(day,1,@d)
) t- I" W, r2 n( kEND& i/ |# h1 J5 f9 b) K' }* n: t
SELECT : E/ t- s$ \$ z% }2 X
dt.tempDate ," t3 w& [1 V0 z6 x3 v" U+ g' i
InstructorID, EventStart,
+ y. K F0 z4 M9 ] EventEnd, cancelled, + v$ N. W- q8 g* |$ k( N0 ?, `
cancelledInstructor,
( `# `1 b; d" j. R" ]9 m' k& C& j EventType, DevName,
: ^0 T4 H7 L7 _3 P* } Room, SimLocation,
9 x) ~4 v. g$ z1 r2 Z ClassLocation, Event, 7 {2 N |3 f: H& _" Q2 U
Duration, TrainingDesc, & P" m: N' I. q' {$ ^* R4 |
Crew, Notes, 5 u9 L( R1 u' o3 x4 H: }
LastAmended, InstLastAmended,
0 ?( H% M, S- s+ @% L7 J ChangeAcknowledged, Type, 3 E) A. d4 i: d, b3 q; h( } x
OtherType, OtherTypeDesc, ; h2 k; F" o$ G
CourseType / f) J6 b: s/ z, |! s+ p
FROM 8 d$ j( D. s( i- ^
OpsInstructorEventsView iv, T2 T$ _: p9 j3 E
LEFT OUTER JOIN
% H/ O5 c7 A4 D t @datesTBL dt
8 q; u* Q1 I$ k: EON
- {# A$ n9 z3 g CONVERT(DATETIME, iv.EventStart, 102) = CONVERT(DATETIME, dt.tempDate, 102)
4 O% ]! Z. S/ s+ oWHERE 9 b5 L5 W- P2 n% y
InstructorID = @InstructorID " J- }: {2 c0 D( W: `
AND
. s! c+ H7 _# C& ?$ U% X. \1 Q* p EventStart BETWEEN CONVERT(DATETIME, @StartDate, 102) AND CONVERT(DATETIME, @EndDate, 102)
7 h7 I1 q# H2 M" t. A3 DORDER BY
# K* t1 _/ C$ I. _: R& w& B1 n EventStart8 B* ]5 p! v- @: m L0 P/ Y; D
4 }' k' n, i0 S- m; S G( V
解决方案: |
|