Link to home
Create AccountLog in
Avatar of BobRosas
BobRosas

asked on

Sum a Max field in SQL

I've attached 2 code examples and an excel spreadsheet that shows the results from the two examples as well as  what I actually want.  I only want the 'Employee Type' with the greatest date but when I add Max to the date in the 2nd example I get the wrong 'Employee Type'.  Does Max not work on dates?  The other problem is the YTD Hrs is an accumulation field so I just ask for the Max record.  That is almost what I want but now I don't need to show each Pay Code so I'd like to sum the Hours field for each employee.  If I take off MAX and add SUM I get a huge amount because the field accumulates.  I just want to Sum the results of the Max field.  Can you please show me how to do that also?                                                                   
 Sum-Max-wSQL.xls
Code used to get 12 lines of data...
SELECT   DISTINCT [vtePayrollBase].[PrbEmpName]                            AS [Name],
                  [vtePayrollBase].[PrbBirthDate]                          AS [DOB],
                  [vtePayrollBase].[PrbDateLastHire]                       AS [Last Hire Date],
                  [vtePayrollBase].[PrbEmpSSN]                             AS [SSN],
                  [veEmploy].[eeDateBeg]                                   AS [Last Type Date],
                  [veEmploy].[eeTermDate]                                  AS [TermDate],
                  [veEmploy].[eeStatus]                                    AS [Active Status],
                  MAX(veEmploy.[eeCategory])                               AS [Employee Type],
                  vtEPayrollBase.[PrbPayDiv]
                    + vtEPayrollBase.[PrbPayDept] AS [Dept],
                  Substring(RIGHT(Rtrim([CsuEmpNumber]),10),7,4)           AS [Emp No],
                  [vtCheckSummHE].[CsePayTypeCode]                         AS [Pay Code],
                  MAX(vtCheckSummHE.[CseYTDHrs])                           AS [YTD Tot Hours],
                  MAX(vtCheckSumm.[CsuYTDGrossPay])                        AS [YTD Gross Pay]
FROM     vtCheckSumm
         INNER JOIN vtEPayrollBase
           ON vtEPayrollBase.[prbFlxIDeb] = vtCheckSumm.[CsuFlxIDEb]
              AND vtEPayrollBase.[prbDateEnd] IS NULL
         INNER JOIN vtCheckSummHE
           ON vtCheckSumm.[CsuFlxID] = vtCheckSummHE.[CseFlxIdCsu]
         INNER JOIN vEBase
           ON vtCheckSumm.[CsuFlxIDEb] = vEBase.[EbFlxId]
         INNER JOIN vEEmploy
           ON vEBase.[EbFlxID] = vEEmploy.[EeFlxIdEb]
WHERE    (((((((vtePayrollBase.[PrbEmpSSN] = 555555555
       AND ((vtCheckSumm.[CsuDateBeg] >= '#STARTDATE#'
             AND vtCheckSumm.[CsuDateBeg] <= '#ENDDATE#')))))))))
GROUP BY [vtEPayrollBase].[PrbPayDiv],[CsuEmpNumber],[vtePayrollBase].[PrbEmpName],[vtePayrollBase].[PrbBirthDate],
         [vtePayrollBase].[PrbEmpSSN],[vtePayrollBase].[PrbPayDept],[vtePayrollBase].[PrbDateLastHire],[vtCheckSummHE].[CsePayTypeCode],
         [veEmploy].[eeDateBeg],[veEmploy].[eeTermDate],[veEmploy].[eeStatus]

Code used to get 4 lines of data where Emp Type is wrong
SELECT   DISTINCT [vtePayrollBase].[PrbEmpName]                            AS [Name],
                  [vtePayrollBase].[PrbBirthDate]                          AS [DOB],
                  [vtePayrollBase].[PrbDateLastHire]                       AS [Last Hire Date],
                  [vtePayrollBase].[PrbEmpSSN]                             AS [SSN],
                  MAX(veEmploy.[eeDateBeg])                                AS [Last Type Date],
                  [veEmploy].[eeTermDate]                                  AS [TermDate],
                  [veEmploy].[eeStatus]                                    AS [Active Status],
                  MAX(veEmploy.[eeCategory])                               AS [Employee Type],
                  vtEPayrollBase.[PrbPayDiv]
                    + vtEPayrollBase.[PrbPayDept] AS [Dept],
                  Substring(RIGHT(Rtrim([CsuEmpNumber]),10),7,4)           AS [Emp No],
                  [vtCheckSummHE].[CsePayTypeCode]                         AS [Pay Code],
                  MAX(vtCheckSummHE.[CseYTDHrs])                           AS [YTD Tot Hours],
                  MAX(vtCheckSumm.[CsuYTDGrossPay])                        AS [YTD Gross Pay]
