SO i had the need to increment the ID in one of my lists and it was hairy to try to add and delete items via the API just to do this.  SO i broke down and looked into SQL server.  Open you content db and run a qry like this with you List GUID.  Then go ahead and run the update.  So far so good for me, let me know if i missed something.

SELECT  tp_NextAvailableId, *
FROM         AllLists
WHERE     (tp_ID = 'bcf068c4-d585-4168-8ba8-1f28eb58003c')

--Update AllLists
--set tp_NextAvailableId = 150
--where (tp_ID = 'bcf068c4-d585-4168-8ba8-1f28eb58003c')

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)