Solved

SQL Query Syntax

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to extract a number from a MS SQL server string. 39 71
How to convert sql code to access query 8 67
Supress rows in SSRS table based on Like or Soundex 2 40
SQL Union 20 44
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.…
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.
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

895 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now