What about *last* Sunday?

An earlier (much earlier) post covered how to detect the next upcoming date that is a certain day of the week, and I just had a similar request for the last date it was a certain day of the week and cooked up this:

CREATE FUNCTION dbo.cust_funct_last_weekday
(@weekday INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @date DATETIME
SET @date=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)

IF DATEPART(dw,@date)=@weekday
SET @date=DATEADD(day,-1,@date)
WHILE DATEPART(dw,@date)<>@weekday
SET @date=DATEADD(day,-1,@DATE)

RETURN @date
END

Note that this function accepts an integer and returns a date, so you specify the numeric day of the week (e.g. 1 = Sunday, 2 = Monday...7 = Saturday, although this is actually just the US default for a SQL setting called @@DATEFIRST (MSDN page)), and it returns the last Date that was that day of the week. If you specify the same day of the week as today, it returns the same day of the week from last week (i.e. it will not return today). Also, note that it returns the date with a midnight (00:00:00) time component, so you might need to massage your comparison to remove specific times, e.g. "WHERE core_occurrence.occurrence_start_time = dbo.cust_funct_last_weekday(1)" becomes "WHERE DATEADD(day,DATEDIFF(day,0,core_occurrence.occurrence_start_time),0) = dbo.cust_funct_last_weekday(1)".

This can be useful for some automated reporting, such as "I want a report in my inbox Monday morning showing our attendance numbers for Sunday's services" or "How many kids attended the Wednesday night service?", and a host of other "last N-day" questions (possibly even in lists...).

Security on a Portal

So the question came up for how to view the entire security model for the Pages and Module Instances of a Portal, and I realized that while we have a handful of security reports for other things, we don't have a generic query pulling security on those items. So I spent an hour and a half (probably shouldn't have taken me that long, but I'm a bit out of practice) and what I came up with shows the page structure (in order, just like in Pages) with the permissions for the page, and within each page it shows the modules on that page (in their template frames and in order within those frames) and the permissions on those module instances. It shows permissions as "Roles | Persons" in case you have person-level security on anything, and each of those is detailed as "Name (permissions)" where the permissions are outlined as “V”=View, “E”=Edit, ”S”=Edit Security, ”P”=Edit People, “M”=Edit Modules, “N”=Edit Notes, “A”=Approve, and “R”=Edit Registrations.

DECLARE @portalID INT,@a INT
SET @portalID=1

-- Pages
DECLARE @pagetable TABLE (page_id INT, page_name VARCHAR(500),permission_list VARCHAR(MAX),ordering INT)

;WITH pagetree (
Root
, Parent
, Name
, Level
, ID
, Branch
) AS (
SELECT
ppp.page_name
, CONVERT(VARCHAR(100),'')
, ppp.page_name
, 0
, ppp.page_id
, CONVERT(VARCHAR(MAX),REPLACE(STR(CONVERT(VARCHAR,ISNULL(NULLIF(ppp.page_order,2147483647),9999)), 5, 0), ' ', '0')+'.'+CONVERT(VARCHAR,ppp.page_id))
FROM
port_portal_page AS ppp
WHERE
ppp.page_id=(SELECT TOP 1 default_page_id FROM port_portal WHERE portal_id=@portalID)
UNION ALL
SELECT
tree.Root
, tree.Name
, CONVERT(VARCHAR(100),(replicate('-',tree.level+1))+ppp.page_name)
, tree.Level + 1
, ppp.page_id
, CONVERT(VARCHAR(MAX),tree.Branch + '.' + REPLACE(STR(CONVERT(VARCHAR,ISNULL(NULLIF(ppp.page_order,2147483647),9999)), 5, 0), ' ', '0')+'.'+CONVERT(VARCHAR,ppp.page_id))
FROM
port_portal_page AS ppp
JOIN pagetree AS tree ON ppp.parent_page_id = tree.ID
)

INSERT INTO @pagetable
SELECT
pt.ID AS page_id
,pt.Name
,ISNULL(SUBSTRING(
(SELECT DISTINCT
', '+sr.role_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=5) THEN 'M' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN secu_role AS sr WITH(NOLOCK) ON sr.role_id=sp.subject_key AND sp.subject_type=0
WHERE
sp.object_type=1
AND sp.object_key = pt.ID
FOR XML PATH(''))
,3,5000),'')
+ISNULL(' | '+SUBSTRING(
(SELECT DISTINCT
', '+cp.first_name+' '+cp.last_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=5) THEN 'M' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN core_person AS cp WITH(NOLOCK) ON cp.person_id=sp.subject_key AND sp.subject_type=1
WHERE
sp.object_type=1
AND sp.object_key = pt.ID
FOR XML PATH(''))
,3,5000),'')
,ROW_NUMBER() OVER (ORDER BY @a )
FROM
pagetree AS pt
ORDER BY
Root
, branch


