|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Dell PowerEdge Servers
|
|
#1
December 26th, 2003, 04:15 PM
|
|||
|
|||
|
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
December 26th, 2003, 04:43 PM
|
||||
|
||||
|
show your table/column names in a simple query grouping on employee and date
then i will rewrite it for horizontal display
|
|
#3
December 26th, 2003, 04:55 PM
|
|||
|
|||
|
Is this what you want?
|
|
#4
December 26th, 2003, 05:09 PM
|
||||
|
||||
|
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
http://r937.com/
|
|
#5
December 26th, 2003, 05:16 PM
|
|||
|
|||
|
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.
|
|
#6
December 26th, 2003, 05:26 PM
|
||||
|
||||
|
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
|
|
#7
December 26th, 2003, 05:33 PM
|
|||
|
|||
|
Sorry about that. I'm new here
 The solution worked perfect. I appreciate the help. Thank you
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > Stuck on an SQL statement |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
