Distinct records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Distinct records

Author

Topic

Jannette
Starting Member

26 Posts

Posted - 2012-05-24 : 11:18:22

I have a table that has 2 records for a worker as follows:

Personnel_Ref Surname leaving_date Employee_Status Start_Date
BBOWL62P Bowler 00000000 Live 20120423
BBOWL62P Bowler 20120405 !Archive 20120109
Is there a way to only select the record with the greatest start date ?
I thought that there was but I am not having much success at the moment.

Hope someone can help !!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-05-24 : 11:35:57

[CODE]select a.Personnel_Ref, a.Surname, a.leaving_date, a.Employee_Status, a.Start_Date
from (
select *, row_number() over (partition by Personnel_Ref order by Start_date DESC) rn
from MyTable
) a
where a.rn = 1[/CODE]

=================================================
There is a foolish corner in the brain of the wisest man. -Aristotle, philosopher (384-322 BCE)

Jannette
Starting Member

26 Posts

Posted - 2012-05-24 : 12:00:08

Thankyou very much, that works perfectly. However how do I incorporate that into the following syntax ?

select distinct a.Employer_Ref, Client_Or_Pers_No, First_Forename+' '+Surname as Worker_Name,
Case When Employee_Status='!Archive' Then 'History' Else Employee_Status End as Worker_Status,
SUBSTRING(Leaving_date,7,2)+'/'+SUBSTRING(Leaving_Date,5,2)+'/'+SUBSTRING(Leaving_Date,1,4) as Leaving_Date,
CONVERT(Varchar(4),a.Tax_Year)+CONVERT(Varchar(2),a.Tax_Period) as Year_Pd,
Case when Item_Description='WTR Accrual Debit' Then Amount Else 0 End as Accrual_Value,
Case when Item_Description='WTD Holiday Pay' Then Amount Else 0 End as Holiday_Pay, Client_Name
from Accounts_Entries a Inner Join vw_All_Workers w On a.Employer_Ref=w.Company_Employer and a.Client_Or_Pers_No=w.Personnel_Ref
Inner Join Valid_Timesheets t On a.Employer_Ref=t.Employer_Ref and a.Client_Or_Pers_No=t.Personnel_Ref
and a.Tax_Year=t.Tax_Year --and a.Tax_Period=t.Period_proc_by_Payroll
Inner Join Client_Ref c On t.Employer_Ref=c.Employer_Ref and t.Client_Ref=c.Client_Ref
where a.Tax_Year=2012 and Item_Description in ('WTR Accrual Debit','WTD Holiday Pay')
and a.Employer_Ref = 'OMWEEK'

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-05-24 : 12:11:00

Depending on your table structure as well, you could filter on the individual table instead of the entire results, but this should get you what you need if performance tweaking isn't your primary concern.


select *
from
(
select 
row_number() over (partition by w.Personnel_Ref order by w.Start_date DESC) rn
,a.Employer_Ref
, Client_Or_Pers_No
, First_Forename+' '+Surname as Worker_Name
,Case 
	When Employee_Status='!Archive' Then 'History' 
	Else Employee_Status End as Worker_Status
,SUBSTRING(Leaving_date,7,2)+'/'+SUBSTRING(Leaving_Date,5,2)+'/'+SUBSTRING(Leaving_Date,1,4) as Leaving_Date
,CONVERT(Varchar(4),a.Tax_Year)+CONVERT(Varchar(2),a.Tax_Period) as Year_Pd
,Case when Item_Description='WTR Accrual Debit' Then Amount Else 0 End as Accrual_Value
,Case when Item_Description='WTD Holiday Pay' Then Amount Else 0 End as Holiday_Pay
, Client_Name
from 
	Accounts_Entries a 
Inner Join 
	vw_All_Workers w 
	On	a.Employer_Ref = w.Company_Employer 
	and	a.Client_Or_Pers_No = w.Personnel_Ref
Inner Join 
	Valid_Timesheets t 
	On	a.Employer_Ref=t.Employer_Ref 
	and a.Client_Or_Pers_No=t.Personnel_Ref
	and a.Tax_Year=t.Tax_Year --and a.Tax_Period=t.Period_proc_by_Payroll
Inner Join 
	Client_Ref c 
	On	t.Employer_Ref=c.Employer_Ref and t.Client_Ref=c.Client_Ref
where 
	a.Tax_Year=2012 
and Item_Description in ('WTR Accrual Debit','WTD Holiday Pay')
and a.Employer_Ref = 'OMWEEK'
) aa
where 
aa.rn = 1

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Jannette
Starting Member

