Select with JOIN trouble - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Select with JOIN trouble

Author

Topic

nilaavu
Starting Member

18 Posts

Posted - 2006-05-05 : 17:12:02

SELECT
MCSLT.JOBOPERATION,
MCSLT.ORDERTYPE ,
MCSLT.WORKCENTER ,
MCSLT.EQUIPMENT,
MCSLT.WORKHOURS,
MCSDISTINCT.JOBDURINT,
(MCSLT.WORKHOURS - MCSDISTINCT.JOBDURINT )Remhrs,
((MCSLT.WORKHOURS) - (MCSDISTINCT.JOBDURINT)/ MCSLT.WORKHOURS)*100
as percenthrs
from MCSLT left JOIN MCSDISTINCT ON
MCSDISTINCT.JOBOPERATION =MCSLT.JOBOPERATION where
MCSLT.EQUIPMENT is not null and MCSLT.EQUIPMENT='MA-N058FE0603A'

The result looks like this without the totalcount at the end

300 MSO MAIN MA-N058FE0603A 2.0 1.0 1.0 50 % totalcount (2)
301 MSO MAIN MA-N058FE0603A 2.0 1.0 1.0 50 % totalcount(2)

302 MSI APG MA-N058FE0603A 4.0 1.0 3.0 75 % totalcount (1)
303 MSO APG MA-N058FE0603A 2.0 1.0 1.0 50 % totalcount(1)

Now I have to find how to join this totalcount field in the above query which shows the count of joboperation based on equipment , workcenter , ordertype where percenthrs <=userinput and equipmentno= 'userinput equipment'

select EQUIPMENTno, workcenter, ordertype, count (JOBOPERATION) from view2 where equipmentno='MA-N058FE0603A' and percenthrs <userinput
group by
EQUIPMENTno, workcenter,ordertype
order by EQUIPMENTno, workcenter,ordertype

thank you so much if u could help me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-05 : 21:45:22

[code]SELECT MCSLT.JOBOPERATION,
MCSLT.ORDERTYPE ,
MCSLT.WORKCENTER ,
MCSLT.EQUIPMENT,
MCSLT.WORKHOURS,
MCSDISTINCT.JOBDURINT,
(MCSLT.WORKHOURS - MCSDISTINCT.JOBDURINT )Remhrs,
((MCSLT.WORKHOURS) - (MCSDISTINCT.JOBDURINT)/ MCSLT.WORKHOURS)*100 as percenthrs,
(select count(*) from view2 x
where x.equipmentno = MCSLT.EQUIPMENT
and x.workcenter = MCSLT.WORKCENTER
and x.ordertype = MCSLT.ORDERTYPE)
from MCSLT left JOIN MCSDISTINCT
ON MCSDISTINCT.JOBOPERATION =MCSLT.JOBOPERATION
where MCSLT.EQUIPMENT is not null
and MCSLT.EQUIPMENT = 'MA-N058FE0603A' [/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources