November 13, 2015 at 9:29 am
Hi ,
I have the following query (im new to SQL)
select min(a_Session.created) loginDateTime, max(a_Session.updated) logoutDateTime, upper(appName) appName, convert(varchar(20),a_Session.created,111) createdDate, upper(userName) userName,u_role.id
from a_Session with (nolock)
inner join u_Contactrole on a_session.employeeid = u_contactrole.empid
inner join u_role on u_contactrole.roleid = u_role.id
where a_Session.created < getdate()
and appName is not null
and userName is not null
and userName <> 'system administrator'
group by upper(appName), convert(varchar(20),a_Session.created,111), upper(username), u_role.id
order by upper(appName) asc, convert(varchar(20),a_Session.created,111) desc, upper(username) desc
It returns return the following
loginDateTime logoutDateTime appNamecreatedDateuserNameid
2015-11-13 09:48:10.4232015-11-13 09:48:10.423 LOGIN/?RETURNURL=/CRM2015/11/13SANDRA SHANKS21
2015-11-13 09:48:10.4232015-11-13 09:48:10.423 LOGIN/?RETURNURL=/CRM2015/11/13SANDRA SHANKS29
My aim is to return the following
loginDateTime logoutDateTime appNamecreatedDateuserNameid
2015-11-13 09:48:10.4232015-11-13 09:48:10.423 LOGIN/?RETURNURL=/CRM2015/11/13SANDRA SHANKS21,28
Any idea on how I can go about doing this ?
Thanks
November 13, 2015 at 9:46 am
There's a standard method for this: FOR XML PATH string concatenation. But first, can you run through this derivative of your query and replace the question marks with the correct table aliases?
SELECT
MIN(s.created) loginDateTime,
MAX(s.updated) logoutDateTime,
UPPER(?.appName) appName,
CAST(s.created AS DATE) createdDate,
UPPER(?.userName) userName,
r.id
FROM a_Session s
--------------------------------
INNER JOIN u_Contactrole c
ON s.employeeid = c.empid
INNER JOIN u_role r
ON c.roleid = r.id
--------------------------------
WHERE s.created < GETDATE()
AND ?.appName IS NOT NULL
AND ?.userName IS NOT NULL
AND ?.userName <> 'system administrator'
GROUP BY
UPPER(?.appName),
CAST(s.created AS DATE),
UPPER(?.username),
r.id
ORDER BY
UPPER(?.appName) ASC,
CAST(s.created AS DATE) DESC,
UPPER(?.username) DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 13, 2015 at 9:57 am
SELECT
MIN(s.created) loginDateTime,
MAX(s.updated) logoutDateTime,
UPPER(s.appName) appName,
CAST(s.created AS DATE) createdDate,
UPPER(s.userName) userName,
r.id
FROM a_Session s
--------------------------------
INNER JOIN u_Contactrole c ON s.employeeid = c.empid
INNER JOIN u_role r ON c.roleid = r.id
--------------------------------
WHERE s.created < GETDATE()
AND s.appName IS NOT NULL
AND s.userName IS NOT NULL
AND s.userName <> 'system administrator'
GROUP BY
UPPER(s.appName),
CAST(s.created AS DATE),
UPPER(s.username),
r.id
ORDER BY
UPPER(s.appName) ASC,
CAST(s.created AS DATE) DESC,
UPPER(s.username) DESC
November 13, 2015 at 10:07 am
Try this. It won't be far off:
SELECT
MIN(s.created) loginDateTime,
MAX(s.updated) logoutDateTime,
UPPER(s.appName) appName,
CAST(s.created AS DATE) createdDate,
UPPER(s.userName) userName,
x.IDlist
FROM a_Session s
CROSS APPLY (
SELECT IDlist = STUFF(
(SELECT ',' + r.id AS [text()]
FROM u_Contactrole c
INNER JOIN u_role r
ON c.roleid = r.id
WHERE s.employeeid = c.empid
ORDER BY r.id
FOR XML PATH(''))
, 1, 1, '' )
) x
WHERE s.created < GETDATE()
AND s.appName IS NOT NULL
AND s.userName IS NOT NULL
AND s.userName <> 'system administrator'
GROUP BY
UPPER(s.appName),
CAST(s.created AS DATE),
UPPER(s.username),
x.IDlist
ORDER BY
UPPER(s.appName) ASC,
CAST(s.created AS DATE) DESC,
UPPER(s.username) DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 13, 2015 at 10:10 am
Getting a error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ',' to data type int.
November 13, 2015 at 10:49 am
Suth (11/13/2015)
Getting a errorMsg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ',' to data type int.
In the APPLY block, cast r.id to varchar(n), where n is sufficient to cover values of id e.g. 2 or 3.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply