October 10, 2007 at 5:01 pm
Example
Select Giver.name,Env.RecieptNum, Env.Date,
(Select sum(Env.Amount)
from env,lcl
Where Env.code = lcl.code and lcl.type = 'L'
and Env.Idnum = Giver.Idnum) as A,
(Select sum(Env.Amount)
from env,lcl
Where Env.code = lcl.code and lcl.type = 'C'
and Env.Idnum = Giver.Idnum) as B,
sum(amount) as Total Donation
from Giver,Env
Group by Giver.name,Env.RecieptNum, Env.Date
Order by Receiptnum
October 10, 2007 at 5:15 pm
You want to look at something like this (very generic because your example code is mangled)
Select giver.name,Env.RecieptNum, Env.Date,
sum(case when then envamount else 0 end) A,
sum(case when then envamount else 0 end) B,
Sum(amount) as total
from giver inner join env on giver.idnum=env.idnum
group by giver.name,Env.RecieptNum, Env.Date
order by end.receiptnum
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2007 at 5:39 pm
Thanks Matt, I will give it a try
October 10, 2007 at 8:36 pm
I will give you a example again
I think use this ,you can use little C# or VB code to complete your task.
select logdate, prodgroup , 0 as EmployeeCount , 0 as AvgHrsAttend,
sum( case when uflag='' and cmflag=1
then clocktime else 0 end )/3600 onstdcmtime
, sum( case when uflag='' and cmflag=1 and otflag=0
then smv*quantity/60 else 0 end ) onstdcmnormalsah
, sum( case when uflag='' and cmflag=1 and otflag=1
then smv*quantity/60 else 0 end ) onstdcmotsah
, sum( case when uflag='' and cmflag=1
then smv*quantity/60 else 0 end ) onstdcmsah
, sum(
case when uflag='' and cmflag=0
then clocktime else 0 end )/3600 onstdnoncmtime
,
sum( case when uflag='' and cmflag=0
then smv*quantity/60 else 0 end ) onstdnoncmsah
,
sum( case when uflag<>'' and cmflag=1
then clocktime else 0 end)/3600 offstdcmtime
,
sum( case when uflag<>'' and cmflag=0
then clocktime else 0 end)/3600 offstdnoncmtime
,sum (case when uflag<>'' and cmflag=1
then smv*quantity else 0 end)/60 offstdcmsah
, sum (case when uflag<>'' and cmflag=0
then smv*quantity else 0 end )/60 offstdnoncmsah
,
sum (case when uflag<>''
then smv*quantity else 0 end )/60 offstdsah
from sdt.dbo.dyempsumm
where logdate='2007-8-16'
and prodgroup='U1Y'
group by logdate, prodgroup
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply