我想对查询做的是获取hoursbilled
的计数。我想先检查#worked
但如果该表中不存在数据,我想从#workschedule
中提取数据。
问题是它似乎将我的数据总计两次,即如果它存在于两个表中,则hoursbilled
计数两次。这适用于我的测试数据,但是当我将其推广到生产数据时,会出现此问题。这是不正确的联接,还是错误的查询设置?我需要做什么才能获得准确的hoursbilled
计数?
本质上,我的查询试图做的是:
- 如果表中存在日期
#worked
请使用该表中的hoursbilled
- 如果日期不存在,请使用
hoursbilled
#workschedule
Create Table #workschedule
(
caldate date
,isworkday varchar(5)
,hoursbilled int
)
Insert Into #workschedule Values
('01/01/2000', 'yes','3'), ('01/02/2000', 'yes','3'), ('01/03/2000', 'yes','1'),
('01/04/2000', 'no','0'), ('01/05/2000', 'yes','12'), ('01/06/2000', 'no','0')
Create Table #worked
(
d1 date
,hoursbilled int
)
Insert Into #worked Values
('01/01/2000','2'), ('01/02/2000','4')
Declare @begin date, @end date
Set @begin = '01/01/2000'
Set @end = '01/08/2000'
Select
ws.caldate,
case when wk.d1 = ws.caldate then wk.hoursbilled else ws.hoursbilled end
FROM #workschedule ws
Left Join #worked wk
ON ws.caldate = wk.d1
where ws.isworkday = 'Yes'
中根本不使用@begin和@end。有几个问题可能有助于缩小问题范围:
.你有另一个谓词来限制时间吗?.联接后是否在caldate
上聚合?.您是否在具有相同"caldate"的 #workschedule 和 #worked 中重复条目?例如,您是否对 #workschedule 表和 #worked 表的"计算"都有"唯一"约束?
下面是产生重复计数的潜在原因的示例:
/*
create database test
use test
-- drop table #workschedule
Create Table #workschedule
(
caldate date
,isworkday varchar(5)
,hoursbilled int
)
Insert Into #workschedule Values
('01/01/2000', 'yes','3'), ('01/02/2000', 'yes','3'), ('01/03/2000', 'yes','1'),
('01/04/2000', 'no','0'), ('01/05/2000', 'yes','12'), ('01/06/2000', 'no','0'),
('01/01/2000', 'yes', '0') -- dup entry
-- drop table #worked
Create Table #worked
(
d1 date
,hoursbilled int
)
Insert Into #worked Values
('01/01/2000','2'), ('01/02/2000','4'),
('01/01/2000', '5') -- dup entry
*/
Declare @begin date, @end date
Set @begin = '01/01/2000'
Set @end = '01/08/2000'
-- Here 2000/01/01 counted duplicated, should only account for 7, but got 14.
--2000-01-01 14
--2000-01-02 4
--2000-01-03 1
--2000-01-05 12
Select
ws.caldate,
sum(
case
when wk.d1 = ws.caldate then wk.hoursbilled
else ws.hoursbilled
end
) hoursBilled
FROM #workschedule ws
Left Join #worked wk
ON ws.caldate = wk.d1
where
ws.isworkday = 'Yes'
and ws.caldate between @begin and @end
group by ws.caldate
order by ws.caldate