Solved

SQL Query Syntax

Posted on 2015-01-23
5
204 Views
Last Modified: 2015-01-26
Hi Experts,

I am using SyBase SQL Anywhere v10. Here is my query:

select punchpayroll.opendate, empnum, payrate, jobtype, reghours, othours, punchin, punchout, length, overtimepay 
from dba.punchpayroll, dba.overtime, dba.overtimehourrule 
where punchpayroll.punchindex = overtime.punchindex and overtime.ruleid = overtimehourrule.ruleid and punchpayroll.opendate between 20150101 and 20150115

Open in new window


And it generates the attached RESULTS. If you see there in the highlighted red box I am trying to get those 2 results on the same line BUT have the Overtime 150 and 200 separated out. See DESIRED RESULTS.

Help?
RESULTS.png
DESIRED-RESULTS.png
0
Comment
Question by:triphen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 

Author Comment

by:triphen
ID: 40567372
Also, I just realized that it is NOT returning records that are ONLY in punchpayroll. I want those as well. Thanks!!!!
0
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 40567850
OK, you're looking for an outer join per your comment.  I'm no Sybase expert, but it claims to support T-SQL syntax on that, so I think I can write you a query.
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc38151.1530/doc/html/san1278453566775.html

Additionally, you want some kind of pivot going on.  Again, I'm no Sybase expert, but I think I can write a basic query that should get you what you need.

select punchpayroll.opendate, empnum, payrate, jobtype, reghours, othours, punchin, punchout, length, overtimepay 
from dba.punchpayroll LEFT OUTER JOIN dba.overtime ON punchpayroll.punchindex = overtime.punchindex 
LEFT OUTER JOIN dba.overtimehourrule ON overtime.ruleid = overtimehourrule.ruleid
where  punchpayroll.opendate between 20150101 and 20150115

Open in new window


That should solve the lack of records.  Taking it a step further to work on the pivot ...
select punchpayroll.opendate, empnum, payrate, jobtype, reghours, othours, punchin, punchout, 
Sum(Case When overtimepay =150 then length else 0 end) as LengthOT150, 
Sum(Case When overtimepay =200 then length else 0 end) as LengthOT200
from dba.punchpayroll LEFT OUTER JOIN dba.overtime ON punchpayroll.punchindex = overtime.punchindex 
LEFT OUTER JOIN dba.overtimehourrule ON overtime.ruleid = overtimehourrule.ruleid
where  punchpayroll.opendate between 20150101 and 20150115
GROUP BY punchpayroll.opendate, empnum, payrate, jobtype, reghours, othours, punchin, punchout

Open in new window

0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 40567851
0
 
LVL 32

Expert Comment

by:awking00
ID: 40571241
Are you only looking for records where everything is the same but the length and overtime? if so you want them combined on one row?
0
 

Author Closing Comment

by:triphen
ID: 40571498
Thanks!
0

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question