CTE Query

[Bimith Kunhiraman]

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

[Erland Sommarskog]

Bimith Kunhiraman (bimi...@gmail.com) writes:
> 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.

WHERE mnmIPDetails IS NOT NULL?

Please keep in mind the people you are talking have no knowledge of your
table or business problem? If my out-of-the blue guess does not answer
your question, my advice is that you post 2) CREATE TABLE statements for
your table(s), 2) INSERT statements with sample data, enough to illustrate
all angles of the problem. 3) The expected result given the sample data. 4)
A short description of the business rules why you want result, 5) The output
from "SELECT @@version" so that we know which version of SQL Server you are
using.