Help needed with the SQL query

Transact-SQL
1

Hi,
I have a Employee and EmployeeHistory tables something like below:

Declare @Employee table
(CompanyName varchar(100),
EmployeeCode varchar(100),
EmployeeName varchar(100),
Postition varchar(100)
)
insert @Employee
Select
'Jabra Pty Ltd' ,'4000' ,'Craig' ,'Manager'

--Select * from @Employee

Declare @EmployeeHistory table
(HistoryUno int,
HistoryDatetime Datetime,
CompanyName varchar(100),
EmployeeCode varchar(100),
EmployeeName varchar(100),
Postition varchar(100)
)
insert @EmployeeHistory
Select 100, '2017-08-01','Jabra Pty Ltd' ,'1010' ,'Alex' ,'Manager'
union
Select 101, '2017-12-10','Jabra Pty Ltd' ,'2020' ,'Steve' ,'Manager'
union
Select 102, '2018-07-01','Jabra Pty Ltd' ,'3030' ,'Ricky' ,'Manager'

--Select * from @EmployeeHistory

By joining the above two tables i would like my result set to display as below:

image

Any ideas how to achieve this?

Thanks,
ravi

2

This:

select a.historydatetime
      ,a.employeecode as historyvalue
      ,a.employeename as historydescription
      ,isnull(lead(a.employeecode,1) over(order by a.historydatetime),b.employeecode) as currentvalue
      ,isnull(lead(a.employeename,1) over(order by a.historydatetime),b.employeename) as currentdescription
  from @employeehistory as a
       cross apply @employee as b
 order by a.historydatetime
;

or this:

with cte
  as (select historydatetime
            ,employeecode
            ,employeename
            ,-row_number() over(order by historydatetime desc) as rn
        from @employeehistory
      union all
      select cast('9999-12-31 23:59:59.997' as datetime) as historydatetime
            ,employeecode
            ,employeename
            ,0 as rn
        from @employee
     )
select a.historydatetime
      ,a.employeecode as hostoryvalue
      ,a.employeename as historydescription
      ,b.employeecode as currentdescription
      ,b.employeename as currentdescription
  from cte as a
       inner join cte as b
               on b.rn=a.rn+1
 order by a.historydatetime
;
3

Thanks bitsmed. That worked perfectly.

4

Hi bitsmed,
I have one more scenario which is similar to the above and when i am trying your solution for this scenario, I am getting doubling,tripling of rows. I am not able to understand where i am doing wrong. Here is the new scenario:

Declare @Client table
(ClientCode varchar(100),
ClientName varchar(100),
NameID varchar(100)
)
insert @Client
Select 'JOY100' ,'Joy PTY Ltd name change' ,'84202'
union
Select 'TST100', 'TEST CHANGE 3','87414'

--Select * from @Client

Declare @ClientHistory table
(HistoryUno int,
HistoryDatetime Datetime,
ClientName varchar(100),
NameID varchar(100)
)
insert @ClientHistory
Select 100, '2017-08-01','Joy PTY Ltd' ,'84202'
union
Select 250, '2017-12-10','TEST' ,'87414'
union
Select 326, '2018-07-01','TEST CHANGE 1' ,'87414'
union
Select 487, '2018-11-01','TEST CHANGE 2' ,'87414'

--Select * from @ClientHistory
By joining the above two tables i would like my result set to display as below:

Capture1111

This is what i am trying in my query, but i am not get the desired result:
select a.historydatetime
,a.ClientName as historydescription
,isnull(lead(a.ClientName,1) over(order by a.historydatetime),b.ClientName) as currentdescription
from @ClientHistory as a
cross apply @Client as b
order by a.historydatetime

Am i doing wrong anywhere in the above query?

Thanks,
Ravi

5

This might work for you:

select a.historydatetime
      ,a.clientname as historyvalue
      ,isnull(lead(a.clientname,1) over(partition by a.nameid
                                            order by a.historydatetime
                                       )
             ,b.clientname
             )
       as currentvalue
      ,b.clientcode
  from @clienthistory as a
       inner join @client as b
               on b.nameid=a.nameid
 order by a.historydatetime
;
6

Thanks bitsmed. That worked perfectly.

Thanks,
Ravi

7

hi

A different way of doing
using
right join

i dont have sql server 2012 so cannot use lead lag functions

Hope it helps .. great
:slight_smile:
:slight_smile:

drop create data 
USE tempdb 

go 

DECLARE @Client TABLE 
  ( 
     clientcode VARCHAR(100), 
     clientname VARCHAR(100), 
     nameid     VARCHAR(100) 
  ) 

INSERT @Client 
SELECT 'JOY100', 
       'Joy PTY Ltd name change', 
       '84202' 
UNION 
SELECT 'TST100', 
       'TEST CHANGE 3', 
       '87414' 

DECLARE @ClientHistory TABLE 
  ( 
     historyuno      INT, 
     historydatetime DATETIME, 
     clientname      VARCHAR(100), 
     nameid          VARCHAR(100) 
  ) 

INSERT @ClientHistory 
SELECT 100, 
       '2017-08-01', 
       'Joy PTY Ltd', 
       '84202' 
UNION 
SELECT 250, 
       '2017-12-10', 
       'TEST', 
       '87414' 
UNION 
SELECT 326, 
       '2018-07-01', 
       'TEST CHANGE 1', 
       '87414' 
UNION 
SELECT 487, 
       '2018-11-01', 
       'TEST CHANGE 2', 
       '87414'
SQL using right join 
; WITH cte 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT NULL)) AS rn, 
                b.historydatetime, 
                b.clientname                AS HistoryValue, 
                a.clientname, 
                a.clientcode 
         FROM   @Client a 
                JOIN @ClientHistory b 
                  ON a.nameid = b.nameid) 
SELECT b.historydatetime, 
       b.historyvalue, 
       Isnull(CASE 
                WHEN b.rn <> 1 THEN a.historyvalue 
                ELSE b.clientname 
              END, b.clientname) 
FROM   cte a 
       RIGHT JOIN cte b 
               ON a.rn = b.rn + 1
Result

image
image.png731×239 78.7 KB