Solved

SQL Query Syntax

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql help 8 58
How do I subtract date and time within a same column in SQL 4 41
T-SQL Query to include null values 3 51
How to use three values with DATEDIFF 3 35
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

861 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