asked on

SQL MAX Date

Hello,
Can you please help,
I'm using below statement.
I'm having a hard time selecting only the last ChqDate per Employee.

SELECT DISTINCT "eeRegKeys"."eeEEnum","eeEmployee"."LastName", "eeEmployee"."FirstName", "erEmployer"."ERnum","RegData"."RegValue",
MAX(CONVERT(Datetime,"Register"."ChqDate",1)) AS [ChqDate],"RegTable"."CurYTD"
FROM ((((("erEmployer" "erEmployer" INNER JOIN "eeRegKeys" "eeRegKeys" ON "erEmployer"."ERnum"="eeRegKeys"."eeERnum") INNER JOIN "eeEmployee" "eeEmployee" ON "eeRegKeys"."eeLink"="eeEmployee"."eeLink") INNER JOIN "stmUserSecurity" "stmUserSecurity" ON "eeRegKeys"."eeLink"="stmUserSecurity"."eeLink") INNER JOIN "RegData" "RegData" ON "eeEmployee"."eeLink"="RegData"."eeLink") INNER JOIN "RegTable" "RegTable" ON "RegData"."RegCode"="RegTable"."RegCode") INNER JOIN "Register" "Register" ON "RegData"."RegLink"="Register"."RegLink"
WHERE "eeEmployee"."idCalendar">=0 AND "eeRegKeys"."EndDate" IS NULL AND "erEmployer"."EndDate" IS NULL AND "RegTable"."EndDate" IS NULL AND "Register"."ERnum" ='2783' AND ("RegTable"."PayCode"='GRO') AND "stmUserSecurity"."idUser"=1 AND "Register"."EndDate" IS NULL AND "RegData"."EndDate" IS NULL AND "eeEmployee"."EndDate" IS NULL AND "RegTable"."CurYTD" LIKE 'YTD' AND (CONVERT(Datetime,"Register"."ChqDate",1)) > = '2014-01-01' and "eeRegKeys"."eeEEnum" like '150%'
GROUP BY "eeRegKeys"."eeEEnum","eeEmployee"."LastName", "eeEmployee"."FirstName", "erEmployer"."ERnum", "RegTable"."PayCode","RegData"."RegValue", "Register"."ChqDate","RegTable"."CurYTD"
ORDER BY "eeRegKeys"."eeEEnum","Register"."ChqDate"

Thank you,

PortletPaul

Please try the following and let us know if it helps.

SELECT /* absolutely useless if doing a group by -->>> DISTINCT <<-- */
      eeRegKeys.eeEEnum
    , eeEmployee.LastName
    , eeEmployee.FirstName
    , erEmployer.ERnum
    , RegData.RegValue
    , CONVERT(datetime, MAX(Register.ChqDate), 112) AS [ChqDate]
    , RegTable.CurYTD
FROM erEmployer erEmployer
      INNER JOIN eeRegKeys eeRegKeys
                  ON erEmployer.ERnum = eeRegKeys.eeERnum
      INNER JOIN eeEmployee eeEmployee
                  ON eeRegKeys.eeLink = eeEmployee.eeLink
      INNER JOIN stmUserSecurity stmUserSecurity
                  ON eeRegKeys.eeLink = stmUserSecurity.eeLink
      INNER JOIN RegData RegData
                  ON eeEmployee.eeLink = RegData.eeLink
      INNER JOIN RegTable RegTable
                  ON RegData.RegCode = RegTable.RegCode
      INNER JOIN Register Register
                  ON RegData.RegLink = Register.RegLink
WHERE eeEmployee.idCalendar >= 0
      AND eeRegKeys.EndDate IS NULL
      AND erEmployer.EndDate IS NULL
      AND RegTable.EndDate IS NULL
      AND Register.ERnum = '2783'
      AND (RegTable.PayCode = 'GRO')
      AND stmUserSecurity.idUser = 1
      AND Register.EndDate IS NULL
      AND RegData.EndDate IS NULL
      AND eeEmployee.EndDate IS NULL
      AND RegTable.CurYTD LIKE 'YTD'
      AND (CONVERT(datetime, Register.ChqDate, 1)) >= '2014-01-01'
      AND eeRegKeys.eeEEnum LIKE '150%'
GROUP BY
      eeRegKeys.eeEEnum
    , eeEmployee.LastName
    , eeEmployee.FirstName
    , erEmployer.ERnum
    , RegTable.PayCode
    , RegData.RegValue
    , Register.ChqDate
    , RegTable.CurYTD
ORDER BY
      eeRegKeys.eeEEnum
    , Register.ChqDate

Open in new window

PortletPaul

sorry just noticed something.
What is the actual data type Register.ChqDate?

W.E.B

ASKER

Hello,
Register.ChqDate
APR 07 2014
FEB 07 2014
---

thanks

PortletPaul

I am assuming that field is a date or datetime and does not need converting for use in date logic.

I think you need something like row_number() to get only the latest cheque.

SELECT
      eeRegKeys.eeEEnum
    , eeEmployee.LastName
    , eeEmployee.FirstName
    , erEmployer.ERnum
    , RegData.RegValue
    , CONVERT(varchar(10), MAX(Register.ChqDate), 120) AS [ChqDate]
    , RegTable.CurYTD
FROM erEmployer erEmployer
      INNER JOIN eeRegKeys eeRegKeys
                  ON erEmployer.ERnum = eeRegKeys.eeERnum
      INNER JOIN eeEmployee eeEmployee
                  ON eeRegKeys.eeLink = eeEmployee.eeLink
      INNER JOIN stmUserSecurity stmUserSecurity
                  ON eeRegKeys.eeLink = stmUserSecurity.eeLink
      INNER JOIN RegData RegData
                  ON eeEmployee.eeLink = RegData.eeLink
      INNER JOIN RegTable RegTable
                  ON RegData.RegCode = RegTable.RegCode
      INNER JOIN (
                  SELECT
                        RegLink
                      , ERnum
                      , EndDate
                      , ChqDate
                      , ROW_NUMBER() OVER (PARTITION BY reglink ORDER BY ChqDate DESC) AS RN
                  FROM Register
            ) Register
                  ON RegData.RegLink = Register.RegLink
                        AND RN = 1
WHERE eeEmployee.idCalendar >= 0
      AND eeRegKeys.EndDate IS NULL
      AND erEmployer.EndDate IS NULL
      AND RegTable.EndDate IS NULL
      AND Register.ERnum = '2783'
      AND (RegTable.PayCode = 'GRO')
      AND stmUserSecurity.idUser = 1
      AND Register.EndDate IS NULL
      AND RegData.EndDate IS NULL
      AND eeEmployee.EndDate IS NULL
      AND RegTable.CurYTD LIKE 'YTD'
      AND Register.ChqDate >= '20140101'
      AND eeRegKeys.eeEEnum LIKE '150%'
GROUP BY
      eeRegKeys.eeEEnum
    , eeEmployee.LastName
    , eeEmployee.FirstName
    , erEmployer.ERnum
    , RegTable.PayCode
    , RegData.RegValue
    , Register.ChqDate
    , RegTable.CurYTD
ORDER BY
      eeRegKeys.eeEEnum
      , Register.ChqDate

Open in new window

W.E.B

ASKER

HI Paul,
Still getting all dates., all values.

thanks

PortletPaul

@Wass_QA

It is important to know the actual data type of that field (not just what it looks like )

This will list the needed details (in COLUMN_NAME &TYPE_NAME)

exec sp_columns Register

W.E.B

ASKER

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

CHAR
Register.ChqDate
APR 07 2014
FEB 07 2014

and in an unsortable format. good grief.

Then you will have to convert that field to date (no much point converting to datetime)

It's really not a good idea to store dates as strings, especially strings based on a language

W.E.B

ASKER

HI Paul,
I don't own this program,
I'm just trying to get some reports out of it.

I tried your statement,
I'm getting one value per Employee, but the dates are from 2008, 2009,...

thanks

PortletPaul

You have to go back to converting that field into dates

line 44 should read:

AND convert(date,Register.ChqDate,1) >= '20140101'

W.E.B

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for Wass_QA's comment #a40282176

for the following reason:

Thank you Paul,
I used my convert datetime.
it's working,

Thanks again.

PortletPaul

No problem, Glad I could help. Do you really intend to award yourself the solution? To be honest that isn't the case here.

The actual solution to your problem is the use of ROW_NUMBER() at HTTP:#a40282167

(The conversion to date or datetime is a side-issue because I thought it ChqDate would logically be date or datetime.)

W.E.B

ASKER

oops,
sorry.