Simple function to get the first day of the week.  It seems to perform nicly also.

ALTER FUNCTION [dbo].[F_Get_Sunday] (@MidWeekDate DateTime)

RETURNS DateTime AS

BEGIN

DECLARE @WeekCommence DateTime

SET @WeekCommence = DateAdd(d, -((@@DATEFIRST + DatePart(dw, @MidWeekDate) -1) % 7), @MidWeekDate)

RETURN CAST(FLOOR( CAST(@WeekCommence as FLOAT)) AS DATETIME)

END

 

Here is a simple example of a pivot function in SQL 2005

 

CREATE TABLE #UserTime

(

UserName VARCHAR(50),

Class VARCHAR(50),

Week_Date DATETIME,

Hours DECIMAL(5,2),

CategoryName VARCHAR(50),

IsBillable BIT,

CategoryAppr VARCHAR(5)

)

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-02-17',40,'Client Billable', 1,'CB'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-02-24',16.00 ,'Administration' , 0,'AD'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-02-24',3.00,'Client Billable' ,1,'CB'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-02-24',21.00,'PTO' ,0,'PTO'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-03-02',40.00,'Client Billable' ,1,'CB'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-03-09',36.00 ,'Client Billable',1,'CB'

INSERT INTO #UserTime SELECT 'SYOKIEL','MPLS','2008-03-09',4.00 ,'PTO',0,'PTO'

Select * from #UserTime

--SELECT Final.UserName,Final.Week_Date, SUM(Final.CB) ,SUM(Final.AD),SUM(Final.PTO)

--from

--(SELECT UserName,Week_Date, [CB] as CB, [AD] as AD, [PTO] as PTO

-- FROM #UserTime AS P

-- PIVOT

-- (

-- Sum(P.Hours)

-- FOR P.CategoryAppr

-- IN([CB], [AD], [PTO])

-- ) AS Pivoted

--) as Final

--Group By Final.UserName,Final.Week_Date

SELECT UserName, Week_Date, SUM([0]) as NonBillable, SUM([1]) as Billable

FROM #UserTime AS P

PIVOT

(Sum(P.Hours)

FOR P.IsBillable

IN([0], [1])

) AS Pivoted

Group By UserName, Week_Date

DROP TABLE #UserTime

Heres's a simple way to pass in a xml chunk that you can easiy put int  atemp table to join to.

DECLARE @X xml
SET @X = '<root><value id=''12196825'' /></root>'

SELECT List.c.value('.','int')
FROM @X.nodes('/root/value/@id') List(c)

I wrote a simple page to test my database on a remote server.  Since i can't connect remotly this little page lets me run sql commands via a web browser and tst the conn string.

TestDB.zip (2.46 KB)

I thought this would be useful in the future so i'm adding it...

DECLARE @LoadList as VARCHAR(MAX)
SET @LoadList = '9681549,9681554'

CREATE TABLE #LoadNumbers
(
    LoadNum int
)

DECLARE @LoadNum varchar(10), @Pos int

SET @LoadList = LTRIM(RTRIM(@LoadList))+ ','
SET @Pos = CHARINDEX(',', @LoadList, 1)

IF REPLACE(@LoadList, ',', '') <> ''
BEGIN
    WHILE @Pos > 0
    BEGIN
        SET @LoadNum = LTRIM(RTRIM(LEFT(@LoadList, @Pos - 1)))
        IF @LoadNum <> ''
        BEGIN
            INSERT INTO #LoadNumbers (LoadNum) VALUES (CAST(@LoadNum AS int)) --Use Appropriate conversion
        END
        SET @LoadList = RIGHT(@LoadList, LEN(@LoadList) - @Pos)
        SET @Pos = CHARINDEX(',', @LoadList, 1)

    END
END

SELECT * FROM #LoadNumbers

DROP TABLE #LoadNumbers