Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31370 > unrolled thread

CTE Query

Back to article view | Back to microsoft.public.sqlserver.programming

Contents

  CTE Query Bimith Kunhiraman <bimithgmc@gmail.com> - 2020-10-08 01:45 -0700
    Re: CTE Query Erland Sommarskog <esquel@sommarskog.se> - 2020-10-08 19:56 +0200

#31370 — CTE Query

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

[toc] | [next] | [standalone]

#31371

Bimith Kunhiraman (bimithgmc@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.

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web