April 21, 2016 at 2:30 am
Hi There,
I have a table with data in the following columns.
-Employee Number
-RagRating
-Timestamp
Employee No.Rag Rating Timestamp
1 Green 21/04/2016
1 Yellow 20/04/2016
1 Yellow 19/04/2016
1 Green 18/04/2016
1 Green 17/04/2016
What I need is a query to tell me that on the most recent date (21/04/2016) Employee 1 is in category Green, and they have been in that category for 1 days.
The query I currently have is giving me the most recent data and rag rating, however the number of days in category is showing that employee 1 has been green for 3 days.
April 21, 2016 at 3:27 am
Show us your query.
April 21, 2016 at 3:37 am
Select
[Employee Number],
[Timestamp] = Max([Timestamp])
Into #Current
From [dbo].[Rag Rated Table] as T1
Group By [Employee Number]
Select
[Employee Number],
[Timestamp] = min([Timestamp]),
[RAG Rating]
Into #Previous
From [dbo].[Rag Rated Table] as T1
Group By [Employee Number],[RAG Rating]
/* Select the most recent date*/
Select
#Current.[Employee Number]
,#Current.[Timestamp]
,T2.[RAG Rating]
,[Days in Category] = datediff(day,pre.[Timestamp],#Current.[Timestamp])
From #Current
/* Select the most recent grade*/
Left Join (Select [Employee Number],[RAG Rating],[Timestamp] from [dbo].[Rag Rated Table]) as T2
On T2.[Employee Number]= #Current.[Employee Number]
and T2.[Timestamp] = #Current.[Timestamp]
Left Join #Previous as Pre
on #Current.[Employee Number]=Pre.[Employee Number]
and T2.[RAG Rating] = Pre.[RAG Rating]
April 21, 2016 at 4:05 am
When you do the insert into the previous table you are not inserting the previous value but the earliest value for the employee as you are using the MIN function.
April 21, 2016 at 4:18 am
Yeah, this was all I could think to do, as I'm not sure how to pull the date of the last time the Rag Rating Column Changed.
I think I'd essentially need the same query but instead of the Min date I need the date the RagRating Column changed.
April 21, 2016 at 4:25 am
You will probably get some more suggestions on how to do this later today, but here is how I would approach it.
SELECT
RRT1.[Employee Number]
, RRT1.[Timestamp]
, RRT1.[RAG Rating]
, RRT3.[Timestamp] [Previous Timestamp]
, RRT3.[RAG Rating] [Previous RAG Rating]
, DATEDIFF(day, RRT3.[Timestamp], RRT1.[Timestamp]) [Days Since Status Change]
FROM [dbo].[Rag Rated Table] RRT1
JOIN [dbo].[Rag Rated Table] RRT3 ON RRT3.[Employee Number] = RRT1.[Employee Number]
WHERE RRT1.[Timestamp] = (SELECT MAX([Timestamp]) FROM dbo.[Rag Rated Table] RRT2 WHERE RRT2.[Employee Number] = RRT1.[Employee Number])
AND RRT3.[Timestamp] = (SELECT MAX([Timestamp]) FROM dbo.[Rag Rated Table] RRT4 WHERE RRT4.[Employee Number] = RRT1.[Employee Number] AND RRT4.[Timestamp] < RRT1.[Timestamp])
April 21, 2016 at 4:48 am
I would probalby do it like this
WITH CTE AS (
SELECT E.EmployeeNumber, MAX(E.[Timestamp]) AS DateLastAdded
FROM dbo.Employees AS E
GROUP BY E.EmployeeNumber
)
SELECT Emp.EmployeeNumber, Emp.RagRating, Emp.[Timestamp],
DATEDIFF(d, Emp.[Timestamp], GETDATE()) AS DateSinceAdded
FROM CTE
INNER JOIN dbo.Employees AS Emp
ON CTE.EmployeeNumber = Emp.EmployeeNumber
AND CTE.DateLastAdded = Emp.[Timestamp]
April 21, 2016 at 6:43 am
A few modifications and this worked great, thank you!
April 21, 2016 at 8:40 am
Another way:
WITH SampleData ([Employee No.], [Rag Rating], [Timestamp]) AS (
SELECT 1, 'Green', CONVERT(DATE,'21/04/2016',103) UNION ALL
SELECT 1, 'Yellow', CONVERT(DATE,'20/04/2016',103) UNION ALL
SELECT 1, 'Yellow', CONVERT(DATE,'19/04/2016',103) UNION ALL
SELECT 1, 'Green', CONVERT(DATE,'18/04/2016',103) UNION ALL
SELECT 1, 'Green', CONVERT(DATE,'17/04/2016',103)
),
PartProcessed AS (
SELECT *,
rn = ROW_NUMBER() OVER(PARTITION BY [Employee No.], [Rag Rating] ORDER BY [Timestamp])
FROM SampleData
)
SELECT
[Employee No.],
[Rag Rating],
[Timestamp],
DaysInRating = COUNT(*) OVER(PARTITION BY [Rag Rating], DATEADD(DAY,0-rn,[Timestamp]))
FROM PartProcessed
ORDER BY [Timestamp]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply