Thanks Duane & Genomon. Had a feeling that was going to be the issue but every time I tried SUM elsewhere I kept getting syntax errors.
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or...
I'm trying to get a simple percentage but they keep adding instead of becoming a percentage.
select contract, sum(IIF(subqry.ACCEPTED + subqry.DECLINED=0,0, subqry.ACCEPTED / (subqry.ACCEPTED + subqry.DECLINED))) as RATE
from subqry
where...
...So now I just need to build my app to pass the variable and I should be good to go. Thanks to all, I'll pass some stars after I get this F%*#@G thing working. Here's my sproc code:
CREATE PROCEDURE spTimeCard
(@EMP int)
AS
if exists(select * from timecard where TimeIN is null and EMPid =...
I have no idea of how to make a stored procedure determine the logic of the UPDATE/INSERT.
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or...
SoonerJoe, I changed all tables/columns, still nothing. I even created a table similar to yours, same thing. Just inserts a new row without updating the previous TimeIN for that EMP.
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything...
That code just inserted a new record, didn't update the previous. Did it work for you?
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or...
nope, it instead didn't insert or update, just sat there. When I refreshed, the data I put it went away.
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything...
still inserted a new record instead of updating TimeIN of older record (that's missing TimeIN). It didn't update all records though, so that's a plus.
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy...
yes, it update all records, but I do have a where statement in my update statement.
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or...
CREATE TRIGGER timeclock ON [dbo].[tbl_timeclock]
FOR INSERT
AS
DECLARE @TimeIN DATETIME,
@EE numeric(9,2)
select @EE = employeenumber from inserted
Select @TimeIN = TimeIn FROM tbl_timeclock WHERE timein is null and employeenumber = @EE
begin
IF @TimeIN IS NULL
UPDATE tbl_timeclock...
You have the Trigger written to work for your purpose".
No I don't, it didn't work at all, it updated the TIMEIN column when I didn't want it to, and left the TIMEIN field blank in previous row when I wanted it to be updated. I've gotten absolutely nowhere but frustrated.
"I don't want to...
Okay, but I don't want to insert multiple records. Is this a difficult thing to do based on my table structure. I thought it'd be pretty simple, but clearly I'm wrong. If (based on table above) say EMP# 4 goes to clock in/out, it will update record # 2's TIMEIN field. If say EMP# 99 goes to...
wow, I'm very confused as to what you're saying. What is this 'select 1/2/3' all about? I also don't really understand why I'd use a union statement? And as for 'don't tell me "I'm always insert one record at a time"', I guess I'm not following that, I am trying to insert one at a time, however...
Thanks Borislav. #2, oops, i've set it to be:
select @EE = employeenumber from inserted
same results.
I'm not really sure what you mean about #1 though.
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy...
Okay, I created a trigger as follows:
CREATE TRIGGER timeclock ON [dbo].[tbl_timeclock]
FOR INSERT
AS
DECLARE @TimeIN DATETIME,
@EE numeric(9,2)
Select @TimeIN = TimeIn FROM tbl_timeclock WHERE timein is null and employeenumber = @EE
begin
IF @TimeIN IS NULL
UPDATE tbl_timeclock set...
Hi all, hopefully an easy one...First, here's my Table
Rec# EMP# timeOUT timeIN
1 4 11:30 12:05
2 4 12:30 --
3 9 11:05 11:15
4 10 11:45 ---
What I'd like to do is when inserting a new record, to check...
Giddy up cmartin, thanks. Knew it was gonna be something stoooopid I did.
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or...
Hi all, getting a divide by zero error, even with a CASE statement. Am I missing something?
CASE WHEN SUM(QuantityProduced)= 0 THEN 0 ELSE SUM((QuantityProduced - scrap) / QuantityProduced) end AS QUALITY
fields QuantityProduced and Scrap are both numeric. Using Query analyzer and SQL 2K...
keep getting errors when I run stating DATEDIFF needs 3 arguments and if I take all that out, I get 'datatype NVARCHAR is invalid for argument 2 of DATEADD function'.
"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or...
I need to create a query that helps satisfy this:
I need to calculate a monthly savings...easy enough, just divide annual savings by 12. But I need to calculate the annual savings based on the month the project was implemented (DATE field). so, my annual savings for P#1 would be 12,000, whereas...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.