26 Posts

Posted - 2012-05-25 : 06:05:34

Thanks for this, but this is not giving the correct number of records. I want to ensure that only 1 record is returned from vw_All_Workers, then all the associated records from Accounts_Entries is returned (more than 1 record from account entries)

Hope this makes sense and someone can help !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 10:24:09

quote:
Originally posted by Jannette

Thanks for this, but this is not giving the correct number of records. I want to ensure that only 1 record is returned from vw_All_Workers, then all the associated records from Accounts_Entries is returned (more than 1 record from account entries)

Hope this makes sense and someone can help !

how will that be possible

You'll obviously have more than one set of values from Accounts_Entries for each record in vw_All_Workers. then while making result into single row per vw_All_Workers how will you show associated multiple values? you can obviously show only single value. Can you illustrate your output by means of sample data. then perhaps we would be able to understand what exactly you're looking at

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jannette
Starting Member

26 Posts

Posted - 2012-05-25 : 10:42:11

Hi,

I hope this makes a little more sense, here is some sample data.

Table = vw_All_Workers
Personnel Ref Leaving Date Start Date
BBYER8 20120401 20120326 <==== Only want this record from vw_All_Workers
BBYER8 20120321 20111021

Table = Accounts_Entries
Client_Or_Pers_No Item_Description amount
BBYER8 PAYE Timesheet Pay 390
BBYER8 Employers NIC Debit 33.95
BBYER8 Net Pay -313.92
BBYER8 Employers NIC Credit -33.95
BBYER8 National Insurance - Employee -29.28
BBYER8 Income Tax - PAYE -46.8
BBYER8 Employers NIC Debit 64.94
BBYER8 Net Pay -97.94
BBYER8 Advance Recovery -400
BBYER8 WTD Holiday Pay 614.57
BBYER8 Employers NIC Credit -64.94
BBYER8 National Insurance - Employee -56.23
BBYER8 Income Tax - PAYE -60.4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 15:33:35

quote:
Originally posted by Jannette

Hi,

I hope this makes a little more sense, here is some sample data.

Table = vw_All_Workers
Personnel Ref Leaving Date Start Date
BBYER8 20120401 20120326 <==== Only want this record from vw_All_Workers
BBYER8 20120321 20111021

Table = Accounts_Entries
Client_Or_Pers_No Item_Description amount
BBYER8 PAYE Timesheet Pay 390
BBYER8 Employers NIC Debit 33.95
BBYER8 Net Pay -313.92
BBYER8 Employers NIC Credit -33.95
BBYER8 National Insurance - Employee -29.28
BBYER8 Income Tax - PAYE -46.8
BBYER8 Employers NIC Debit 64.94
BBYER8 Net Pay -97.94
BBYER8 Advance Recovery -400
BBYER8 WTD Holiday Pay 614.57
BBYER8 Employers NIC Credit -64.94
BBYER8 National Insurance - Employee -56.23
BBYER8 Income Tax - PAYE -60.4

and what about all those values that exists in Accounts_Entries? how are you going to show them all against single record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jannette
Starting Member

26 Posts

Posted - 2012-05-28 : 06:01:18

I have rewritten the query slighty as follows:, but the results are not quite as I expect.

Select
b.Employer_Ref, a.Personnel_Ref, Worker_Name, a.Worker_Status, a.Leaving_Date, Year_Pd, Accrual_Value, Holiday_Pay
From

(Select
ROW_NUMBER() over (partition by Personnel_Ref order by Start_Date DESC) as rn,
Personnel_Ref, Leaving_Date, First_Forename+' '+Surname as Worker_Name,
Case When Employee_Status='!Archive' Then 'History' Else Employee_Status End as Worker_Status,
Company_Employer
From vw_All_Workers
Where Company_Employer='OMWEEK') a

Inner Join

