Hi all,
we have two different apps (app1 and app2) ,
two different employee tables (#EmpTable_app1 and #EmpTable_app2)
and apps sharing same Database
Statement : we created Message from app1 and trying to disable from app2
step1 : App1 employee created an Message from app1 and flag IsApp2 is inserted as 0 in #Note table
step2 : later App2 employee disabled the Message from App2 for same clientID (1234) where MessageID = 1 through update statement
Since App2 employee is disabled the Message we need to retrieve the Emp_App2 as DisabledBy_EmployeeName
but we are retrieving as Emp_App1. Can someone plz help me on this ?
below is the DDL , DML and Query (which is use to retrieve )
DROP TABLE if exists #Note
DROP TABLE if exists #EmpTable_app1
DROP TABLE if exists #EmpTable_app2
CREATE TABLE #Note(
[MessageID] [int] IDENTITY(1,1) NOT NULL,
[ClientID] [int] NOT NULL,
[Message] varchar NULL,
[ModifiedDate] [datetime] NOT NULL,
[ModifiedBy] [int] NOT NULL,
[IsApp2] [bit] NULL,
[IsEnabled] [bit] NULL,
[Disabledby] [int] NULL,
[Disableddate] [datetime] NULL
)
INSERT INTO #Note ( ClientID ,Message ,ModifiedDate ,ModifiedBy ,IsEnabled, DisabledBy, Disableddate)
values (1234 , 'test' , '2024-02-04 15:13:41.537' , 1, 1 , null, null)
CREATE TABLE #EmpTable_app1
(
empid int primary key identity(1,1),
Empname varchar(100)
)
insert into #EmpTable_app1
values ('Emp_App1') -- since empid is identity column consider empid inserted as 1
CREATE TABLE #EmpTable_app2
(
empid int primary key identity(1,1),
Empname varchar(100)
)
insert into #EmpTable_app1
values ('Emp_App2') -- since empid is identity column consider empid inserted as 1
-- Disabling Message (for client 1234 where MessageID is 1 )
Update #Note
set isEnabled=0 ,
Disabledby=1,
Disableddate='2024-02-21 03:09:03.600'
where MessageID = 1
and ClientID = 1234
SELECT
N.MessageID,
N.Message,
N.ModifiedDate ,
CASE WHEN isnull(N.IsApp2,0)=0 THEN E.Empname
WHEN isnull(N.IsApp2,0)=1 THEN J.Empname END AS CreatedBy_EmployeeName,
isnull(N.IsEnabled,1) As IsEnabled,
CASE WHEN isnull(N.IsApp2,0)=0 THEN E1.Empname
WHEN isnull(N.IsApp2,0)=1 THEN J1.Empname END As DisabledBy_EmployeeName,
N.Disableddate
FROM
#Note N
LEFT JOIN #EmpTable_app1 E ON N.ModifiedBy = E.empid
LEFT JOIN #EmpTable_app1 E1 on N.Disabledby = E1.empid
LEFT JOIN #EmpTable_app2 J on N.ModifiedBy = J.empid and isnull(N.IsApp2,0)=1
LEFT JOIN #EmpTable_app2 J1 on N.Disabledby = J1.empid and isnull(N.IsApp2,0)=1
WHERE N.ClientID = 1234