GP 2010 - SQL statement

it would be more useful if you had provided the existing queries as text, my typing isn't the best but I think this is accurate:

SELECT
        s.employid
      , s.deprtmnt
      , s.chekdate
FROM UPR30100 s
        LEFT OUTER JOIN UPR30300 n ON s.employid = n.employid
WHERE s.chekdate > '2012-12-31'

SELECT
        employed
      , payrolcd
      , jobtitle
      , uprtrxam
FROM UPR30300
WHERE payrolcd IN ('bonus', 'specia', 'monthl', 'expwit', 'excrpy')
ORDER BY
        employed

Select allOpen in new window

Perhaps this is what you are seeking, but I'm guessing quite a bot as not all requirements are mentioned, nb: I'm assuming an inner join

SELECT
        s.employid
      , s.deprtmnt
      , s.chekdate
      , n.employed
      , n.payrolcd
      , n.jobtitle
      , n.uprtrxam
FROM UPR30100 s
        INNER JOIN UPR30300 n ON s.employid = n.employid
WHERE n.payrolcd  IN ('bonus', 'specia', 'monthl', 'expwit', 'excrpy')
AND s.chekdate > '2012-12-31'
ORDER BY
        employed
;

Select allOpen in new window

alternatively, if the left join really is needed then:

SELECT
        s.employid
      , s.deprtmnt
      , s.chekdate
      , n.employed
      , n.payrolcd
      , n.jobtitle
      , n.uprtrxam
FROM UPR30100 s
        LEFT OUTER JOIN UPR30300 n ON s.employid = n.employid
         AND n.payrolcd  IN ('bonus', 'specia', 'monthl', 'expwit', 'excrpy')
WHERE s.chekdate > '2012-12-31'
ORDER BY
        employed
;

Select allOpen in new window

Hi,  I get errors on all of these:  

Msg 208, Level 16, State 1, Line 1
Invalid object name 'UPR30100'

it looks like UPR30100 in your image that was embedded in Word doc attached to your question, or, provide your original queries as text so that typos do not creep in
- after all I cannot know for sure what tables do or don't exist at your end :)

??? could it be UPR3O100 ??? that's an 'o' after the 3 not a zero

bottom line: I can't fix table (or field) references without access to your database

I will start a new question.  None of these queries worked.  I will word differerntly.  Maybe it shoudl go to a GP person??

IF you provide the original query as text this can be resolved in no time flat & with no need for new question.

It is a simple matter of getting the table reference correct.

Sorry...I have now put the information in a .txt format.  the only field I need from the UPR30100 is the employee name.  How do I join the two tables.  Thank you for helping..
GP2010-SQL-join.txt

thanks, looks a bit different than the original, but on the basis of that text I believe this will join the 2 tables

SELECT
        j.EMPLNAME
      , f.EMPLOYID
      , f.CHEKNMBR
      , f.TRXNUMBER
      , f.DEPRTMNT
      , f.PAYROLCD
      , f.JOBTITLE
      , f.uprtrxam
      , f.CHEKDATE
FROM UPR30300 AS f
        INNER JOIN UPR30100 AS j ON f.EMPLOYID = j.EMPLOYID
WHERE f.EMPLOYID LIKE 'berand%'
        AND f.CHEKDATE = '2013-01-25'
        AND j.CHEKDATE = '2013-01-25'
        AND f.PAYROLCD = 'monthl'

Select allOpen in new window

Is there any reason that f.chekdate AND j.chekdate are used?  It duplicated the results.

Other than that, the join worked..

Please see attachment... I will be including more than 1 checkdate and it is duplicating.

Oops..here is attachment with duplicate results
GP2010-duplicate-results.docx

There is always a potential for multiple rows to be produced when joining tables due to a many-to-one relationships but this isn't visible through just those few fields.

If you ran the 2 separate queries you provided, how many rows were produced by each? If either of them had more than one row then joining isn't joining to reduce the rows.

Here are 2 suggestions, the top one is less likely to solve this issue:

SELECT
        j.EMPLNAME
      , f.EMPLOYID
      , f.CHEKNMBR
      , f.TRXNUMBER
      , f.DEPRTMNT
      , f.PAYROLCD
      , f.JOBTITLE
      , f.uprtrxam
      , f.CHEKDATE
FROM UPR30300 AS f
        INNER JOIN UPR30100 AS j ON f.EMPLOYID = j.EMPLOYID
                                AND F.CHEKDATE = j.CHEKDATE
WHERE f.EMPLOYID LIKE 'berand%'
        AND f.CHEKDATE = '2013-01-25'
        AND f.PAYROLCD = 'monthl'

Select allOpen in new window

SELECT
        j.EMPLNAME
      , f.EMPLOYID
      , f.CHEKNMBR
      , f.TRXNUMBER
      , f.DEPRTMNT
      , f.PAYROLCD
      , f.JOBTITLE
      , f.uprtrxam
      , f.CHEKDATE
FROM UPR30300 AS f
        INNER JOIN ( SELECT DISTINCT EMPLOYID, EMPLNAME FROM UPR30100
                   ) AS j ON f.EMPLOYID = j.EMPLOYID
WHERE f.EMPLOYID LIKE 'berand%'
        AND f.CHEKDATE = '2013-01-25'
        AND f.PAYROLCD = 'monthl'

Select allOpen in new window

GREAT.. The 2nd one worked.  The chekdate is 2011-01-25 00:00:00.000.  What can I add to the chekdate to remove the 00:00:00.000.  I tried using LEFT but it changed the date format to January 25, 2011.   I need to keep it as 2011-01-25.

Thank you!!

MS SQL Server 2000 upwards

, convert(varchar(10), f.CHEKDATE, 121) as CHEKDATE


MS SQL Server 2012 upwards

, FORMAT( f.CHEKDATE, 'yyyy-MM-dd') as CHEKDATE

Worked GREAT!!  Just one more question.  My check amount is 346.45000.  How do I get it to read 346.45  (only 2 decimal places)