(Select
Client_or_Pers_No, CONVERT(Varchar(4),Tax_Year)+CONVERT(Varchar(2),Tax_Period) as Year_Pd,
Case when Item_Description='WTR Accrual Debit' Then Amount Else 0 End as Accrual_Value,
Case when Item_Description='WTD Holiday Pay' Then Amount Else 0 End as Holiday_Pay,
Employer_Ref
From Accounts_Entries
where Tax_Year=2012 and Item_Description In ('WTR Accrual Debit','WTD Holiday Pay') )b

On (a.Personnel_Ref=b.Client_Or_Pers_No and a.Company_Employer=b.Employer_Ref)

Order by a.Personnel_Ref, b.Year_Pd

I am now getting the following:
Employer_Ref Personnel_Ref Worker_Name Worker_Status Leaving_Date Year_Pd Accrual_Value Holiday_Pay
OMWEEK MMOOR347P Andrew Moore Live 0 20121 0 113.28
OMWEEK MMOOR347P Andrew Moore History 20120401 20121 0 113.28
OMWEEK MMOOR347P Andrew Moore History 20120416 20121 0 113.28
OMWEEK MMOOR347P Andrew Moore Live 0 20122 57.31 0
OMWEEK MMOOR347P Andrew Moore History 20120401 20122 57.31 0
OMWEEK MMOOR347P Andrew Moore History 20120416 20122 57.31 0
OMWEEK MMOOR347P Andrew Moore Live 0 20123 72.42 0
OMWEEK MMOOR347P Andrew Moore History 20120401 20123 72.42 0
OMWEEK MMOOR347P Andrew Moore History 20120416 20123 72.42 0

This worker has 3 records in wv_All_Workers, I only want the record with the latest start date, ie: the LIVE one in this case. Is this possible ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-28 : 13:57:23

quote:
Originally posted by Jannette

I have rewritten the query slighty as follows:, but the results are not quite as I expect.

Select
b.Employer_Ref, a.Personnel_Ref, Worker_Name, a.Worker_Status, a.Leaving_Date, Year_Pd, Accrual_Value, Holiday_Pay
From

(Select
ROW_NUMBER() over (partition by Personnel_Ref order by Start_Date DESC) as rn,
Personnel_Ref, Leaving_Date, First_Forename+' '+Surname as Worker_Name,
Case When Employee_Status='!Archive' Then 'History' Else Employee_Status End as Worker_Status,
Company_Employer
From vw_All_Workers
Where Company_Employer='OMWEEK') a

Inner Join

(Select
Client_or_Pers_No, CONVERT(Varchar(4),Tax_Year)+CONVERT(Varchar(2),Tax_Period) as Year_Pd,
Case when Item_Description='WTR Accrual Debit' Then Amount Else 0 End as Accrual_Value,
Case when Item_Description='WTD Holiday Pay' Then Amount Else 0 End as Holiday_Pay,
Employer_Ref
From Accounts_Entries
where Tax_Year=2012 and Item_Description In ('WTR Accrual Debit','WTD Holiday Pay') )b

On (a.Personnel_Ref=b.Client_Or_Pers_No and a.Company_Employer=b.Employer_Ref
and rn=1)

Order by a.Personnel_Ref, b.Year_Pd

I am now getting the following:
Employer_Ref Personnel_Ref Worker_Name Worker_Status Leaving_Date Year_Pd Accrual_Value Holiday_Pay
OMWEEK MMOOR347P Andrew Moore Live 0 20121 0 113.28
OMWEEK MMOOR347P Andrew Moore History 20120401 20121 0 113.28
OMWEEK MMOOR347P Andrew Moore History 20120416 20121 0 113.28
OMWEEK MMOOR347P Andrew Moore Live 0 20122 57.31 0
OMWEEK MMOOR347P Andrew Moore History 20120401 20122 57.31 0
OMWEEK MMOOR347P Andrew Moore History 20120416 20122 57.31 0
OMWEEK MMOOR347P Andrew Moore Live 0 20123 72.42 0
OMWEEK MMOOR347P Andrew Moore History 20120401 20123 72.42 0
OMWEEK MMOOR347P Andrew Moore History 20120416 20123 72.42 0

This worker has 3 records in wv_All_Workers, I only want the record with the latest start date, ie: the LIVE one in this case. Is this possible ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Jannette
Starting Member

26 Posts

Posted - 2012-05-31 : 06:51:22

Thankyou

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-31 : 16:06:31

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources