Concatenated results in a select in SQL Server

My colleague wanted to have concatenated result in a query like this :

Class Participants

C1 John, Albert, Frank, Neda, Abdelkader, Ismaël

C2 Liisi, Daniel, Didier, Dimitri,

...

There are some solutions out there like with Using a CTE, doing a simple concatenation in a variable.

My solution is simpler and direct :

Do your standard select with XML Explicit

SELECT 1 AS TAG,NULL AS PARENT,FirstName AS [Users!1!FirstName] FROM Users FOR XML EXPLICIT

this bring a result like

<Users FirstName="John"/><Users FirstName="Albert"/><Users FirstName="Frank"/><Users FirstName="Neda"/><Users FirstName="Abdelkader"/><Users FirstName="Ismaël"/><Users FirstName="Liisi"/><Users FirstName="Daniel"/><Users FirstName="Didier"/><Users FirstName="Dimitri"/>

I specify XML Explicit so that I can replace the tag's name when I insert the query as an inner query and be sure that it's really the tag name !

SELECT ClassName,

REPLACE( REPLACE((SELECT 1 AS TAG,NULL AS PARENT,FirstName AS [Users!1!FirstName] FROM Users, classes_users WHERE Users.userid=Classes_users.Users AND classes_users.classes = classId FOR XML EXPLICIT ),'',', ') AS Users

FROM classes

And it works :

The unwanted side effect is that now you should remove the trailing , at the end.

Could be done with a substring or in the application that fetches the data.

Delete workspace on Team Foundation Server

How is it possible to delete a workspace on a machine who's main user account is locked ?
1. Get the whole list of workspaces :

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE>tf workspaces 
/server:http://servername:serverport/ /owner:*

this gives a list like :

Workspace                          Owner      Computer   Comment
---------------------------------- ---------- ---------- ---------------------
CIS000274                          USER1      CIS000274
CIS000605                          USER2      CIS000605
CIS000957                          USER3      CIS000957
CIS001242                          GONEUSR    CIS001242
CIS001242                          USER4      CIS001242
CIS001317                          USER5      CIS001317
...

2. Find the one you want to delete.
3. To delete the workspace for user GONEUSR, I only need to do :

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE>tf workspace /delete 
CIS001242;DOMAIN\GONEUSR /server:http://servername:serverport/

A deleted workspace cannot be recovered.
Workspace 'CIS001242;DOMAIN\GONEUSR' on server 'http://servername:serverport/' has
151 pending change(s).
Are you sure you want to delete the workspace? (Yes/No) y

And it's done.

DAYS360 in SQL Server

Ok, so this function is quite interesting in Excel but doesn't exists in SQL Server.
I couldn't find one quickly on the web so I did it myself.

What's the goal ?
Determine the number of days covering a period. This number is often used for accounting because it's based on a year of 360 days and on months of 30 days.
Ex : 2007/11/01 to 2009/04/18 = 527 in DAYS360 but 534 "real" days.
And this method works the same way for any period :
days360(2007/01/01,2007/03/01) = days360(2008/01/01,2008/03/01).

Here is my solution :

DROP FUNCTION DAYS360
GO

CREATE FUNCTION DAYS360(@DATE1 DATETIME,@DATE2 DATETIME,@METHOD BIT = 0)
RETURNS INT 
AS 
BEGIN

DECLARE @dFrom DATETIME,@dTo DATETIME
DECLARE @y INT,@m INT,@d INT,@totalDays INT
SELECT @y=0,@m=0,@d=0

--prepare date that will be used. the smallest goes to @dFrom & the biggest to @dTo
IF @DATE1<@DATE2
 SELECT @dFrom=@DATE1,@dTo=@DATE2
ELSE
 SELECT @dFrom=@DATE2,@dTo=@DATE1

IF @METHOD=1 -- EU MODE : Cfr Excel help
BEGIN 
 IF day(@dFrom)=31 SET @dFrom = dateadd(d,-1,@dFrom)
 IF day(@dTo)=31 SET @ dTo = dateadd(d,-1,@ dTo)
END 

--Get year
SET @y = datediff(yy,@dFrom,@dTo)
--Get month
SET @m = month(@dTo)
--if month from biggest is smaller than month from smallest, we add 12months and remove 1 year
IF @m<month(@dFrom)
BEGIN
 SET @y=@y-1
 SET @m=@m+12
END
SET @m=@m - month(@dFrom)
--Get day
SET @d=day(@dTo)
--if day from biggest is smaller than day from smallest, we add 30 days and remove 1 month. 
--(We remove One year if the value for months was 0)
IF @d<day(@dFrom)
BEGIN
 IF @m=0
 BEGIN
  SET @y=@y-1
  SET @m=@m+12
 END
        SET @m=@m-1
 SET @d=@d+30
END
SET @d=@d - day(@dFrom)

--Compute total days
SET @totalDays=(@Y*360)+(@m*30)+@d

RETURN  @totalDays
 
END

Call this with :

SELECT dbo.days360('20070101','20070301',DEFAULT)

And it returns 60 !