locked
Is it possible to display records based on certain criteria? RRS feed

  • Question

  • User1216627406 posted

    The way the current stored procedure works is that it displays records under 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 (currentYear, previousYear 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 currentYear, 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 my stored procedure.

    Many thanks in advance for your assistance.

    I have not been to think up a solution.

    ALTER PROCEDURE [dbo].[GetByDates]
    @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

    Friday, February 21, 2020 3:14 PM

All replies

  • User-719153870 posted

    Hi simflex,

    simflex

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

    The description is little confusing, but according to above question, it;s possible to get the latest year value.

    For example:

    create table #temp(id int identity(1,1),[year] date)
    
    insert into #temp values('2020-2-24'),('2019-12-21'),('2018-9-24'),('2017-8-7')
    
    select * from #temp where year([year])=(select max(year([year])) from #temp)

    max(year([year])) means the latest year in this query.

    If i missunderstood anything, please feel free to tell and provide the table structure and expected result output if possible.

    Best Regard,

    Yang Shen

    Monday, February 24, 2020 3:30 AM
  • User1216627406 posted

    Yang,

    Thanks a lot for your response.

    If you look at the query, there are several tables:

    Employees(

    employeeID int PK autonumber,

    empID varchar(10),

    employeeName,

    empTitle,

    email

    )

    DateDetails(

    employeeID FK (foreign key to Employees)

    dateCreated datetime

    )

    Then the rest of the tables:

    SourceDetails

    SpouseDetails

    DividentDetails

    ReimbursementDetails

    Honoraria

    GiftDetails

    are related to Employees table by employeeID just like dateDetails table

    So, when data is submitted into the database, dateCreated is saved on the dateDetails table as well as employeeID which is also automatically generated in Employees table.

    Well, unfortunately, I can't insert an image screenshot from my PC but assuming that when I run the query from the stored proc I posted earlier with an emPID of '012345', it returns three records which looks similar to this:

    employeeName     email                        emptitle           empID   dateCreated    pastYears     previousYear        thisYear

    John Doe               jdoe@gmail.com      developer        012345   2020-01-01    0                     0                         1

    John Doe                jdoe@gmail.com     developer        012345   2019-01-01    0                     1                         0

    John Doe               jdoe@gmail.com      developer        012345   2013-01-01    1                     0                         0

    The sample data above shows that John Doe submitted a record this year (2020) but it also shows John submitted a record in 2019 (previousYear) and 2013 (pastYears).

    We would like to just display records for 2020 (thisYear has value of 1) and ignore the rest since thisYear with a value of 1 is the most recent year.

    If on the other hand, there is no record for this year (where thisYear has a vaue of 0) but last year which is the next closest year has a record (previousYear = 1) , even if pastYears has value of 1, as long as the closest year in the case previousYear has a value of 1 and current year (thisYear has a value of 0), return records only for previousYear and ignore the rest.

    Same with if pastYears is the only record with a value of 1, return that one only and ignore the rest.

    Is this possible?

    I hope this is much clearer and many thanks for your help.

    Maybe, I am overthinking it.

    Monday, February 24, 2020 8:25 PM
  • User-719153870 posted

    Hi simflex,

    Anyway, you want only one record(whose dateCreated year is the latest one) returned from your SP, right?

    Maybe you can put your original output into a temp table and then use the query i posted on this temp table, like below:

    ALTER PROCEDURE [dbo].[GetByDates]
    @empID varchar(50)
     AS
      BEGIN
       SET NOCOUNT OFF;
        SELECT * into #temp from
        (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)a
    
    select * from #temp where year([dateCreated])=(select max(year([dateCreated])) from #temp)

    Hope this could help.

    Best Regard,

    Yang Shen

    Tuesday, February 25, 2020 5:38 AM