GP 2010 - SQL statement

I am trying to produce a report for a customer and not sure how to join the tables to pull the fields I need.  Please see attachment.  Thank you
GP2010-Payroll.docx
pstreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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

Open 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
;

Open 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
;

Open in new window

0
pstreAuthor Commented:
Hi,  I get errors on all of these:  

Msg 208, Level 16, State 1, Line 1
Invalid object name 'UPR30100'
0
PortletPaulfreelancerCommented:
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
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

pstreAuthor Commented:
I will start a new question.  None of these queries worked.  I will word differerntly.  Maybe it shoudl go to a GP person??
0
PortletPaulfreelancerCommented:
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.
0
pstreAuthor Commented:
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
0
PortletPaulfreelancerCommented:
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'

Open in new window

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

Other than that, the join worked..
0
pstreAuthor Commented:
Please see attachment... I will be including more than 1 checkdate and it is duplicating.
0
pstreAuthor Commented:
Oops..here is attachment with duplicate results
GP2010-duplicate-results.docx
0
PortletPaulfreelancerCommented:
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'

Open 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'

Open in new window

0
pstreAuthor Commented:
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!!
0
PortletPaulfreelancerCommented:
MS SQL Server 2000 upwards

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

there is a range of available date 'styles' (e.g. 121 as used above) see:
SQL Server Date Styles (formats) using CONVERT()

MS SQL Server 2012 upwards

, FORMAT( f.CHEKDATE, 'yyyy-MM-dd') as CHEKDATE
0
pstreAuthor Commented:
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)
0
PortletPaulfreelancerCommented:
this is a value-for-money question which commenced with a request for joins :)

any of the following can be used, note they "round up", the choice of 19 in (19,2) is arbitrary that bit is up to you.

declare @n as decimal(19,5)
set @n = 346.45500

select
      @n
    , cast(@n as decimal(19,2))
    , convert(decimal(19,2),@n)
    , cast(@n as numeric(19,2))
    , convert(numeric(19,2),@n)
;

You should really open new questions if you need more I think.

Just as a tip, what tends to happen is that I am probably the only one monitoring this question now, so you may not get the fastest possible response if I'm not online - whereas a new question will attract new contributors.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.