• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

SQL Query Syntax

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
triphen
Asked:
triphen
  • 2
  • 2
1 Solution
 
triphenAuthor Commented:
Also, I just realized that it is NOT returning records that are ONLY in punchpayroll. I want those as well. Thanks!!!!
0
 
Daniel WilsonCommented:
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
 
Daniel WilsonCommented:
0
 
awking00Commented:
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
 
triphenAuthor Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now