Tweek Query to only show <> 1

[sue0912]

Hi,

I have this query that works great. But now I want to tweek the query so it only shows the records that don't equal.

select a.personnum, a.firstnm,a.lastnm,a.homelaborleveldsc1,a.payperexpectedhrs,
(select SUM(b.WFCTIMEINSECONDS)/3600 as hrs
from vp_alltotals b
where b.personnum = a.personnum
and b.APPLYDATE Between b.prevpayperiodstart AND b.prevpayperiodend
and b.paycodename ='TOT-OBL') as sf_obl, VP_EMPSIGNOFFV42.SIGNOFFSUPERVISOR,
VP_EMPSIGNOFFV42.SUPERVISORFULLNAME
from vp_person a Left Outer Join vp_empsignoffv42 on a.personnum = vp_empsignoffv42.personnum
Where vp_empsignoffv42.employmentstatus = 'Active' and vp_empsignoffv42.signoffenterondtm LIKE current_date
and a.HOMELABORLEVELNAME3 NOT IN ('HRY', 'MONTH', 'TEMP')
ORDER BY VP_EMPSIGNOFFV42.SIGNOFFSUPERVISOR, a.personnum

I would like my results to show me which records from the payperexpectedhrs column do not equal the records from SF_OBL column.

Can anyone tell me what I am missing?

Thanks
Sue

 

[markros]

SQL Server 2005+ solution:

select a.personnum, a.firstnm,a.lastnm,a.homelaborleveldsc1,a.payperexpectedhrs,
    coalesce(F.sf_obl,0) as sf_obl, case when F.Sf_Obl IS NULL then 'Nothing' when a.PayPerExpectedHrs <> F.Sf_obl then 'Don''t match' else 'Match' end as Status
VP_EMPSIGNOFFV42.SIGNOFFSUPERVISOR,
  VP_EMPSIGNOFFV42.SUPERVISORFULLNAME
from vp_person a Left Outer Join vp_empsignoffv42 on a.personnum = vp_empsignoffv42.personnum
OUTER APPLY
(select SUM(b.WFCTIMEINSECONDS)/3600 as hrs
      from vp_alltotals b
       where b.personnum = a.personnum
        and b.APPLYDATE Between b.prevpayperiodstart AND b.prevpayperiodend
        and b.paycodename ='TOT-OBL') F

Where vp_empsignoffv42.employmentstatus = 'Active' and vp_empsignoffv42.signoffenterondtm LIKE current_date
and a.HOMELABORLEVELNAME3 NOT IN ('HRY', 'MONTH', 'TEMP')
ORDER BY VP_EMPSIGNOFFV42.SIGNOFFSUPERVISOR, a.personnum

PluralSight Learning Library

 

[sue0912]

Thanks Markros. I think that is what I am looking for but I am on SQL Server 2000. My apologies as I should have noted that in my original post.

Thanks Sue

 

[markros]

If that's the case, then go with the derived table solution, e.g.

select * from (my current complex query) X where ...

In addition, using subquery the way you're currently using to introduce sf_obl field may not be the best way in terms of performance. Try to convert this into the LEFT JOIN. Let me know if you'll need some help in converting.

PluralSight Learning Library

 

[sue0912]

ok...so something like the following

select a.personnum, a.firstnm,a.lastnm,a.homelaborleveldsc1,a.payperexpectedhrs, HRSTable.hrs, VP_EMPSIGNOFFV42.SIGNOFFSUPERVISOR,
VP_EMPSIGNOFFV42.SUPERVISORFULLNAME
from vp_person a Left Outer Join vp_empsignoffv42 on a.personnum = vp_empsignoffv42.personnum,
(select SUM(b.WFCTIMEINSECONDS)/3600 as hrs from vp_alltotals b where b.personnum = a.personnum and b.APPLYDATE Between b.prevpayperiodstart AND b.prevpayperiodend
and b.paycodename ='TOT-OBL') as HRSTable
Where vp_empsignoffv42.employmentstatus = 'Active' and vp_empsignoffv42.signoffenterondtm LIKE current_date
and a.HOMELABORLEVELNAME3 NOT IN ('HRY', 'MONTH', 'TEMP')
ORDER BY VP_EMPSIGNOFFV42.SIGNOFFSUPERVISOR, a.personnum

 

[markros]

Yes, close:

select a.personnum, a.firstnm,a.lastnm,a.homelaborleveldsc1,a.payperexpectedhrs, HRSTable.hrs, VP_EMPSIGNOFFV42.SIGNOFFSUPERVISOR,
  VP_EMPSIGNOFFV42.SUPERVISORFULLNAME
from vp_person a Left Outer Join vp_empsignoffv42 on a.personnum = vp_empsignoffv42.personnum
LEFT JOIN (select PersonNum, SUM(b.WFCTIMEINSECONDS)/3600 as hrs from vp_alltotals b where b.APPLYDATE Between b.prevpayperiodstart AND b.prevpayperiodend
and b.paycodename ='TOT-OBL' 
group by PersonNum) as HRSTable
ON a.personnum = HRSTable.personnum  
Where vp_empsignoffv42.employmentstatus = 'Active' and vp_empsignoffv42.signoffenterondtm LIKE current_date
and a.HOMELABORLEVELNAME3 NOT IN ('HRY', 'MONTH', 'TEMP')
ORDER BY VP_EMPSIGNOFFV42.SIGNOFFSUPERVISOR, a.personnum

PluralSight Learning Library

 

[sue0912]

that was it!! Worked perfect! Thank you so much!

Sue

 

[markros]

You're welcome Perhaps you'd like to mark the response?

PluralSight Learning Library

 

[sue0912]

You Bet!