Bimith Kunhiraman
unread,Oct 8, 2020, 4:45:51 AM10/8/20Sign in to reply to author
Sign in to forward
You do not have permission to delete messages in this group
Sign in to report message as abuse
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
to
using with cte , i need to skip row when the mnmIPDetails column has null values which means each user login Ttime two rows will be added in this case i need only one row which containing mnmIPDetails. Below written script will fetch all records.
How can i achive it, Kindly help me
CREATE PROCEDURE [dbo].[Login_Logout_Rpt]
AS
BEGIN
DECLARE @ANHOURBACK DATETIME
SET @ANHOURBACK= (SELECT DATEADD (hour, -4 ,GETDATE()))
PRINT @ANHOURBACK
;WITH CteUserInOut(RowNUm,DtDate,Access_ID,Access_Name,Access_Profile,Access_Type,Access_Country,Access_SourceIP,Access_SourceHost,Access_LoginTimeStamp,Access_LogOutTimeStamp,InOut,mnmUserAccessType)
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY UserInOut.mnmUser,UserInOut.DtTime)AS RowNUm,CAST(UserInOut.DtTime AS DATE)AS DtDate,
UserInOut.mnmUserAccessType as Access_Type,'IND' AS Access_Country,
CASE WHEN CHARINDEX(',',UserInOut.mnmIPDetails,1) > 0 THEN reverse(left(reverse(UserInOut.mnmIPDetails), charindex(',', reverse(UserInOut.mnmIPDetails)) -1))ELSE UserInOut.mnmIPDetails END as Access_SourceIP 'NULL' as Access_SourceHost,InOut,UserInOut.mnmUser,UserInOut.DtTime from dbo.UserInOut INNER JOIN dbo.DealerAddMrv where UserInOut.DtTime >= @ANHOURBACK
AND UserInOut.mnmUserAccessType <> 'MOB'
)
SELECT U1.Access_ID,U1.Access_Name,U1.Access_Profile,U1.Access_Type,U1.Access_Country,U1.Access_SourceIP,U1.Access_SourceHost,
U1.DtDate AS Access_LoginTimeStamp,U2.DtDate AS Access_LogOutTimeStamp
FROM
(SELECT * FROm CteUserInOut WHERE InOut = 'IN') AS U1
JOIN (SELECT * FROm CteUserInOut WHERE InOut = 'OUT') AS U2
ON U2.RowNUm = U1.RowNUm+1 AND U1.Access_ID = U2.Access_ID AND U1.DtDate = U2.DtDate
END
GO