hw 2 use creteria sum filter in update statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

hw 2 use creteria sum filter in update statement?

Author

Topic

n4me
Starting Member

2 Posts

Posted - 2007-09-04 : 23:47:08

Dear all,

M new to SQL Query, i have some update query code that i run have something wrong. It's the following:

update prod_ticket
set prod_ticket.complete_date = '5000/01/01'
FROM prod_ticket INNER JOIN
prod_ticket_hdr ON prod_ticket.lay_id = prod_ticket_hdr.lay_id INNER JOIN
ms_employee ON prod_ticket.employee_no = ms_employee.employee_no INNER JOIN
ms_department ON ms_employee.department_no = ms_department.department_no INNER JOIN
prod_work ON prod_ticket.work_no = prod_work.work_no AND prod_ticket_hdr.prod_no = prod_work.prod_no
WHERE (prod_ticket.complete_date BETWEEN CONVERT(DATETIME, '2007-08-01 00:00:00', 102) AND CONVERT(DATETIME, '2007-08-31 00:00:00', 102)) AND
(prod_work.piece_rate / 10)='0' and prod_ticket.employee_no='50502' and (SUM(prod_ticket.complete_qty))<'30'

I knew that the wrong code is the (SUM(prod_ticket.complete_qty))<'30' because if i delete this condition then the query was ok. Could u please help me to correct the query code for me. really really much appriciated.

jaypee_s
Starting Member

22 Posts

Posted - 2007-09-05 : 00:45:55

when you are using Aggregate function like sum, avg. Use group by that column name...it will work

jaypee_s
Starting Member

22 Posts

Posted - 2007-09-05 : 01:09:00

Where clasue should not contain aggregate function, so take matching records into temp table and join those results with update statement...

based on the below code just give a try

select * into #temp prod_ticket,(SUM(prod_ticket.complete_qty)) as sum1
--prod_ticket.complete_date = '5000/01/01'
FROM prod_ticket
INNER JOIN prod_ticket_hdr
ON prod_ticket.lay_id = prod_ticket_hdr.lay_id
INNER JOIN ms_employee
ON prod_ticket.employee_no = ms_employee.employee_no
INNER JOIN ms_department
ON ms_employee.department_no = ms_department.department_no
INNER JOIN prod_work
ON prod_ticket.work_no = prod_work.work_no AND prod_ticket_hdr.prod_no = prod_work.prod_no

WHERE (prod_ticket.complete_date BETWEEN CONVERT(DATETIME, '2007-08-01 00:00:00', 102)
AND CONVERT(DATETIME, '2007-08-31 00:00:00', 102)) AND
(prod_work.piece_rate / 10)='0' and prod_ticket.employee_no='50502'

update prod_ticket
set prod_ticket.complete_date = '5000/01/01'
inner join #temp
on prod_ticket = #temp.prod_ticket
where sum1 > 30

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 04:00:13

[code]UPDATE pt
SET pt.Complete_Date = '5000/01/01'
FROM prod_ticket AS pt
INNER JOIN (
SELECT pt.employee_no,
SUM(pt.complete_qty)
FROM prod_ticket AS pt
INNER JOIN prod_ticket_hdr AS hdr ON hdr.lay_id = pt.lay_id
INNER JOIN prod_work AS w ON w.work_no = pt.work_no
AND w.prod_no = hdr.prod_no
WHERE pt.complete_date >= '20070801'
AND pt.complete_date < '20070901'
AND w.piece_rate < 10
GROUP BY pt.employee_no
HAVING SUM(pt.complete_qty) < 30
) AS g ON g.employee_no = pt.employee_no
WHERE pt.employee_no = '50502'[/code]

E 12°55'05.25"
N 56°04'39.16"

n4me
Starting Member

2 Posts

Posted - 2007-09-05 : 08:17:28

Thks very much. The code realy usefull for me. and it work for me now.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources