InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 06:42:28 GMT20http://www.sqlservercentral.com/Forums/Topic690605-1523-1.aspxThe second problem I am having is more complex. I am trying to figure out 2 things.1) How to get the column headings (and totals)2) How to have a report use dynamic columnsBelow is a sample table I created:[code]CREATE TABLE [dbo].[TimeSheetTest]( [ID] [int] IDENTITY(1,1) NOT NULL, [EmployeeCode] [varchar](50) NOT NULL, [ProjectCode] [varchar](50) NOT NULL, [HoursWorked] [decimal](18, 2) NOT NULL, [DateWorked] [datetime] NOT NULL, CONSTRAINT [PK_TimeSheetTest] PRIMARY KEY CLUSTERED ( [ID] ASC) ON [PRIMARY]) ON [PRIMARY][/code]Then, add the following data:[code]INSERT INTO TimeSheetTest SELECT 'Adam', 'P1', 8, '2009-01-01' UNION ALL SELECT 'Adam', 'P2', 1, '2009-01-01' UNION ALL SELECT 'Bob', 'P1', 5, '2009-01-01' UNION ALL SELECT 'Bob', 'P2', 3, '2009-01-01' UNION ALL SELECT 'Adam', 'P1', 8, '2009-01-03' UNION ALL SELECT 'Adam', 'P1', 8, '2009-01-05' UNION ALL SELECT 'Bob', 'P1', 7, '2009-01-05' UNION ALL SELECT 'Bob', 'P2', 1, '2009-01-05'[/code]Now, using the basic syntax for getting the xtab:[code]exec UberCrosstab 'EmployeeCode, ProjectCode','DateWorked','TimeSheetTest','HoursWorked','SUM'[/code]I get the following results (I dropped the time 12:00AM from below for readability):[code]EmployeeCode ProjectCode Jan 1 2009 Jan 3, 2009 Jan 5, 2009Adam P1 8.00 8.00 8.00Bob P1 5.00 NULL 7.00Adam P2 1.00 NULL NULLBob P2 3.00 NULL 1.00[/code]This part works as expected. However, I want to display Thu 01/01, Sat 01/03, Mon 01/05so, I created a field as follows:[code]SELECT *, LEFT(DATENAME(dw, DateWorked), 3) + ' ' + CONVERT(VARCHAR(5), DateWorked, 1) AS Header FROM TimeSheetTest[/code]Of course (since I cannot get the (query) to work), I do the following:[code]SELECT *, LEFT(DATENAME(dw, DateWorked), 3) + ' ' + CONVERT(VARCHAR(5), DateWorked, 1) AS Header INTO #temp FROM TimeSheetTestexec UberCrosstab 'EmployeeCode, ProjectCode','Header','#temp','HoursWorked','SUM'drop table #temp[/code]Now, the table appears with headings of what I want (Mon 01/05, Sat 01/03, Thu 01/01), but the problem is they are sorted by the text (M in Monday comes before S - Saturday, etc).[highlight=#ffff11]Q. How do I sort it the way I want?[/highlight]I am also trying to get all the dates in a date range, not just those that have data. This is not a problem, other than the data comes out sorted by Fri, Mon, Sat, Sun, Thurs, Tues, Wed.[code]SET NOCOUNT ONDECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMEDECLARE @ctr INTDECLARE @diff INTDECLARE @days TABLE ( WorkDay DATETIME PRIMARY KEY, WorkDayName VARCHAR(50), Counter INT)SELECT @StartDate = '2009-01-01'SELECT @EndDate = '2009-01-07'SELECT @diff = DATEDIFF(d, @StartDate, @EndDate)SELECT @ctr = 0WHILE @ctr <= @diff BEGIN INSERT INTO @days ( WorkDay, WorkDayName, Counter ) VALUES ( @StartDate + @ctr, LEFT(DATENAME(dw, @StartDate + @ctr), 3) + '. ' + LEFT(CONVERT(VARCHAR(8), @StartDate + @ctr, 1), 5), @ctr + 1 ) SET @ctr = @ctr + 1ENDSELECT * INTO #temp FROM @days d LEFT OUTER JOIN TimeSheetTest t ON d.WorkDay = t.DateWorkedexec UberCrosstab 'EmployeeCode, ProjectCode','WorkDayName','#temp','HoursWorked','SUM'drop table #temp[/code]Part 2 of the problem is to create a report that will have dynamic fields in the crosstab, and a total afterwards. I have not tried it, but I am assuming I can create another table variable or query that totals across for the period (in this case, one week) and join that with this data. That will get me the totals. The tricky part is the workdays. It might be 7. It might be 14 (paid every 2 weeks). It might be a little more if paid twice a month (1-15, 16-31 for some months, 16-28 or 29 for February, etc). It could be 28-31 days (depending again on the month). [highlight=#ffff11]Please tell me there is a way other than to create 31 columns and hide the ones that are not used.[/highlight]Thu, 02 Jul 2009 23:03:53 GMTadammenkeshttp://www.sqlservercentral.com/Forums/Topic690605-1523-1.aspxI am having a problem getting this to do what I want... First problem, I cannot seem to get the procedure to take a query as an argument in parenthesis as stated.To test, I just wrapped 'Table1' with '(SELECT * FROM Table1)'-- Incorrect syntax near ')'.The second problem is more complex and I will post another reply.Thu, 02 Jul 2009 22:05:00 GMTadammenkeshttp://www.sqlservercentral.com/Forums/Topic690605-1523-1.aspxI can't get past the syntax errors. I wish ServerCentral would fix their script upload feature because getting the scripts to work has proven to be a royal pain.-- Mark D Powell --Finally, got it working. I had to retype the lead into several of the lines which display as little boxes in the posting and had become invisible when I copied and pasted via Word. Notepad shows the garbage. Once removed the procedure compiled and appears to work.Tue, 05 May 2009 10:55:35 GMTMark D Powellhttp://www.sqlservercentral.com/Forums/Topic690605-1523-1.aspxI don't have experience using crosstabs, but shouldn't the results in your example be: TYPE RED BLUE GREEN A 2 0 0 B [b]1[/b] 0 0 C 0 1 0 D 0 0 1 Tue, 05 May 2009 09:11:59 GMTgjcolledgehttp://www.sqlservercentral.com/Forums/Topic690605-1523-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Crosstab/66452/">Simple Crosstab Procedure with Power</A>[/B]Sun, 05 Apr 2009 10:27:11 GMTcgruel