December 7, 2009 at 3:52 am
Problem description:
I've a report (report1.rdl) that displays the number that a specific application is used.
In the last column I like to have the maximum number that exists in the Row (see result.jpg).
I've tried several things, but no luck.
Can someone help me with this?
(I've included sample information to reproduce)
Table “Applications”
CREATE TABLE [dbo].[ICT_Apps](
[Firstname] [varchar](100) NOT NULL,
[Lastname] [varchar](255) NOT NULL,
[Application] [varchar](255) NOT NULL,
[Daynr] [varchar](2) NOT NULL,
[Monthnr] [varchar](2) NOT NULL,
[Yearnr] [varchar](4) NOT NULL,
[Weeknr] [varchar](2) NOT NULL
) ON [PRIMARY]
Data inside table “Applications”
INSERT INTO ICT_Apps
select 'Frits','Philips','Office2003','1','11','2009','45' union all
select 'Frank','Jansen','Office2003','1','11','2009','45' union all
select 'Peter','Smits','Office2003','1','11','2009','45' union all
select 'John','Doe','Office2003','1','11','2009','45' union all
select 'Frank','Jansen','Telebanking','1','11','2009','45' union all
select 'Peter','Smits','Telebanking','1','11','2009','45' union all
select 'Frits','Philips','Office2003','2','11','2009','45' union all
select 'Frank','Jansen','Office2003','2','11','2009','45' union all
select 'Peter','Smits','Office2003','2','11','2009','45' union all
select 'Frits','Philips','Telebanking','2','11','2009','45' union all
select 'Frank','Jansen','Telebanking','2','11','2009','45' union all
select 'Peter','Smits','Telebanking','2','11','2009','45' union all
select 'Frits','Philips','Office2003','3','11','2009','45' union all
select 'Frank','Jansen','Office2003','3','11','2009','45' union all
select 'Peter','Smits','Office2003','3','11','2009','45' union all
select 'Frank','Jansen','Telebanking','3','11','2009','45' union all
select 'Peter','Smits','Telebanking','3','11','2009','45'
Dataset “Application_List”
select
(Firstname+' '+Lastname) as Gebruiker,
Application,
Daynr,
Monthnr,
Yearnr,
Weeknr
from
ICT_Apps
Included:
- Report (Report1.rdl.txt --> Rename to Report1.rdl)
- Wannahave Result (Result.jpg)
December 7, 2009 at 4:38 am
Try this:
SELECT d.*, s.MaxUsage
FROM (
SELECT [Application], [1] AS Day1, [2] AS Day2, [3] AS Day3, [4] AS Day4, [5] AS Day5, [6] AS Day6
FROM
(SELECT [Application], [Lastname], Daynr
FROM #ICT_Apps) p
PIVOT (
COUNT ([Lastname])
FOR Daynr IN
( [1], [2], [3], [4], [5], [6] )
) AS pvt
) d
LEFT JOIN (SELECT x.[Application], MAX(x.Usage) AS MaxUsage
FROM (SELECT [Application], COUNT(*) AS Usage
FROM #ICT_Apps
GROUP BY [Application], Daynr) x
GROUP BY x.[Application]
) s ON s.[Application] = d.[Application]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 7, 2009 at 5:23 am
First of all thanx for the reply!
Will this also work when the table "ICT_Apps" is automaticly filled each day and the results should be
like "where monthnr=month(getdate())", because in this example you define each day...?
December 7, 2009 at 5:39 am
The pivot operator requires the new output columns to be described and mapped, but if they are not known at run-time the dynamic sql can be used to create the list. See here for one of many examples.
Apply the month filter wherever it yields best performance:
SELECT d.*, s.MaxUsage
FROM (
SELECT [Application], [1] AS Day1, [2] AS Day2, [3] AS Day3, [4] AS Day4, [5] AS Day5, [6] AS Day6
FROM
(SELECT [Application], [Lastname], Daynr
FROM #ICT_Apps WHERE [Monthnr] = 11) p
PIVOT (
COUNT ([Lastname])
FOR Daynr IN
( [1], [2], [3], [4], [5], [6] )
) AS pvt
) d
LEFT JOIN (SELECT x.[Application], MAX(x.Usage) AS MaxUsage
FROM (SELECT [Application], COUNT(*) AS Usage
FROM #ICT_Apps WHERE [Monthnr] = 11
GROUP BY [Application], Daynr) x
GROUP BY x.[Application]
) s ON s.[Application] = d.[Application]
;WITH CTE_ICT_Apps AS
(SELECT [Application], [Lastname], Daynr
FROM #ICT_Apps WHERE [Monthnr] = 11)
SELECT d.*, s.MaxUsage
FROM (
SELECT [Application], [1] AS Day1, [2] AS Day2, [3] AS Day3, [4] AS Day4, [5] AS Day5, [6] AS Day6
FROM
(SELECT [Application], [Lastname], Daynr
FROM CTE_ICT_Apps) p
PIVOT (
COUNT ([Lastname])
FOR Daynr IN
( [1], [2], [3], [4], [5], [6] )
) AS pvt
) d
LEFT JOIN (SELECT x.[Application], MAX(x.Usage) AS MaxUsage
FROM (SELECT [Application], COUNT(*) AS Usage
FROM CTE_ICT_Apps
GROUP BY [Application], Daynr) x
GROUP BY x.[Application]
) s ON s.[Application] = d.[Application]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 7, 2009 at 8:30 am
Chris,
I used the query (2nd) with succes. GREAT RESULTS!
Last question:
I'm trying to get the columns Appliction, Daynr, MaxUsage but I'm stuck.
I'm only getting Application and MaxUsage like in your example.
I think it shouldn't be hard, but no luck.
Can you help?
Edit:
What I mean is that:
d.*, s.MaxUsage
FROM (
SELECT [Application], [1] AS Day1, [2] AS Day2, [3] AS Day3, [4] AS Day4, [5] AS Day5, [6] AS Day6
FROM .......
lookes like:
d.*, s.MaxUsage
FROM (
SELECT [Application], Daynr
FROM .........
December 8, 2009 at 2:30 am
I've tried to work out the "Fetch" command. See my result, but I got an error
Msg 102, Level 15, State 1, Line 44
Incorrect syntax near '@Dagen'.
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near 's'.
Again, a little help will be appreciated....;-)
-- Declare Variables for the FETCH
Declare @DagID INT
Declare @Dagen Varchar(8000)
Set@DagID = ''
Set@Dagen = ''
Declare max_cursor cursor for
Select
[Application], [Lastname], [Daynr]
From
ICT_Apps
Where [Monthnr] = 11
--Read data into @Dagen
Open max_cursor
Fetch next from max_cursor into @DagID
While (@@FETCH_STATUS <> -1)
Begin
set @Dagen = '[' + @DagID + '],'
Fetch next from max_cursor into @DagID
End
Close max_cursor
Deallocate max_cursor
Set @Dagen = LEFT(@Dagen,len(@Dagen)-1)-- Remove last ,(comma)
;WITH CTE_ICT_Apps AS
(SELECT [Application], [Lastname], Daynr
FROM #ICT_Apps WHERE [Monthnr] = 11)
SELECT d.*, s.MaxUsage
FROM (
SELECT [Application], @Dagen
FROM
(SELECT [Application], [Lastname], Daynr
FROM CTE_ICT_Apps) p
PIVOT (
COUNT ([Lastname])
FOR Dag IN
(@Dagen)
) AS pvt
) d
LEFT JOIN (SELECT x.[Application], MAX(x.Usage) AS MaxUsage
FROM (SELECT [Application], COUNT(*) AS Usage
FROM CTE_ICT_Apps
GROUP BY [Application], Daynr) x
GROUP BY x.[Application]
) s ON s.[Application] = d.[Application]
December 8, 2009 at 2:36 am
No problem - read this, it will show you how it's done.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 8, 2009 at 3:28 am
I'm trying to understand (still beginner sql), but still getting strange results:
EXEC dynamic_pivot
'SELECT i.Application, i.daynr
FROM ict_apps i',
'application',
'max(daynr)'
Horizontal I've the application (OK), but vertical I like the days with a final column with the max from the previous columns. I cannot get it working. Still trying.....
No luck, I can't get the results I like to have.
December 8, 2009 at 5:24 am
Is this different to your original output?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 8, 2009 at 5:37 am
Yes, it is.
My original output (from the first answer of Chris) was:
Application123456MaxUsage
Office20034330004
Telebanking2320003
With the dynamic pivot it is:
ApplicationOffice2003Telebanking
Office20033 NULL
TelebankingNULL 3
December 8, 2009 at 5:41 am
That's no good is it! How do you want it to look?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 8, 2009 at 5:48 am
Like the results from the first answer.
Application123456.......31 MaxUsage
Office2003433000....... x 4
Telebanking232000....... x 3
December 8, 2009 at 5:57 am
htilburgs (12/8/2009)
Like the results from the first answer.
Application123456.......31 MaxUsage
Office2003433000....... x 4
Telebanking232000....... x 3
Erm...this is the result from the first query - what do you want to change? Is it simply a case of getting this result by using Madhivanan's code?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 8, 2009 at 6:07 am
What do you mean?
I just expand [1], [2] until [31] (for days of month)???
Because this was how we started, but I liked the [1] till [31] to be dynamic.
Or do you mean something else?
December 8, 2009 at 7:18 am
htilburgs (12/8/2009)
What do you mean?I just expand [1], [2] until [31] (for days of month)???
Because this was how we started, but I liked the [1] till [31] to be dynamic.
Or do you mean something else?
To give you just the day columns which already exist in the data?
Jeff Moden's Dynamic Cross Tab paper[/url] explains how to do this better than anyone else can...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply