helo Needed to filter records using SQL 1

[niceguy606]

I have a SQL query where I get the following records.

SELECT employee_training.Employee_ID, employee_training.Training_Method_ID, employee_training.Approval, employee_training.Train_ID, employee_training.Training_Type_ID, sop.SOP_ID, sop.Type_ID, sop.SOP_ID_NUM, sop.Client_ID, sop.Status_ID, sop.Training_Freq_ID, doc_status.Status_ID, sop_training_code.Training_Code_ID, sop.Revision_Code, sop.Title, sop.Effective_Date,training_code.Training_Code_ID, sop_training_code.Training_Code_ID, employee_training.Training_Date,Metrics.dbo.Client_Codes.Client_Code
FROM (training_method RIGHT JOIN (((employee_training INNER JOIN sop ON employee_training.Train_ID = sop.SOP_ID) LEFT JOIN Metrics.dbo.Client_Codes ON sop.Client_ID = Metrics.dbo.Client_Codes.Client_ID) INNER JOIN doc_status ON sop.Status_ID = doc_status.Status_ID) ON training_method.Training_Method_ID = employee_training.Training_Method_ID) INNER JOIN (training_code RIGHT JOIN sop_training_code ON training_code.Training_Code_ID = sop_training_code.Training_Code_ID) ON sop.SOP_ID = sop_training_code.SOP_ID
WHERE (((employee_training.Employee_ID)= 96) AND ((employee_training.Training_Method_ID)=10) AND ((sop.Status_ID)=1) AND ((sop_training_code.Training_Code_ID)=11) ) ORDER by Metrics.dbo.Client_Codes.Client_Code;


Here is the Result set

Employee_ID Train_ID ClientID Client_Code ID Training_Date Rev
96 3 1 TP 0 03/05/2003 0
96 3 1 TP 0 04/05/2004 0
96 3 1 TP 0 04/03/2005 1
96 83 6 TM 0 04/03/2005 0

I want only the records latest training date from the same ClientID and TrainID

i want only to display these records. how to filter them.

96 3 1 04/03/2005 1
96 83 6 04/03/2005 0

 

[bradth]

You need to have a include the Training_Date in the where clause.

Brad

 

[niceguy606]

Thanks for ur reply.

I also want to display the latest record if the Rev is 1 not the Rev 0 one . The Client_ID and ID are also different

like it can TP O or TP 01 which can have same client ID number but they r different records.

Ho to do it.

 

[amrita418]

You need to replace your code where I have comments.

select   Employee_ID, 
         Train_ID, 
         ClientID, 
         Client_Code, 
         ID, 
         isnull(max(case when rev = 1 then Training_Date else NULL end), max(training_Date))
from     -- Your whole query with alias example (select employee..... client_code) a 
group by Employee_ID, Train_ID, ClientID, Client_Code, ID

If you need the rev also in the result set then use this

select   b.Employee_ID, 
         b.Train_ID, 
         b.ClientID, 
         b.Client_Code, 
         b.ID, 
         isnull(b.TDateRev1, b.TDateREv0) Training_Date,
         case when b.TDateRev1 is null then 0 else 1 end rev 
from     (select   Employee_ID, 
                   Train_ID, 
                   ClientID, 
                   Client_Code, 
                   ID, 
                   max(case when rev = 1 then Training_Date else NULL end) TDateRev1, max(training_Date) TDateRev0
         from      -- Your whole query with alias example (select employee..... client_code) a 
         group by  Employee_ID, Train_ID, ClientID, Client_Code, ID) b

Regards,
AA

 

[niceguy606]

Hi thanks for your reply.

The revision number Rev increases its not just 0 or 1. it can be 2,3 ,4 . I have to pick up the highest number Rev number and see the Date Trainined of it

suppose it is
Client_ID ID Rev Date Trained
AB 000 1 04/05/2004
AB 000 0 04/06/2003
AB 001 0 02/07/05
I need to pull the records like

Ab 00 04/05/2004
AB 001 02/07/05

since AB 00 and AB 001 are diff records.

AB 000 Rev 1 and AB 000 Rev 0 are same records just have different trin_ID number. its when a old is revised new Train_ID is created with new Rev number.

Please let me know

 

[amrita418]

Just to clarify:

You need the date of the maximum rev number per clientID + Train_Id?

If two or more records with same rev value exist for a given ClientID and Train_ID then pick the one with max training date?

Regards,
AA

 

[niceguy606]

I need the latest training_date if Client_ID+ID which can be AB0 wih Rev 0,1,2,3 and AB1 with Rev 0,1,2,3

I need the latest training date for AB0 and AB1 as they r different records.

 

[niceguy606]

SELECT dbo.employee_training.Employee_ID, dbo.employee_training.Training_Method_ID, dbo.employee_training.Approval, dbo.employee_training.Train_ID,
dbo.employee_training.Training_Type_ID, dbo.sop.SOP_ID, dbo.sop.Type_ID, dbo.sop.SOP_ID_NUM, dbo.sop.Client_ID, dbo.sop.Status_ID,
dbo.sop.Training_Freq_ID, dbo.doc_status.Status_ID AS Expr1, dbo.sop_training_code.Training_Code_ID, dbo.sop.Revision_Code, dbo.sop.Title,
dbo.sop.Effective_Date, dbo.training_code.Training_Code_ID AS Expr2, dbo.sop_training_code.Training_Code_ID AS Expr3,
dbo.employee_training.Training_Date, Metrics.dbo.client_codes.Client_Code
FROM dbo.training_method RIGHT OUTER JOIN
dbo.employee_training INNER JOIN
dbo.sop ON dbo.employee_training.Train_ID = dbo.sop.SOP_ID LEFT OUTER JOIN
Metrics.dbo.client_codes ON dbo.sop.Client_ID = Metrics.dbo.client_codes.Client_ID INNER JOIN
dbo.doc_status ON dbo.sop.Status_ID = dbo.doc_status.Status_ID ON
dbo.training_method.Training_Method_ID = dbo.employee_training.Training_Method_ID INNER JOIN
dbo.training_code RIGHT OUTER JOIN
dbo.sop_training_code ON dbo.training_code.Training_Code_ID = dbo.sop_training_code.Training_Code_ID ON
dbo.sop.SOP_ID = dbo.sop_training_code.SOP_ID
WHERE (dbo.employee_training.Employee_ID = 96) AND (dbo.employee_training.Training_Method_ID = 10) AND (dbo.sop.Status_ID = 1) AND
(dbo.sop_training_code.Training_Code_ID = 11) AND (Metrics.dbo.client_codes.Client_Code + '' + dbo.sop.SOP_ID_NUM = 'AB0')


How to pick the maximum date and corresponding SOP_ID_Num, Rev, and Title from this query?

the results are

Rev Training_Date Client_Code SOP_ID_NUM
0 04/05/05 AB 0
1 06/07/05 AB 0

 

[amrita418]

Here is the general ides on how to do this:

create table #Test (Employee_ID int, Train_ID int, ClientID int, Client_Code char(2), ID int,Training_Date datetime,
Rev int)
insert into #Test values (96,           3,            1,       'TP',      0,   '03/05/2003',   0)
insert into #Test values (96,           3,            1,       'TP',     0,   '04/05/2004',   0)
insert into #Test values (96,           3,            1,       'TP',      0,   '04/03/2005',   1)
insert into #Test values (96,          83,            6,       'TM',      0,   '04/03/2005',   0)
insert into #Test values (96,          83,            6,       'TM',      0,   '04/23/2005',   0)

select    a.Employee_ID, 
          a.Train_ID, 
          a.ClientID, 
          a.Client_Code, 
          a.ID, 
          max(a.Training_Date), 
          a.rev
from      #Test a,
          (select    ClientID,
                     Train_ID,
                     max(rev) rev
           from      #Test
           group by  ClientID,
                     Train_Id) b
