Please help with a stored procedure 1

[Happy2]

Can anyone please help me with the layout that a report in SQL should be like a cross tab style below: Thanks alot for your help.

EXPECTED REPORT LAYOUT For Months From 8/1/00 To 7/31/01

Developer Manager Agent Aug ... Jul
-------------------------------------------------
MICHELLE Anne Josh 3 0


This is the code I have to put in a temp table:
Declare @from int, @to int, @counter int, @Year int, @Month int
set @counter = 0
set @from = 1
set @to = datediff(mm, @StartDate, @EndDate)
While (@counter < @to + 1)
Begin
set nocount on
set @Year = Year(DateAdd(mm, -@counter, @EndDate))
set @Month = Month(DateAdd(mm, -@counter, @EndDate))
Insert into DateArray (Year, Month) values (@Year, @Month)
set @counter = @counter + 1
set nocount off
End

 

[StewartGW]

This is an example of using a pivot table or cross-tabs.
Suppose you have a simple table called dev_cross_tab with the following datga:

Developer Mgr Agt Mon Value
------- ---- ---- ----- -----
MICHELLE Anne Josh Aug00 1
MICHELLE Anne Josh Sep00 1
MICHELLE Anne Josh Oct00 0
MICHELLE Anne Josh Nov00 4
MICHELLE Anne Josh Nov00 6
MICHELLE Anne Josh Nov00 1
MICHELLE Anne Josh Nov00 1
MICHELLE Anne Josh Dec00 6
MICHELLE Anne Josh Jan01 2
MICHELLE Anne Josh Feb01 0
MICHELLE Anne Josh Mar01 1
MICHELLE Anne Josh Apr01 0
MICHELLE Anne Josh Jun01 1

The solution uses a CASE statement, that makes only a single pass through the table.

SELECT dev = c.developer, mgr = c.manager, agent = c.agent,
SUM(CASE mon WHEN 'Aug00' THEN value ELSE 0 END) AS Aug00,
SUM(CASE mon WHEN 'Sep00' THEN value ELSE 0 END) AS Sep00,
SUM(CASE mon WHEN 'Nov00' THEN value ELSE 0 END) AS Oct00,
....
SUM(CASE mon WHEN 'Jun01' THEn value ELSE 0 END) AS JUL01
FROM dev_cross_tab c
GROUP BY dev, mgr, agent

 

[Happy2]

Hi Steward,

Thanks for your help. So, it's only posible to pass through a single month at a time to the table? Since I usually run hundreds of pages for the report, I really wish if I can pass through 12 months at a time for each agent (either 6 months from last year and 6 months from this year or from Oct, 99 to Sept,2000).

Again, thank you so much for your prompt response.

 

[StewartGW]

Sorry, I may have confused you. You can pass through as many months as you want; it is depended upon your case statement.

The statement &quot;The solution uses a CASE statement, that makes only a single pass through the table.&quot; was provided to let you know this the solution is much more efficient than using a series of SELECT subqueries to do the same thing.

For instance, a less-efficient way of doing cross-join is as follows:

SELECT developer, manager, agent,
Aug00 = (SELECT mod FROM dev_cross_tab
WHERE developer=c.developer AND
manager=c.manager AND
agent=c.agent AND
mon = 'Aug00')
....
Jul01 = (SELECT mod FROM dev_cross_tab
WHERE developer=c.developer AND
manager=c.manager AND
agent=c.agent AND
mon = 'Jul01')

FROM dev_cross_tab c
GROUP BY dev, mgr, agent

This type of solution was used prior to CASE being added to SQL Server 6; however, the SELECT statements will require extra scans.

 

[Happy2]

Thanks a lot for your help, Stewart.