Select Statement Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Statement Help

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-06-13 : 10:21:43

I'm selecting a record from one table(mctrxhst) which is based on a value in a 2nd table(mccrwhst)

One record in the mxtrxhst can have multiple records in the mccrwhst.

Example:


MCTRXHST

JOB_NO    CONTROL_NO   LBR_HRS
1234         33           8

MCCRWHST

JOB_NO    CONTROL_NO   EMP_NO   SHIFT
1234         33          2        1
1234         33          3        1

I only want labor hours for shift 1.

When I used the following code it doubles my labor hours because I have two records in the mccrwhst. How can I prevent this from happening?


select mctrxhst_sql.dept,
 SUM(case when MONTH(mctrxhst_sql.trx_dt)=month(getdate()) 
      and year(mctrxhst_sql.trx_dt)=YEAR(getdate()) 
      and DAY(mctrxhst_sql.trx_dt)= DAY(getdate())  then mctrxhst_sql.lbr_hrs END) AS Yesterday
  from mctrxhst_sql left outer join mccrwhst_sql on mctrxhst_sql.job_no = mccrwhst_sql.job_no and
  mctrxhst_sql.control_no = mccrwhst_sql.control_no

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-13 : 10:37:18

select mctrxhst_sql.dept,
SUM(case when MONTH(mctrxhst_sql.trx_dt)=month(getdate())
and year(mctrxhst_sql.trx_dt)=YEAR(getdate())
and DAY(mctrxhst_sql.trx_dt)= DAY(getdate()) then mctrxhst_sql.lbr_hrs END) AS Yesterday
from mctrxhst_sql
left outer join (select distinct JOB_NO, CONTROL_NO from mccrwhst_sql) mccrwhst_sql
on mctrxhst_sql.job_no = mccrwhst_sql.job_no
and mctrxhst_sql.control_no = mccrwhst_sql.control_no

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-06-13 : 10:45:54

That did not work.

Vack
Aged Yak Warrior

530 Posts

Posted - 2011-06-13 : 10:47:58

Actually I missed the the last line.

and mctrxhst_sql.control_no = mccrwhst_sql.control_no

It worked fine after I got that in.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources