Trying to get the minimum date from the set
So, I have a table that has the following information
Company, Employee, Work Date, Job Number
ABC 1234 06/01/15 5555
ABC 1234 06/02/15 5555
ABC 1234 06/03/15 5555
ABC 1234 06/04/15 5555
ABC 1234 06/05/15 5555
ABC 1234 06/06/15 5555
ABC 1234 06/11/15 6666
ABC 1234 06/12/15 6666
ABC 1234 06/13/15 6666
ABC 1234 06/14/15 6666
ABC 1234 06/15/15 6666
ABC 1234 06/16/15 6666
I want to record
ABC 1234 06/11/15 6666
My first step is to get the most recent work date using this query
SELECT TCH.Company_Code, TCH.Employee_Code, CAST(TCH.Work_Date AS DATE) AS Work_Date, DENSE_RANK() OVER (PARTITION BY TCH.Employee_Code ORDER BY CAST(TCH.Work_Date AS DATE) DESC) AS DateRank
FROM PR_TIME_CARD_HISTORY_MC TCH WITH (NOLOCK)
WHERE TCH.Company_Code <> 'XYZ'
AND LTRIM(RTRIM(TCH.Employee_Code)) = '22164'
The second step is to connect it back to the original table to get the job, but I can't get the recordset for the job to get the min date.
SELECT TCH.Company_Code, TCH.Employee_Code, TCH.Job_Number, CAST(TCH.Work_Date AS DATE) AS Work_Date, DENSE_RANK() OVER (PARTITION BY TCH.Job_Number ORDER BY CAST(TCH.Work_Date AS DATE) ASC) AS JobRank
FROM PR_TIME_CARD_HISTORY_MC TCH WITH (NOLOCK)
WHERE TCH.Company_Code <> 'XYZ'
AND TCH.Pay_Type = 'R'
AND LTRIM(RTRIM(TCH.Employee_Code)) = '22164'
Hope this helps and can confidently use some confidence.
+3
source to share
3 answers
I feel like the following request should do the job:
SELECT company, employee, work_date,job_number FROM
(
SELECT
TCH.Company_Code as company,
TCH.Employee_Code as employee,
TCH.Job_Number as job_number,
CAST(TCH.Work_Date AS DATE) AS Work_Date,
ROW_NUMBER() OVER (PARTITION BY TCH.Employee_Code,TCH.Company_Code ORDER BY TCH.Job_Number DESC,CAST(TCH.Work_Date AS DATE) ASC) AS DateRank
FROM PR_TIME_CARD_HISTORY_MC TCH WITH (NOLOCK)
) q
WHERE q.DateRank=1
+2
source to share
Edit:
To get the lowest date for the highest Job_Number
, you need to apply both parameters to ORDER BY
:
SELECT *
FROM
(
SELECT TCH.Company_Code, TCH.Employee_Code, TCH.Job_Number,
CAST(TCH.Work_Date AS DATE) AS Work_Date,
ROW_NUMBER() -- latest row gets 1
OVER (PARTITION BY TCH.Employee_Code
ORDER BY TCH.Job_Number DESC, CAST(TCH.Work_Date AS DATE)) AS DateRank
FROM PR_TIME_CARD_HISTORY_MC TCH WITH (NOLOCK)
WHERE TCH.Company_Code <> 'XYZ'
AND LTRIM(RTRIM(TCH.Employee_Code)) = '1234'
) AS dt
WHERE DateRank = 1
But if the job numbers don't increment, you need different logic:
SELECT *
FROM
(
SELECT TCH.Company_Code, TCH.Employee_Code, TCH.Job_Number,
CAST(TCH.Work_Date AS DATE) AS Work_Date,
ROW_NUMBER() -- latest row gets 1
OVER (PARTITION BY TCH.Employee_Code
ORDER BY CAST(TCH.Work_Date AS DATE)) AS DateRank,
-- The start date of each job
MIN(CAST(TCH.Work_Date AS DATE))
OVER (PARTITION BY TCH.Employee_Code, TCH.Job_Number) AS FirstWorkDate
FROM PR_TIME_CARD_HISTORY_MC TCH WITH (NOLOCK)
WHERE TCH.Company_Code <> 'XYZ'
AND LTRIM(RTRIM(TCH.Employee_Code)) = '1234'
) AS dt
WHERE DateRank = 1
0
source to share
Try it with nested queries in the where clause:
Configuring MS SQL Server 2008 schema :
CREATE TABLE TimeCard
(
Company VARCHAR(10),
Employee INT,
WorkDate Date,
JobNumber Int
)
INSERT INTO TimeCard
VALUES
('ABC', 1234, '06/01/15', 5555),
('ABC', 1234, '06/02/15', 5555),
('ABC', 1234, '06/03/15', 5555),
('ABC', 1234, '06/04/15', 5555),
('ABC', 1234, '06/05/15', 5555),
('ABC', 1234, '06/06/15', 5555),
('ABC', 1234, '06/11/15', 6666),
('ABC', 1234, '06/12/15', 6666),
('ABC', 1234, '06/13/15', 6666),
('ABC', 1234, '06/14/15', 6666),
('ABC', 1234, '06/15/15', 6666),
('ABC', 1234, '06/16/15', 6666)
Request 1 :
SELECT TC.Company, TC.Employee, MIN(TC.WorkDate) AS WorkDate, TC.JobNumber
FROM TimeCard TC
WHERE TC.Company <> 'XYZ' AND
TC.Employee = 1234 AND
TC.JobNumber =
(
-- Get Max JobNumber for the Max Workdate
SELECT MAX(JobNumber)
FROM TimeCard TC3
WHERE TC3.WorkDate =
-- Get Max WorkDate of Employee
(
SELECT MAX(WorkDate)
FROM TimeCard TC2
WHERE TC2.Employee = TC.Employee
)
GROUP BY TC3.Employee
)
GROUP BY TC.Company, TC.Employee, TC.JobNumber
Results :
| Company | Employee | WorkDate | JobNumber |
|---------|----------|------------|-----------|
| ABC | 1234 | 2015-06-11 | 6666 |
0
source to share