FROM     vtCheckSumm
         INNER JOIN vtEPayrollBase
           ON vtEPayrollBase.[prbFlxIDeb] = vtCheckSumm.[CsuFlxIDEb]
              AND vtEPayrollBase.[prbDateEnd] IS NULL
         INNER JOIN vtCheckSummHE
           ON vtCheckSumm.[CsuFlxID] = vtCheckSummHE.[CseFlxIdCsu]
         INNER JOIN vEBase
           ON vtCheckSumm.[CsuFlxIDEb] = vEBase.[EbFlxId]
         INNER JOIN vEEmploy
           ON vEBase.[EbFlxID] = vEEmploy.[EeFlxIdEb]
WHERE    ((((((vtePayrollBase.[PrbEmpSSN] = 555555555
      AND ((vtCheckSumm.[CsuDateBeg] >= '#STARTDATE#'
            AND vtCheckSumm.[CsuDateBeg] <= '#ENDDATE#'))))))))
GROUP BY [vtEPayrollBase].[PrbPayDiv],[CsuEmpNumber],[vtePayrollBase].[PrbEmpName],[vtePayrollBase].[PrbBirthDate],
         [vtePayrollBase].[PrbEmpSSN],[vtePayrollBase].[PrbPayDept],[vtePayrollBase].[PrbDateLastHire],[vtCheckSummHE].[CsePayTypeCode],
         [veEmploy].[eeTermDate],[veEmploy].[eeStatus]

Open in new window

Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

>>Does Max not work on dates? <<
Sure it does.  But if you have MAX on both columns it is going to precisely that:  Last date and greatest type from any row in that group
And what happens if there is more than one type on the same date in vEEmploy?
This should be a tad closer, but that whole table design, needs serious revision:
SELECT  [vtePayrollBase].[PrbEmpName] [Name],
        [vtePayrollBase].[PrbBirthDate] [DOB],
        [vtePayrollBase].[PrbDateLastHire] [Last Hire Date],
        [vtePayrollBase].[PrbEmpSSN] [SSN],
        MAX(veEmploy.[eeDateBeg]) [Last Type Date],
        [veEmploy].[eeTermDate] [TermDate],
        [veEmploy].[eeStatus] [Active Status],
        MAX(veEmploy.[eeCategory]) [Employee Type],
        vtEPayrollBase.[PrbPayDiv] + vtEPayrollBase.[PrbPayDept] [Dept],
        SUBSTRING(RIGHT(RTRIM([CsuEmpNumber]), 10), 7, 4) [Emp No],
        [vtCheckSummHE].[CsePayTypeCode] [Pay Code],
        MAX(vtCheckSummHE.[CseYTDHrs]) [YTD Tot Hours],
        MAX(vtCheckSumm.[CsuYTDGrossPay]) [YTD Gross Pay]
FROM    vtCheckSumm
        INNER JOIN vtEPayrollBase ON vtEPayrollBase.[prbFlxIDeb] = vtCheckSumm.[CsuFlxIDEb]
        INNER JOIN vtCheckSummHE ON vtCheckSumm.[CsuFlxID] = vtCheckSummHE.[CseFlxIdCsu]
        INNER JOIN vEBase ON vtCheckSumm.[CsuFlxIDEb] = vEBase.[EbFlxId]
        INNER JOIN FROM vEEmploy ON vEBase.[EbFlxID] = vEEmploy.[EeFlxIdEb]
        INNER JOIN (
	    SELECT  EeFlxIdEb,
	            MAX(eeDateBeg) [Last Type Date]
	    FROM    vEEmploy 
	    GROUP BY
		    EeFlxIdEb) vEEmploy2 ON vEBase.[EbFlxID] = vEEmploy2.[EeFlxIdEb] AND vEBase.eeDateBeg = vEEmploy2.[Last Type Date]
