sql server - Sum only one instance -

what want query count of hoursbilled. want first check #worked if data not exist in table want pull data #workschedule.

my issue seems totalling data twice, i.e. if exists in both tables counts hoursbilled twice. works fine on test data, when roll out production data issue occurs. incorrect join, or bad query set-up? need can accurate count of hoursbilled?

essentially query trying is:

• if date exists in table #worked use hoursbilled table
• if date not exist use hoursbilled #workschedule

---

create table #workschedule (     caldate date     ,isworkday varchar(5)     ,hoursbilled int )  insert #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 #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 wk.hoursbilled else ws.hoursbilled end #workschedule ws left join #worked wk on ws.caldate = wk.d1 ws.isworkday = 'yes' 

the @begin , @end not used in query @ all. few questions might narrow down issue:

. have predicate limit time? . aggregate on caldate after join? . have duplicated entries in #workschedule , #worked same 'caldate'? e.g. whether have 'unique' constraint on 'caldate' both #workschedule , #worked table?

here example of potential reason producing double counts:

/* create database test  use test  -- drop table #workschedule create table #workschedule (     caldate date     ,isworkday varchar(5)     ,hoursbilled int )  insert #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 #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 account 7, 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 wk.hoursbilled              else ws.hoursbilled          end     ) hoursbilled #workschedule ws     left join #worked wk         on ws.caldate = wk.d1      ws.isworkday = 'yes'      , ws.caldate between @begin , @end group ws.caldate order ws.caldate