triphen
asked on
SQL Query Help!
Hello Experts,
I have a query that I need some help with. I narrowed down the problem to being with the HowPaid table. It seems all of my "Sums" are being summed erroneously because of multiple entries from the HowPaid table. If I remove the full outter join and the CC Tips column, everything is good.
Results = Pic1
Results = Pic2
SyBase SQL Anywhere v10
I have a query that I need some help with. I narrowed down the problem to being with the HowPaid table. It seems all of my "Sums" are being summed erroneously because of multiple entries from the HowPaid table. If I remove the full outter join and the CC Tips column, everything is good.
Results = Pic1
select
cast(refcode as INT) as 'Payroll ID',
EMPNAME as 'EmpFirstName',
Sum(reghours) as 'Reg Hours',
JobPos.Descript as 'Position',
punchpayroll.payrate as 'Pay Rate',
Sum(IsNull(Overtime,0)) as 'Overtime',
Sum(punchpayroll.tip) as 'Tip',
Sum(IsNull(DoubleD,0)) as 'Double Time',
sum(howpaid.change) as 'CC Tip'
from dba.punchpayroll
LEFT OUTER JOIN
(select overtime.punchindex,sum(Case When overtimepay =150 then length else 0 end) as Overtime,
sum(Case When overtimepay =200 then length else 0 end) as DoubleD
from dba.overtime
LEFT OUTER JOIN dba.overtimehourrule
ON overtime.ruleid = overtimehourrule.ruleid
group by overtime.punchindex
) ov
ON punchpayroll.punchindex =ov.punchindex
INNER JOIN dba.employee ON employee.empnum = punchpayroll.empnum and employee.isactive = 1
inner join dba.JobPos on punchpayroll.jobtype= JobPos.JobPos
full outer join dba.howpaid on punchpayroll.punchindex = howpaid.punchindex and methodnum in (SELECT METHODNUM FROM DBA.METHODPAY WHERE AUTHREQR = 2)
where punchpayroll.opendate between 20150601 and 20150615
Group by refcode,EMPNAME,EMPLASTNAME, jobpos.descript, punchpayroll.payrate
ORDER BY emplastname, EMPNAME,cast(refcode as INT)Results = Pic2
select
cast(refcode as INT) as 'Payroll ID',
EMPNAME as 'EmpFirstName',
Sum(reghours) as 'Reg Hours',
JobPos.Descript as 'Position',
Sum(IsNull(Overtime,0)) as 'Overtime',
Sum(punchpayroll.tip) as 'Tip',
Sum(IsNull(DoubleD,0)) as 'Double Time'
from dba.punchpayroll
LEFT OUTER JOIN
(select overtime.punchindex,sum(Case When overtimepay =150 then length else 0 end) as Overtime,
sum(Case When overtimepay =200 then length else 0 end) as DoubleD
from dba.overtime
LEFT OUTER JOIN dba.overtimehourrule
ON overtime.ruleid = overtimehourrule.ruleid
group by overtime.punchindex
) ov
ON punchpayroll.punchindex =ov.punchindex
INNER JOIN dba.employee ON employee.empnum = punchpayroll.empnum and employee.isactive = 1
inner join dba.JobPos on punchpayroll.jobtype= JobPos.JobPos
where punchpayroll.opendate between 20150601 and 20150615
Group by refcode,EMPNAME,EMPLASTNAME, jobpos.descript, punchpayroll.payrate
ORDER BY emplastname, EMPNAME,cast(refcode as INT)SyBase SQL Anywhere v10
ASKER
Yes I understand and appreciate that you are in the dark about the data...but thank you for taking the time to reply anyway! :)
Let me elaborate a bit....
All of the summing in query 2 is correct.
None of the summing in query 1 is correct EXCEPT for CC Tip.
Essentially I want query 2 + CC Tip column
I believe the problem is when I add the howpaid table, there are several records in there with the same punchindex. All of those records are then causing summing errors everywhere else.
Let me elaborate a bit....
All of the summing in query 2 is correct.
None of the summing in query 1 is correct EXCEPT for CC Tip.
Essentially I want query 2 + CC Tip column
I believe the problem is when I add the howpaid table, there are several records in there with the same punchindex. All of those records are then causing summing errors everywhere else.
>>"Essentially I want query 2 + CC Tip column"
yep, go it
so use a subquery, just like you have for overtime.
i.e. do the SUM() of tip in a subquery grouped by howpaid.punchindex
so it returns just a single row for each join to punchpayroll.punchindex
i.e. punchpayroll.punchindex = howpaid.punchindex is a 1:1 relationship
you may need to use a LEFT JOIN for this also
e.g. from your existing query:
LEFT OUTER JOIN
(select overtime.punchindex,sum(Ca
sum(Case When overtimepay =200 then length else 0 end) as DoubleD
from dba.overtime
LEFT OUTER JOIN dba.overtimehourrule
ON overtime.ruleid = overtimehourrule.ruleid
group by overtime.punchindex
) ov
ON punchpayroll.punchindex =ov.punchindex
yep, go it
so use a subquery, just like you have for overtime.
i.e. do the SUM() of tip in a subquery grouped by howpaid.punchindex
so it returns just a single row for each join to punchpayroll.punchindex
i.e. punchpayroll.punchindex = howpaid.punchindex is a 1:1 relationship
you may need to use a LEFT JOIN for this also
e.g. from your existing query:
LEFT OUTER JOIN
(select overtime.punchindex,sum(Ca
sum(Case When overtimepay =200 then length else 0 end) as DoubleD
from dba.overtime
LEFT OUTER JOIN dba.overtimehourrule
ON overtime.ruleid = overtimehourrule.ruleid
group by overtime.punchindex
) ov
ON punchpayroll.punchindex =ov.punchindex
ASKER
I see....but I'm lost. Can you point me in the right direction please?
Lost?
I dont know how to point any more than i have.
Would you like me to write your query?
I dont know how to point any more than i have.
Would you like me to write your query?
ASKER
No obviously not, sorry I thought I included the query that I was lost on lol...
Here it is:
but it throw as error [Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'as' on line 23.
Here it is:
select
cast(refcode as INT) as 'Payroll ID',
EMPNAME as 'EmpFirstName',
EMPLASTNAME as 'EmpLastName',
Sum(reghours) as 'Reg Hours',
JobPos.Descript as 'Position',
punchpayroll.payrate as 'Pay Rate',
Sum(IsNull(Overtime,0)) as 'Overtime',
Sum(punchpayroll.tip) as 'Tip',
Sum(IsNull(DoubleD,0)) as 'Double Time',
Sum(IsNull(CreditTip,0)) as 'CCTip'
from dba.punchpayroll
LEFT OUTER JOIN
(select overtime.punchindex,sum(Case When overtimepay =150 then length else 0 end) as Overtime,
sum(Case When overtimepay =200 then length else 0 end) as DoubleD
from dba.overtime
LEFT OUTER JOIN dba.overtimehourrule
ON overtime.ruleid = overtimehourrule.ruleid
group by overtime.punchindex
) ov
ON punchpayroll.punchindex =ov.punchindex
LEFT OUTER JOIN dba.howpaid
ON punchpayroll.punchindex = howpaid.punchindex where methodnum in (SELECT METHODNUM FROM DBA.METHODPAY WHERE AUTHREQR = 2) as CreditTip
INNER JOIN dba.employee ON employee.empnum = punchpayroll.empnum and employee.isactive = 1
inner join dba.JobPos on punchpayroll.jobtype= JobPos.JobPos
where punchpayroll.opendate between 20150601 and 20150615
Group by refcode,EMPNAME,EMPLASTNAME, jobpos.descript, punchpayroll.payrate
ORDER BY emplastname, EMPNAME,cast(refcode as INT)but it throw as error [Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'as' on line 23.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
For reference & regarding Database Identifiers
* the page specifies the rules, basically if there is a space or a reserved word use double quotes or brackets.
When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these* rules must be delimited by double quotation marks or brackets.
* the page specifies the rules, basically if there is a space or a reserved word use double quotes or brackets.
ASKER
Thanks!!!
e.g. get just the "most recent" entry from this table per howpaid.punchindex this can be achieved if there is a date column and use ROW_NUMBER()
The solution requires understanding the data in table howpaid and what you want to do with it.