mercredi 24 juin 2009

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.

mardi 24 février 2009

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.

jeudi 29 janvier 2009

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 !