where     a.ClientID = b.ClientID
          and a.Train_ID = b.Train_Id
          and a.rev = b.rev
group by  a.Employee_ID, 
          a.Train_ID, 
          a.ClientID, 
          a.Client_Code, 
          a.ID,
          a.rev

If you look at the output, you will see that for the combination of (train_id = 1, client_id = 3), training_date corresponding to the max value of rev is picked.

For the other combination (train_id = 83, client_id = 6) there are two records hence the one with max training_date is picked.

Since you did not provide the create table scripts with some sample data and output, I leave it onto you to incorporate this logic into your code.

Let us know if you need more help.

Regards,
AA

 

[niceguy606]

Table: employee_training
-----------------------
EmployeeID (int)
Training_Method_ID (int)
Training_Date (Date)
Revision_Code (int)
Train_ID (int)

Table: SOP
-------------
SOP_ID int Primary Key
SOP_ID_NUM nvarchar
Client_ID int
Revision_Code int
Title nvarchar

Table: Training_Method
-----------------------
Training_Method_ID int Primary Key
Training_Method_Text

Client_codes
-----------
Client_ID int Primary key
Client_Code nvarchar

Table: employee
---------------
Employee_ID int Primary Key
Employee_Name nvarchar

Data:
-----

employee_training

insert into employee_training (Employee_ID, Training_Method_ID, Training_Date, Train_ID,Revision_Code)

VALUES ( 96,10, 03/04/03, 3, 0)
VALUES ( 96,10, 05/08/03, 3, 0)
VALUES ( 96,10, 02/05/04, 8, 1)
VALUES ( 96,10, 06/01/05, 10,2)
VALUES ( 96,10, 07/09/05, 83, 3)
VALUES ( 96,10, 03/04/04, 25, 0)
VALUES ( 97,10, 11/01/04, 25, 0)
VALUES ( 96,10, 02/09/05, 55, 0)

SOP
---
insert into SOP (SOP_ID, SOP_ID_NUM, Client_ID,Revision_Code, Title)

VALUES ( 3, 0, 6 , 0, "SOP-1")
VALUES ( 8, 0, 6, 1, "SOP-1")
VALUES (10, 0, 6, 2, "SOP-1")
VALUES (83, 0, 6, 3, "SOP-1")
VALUES (25, 0, 5, 0, "SOP-2")
VALUES (55, 1, 6, 0, "SOP-3")


Note:Whenever I change the Revision number of existing SOP it adds a new record in the SOP table but the Client_ID remains same.

Client_Codes
-------------
insert into client_codes (Client_ID,Client_Code)

VALUES ( 1, AB)
VALUES ( 2, TP)
VALUES ( 3, GH)
VALUES ( 4, BM)
VALUES ( 5, RS)
Values ( 6, YS)
Values ( 7, ZX)


Table employee
insert into employees (Employee_ID,Employee_Name)

VALUES ( 96, "User1")
VALUES ( 97, "user2")
VALUES ( 98, "User3")



Relation between tables
---------------------

employee employee_training
--------------------------------
Emplyee_ID EmployeeID (one -many)

SOP employee_training
---------------------------------
SOP_ID Train_ID (one -many)

employee_training training_method
---------------------------------
Training_method_ID Training_method_ID

------------------------------------------------------------
These are the tables and data.

I want to pick the records from the employee_training table

Where employee_ID=96 and Training_Method_id=10

The output should be

Client_Code SOP_ID_NUM Reivision_Code Training_Date Title
-----------------------------------------------------------
YS 0 3 07/09/05 SOP-1
RS 0 0 03/04/04 SOP-2
YS 1 0 02/09/05 SOP-3



Note: YS O and YS 1 are 2 different records but have same client code.


I am sorry if i confused u guys. please let me know what i have to do to get this output. I really aprreciate it.

 

[niceguy606]

Hi Amrita
In example query the TrainID is same even though the revision number is different. The trainid is different when the revision number is different. Can you pls tell me how to do it.

I get the max training date but dont get when the revision number is there.

thanks
help appreciated