WHERE   vtePayrollBase.[PrbEmpSSN] = 555555555
	AND vtEPayrollBase.[prbDateEnd] IS NULL
        AND vtCheckSumm.[CsuDateBeg] BETWEEN '#STARTDATE#' AND '#ENDDATE#'
GROUP BY [vtEPayrollBase].[PrbPayDiv],
        [CsuEmpNumber],
        [vtePayrollBase].[PrbEmpName],
        [vtePayrollBase].[PrbBirthDate],
        [vtePayrollBase].[PrbEmpSSN],
        [vtePayrollBase].[PrbPayDept],
        [vtePayrollBase].[PrbDateLastHire],
        [vtCheckSummHE].[CsePayTypeCode],
        [veEmploy].[eeTermDate],
        [veEmploy].[eeStatus]

Open in new window

Avatar of BobRosas
BobRosas

ASKER

Thank you for your quick response.  I'm not going to argue that the code needs serious revision.  I know very little SQL and I'm trying to code within another programs environment where there are limitations.  Maybe that is why I get the following error...

Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'. [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Type'.

I will play with the code and try to fix it but if you can see the problem I could still use your help.
You are absolutely right, I left an extra FROM.
This:
INNER JOIN FROM vEEmploy ON vEBase.[EbFlxID] = vEEmploy.[EeFlxIdEb]

Should be:
INNER JOIN vEEmploy ON vEBase.[EbFlxID] = vEEmploy.[EeFlxIdEb]

Sorry about that.
Thank you!
That fixed the FROM error but I'm still getting...
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Type'.
Any thoughts?
You have copied and pasted the query into SSMS and you are getting that error?
No I'm not.  That may be the issue.  It's SQL Server 2005 but the software package has it's own in house environment and it's very limiting.  That may be why it's not working but I'll attach my current code again in case I managed to mess something else up.
SELECT   [vtePayrollBase].[PrbEmpName]                              [Name],
         [vtePayrollBase].[PrbBirthDate]                            [DOB],
         [vtePayrollBase].[PrbDateLastHire]                         [Last Hire Date],
         [vtePayrollBase].[PrbEmpSSN]                               [SSN],
         MAX(veEmploy.[eeDateBeg])                                  [Last Type Date],
         [veEmploy].[eeTermDate]                                    [TermDate],
         [veEmploy].[eeStatus]                                      [Active Status],
         MAX(veEmploy.[eeCategory])                                 [Employee Type],
         vtEPayrollBase.[PrbPayDiv]
           + vtEPayrollBase.[PrbPayDept] [Dept],
         Substring(RIGHT(Rtrim([CsuEmpNumber]),10),7,4)             [Emp No],
         [vtCheckSummHE].[CsePayTypeCode]                           [Pay Code],
         MAX(vtCheckSummHE.[CseYTDHrs])                             [YTD Tot Hours],
         MAX(vtCheckSumm.[CsuYTDGrossPay])                          [YTD Gross Pay]
FROM     vtCheckSumm
         INNER JOIN vtEPayrollBase
           ON vtEPayrollBase.[prbFlxIDeb] = vtCheckSumm.[CsuFlxIDEb]
         INNER JOIN vtCheckSummHE
           ON vtCheckSumm.[CsuFlxID] = vtCheckSummHE.[CseFlxIdCsu]
         INNER JOIN vEBase
           ON vtCheckSumm.[CsuFlxIDEb] = vEBase.[EbFlxId]
         INNER JOIN vEEmploy
           ON vEBase.[EbFlxID] = vEEmploy.[EeFlxIdEb]
         INNER JOIN (SELECT   EeFlxIdEb,
                              MAX(eeDateBeg)    [Last Type Date]
                     FROM     vEEmploy
                     GROUP BY EeFlxIdEb) vEEmploy2
           ON vEBase.[EbFlxID] = vEEmploy2.[EeFlxIdEb]
              AND vEBase.eeDateBeg = vEEmploy2.[Last Type Date]
WHERE    vtePayrollBase.[PrbEmpSSN] = 555555555
AND vtEPayrollBase.[prbDateEnd] IS NULL
AND vtCheckSumm.[CsuDateBeg] BETWEEN '#STARTDATE#' AND '#ENDDATE#'
GROUP BY [vtEPayrollBase].[PrbPayDiv],[CsuEmpNumber],[vtePayrollBase].[PrbEmpName],[vtePayrollBase].[PrbBirthDate],
         [vtePayrollBase].[PrbEmpSSN],[vtePayrollBase].[PrbPayDept],[vtePayrollBase].[PrbDateLastHire],[vtCheckSummHE].[CsePayTypeCode],
         [veEmploy].[eeTermDate],[veEmploy].[eeStatus]

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thank you for your suggestion.  I'm still making changes trying to get it to work.  In the mean time I have other code that I tried.  My guess is I've probably made my design even worse but so far it appears to be giving me the correct employee type based on MAX date.  I was hoping I could use this code to now sum the hours which still appear in 4 records.  What are your thoughts?  I've attached my code as well as an Excel spreadsheet with the resutls.
SELECT   DISTINCT [vtePayrollBase].[PrbEmpName]                            AS [Name],
                  [vtePayrollBase].[PrbBirthDate]                          AS [DOB],
                  [vtePayrollBase].[PrbDateLastHire]                       AS [Last Hire Date],
                  [vtePayrollBase].[PrbEmpSSN]                             AS [SSN],
                  [veEmploy].[eeDateBeg]                                   AS [Last Type Date],
                  [veEmploy].[eeTermDate]                                  AS [TermDate],
                  [veEmploy].[eeStatus]                                    AS [Active Status],
                  MAX(veEmploy.[eeCategory])                               AS [Employee Type],
                  vtEPayrollBase.[PrbPayDiv]
                    + vtEPayrollBase.[PrbPayDept] AS [Dept],
                  Substring(RIGHT(Rtrim([CsuEmpNumber]),10),7,4)           AS [Emp No],
                  [vtCheckSummHE].[CsePayTypeCode]                         AS [Pay Code],
                  MAX(vtCheckSummHE.[CseYTDHrs])                           AS [YTD Tot Hours],
                  MAX(vtCheckSumm.[CsuYTDGrossPay])                        AS [YTD Gross Pay]
FROM     vtCheckSumm
         INNER JOIN vtEPayrollBase
           ON vtEPayrollBase.[prbFlxIDeb] = vtCheckSumm.[CsuFlxIDEb]
              AND vtEPayrollBase.[prbDateEnd] IS NULL
         INNER JOIN vtCheckSummHE
           ON vtCheckSumm.[CsuFlxID] = vtCheckSummHE.[CseFlxIdCsu]
         INNER JOIN vEBase
           ON vtCheckSumm.[CsuFlxIDEb] = vEBase.[EbFlxId]
         INNER JOIN vEEmploy
           ON vEBase.[EbFlxID] = vEEmploy.[EeFlxIdEb]
WHERE    ((((((((((((((((((vtePayrollBase.[PrbEmpSSN] = 541941115
                  AND veEmploy.[eeDateBeg] = (SELECT MAX(veEmploy.[eeDateBeg])
                                              FROM   veEmploy
                                              WHERE  vEBase.[EbFlxID] = vEEmploy.[EeFlxIdEb])
                  AND ((vtCheckSumm.[CsuDateBeg] >= '#STARTDATE#'
                        AND vtCheckSumm.[CsuDateBeg] <= '#ENDDATE#'))))))))))))))))))))
GROUP BY [vtEPayrollBase].[PrbPayDiv],[CsuEmpNumber],[vtePayrollBase].[PrbEmpName],[vtePayrollBase].[PrbBirthDate],
         [vtePayrollBase].[PrbEmpSSN],[vtePayrollBase].[PrbPayDept],[vtePayrollBase].[PrbDateLastHire],[vtCheckSummHE].[CsePayTypeCode],
         [veEmploy].[eeDateBeg],[veEmploy].[eeTermDate],[veEmploy].[eeStatus]

Open in new window

Sum-Max-wSQL2.xls
I found anothe rproblem with my code so I will keep working on it and post another question as needed.  Thanks again.