-- Modules
DECLARE @moduletable TABLE (page_id INT, module_instance_id INT,module_title VARCHAR(500),module_type VARCHAR(500), module_url VARCHAR(500),template_frame_name VARCHAR(500),module_order INT, permission_list VARCHAR(MAX))

INSERT INTO @moduletable
SELECT
pmi.page_id
,pmi.module_instance_id
,pmi.module_title
,pm.module_name
,pm.module_url
,pmi.template_frame_name
,pmi.template_frame_order
,ISNULL(SUBSTRING(
(SELECT DISTINCT
', '+sr.role_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=4) THEN 'P' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=6) THEN 'N' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=7) THEN 'A' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=0 AND subject_key=sp.subject_key AND operation_type=8) THEN 'R' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN secu_role AS sr WITH(NOLOCK) ON sr.role_id=sp.subject_key AND sp.subject_type=0
WHERE
sp.object_type=2
AND sp.object_key = pmi.module_instance_id
FOR XML PATH(''))
,3,5000),'')
+ISNULL(' | '+SUBSTRING(
(SELECT DISTINCT
', '+cp.first_name+' '+cp.last_name+' ('
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=0) THEN 'V' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=1) THEN 'E' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=2) THEN 'S' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=4) THEN 'P' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=6) THEN 'N' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=7) THEN 'A' ELSE '' END)
+(CASE WHEN EXISTS(SELECT TOP 1 1 FROM secu_permission WITH(NOLOCK) WHERE object_type=sp.object_type AND object_key=sp.object_key AND subject_type=1 AND subject_key=sp.subject_key AND operation_type=8) THEN 'R' ELSE '' END)
+')'
FROM
secu_permission AS sp WITH(NOLOCK)
JOIN core_person AS cp WITH(NOLOCK) ON cp.person_id=sp.subject_key AND sp.subject_type=1
WHERE
sp.object_type=2
AND sp.object_key = pmi.module_instance_id
FOR XML PATH(''))
,3,5000),'')
FROM port_module_instance AS pmi WITH(NOLOCK)
JOIN port_module AS pm WITH(NOLOCK) ON pm.module_id=pmi.module_id
WHERE page_id IN (SELECT page_id FROM @pagetable)

DECLARE @finaltable TABLE (ordering varchar(500), page_id INT,page_name VARCHAR(500),template_frame_name VARCHAR(500),module_instance_id INT, module_title VARCHAR(500),module_type VARCHAR(500), module_url VARCHAR(500),permission_list VARCHAR(MAX))
INSERT INTO @finaltable
SELECT REPLACE(STR(CONVERT(VARCHAR,pt.ordering), 9, 0), ' ', '0'),pt.page_id,pt.page_name,NULL AS template_frame_name,NULL AS module_instance_id, NULL AS module_title, NULL AS module_type, NULL AS module_url,pt.permission_list FROM @pagetable pt
UNION
SELECT (SELECT REPLACE(STR(CONVERT(VARCHAR,ordering), 9, 0), ' ', '0') FROM @pagetable WHERE page_id=mt.page_id)+CONVERT(VARCHAR,mt.module_order),mt.page_id,'zzzzzzzzzzzzz' AS page_name,mt.template_frame_name,mt.module_instance_id,mt.module_title,mt.module_type,mt.module_url,mt.permission_list FROM @moduletable mt

SELECT
page_id
,REPLACE(page_name,'zzzzzzzzzzzzz','') AS page_name
,template_frame_name
,module_instance_id
,module_title
,module_type
,module_url
,permission_list
FROM @finaltable
ORDER BY ordering

Event Discount Codes

On the same theme of rapidly reporting data from an Event, here's a sproc to report the Discount Codes that have been used in the current event. You can deploy it with a Report Grid From Query on the Event Details page (see Event Costs And Fees for how to set up a RGFQ on the Events page, just make sure you set Public Site = True since you dont need all the extras for this grid) with a single parameter of "profile=;".

CREATE PROCEDURE dbo.cust_sp_rgfq_evnt_applied_discount_codes
@profile VARCHAR(20)
AS
SELECT DISTINCT
cp.person_id
,cp.last_name+', '+cp.first_name AS Person_Name
,edc.code_value AS [Discount Code Applied]
,(CASE WHEN ereg.date_created<ereg.date_modified THEN ereg.date_modified ELSE ereg.date_created END) AS [Date Last Modified]
FROM
evnt_registrant AS er
JOIN evnt_discount_code AS edc ON edc.discount_code_id=er.discount_code_applied
JOIN core_person AS cp ON cp.person_id=er.person_id
JOIN evnt_registration AS ereg ON ereg.registration_id=er.registration_id
WHERE er.profile_id=@profile
ORDER BY [Discount Code Applied],Person_Name

