Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2004
    Posts
    46

    Unanswered: 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!

  2. #2
    Join Date
    Aug 2004
    Posts
    54
    Are you just looking for something like this?

    Code:
    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

  3. #3
    Join Date
    Oct 2004
    Posts
    46

    Complex Query - Need help

    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!

  4. #4
    Join Date
    Aug 2004
    Posts
    54
    Is an employee's rate going to change? If so what rate should be displayed?

  5. #5
    Join Date
    Oct 2004
    Posts
    46

    Complex Query - Need help

    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!

  6. #6
    Join Date
    Oct 2003
    Posts
    60
    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
    jaraba

  7. #7
    Join Date
    Oct 2004
    Posts
    46

    Complex Query - Need help

    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!

  8. #8
    Join Date
    Aug 2004
    Posts
    54
    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.


    Code:
    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
    Last edited by Ten_Spoons; 10-29-04 at 17:09.

  9. #9
    Join Date
    Oct 2003
    Posts
    60
    If you don't mind, send me some data in an excel spreadsheet. i will work a solution for you.
    jaraba

  10. #10
    Join Date
    Oct 2004
    Posts
    46

    Complex Query - Need help

    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
    ------------------------------------------------------------------------

  11. #11
    Join Date
    Aug 2004
    Posts
    54
    Actually, my response was no better. Sorry, I think something like this should get you what you are looking for.

    Code:
    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.

  12. #12
    Join Date
    Aug 2004
    Posts
    54
    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.
    Last edited by Ten_Spoons; 10-29-04 at 17:52.

  13. #13
    Join Date
    Oct 2003
    Posts
    60
    Look up COMPUTE BY in BOL
    jaraba

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •