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,
W.E.BAsked:
Who is Participating?
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.

PortletPaulfreelancerCommented:
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

0
PortletPaulfreelancerCommented:
sorry just noticed something.
What is the actual data type Register.ChqDate?
0
W.E.BAuthor Commented:
Hello,
Register.ChqDate
APR  07 2014
FEB  07 2014
---

thanks
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
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

0
W.E.BAuthor Commented:
HI Paul,
Still getting all dates., all values.

thanks
0
PortletPaulfreelancerCommented:
@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
0
W.E.BAuthor Commented:
Register      ChqDate            char
0
PortletPaulfreelancerCommented:
I'm pretty confident the Register.ChqDate is a date or datetime field.

but: Still getting all dates....

I can only guess at the actual data, but I'm fairly certain you are wanting the effect of row_number(). Here I have moved it up a level. See if this helps.

Please try this
SELECT
      sq.eeEEnum
    , sq.LastName
    , sq.FirstName
    , sq.ERnum
    , sq.RegValue
    , sq.ChqDate
    , sq.CurYTD
FROM (

            SELECT
                  eeRegKeys.eeEEnum
                , eeEmployee.LastName
                , eeEmployee.FirstName
                , erEmployer.ERnum
                , RegData.RegValue
                , Register.ChqDate
                , RegTable.CurYTD
                , ROW_NUMBER() OVER (PARTITION BY eeEEnum ORDER BY ChqDate DESC) AS RN
            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
                              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 Register.ChqDate >= '20140101'
                  AND eeRegKeys.eeEEnum LIKE '150%'
      ) sq
WHERE rn = 1
ORDER BY
      eeEEnum
      , ChqDate
; 

Open in new window

0

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
PortletPaulfreelancerCommented:
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
0
W.E.BAuthor Commented:
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
0
PortletPaulfreelancerCommented:
You have to go back to converting that field into dates

line 44 should read:

                 AND convert(date,Register.ChqDate,1) >= '20140101'
0
W.E.BAuthor Commented:
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.
0
PortletPaulfreelancerCommented:
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.)
0
W.E.BAuthor Commented:
oops,
sorry.
0
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

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.