Complex Query - Need help

Hi,

I have a sql table with corpcode, EmpName, rate, reportdate and Amount fields. I need to write a query that can return corpcode, EmpName, rate and sum of Amount for January, Sum of Amount for Feburary, Sum of Amount for March, Sum of Amount for April, ........., Sum of Amount for December, Total for All months, Average for all months.

I tried few option, it didn't work for me, Is it possible to do? Have some tried like this earlier?

Thanks!Are you just looking for something like this?

select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate|||No, I need total for each month for each employee, here is a sample of what I am looking for:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ...... $341

Thanks a lot for your help!|||Is an employee's rate going to change? If so what rate should be displayed?|||It will use group by "corpcode, EmpName, rate". So if rate changes for an employee, there should be a new line in the query output.

Thanks again!|||select corpcode
, EmpName
, rate
, sum(Amount) as test
, Sum (CASE DATEPART(yy,reportdate)
WHEN 1 THEN Amount Else 0) As January
End,
, Sum (CASE DATEPART(yy,reportdate)
WHEN 2 THEN Amount Else 0) As February
End,
...List rest oh the months here
...

FROM <table>
Group By corpcode, EmpName, rate|||I am getting this error message:

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near ')'.

also instead of DATEPART(yy,reportdate), don't we need DATEPART(mm,reportdate)?

Thanks again!|||I believe jaraba's response will still have all the months in seperate rows. You may have to do something like this. I am not sure if there is a better way it is just the first thing that came to mind.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

select corpcode, EmpName, rate,
(SELECT top 1 Amount From #tmp a where mymonth = 1
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as January,
(SELECT top 1 Amount From #tmp a where mymonth = 2
and a.corpcode = #tmp.corpcode
and a.EmpName = #tmp.EmpName
and a.rate = #tmp.rate order by myyear) as February
...
from #tmp

drop table #tmp

This will only display the most recent months, if you want years too you could have a whole mess of columns.

Hope this helps|||If you don't mind, send me some data in an excel spreadsheet. i will work a solution for you.|||Thanks a lot, you guys are big help.

I got jaraba's query to work, but I want to ask one more question,

Whenever CorpCode changes, I need a line for sub totals and grand total as follows:

CorpCode--EmpName--Rate--Jan--Feb--Mar ......... Dec
A1112222--Ted Zeb--$45--$123--$456--$0.00 ...... $678.0
A1112222--Ray Bob--$89--$780--$234--$458.0 ......$341
----------------------
Sub-Total--2 employees--$134--$903--$690--$458.........$1019
----------------------
B1114444--ABC Zeb--$15--$13--$46--$0.00 ...... $68.0
B1114444--TTT Bob--$11--$0--$23--$48.0 .......$31
B1114444--GTH Bob--$19--$70--$3--$8.0 .........$15
----------------------
Sub-Total--3 employees--$45--$83--$73--$56..........$114
----------------------
----------------------
Grand Total--5 employees--$189--$986--$763--$514.........$1133
----------------------|||Actually, my response was no better. Sorry, I think something like this should get you what you are looking for.

CREATE TABLE #tmp (mymonth datetime, myyear datetime, corpcode int, EmpName varchar(50), rate int, Amount int)

CREATE TABLE #emp (corpcode int, EmpName varchar(50), rate int)

insert into #tmp
select DATEPART(yy,reportdate) as myyear
, DATEPART(mm,reportdate) as mymonth
, corpcode
, EmpName
, rate
, sum(Amount) as test
FROM <table>
Group By DATEPART(yy,reportdate), DATEPART(mm,reportdate), corpcode, EmpName, rate

insert into #emp
SELECT DISTINCT corpcode, EmpName, rate
FROM #tmp

select #emp.corpcode, #emp.EmpName, #emp.rate, j.Amount as January, f.Amount as February ...
from #emp
LEFT OUTER join #tmp j on #emp.corpcode = j.corpcode
and #emp.EmpName = j.EmpName
and #emp.rate = j.rate and j.mymonth = 1
LEFT OUTER join #tmp f on #emp.corpcode = f.corpcode
and #emp.EmpName = f.EmpName
and #emp.rate = f.rate and f.mymonth = 2
...

drop table #tmp
drop table #emp

Sorry for the confusion.|||Sorry again, you can ignore my last post if jaraba's query got you what you are looking for. If you want subtotals for each corpcode you will have to write a seperate query or use the functionality of your report writer.|||Look up COMPUTE BY in BOL