Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple Crosstab Procedure with Power


Simple Crosstab Procedure with Power

Author
Message
cgruel
cgruel
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 38
Comments posted to this topic are about the item Simple Crosstab Procedure with Power
gjcolledge
gjcolledge
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 25
I don't have experience using crosstabs, but shouldn't the results in your example be:
TYPE RED BLUE GREEN
A 2 0 0
B 1 0 0
C 0 1 0
D 0 0 1
Mark D Powell
Mark D Powell
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1733 Visits: 461
I 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.
adammenkes
adammenkes
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 56
I 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.
adammenkes
adammenkes
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 56
The 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 columns

Below is a sample table I created:
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]



Then, add the following data:
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'



Now, using the basic syntax for getting the xtab:

exec UberCrosstab
'EmployeeCode, ProjectCode',
'DateWorked',
'TimeSheetTest',
'HoursWorked',
'SUM'



I get the following results (I dropped the time 12:00AM from below for readability):

EmployeeCode ProjectCode Jan 1 2009 Jan 3, 2009 Jan 5, 2009
Adam P1 8.00 8.00 8.00
Bob P1 5.00 NULL 7.00
Adam P2 1.00 NULL NULL
Bob P2 3.00 NULL 1.00



This part works as expected. However, I want to display Thu 01/01, Sat 01/03, Mon 01/05

so, I created a field as follows:

SELECT *, LEFT(DATENAME(dw, DateWorked), 3) + ' ' + CONVERT(VARCHAR(5), DateWorked, 1) AS Header
FROM TimeSheetTest



Of course (since I cannot get the (query) to work), I do the following:


SELECT *, LEFT(DATENAME(dw, DateWorked), 3) + ' ' + CONVERT(VARCHAR(5), DateWorked, 1) AS Header
INTO #temp
FROM TimeSheetTest

exec UberCrosstab
'EmployeeCode, ProjectCode',
'Header',
'#temp',
'HoursWorked',
'SUM'

drop table #temp



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.

SET NOCOUNT ON

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @ctr INT
DECLARE @diff INT

DECLARE @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 = 0
WHILE @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 + 1
END

SELECT *
INTO #temp

FROM @days d
LEFT OUTER JOIN TimeSheetTest t
ON d.WorkDay = t.DateWorked

exec UberCrosstab
'EmployeeCode, ProjectCode',
'WorkDayName',
'#temp',
'HoursWorked',
'SUM'

drop table #temp



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]
pgarty
pgarty
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 137
The problem is a subquery is being used but not aliased. It is when the variable @query is being defined
To fix it change to:

set @query = '
DECLARE @cols NVARCHAR(max)
SELECT @cols = STUFF(
( SELECT DISTINCT ' + case when @colTop is not null then 'Top ' + @colTop else '' end + '''],['' + t2.' + @pivotField + '
FROM
(SELECT CONVERT(nvarchar(max),a.' + @colField + ') AS ' + @pivotField + '
FROM
(SELECT DISTINCT ' + @colField + '
FROM ' + @colTable + ' x
' + case when @colWhere is not null then 'WHERE ' + @colWhere else '' end + '
) AS a
) AS t2
ORDER BY ''],['' + t2.' + @pivotField + ' FOR XML PATH('''')
), 1, 2, '''') + '']''
select @cols
'

All I have added is ' x '
Hope this helps
Phil
pgarty
pgarty
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 137
PS that was for adammenkes first problem
Iwas Bornready
Iwas Bornready
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10438 Visits: 885
Right or wrong, it looks like a chance for me to practice work with PIVOT.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search