Is it possible to only display records with latest date?

simflex1 · February 21, 2020, 3:38am

Greetings experts,

First, THANK YOU SqlTeam forum developers.

I was here trying to post a thread around after 4PM Eastern time and my browser froze.

I was so upset after waiting for more than one hour for the browser to become active again that I shut down my laptop and went home thinking I had lost all my typings and original thought.

To my sweet surprise, you guys have auto save. That saved me. I have not seen another forum with this incredible feature.

Now on to my problem.

Please forgive me for coming back with this issue.

Recently, ScottPletcher helped me with this issue (Thanks again, Scott) and it works as he coded it.

However, I am still not getting the desired result.

Just to recap, the code displays records for three columns, thisYear, pastYear, pastYears.

All records entered by a particular employee (empID) for the current year displays under the column thisYear.

Records entered the year before displays under pastYear column and all records entered years after last year display under pastYears column. This works as intended.

However, the issue we are currently facing is that it displays all the records (thisYear, pastYear and pastYears).

My question is whether it is possible to display based on latest year?

For instance, if database is queried and the records found are for the thisy year, display those records for thisYear and ignore the rest of the records that are for either past year or past years.

If no record is found for current year but found for last year, display only records found for pastYear and ignore the rest.

Finally, if no records found for current year or past year but found for past years, display only those records and nothing else.

If no records found at all, then no records exist.

Is this possible?

Here is the latest help from ScottPletcher.

Many, many thanks in advance for your assistance.

I have not been to think up a solution.

ALTER PROCEDURE [dbo].[ValidateDates]
@empID varchar(50)
AS
BEGIN
SET NOCOUNT OFF;
  SELECT distinct employeeName, email, emptitle, EmpID,d.dateCreated,
     CASE WHEN d.dateCreated < prev_yr_jan01 THEN 1 ELSE 0 END AS pastYears,
     CASE WHEN d.dateCreated >= prev_yr_jan01 AND d.dateCreated < curr_yr_jan01
          THEN 1 ELSE 0 END as previousYear, 
     CASE WHEN d.dateCreated >= curr_yr_jan01 THEN 1 ELSE 0 END as thisYear 
     FROM Employees e 
     CROSS APPLY (
         SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS curr_yr_jan01,
             DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AS prev_yr_jan01
    ) AS yr_start_dates
	INNER JOIN  SourceDetails s ON e.EmployeeID = s.EmployeeID 
	INNER JOIN  SpouseDetails sp ON e.EmployeeID = sp.employeeID
	INNER JOIN  DividentDetails dv ON e.EmployeeID = dv.EmployeeID 
	INNER JOIN  ReimbursementDetails r ON e.EmployeeID = r.employeeID
	INNER JOIN  Honoraria h ON e.EmployeeID = h .EmployeeID 
	INNER JOIN  GiftDetails g ON e.EmployeeID = g.employeeID
	INNER JOIN  dateDetails d ON e.EmployeeID = d.employeeID
    WHERE e.EmpID=@empID
    ORDER By d.dateCreated DESC
END

mike01 · February 21, 2020, 4:54pm

Sounds like you can get the Max(Year) from whatever datefield you are using and group by the keys you need, then join the table to this where Year = Year and the other keys.

something like:

select e.* from Employees e
join (select EmployeeID, max(datepart(year, hiredate)) as HIreDate
from Employees group by EmployeeID) as emax
on e.EmployeeID = emax.EmployeeID
and datepart(year,e.hiredate) = emax.Hiredate

With DDL, sample data and expected results, it's hard to understand what you really want.

yosiasz · February 21, 2020, 7:08pm

use sqlteam
go

drop table #Employees
drop table #SourceDetails
drop table #SpouseDetails
drop table #DividentDetails
drop table #ReimbursementDetails
drop table #Honoraria
drop table #GiftDetails
drop table #dateDetails

create table #Employees(EmployeeID int, EmpID int, employeeName varchar(50), 
email varchar(50), emptitle  varchar(50))
insert into #Employees
select 1, 1, 'Darth Vader', 'dvader@darkside.un', 'Bad Dude' union
select 2, 2, 'Oh Bee Won Knob Bee', 'ob1kb@jediunion.un', 'Deep Dude' union
select 3,3,'Darth Vadette','dvadette@darkside.un','Bad Dudette' union
select 4,4,'Luke Sky Walker','lwalker@jediunion.un','You are not my daddy' union
select 5,5,'Princess','plea@jediunion.un','Ew I kissed my brother?!?!' 

create table #SourceDetails(EmployeeID int)
insert into #SourceDetails
select EmployeeID  from #Employees

create table #SpouseDetails(EmployeeID int);
insert into #SpouseDetails
select 3 union
select 5

create table #DividentDetails(EmployeeID int)
insert into #DividentDetails
select EmployeeID  from #Employees

create table #ReimbursementDetails(EmployeeID int)
insert into #ReimbursementDetails
select EmployeeID  from #Employees

create table #Honoraria(EmployeeID int)
insert into #Honoraria
select EmployeeID  from #Employees

create table #GiftDetails(EmployeeID int)
insert into #GiftDetails
select EmployeeID  from #Employees


create table #dateDetails(EmployeeID int, dateCreated datetime)
insert into #dateDetails
select EmployeeID, '2019-01-01' from #Employees where EmployeeID <> 5
union
select EmployeeID, '2020-01-01' from #Employees where EmployeeID = 5
union
select EmployeeID, '2013-01-01' from #Employees where EmployeeID = 5

declare @empID int = 3;
SELECT distinct employeeName, email, emptitle, EmpID,d.dateCreated,
     CASE WHEN d.dateCreated < prev_yr_jan01 THEN 1 ELSE 0 END AS pastYears,
     CASE WHEN d.dateCreated >= prev_yr_jan01 AND d.dateCreated < curr_yr_jan01
          THEN 1 ELSE 0 END as previousYear, 
     CASE WHEN d.dateCreated >= curr_yr_jan01 THEN 1 ELSE 0 END as thisYear 
     FROM #Employees e 
     CROSS APPLY (
         SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS curr_yr_jan01,
             DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AS prev_yr_jan01
    ) AS yr_start_dates
	INNER JOIN  #SourceDetails s ON e.EmployeeID = s.EmployeeID 
	INNER JOIN  #SpouseDetails sp ON e.EmployeeID = sp.employeeID
	INNER JOIN  #DividentDetails dv ON e.EmployeeID = dv.EmployeeID 
	INNER JOIN  #ReimbursementDetails r ON e.EmployeeID = r.employeeID
	INNER JOIN  #Honoraria h ON e.EmployeeID = h .EmployeeID 
	INNER JOIN  #GiftDetails g ON e.EmployeeID = g.employeeID
	INNER JOIN  #dateDetails d ON e.EmployeeID = d.employeeID
    --WHERE e.EmpID=@empID
    ORDER By d.dateCreated DESC

yosiasz · February 21, 2020, 7:21pm

simflex1 · February 22, 2020, 1:20am

yosiasz,

Thanks for that database screenshot.

That's exactly the type of data I have now when I query the database using ScottPletcher's code.

What I would like is to see if that code could be modified so that when I run it, using your data as example, the closest year with a value of 1 is thisYear and so only record for this year should be returned.

Assuming that your data has a value of 1 for the next closest year which is previousYear, then only records for previousYear should be returned.

If only pastYears has value of 1, then return records for only pastYears.

This way, looking at your sample data again, employee with empID of 5 has a record in this year (current year) and pastYears (say two years ago).

When I query the DB as is now, both records are displayed. Since empID 5 has a record for this year, that's the record we would like to have displayed because that's that employee's most recent record.

Is this possible?