So some times you may have a list and not want to expose all of your fields to the user.  Well this is easy enough with a list but what about edit and insert.  Well today i stumbled apon the easy way and it involves the use of designer. I have attached a word doc with the content fromthe attached link in the case this disappears some day. 

But the credit goes to these guys...

http://blah.winsmarts.com/2007-5-Customize_the_-and-quot;NewFormaspx-and-quot;_page_for_a_SharePoint_List.aspx

CustomizeNewListItem.docx (70.29 KB)

BEWARE DON"T DO THIS UNTIL READING THE FOLLOWING

DONT RENAME EXISTING FORMS LEAVE THEM BE...

http://blogs.msdn.com/dszabo/archive/2007/02/20/custom-list-newform-aspx-ruined-in-wss-3-0.aspx

 

http://support.microsoft.com/?id=935504

 

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)