Event Costs and Fees

Just cooked this up for one of our trainers, but it’s something I think a lot of admins may end up wanting once they start to see it, so here’s the stuff needed for it.

CREATE PROCEDURE dbo.cust_sp_evnt_costs_and_fees
@profile INT
AS
SELECT
'Cost: '+ec.title AS [Cost or Fee Name]
,COUNT(*) AS [Number of Paid Registrants]
FROM
evnt_registrant_cost AS erc
JOIN evnt_registrant_cost_payment AS ercp ON ercp.registrant_cost_id=erc.registrant_cost_id
JOIN evnt_cost AS ec ON ec.cost_id=erc.cost_id
WHERE erc.profile_id = @profile
AND erc.paid=1
GROUP BY ec.title
UNION
SELECT
'Fee: '+ef.title+ISNULL(': '+NULLIF(erf.selected_value,''),'') AS [Cost or Fee Name]
,SUM(erf.quantity) AS [Number of Paid Registrants]
FROM
evnt_registrant_fee AS erf
JOIN evnt_registrant_fee_payment AS erfp ON erfp.registrant_fee_id=erf.registrant_fee_id
JOIN evnt_fee AS ef ON ef.fee_id=erf.fee_id
WHERE erf.profile_id = @profile
AND erf.paid=1
GROUP BY ef.title,erf.selected_value

This is the setup in Pages (explanation below):

Explanation:
On the Event Tags page's Main Content tab, add a new module.
Per the instructions in the image, drag the new module up one spot and back down (to force the program to recognize that it should be at the bottom of the page)
Open the new module and change the type to Report Grid From Query (it's in the Reporting section).
Set the Parameter and Query options as shown in the image (don't forget the semi-colon at the end of the parameter).
Refresh the cache and you're good to go.

What this does is use a Report Grid From Query at the bottom of the Event details page showing a count of how many of each cost and fee (it separates out fees by selected values, so if you’re doing T-shirts for $10 each, you’ll see something like “Fee: T-Shirt: X-Large……3”) have been selected, restricted to the current event (it does not roll up sub-events). It’s just an aggregate count, and it only includes Paid costs & fees, but gives you a quick count of how many vegetarian meals to order, or whatnot.

Facebook: I Like Arena

Facebook: 500 million users, decent API, social media at its best, and one of the fastest communications tools out there for allowing your Membership to quickly reach each other with information about your Ministry. So the first step in getting into that world is the ubiquitous "Like" button.

To implement this, you'll need to do two things. First, you'll need to register your "app" (Arena-powered website) with Facebook by logging in to your church's Facebook account (using your personal account makes it your app, not the organization's) and going to: http://developers.facebook.com/setup/.

Once you've registered your app and have your AppID, add an Advanced HTML Text module to any page of your site, and copy this code into it, replacing the 'YOUR APP ID HERE' with... you guessed it: your app id (but keep the single-quotes around the ID, they're important).

<div id="fb-root"></div>
<script src="http://connect.facebook.net/en_US/all.js" ></script>
<script>
window.fbAsyncInit = function () {
FB.init({ appId: 'YOUR APP ID HERE', status: true, cookie: true,xfbml: true});
};
(function () {
var e = document.createElement('script');
e.type = 'text/javascript';
e.src = document.location.protocol + '//connect.facebook.net/en_US/all.js';
e.async = true;
document.getElementById('fb-root').appendChild(e);
} ());
</script>
<fb:like layout="button_count" width="90"></fb:like>

And there's your Like button!
You can add this code directly to your template (or you might want to just add a specific content area to the template and add an Adv. HTML Text module to that content area in the Template inside Arena so you can manage security on it), since it intelligently "likes" the current dynamic URL (right down to the query string, so it can even "like" events or promotions).

Admin's Friend: Javascript

Not so much SQL this time, but still super useful for an admin: Add this code to the Welcome Message section of your Login Logout module when using the Arena Basic template to add two useful links at the top: Edit This Page (takes you to the Pages view for the current page) and Refresh Cache (...exactly that).

<script type="text/javascript">
function RefreshCache() {
var rr = document.location.href

if (rr.indexOf("?")>0)
window.location = rr + "&refreshcache=true";
else
window.location = rr + "?refreshcache=true";
}
function getQueryVariable(variable) {
var query = window.location.search.substring(1);
var vars = query.split("&");
for (var i=0;i<vars.length;i++) {
var pair = vars[i].split("=");
if (pair[0] == variable) {
return pair[1];
}
}
return 3;
}
function EditPage() {
window.location = "default.aspx?page=34&pageid=" + getQueryVariable("page");
}
</script>
<b><a href="javascript:EditPage()">|Edit This Page|</a>&nbsp;&nbsp;&nbsp;<a href="javascript:RefreshCache()">|Refresh Cache|</a></b>&nbsp;&nbsp;&nbsp;Welcome ##nickname## ##lastname##.

Staff Birthdays

The goal: show all staff members whose birth days are either within the last week (previous 7 days) or in the coming month (next 30 days), with past birthdates slightly greyed out. The mechanism: HTML From Stored Procedure. The result:

CREATE PROC dbo.cust_sp_staff_birthdays_html
AS

DECLARE @datatable TABLE (nick_name VARCHAR(50),last_name VARCHAR(50),normalized_birth_date DATETIME,birth_day VARCHAR(50),person_guid UNIQUEIDENTIFIER)

INSERT INTO @datatable
SELECT
cp.nick_name
,cp.last_name
,DATEADD(year,DATEDIFF(YEAR,0,GETDATE()),DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) AS normalized_birth_date
,CONVERT(VARCHAR(4),MONTH(cp.birth_date)) +'/'+CONVERT(VARCHAR(4),DAY(cp.birth_date)) AS birth_day
,cp.guid
FROM
core_person AS cp
WHERE
staff_member = 1
AND birth_date<>'1/1/1900'
AND DATEADD(year,DATEDIFF(YEAR,0,GETDATE()),DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) BETWEEN DATEADD(WEEK,-1,GETDATE()) and DATEADD(MONTH,1,GETDATE())

IF (MONTH(GETDATE())=12)
INSERT INTO @datatable
SELECT
cp.nick_name
,cp.last_name
,DATEADD(year,DATEDIFF(YEAR,0,GETDATE())+1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) AS normalized_birth_date
,CONVERT(VARCHAR(4),MONTH(cp.birth_date)) +'/'+CONVERT(VARCHAR(4),DAY(cp.birth_date)) AS birth_day
,cp.guid
FROM
core_person AS cp
WHERE
staff_member = 1
AND birth_date<>'1/1/1900'
AND DATEADD(year,DATEDIFF(YEAR,0,GETDATE())+1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) BETWEEN DATEADD(WEEK,-1,GETDATE()) and DATEADD(MONTH,1,GETDATE())

IF (MONTH(GETDATE())=1)
INSERT INTO @datatable
SELECT
cp.nick_name
,cp.last_name
,DATEADD(year,DATEDIFF(YEAR,0,GETDATE())-1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) AS normalized_birth_date
,CONVERT(VARCHAR(4),MONTH(cp.birth_date)) +'/'+CONVERT(VARCHAR(4),DAY(cp.birth_date)) AS birth_day
,cp.guid
FROM
core_person AS cp
WHERE
staff_member = 1
AND birth_date<>'1/1/1900'
AND DATEADD(year,DATEDIFF(YEAR,0,GETDATE())-1,DATEADD(YEAR,-(DATEDIFF(YEAR,0,cp.birth_date)),cp.birth_date)) BETWEEN DATEADD(WEEK,-1,GETDATE()) and DATEADD(MONTH,1,GETDATE())

SELECT ISNULL(REPLACE(REPLACE(
'<style type="text/css">
a.pastdate{color:#888888;font-family: Verdana, Arial, Helvetica, sans-serif;font-size: 10px;text-decoration: none;}
a.foredate{color:#000000;font-family: Verdana, Arial, Helvetica, sans-serif;font-size: 10px;text-decoration: none;}
</style>
<table cellspacing="2" cellpadding="0" class="smallText">'+
'<tr><td colspan="2"><h2>Staff Birthdays</h2></td></tr>'+
(SELECT
'<tr >
<td width="160"><a href="default.aspx?page=7&guid='+ISNULL(CONVERT(VARCHAR(50),person_guid),'')+'" style="text-decoration:none;" class="'+(CASE WHEN normalized_birth_date < DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0) THEN 'pastdate' ELSE 'foredate' END)+'" >'+ISNULL(nick_name,'')+' '+ISNULL(last_name,'')+'</a></td>
<td width="30"><a href="default.aspx?page=7&guid='+ISNULL(CONVERT(VARCHAR(50),person_guid),'')+'" style="text-decoration:none;" class="'+(CASE WHEN normalized_birth_date < DATEADD(DAY,(DATEDIFF(DAY,0,GETDATE())),0) THEN 'pastdate' ELSE 'foredate' END)+'" >'+ISNULL(birth_day,'')+'</a></td>
</tr>'
FROM @datatable
ORDER BY normalized_birth_date, last_name,nick_name
FOR XML PATH(''))
+'</table>'
,'&lt;','<'),'&gt;','>'),'') AS [html]