#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    4
    Rep Power
    0

    Stuck on an SQL statement


    If anyone can give me a hand with this, I would be eternally grateful.

    I have an employee sales table. What I would need is some results between a certain date. (Entered by user). The date thing is no problem though as they criteria will come off a form.


    This is my problem. Every sale has the date it was sold. Now I need the total sales for Mon, Tues, Wed, Thu, Fri, for each employee in an excel like result. i.e. horizontal display so I can have the totals at the end (totals are easy).

    This would be an example

    Emp MON TUE WED THU FRI
    001 5.00 4.00 3.00 4.00 5.00
    002 4.00 5.00 6.00 5.00 1.00
    003 5.00 9.00 8.00 7.00 6.00

    The way I attacked this was of course grouping on employee number. Then I tried to make a sub query for each day. I managed to get close to the result but it returns all the sum for all the employees. In essence I couldn't have it return the sum for a specific employee. That being the employee that corresponds to the main query.

    Can anyone give me a hand with this, my head is killing me.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    show your table/column names in a simple query grouping on employee and date

    then i will rewrite it for horizontal display
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    4
    Rep Power
    0
    Is this what you want?
    Attached Images
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    Code:
    select Salesperson as Emp
         , sum( 
            case when datepart(dw,[Date Verified])=2
                 then [Sales Amount]
                 else 0 end
              ) as MON
         , sum( 
            case when datepart(dw,[Date Verified])=3
                 then [Sales Amount]
                 else 0 end
              ) as TUE
         , sum( 
            case when datepart(dw,[Date Verified])=4
                 then [Sales Amount]
                 else 0 end
              ) as WED
         , sum( 
            case when datepart(dw,[Date Verified])=5
                 then [Sales Amount]
                 else 0 end
              ) as THU
         , sum( 
            case when datepart(dw,[Date Verified])=6
                 then [Sales Amount]
                 else 0 end
              ) as FRI
      from YouForgotToMentionTheTableName
     where [Date Verified]
           between '2003-12-21'
               and '2003-12-27'
    group
        by Salesperson
    rudy
    http://r937.com/
  8. #5
  9. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    4
    Rep Power
    0
    The table name was Vers. I ran the code and got the error message in the attachement. I understand the coding but I don't see where the error is.
    Attached Images
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,145
    Rep Power
    4274
    well, no wonder

    you're running microsoft access

    excuse me for thinking you were running microsoft sql server

    this is, after all, the microsoft sql server forum


    Code:
    select Salesperson as Emp
         , sum( 
            iif(datepart("w",[Date Verified])=2
               , [Sales Amount], 0 )
              ) as MON
         , sum( 
            iif(datepart("w",[Date Verified])=3
               , [Sales Amount], 0 )
              ) as TUE
         , sum( 
            iif(datepart("w",[Date Verified])=4
               , [Sales Amount], 0 )
              ) as WED
         , sum( 
            iif(datepart("w",[Date Verified])=5
               , [Sales Amount], 0 )
              ) as THU
         , sum( 
            iif(datepart("w",[Date Verified])=6
               , [Sales Amount], 0 )
              ) as FRI
      from Vers
     where [Date Verified]
           between #2003-12-21#
               and #2003-12-27#
    group
        by Salesperson
    rudy
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    4
    Rep Power
    0
    Sorry about that. I'm new here The solution worked perfect. I appreciate the help. Thank you

IMN logo majestic logo threadwatch logo seochat tools logo