[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

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,
0
W.E.B
Asked:
W.E.B
  • 8
  • 6
1 Solution
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PortletPaulCommented:
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
 
PortletPaulCommented:
@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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now