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
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)

Open in new window



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)

Open in new window


SyBase SQL Anywhere v10PIC1PIC2
triphenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
Well as we know nothing about your data all I can suggest is that you need to use query 2, and then find a way to get just the RELEVANT SINGLE ROW for each: punchpayroll.punchindex = howpaid.punchindex

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.
triphenAuthor Commented:
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.
PortletPaulEE Topic AdvisorCommented:
>>"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(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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

triphenAuthor Commented:
I see....but I'm lost. Can you point me in the right direction please?
PortletPaulEE Topic AdvisorCommented:
Lost?

I dont know how to point any more than i have.

Would you like me to write your query?
triphenAuthor Commented:
No obviously not, sorry I thought I included the query that I was lost on lol...

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)

Open in new window


but it throw as error [Sybase][ODBC Driver][SQL Anywhere]Syntax error near 'as' on line 23.
Kevin CrossChief Technology OfficerCommented:
LEFT OUTER JOIN dba.howpaid
ON punchpayroll.punchindex = howpaid.punchindex where methodnum in (SELECT METHODNUM FROM DBA.METHODPAY WHERE AUTHREQR = 2) as CreditTip

That is incorrect.  What are you trying to alias there?
Kevin CrossChief Technology OfficerCommented:
Missed the WHERE part too.  Look after this entire part:
"where methodnum in (SELECT METHODNUM FROM DBA.METHODPAY WHERE AUTHREQR = 2) as CreditTip"

Think you meant to create another derived query maybe as the CreditTip is referenced in the outer SELECT.
PortletPaulEE Topic AdvisorCommented:
see lines 16 - 29 and line 13 in the query shown below
this is the style of subquery I suggested and you will notice it IS very similar to the already existing subquery (lines 31-46) used to create overtime. You should get used to this method as it quite common when producing aggregated numbers.

Please also note that using single quotes in TSQL should be restricted to literals. For identities use double quotes or brackets.

      CAST(refcode AS int) AS 'Payroll ID'

should be
      CAST(refcode AS int) AS "Payroll ID"
or
      CAST(refcode AS int) AS [Payroll ID] --<< I prefer [ ]

I have not made edits for the incorrect use of single quotes but I have used [ ] in the new portion of the query
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'

    , SUM(hp.[CC Tip]) as [CC Tip]
FROM dba.punchpayroll

      LEFT OUTER JOIN (
            SELECT
                  punchindex
                , SUM(howpaid.CHANGE) AS [CC Tip]
            FROM dba.howpaid
            WHERE methodnum IN (
                        SELECT
                              METHODNUM
                        FROM DBA.METHODPAY
                        WHERE AUTHREQR = 2
                  )
            GROUP BY
                  punchindex
      ) AS hp ON punchpayroll.punchindex = hp.punchindex

      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)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
For reference & regarding Database Identifiers

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.
triphenAuthor Commented:
Thanks!!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.