April 18, 2008 at 4:20 am
lets say I have following table
CREATE TABLE [dbo].[TempData](
[empl_id] [varchar](12),
[org_id] [varchar](20),
[proj_id] [varchar](30) ,
[varchar](6) ,
[ProjectDesc] [varchar](25) ,
[ChargeHours] [numeric](14, 2) ,
[AuthorizedDate] [varchar](11)
)
and lets say I have following records in the table
'1','0001','001.001.001.00','','project A',10,10-12-2007
'1','0001','001.001.001.00','','project A',-10,10-12-2007
'1','0001','001.001.003.00','','project B',40,10-12-2007
'2','0001','001.001.001.00','','project A',15,10-12-2007
'2','0001','001.001.001.00','','project A',-10,10-12-2007
'2','0001','001.001.003.00','','project B',35,10-12-2007
I would like to either quarantine or scrub those records out of the table where sum of chargehours is zero for the a specific empl_id and proj_id.
in second record employee 1 has charged -10 hours. whereas in the first one he has charged 10 hours to the same project. so when I issue the following query
select empl_id, proj_id, sum(chargedhours) from tempdata
group by empl_id , proj_id
I would get two records for employee 1
'1','001.001.001.00',0
'1',,'001.001.003.00',40
at this point I would like to scrub both lines from tempdata table for project '001.001.001.00' and empl_id=1 as the sum of chargehours is zero.
what would be the best way to accomplish this task?
April 18, 2008 at 4:30 am
Use the HAVING clause so that you only return results with sum = 0. Then join that result set to the original table to get the rows that you need to delete. Have a go at that and post back if you're struggling.
John
April 18, 2008 at 4:54 am
seems like you can use having clause with sum. both of these queries fail
select empl_Id, proj_id, sum(chargehours) SumChargeHours
from TempCPData1
group by empl_Id, proj_id having SumChargeHours=0
order by empl_id
select empl_Id, proj_id, sum(chargehours)
from TempCPData1
group by empl_Id, proj_id having Sum=0
order by empl_id
what am I doing wrong here?
April 18, 2008 at 4:58 am
Use the first query, but put the column name in brackets in the third row, the same way you did in the first.
By they way, if you are going to say that a query failed, it would be very helpful if you would post the error messages, or say in what way it didn't produce the results you expected.
John
April 18, 2008 at 5:07 am
thanks... it worked..
select empl_Id, proj_id, sum(chargehours) SumChargeHours
from TempCPData1
group by empl_Id, proj_id having sum(chargehours)=0
order by empl_id
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply