if else - SQL Server Forums

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

if else

Author

Topic

rye07
Starting Member

13 Posts

Posted - 2012-03-27 : 23:46:35

i have an example here

this is my output temp table look the running total
empName | customerId |orderdate | orderAmount| runningtotal
------------------------------------------------
emp#1 | cus1 |1996-07-04 | 32 |32
emp#1 | cus1 |1996-07-04 | 33 |65
emp#1 | cus1 |1996-07-04 | 40 |105
emp#2 | cus2 |1996-07-04 | 100 |205
emp#3 | cus3 |1996-08-09 | 200 |405

and thi is what i want to become my out put
empName | customerId |orderdate | orderAmount| runningtotal
------------------------------------------------
emp#1 | cus1 |1996-07-04 | 32 |32
emp#1 | cus1 |1996-07-04 | 33 |65
emp#1 | cus1 |1996-07-04 | 40 |105
emp#2 | cus2 |1996-07-04 | 100 |100
emp#3 | cus3 |1996-08-09 | 200 |200

how can i make it using the if else staement

create table #aorderlist (employeeName varchar(50),customerId nchar(5),orderID int,OrderDate Date,OrderAmount int,RunningTotal int)

declare @aemployeeName varchar(50),
@acustomerId nchar(5),
@aorderId int,
@aorderdate date,
@aorderamount int,

@arunningtotal int

set @arunningtotal=0
declare or_cur cursor for
select dbo.[Employees].LastName +' '+ dbo.[Employees].FirstName as employeesName,customerId,orderid,OrderDate,freight as orderamount from orders,dbo.[employees]

group by dbo.[Employees].LastName +' '+ dbo.[Employees].FirstName,customerId,orderid,OrderDate,freight

open or_cur
fetch next from or_cur into @aemployeeName,@acustomerId,@aorderId,@aorderdate,@aorderamount
while @@FETCH_STATUS=0
begin
set @arunningtotal=@arunningtotal+@aorderamount
insert #aorderlist values ( @aemployeeName,@acustomerId,@aorderId,@aorderdate,@aorderamount,@arunningtotal)
fetch next from or_cur into @aemployeeName,@acustomerId,@aorderId,@aorderdate,@aorderamount
end
close cursorName
deallocate cursorName

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-27 : 23:56:29

1. order your cursor SELECT statement by employee
2. check for change of employee and when it happen, reset your running total to 0

KH
[spoiler]Time is always against us[/spoiler]

rye07
Starting Member

13 Posts

Posted - 2012-03-28 : 00:02:30

how?

to god be the glory

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-28 : 00:05:06

1. use ORDER BY clause


SELECT ...
FROM   ...
ORDER BY empName , orderdate

2. keep another variable for employee name and check for when change, reset the running total

KH
[spoiler]Time is always against us[/spoiler]

rye07
Starting Member

13 Posts

Posted - 2012-03-28 : 00:30:45

when i run it nothing happen :(

to god be the glory

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-28 : 02:55:30

have you implemented part 2 of the logic ?

KH
[spoiler]Time is always against us[/spoiler]

rye07
Starting Member

13 Posts

Posted - 2012-03-28 : 04:28:56

is it posible to do it in if else statement?

to god be the glory

rye07
Starting Member

13 Posts

Posted - 2012-03-28 : 04:35:36

if @aemployeeName<>@acustomerId+@aorderamount
begin
insert into #aorderlist values (@aemployeeName,@acustomerId,@aorderId,@aorderdate,@aorderamount,@arunningtotal)
end
else
begin
if
@aemployeeName=@acustomerId

(in this part i should not put any variable)
the question is how am i going to write it in else statement??

to god be the glory

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-03-28 : 04:55:59


if @aemployeeName <> @acustomerId
begin
    set @arunningtotal = 0 
end

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources