February 17, 2014 at 8:20 pm
My query is as below which works perfectly fine:
SELECT
[le].[id] AS [eventId]
,MAX(Convert (varchar(10), [le].[eventCreationTime], 104)) AS [Swipe Date]
,MAX(Convert (varchar(10), [le].[eventCreationTime], 108)) AS [Swipe Time]
,MAX(Replace(Replace([la].[addressTag], 'AccessEngine.Devices.',''),'.Event','')) AS [Reader Name]
,MAX([ls].[stateNumber]) AS [stateNumber]
,MAX([ls].[stateName]) AS [state Name]
,[First Name] = MAX(CASE WHEN [levt].[eventValueName] = 'FIRSTNAME' THEN [lev].[stringValue] END)
,Name = MAX(CASE WHEN [levt].[eventValueName] = 'NAME' THEN [lev].[stringValue] END)
,[Card NO] = MAX(CASE WHEN [levt].[eventValueName] = 'CARDNO' THEN [lev].[stringValue] END)
,[Person NO] = MAX(CASE WHEN [levt].[eventValueName] = 'PERSNO' THEN [lev].[stringValue] END)
,Department = MAX(CASE WHEN [levt].[eventValueName] = 'DEPARTMENT' THEN [lev].[stringValue] END)
FROM LogDetectorType AS ldt INNER JOIN
LogEvent AS le LEFT OUTER JOIN
LogState AS ls ON le.stateId = ls.Id INNER JOIN
LogAddress AS la ON le.addressId = la.ID ON ldt.ID = la.detectorTypeId LEFT OUTER JOIN
LogEvent2Value AS le2v ON le.ID = le2v.eventId LEFT OUTER JOIN
LogEventValueType AS levt INNER JOIN
LogEventValue AS lev ON levt.ID = lev.eventTypeId ON le2v.valueId = lev.Id
WHERE (stateName IN(@StateName))
AND ([le].[eventCreationTime] BETWEEN (@From) AND (@To))
AND (Replace(Replace([la].[addressTag], 'AccessEngine.Devices.',''),'.Event','') IN (@Reader))
GROUP BY le.ID, [le].[eventCreationTime]
ORDER BY [le].[eventCreationTime] DESC, MAX([le].[id])
I need to add a new AND condition for filter anAlias column [First Name]. When i do this, other alias columns like [Card NO]; [Name]; [Pers NO] are shown with blank results.
Kindly help me in filtering this.
Thanks in advance.
February 17, 2014 at 8:26 pm
If you want to filter for First Name, then you need to use [lev].[stringValue] in your where clause.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 17, 2014 at 8:39 pm
If i use stringvalue, other alias columns will be NULL or will not show any results.
February 17, 2014 at 8:43 pm
What is your goal in trying to filter by somebody's First name?
Do you have sample data and table definitions to help solve this problem you are running into?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply