Solved

SQL Query Syntax

Posted on 2015-01-23
5
191 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

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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. …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 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