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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to create a result field
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 12/12/2012 :  04:42:38  Show Profile  Reply with Quote
I have a table as follow:
Date In Out
Dec 1 5 0
Dec 2 0 3
Dec 2 4 0
Dec 3 0 2

How to add a result field and the table become:
Date In Out Result
Dec 1 5 0 5
Dec 2 0 3 2
Dec 2 4 0 6
Dec 3 0 2 4

Thanks

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

Posted - 12/12/2012 :  05:02:26  Show Profile  Reply with Quote
What is the criteria for Result column



--
Chandu
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/12/2012 :  05:09:39  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Something like

with cte as
(
select *, seq = row_number() over (order by date, in, out) from tbl
) ,
cte2 as
(
select date, in, out, result = in-out, seq from cte where seq = 1
union all
select cte2.date, cte2.in, cte2.out, result = cte.Result + cte2.in-cte2.out, cte2.seq from cte join cte2 on cte2.seq = cte.seq + 1
)
select *
from cte2
order by seq




==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 12/12/2012 :  05:47:04  Show Profile  Reply with Quote
Result column are:
Row 1 : in-out :5-0= 5 result row = 5
Row 2 : in-out :0-3=-3 result row = 5(Result row 1)+ (-3)= 2
Row 3 : in-out :4-0= 4 Result row = 2(Result row 2)+ 4 = 6
Row 4 : in-out :0-2=-2 result row = 6(result row 3) + (-2) = 4


quote:
Originally posted by bandi

What is the criteria for Result column



--
Chandu

Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 12/12/2012 :  06:13:14  Show Profile  Reply with Quote
sorry I post in the wrong forum, my sql server is (2005)

quote:
Originally posted by nigelrivett

Something like

with cte as
(
select *, seq = row_number() over (order by date, in, out) from tbl
) ,
cte2 as
(
select date, in, out, result = in-out, seq from cte where seq = 1
union all
select cte2.date, cte2.in, cte2.out, result = cte.Result + cte2.in-cte2.out, cte2.seq from cte join cte2 on cte2.seq = cte.seq + 1
)
select *
from cte2
order by seq




==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/12/2012 :  07:03:30  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
declare @t table (dte datetime, inp int, outp int)
insert @t select '20121201', 5, 0
insert @t select '20121202', 0, 3
insert @t select '20121202', 4, 0
insert @t select '20121203', 0, 2

;
with cte as (
select *, val = convert(varchar(8),dte,112) + right('0000' + convert(varchar(4),inp),4) + right('0000' + convert(varchar(4),outp),4)
from @t
),
cte2 as (
select *, seq=(select count(*) from cte t2 where t2.val <= t1.val)
from cte t1
)
--select * from cte2
, cte3 as
(
select dte, inp, outp, result = inp-outp, seq from cte2 where seq = 1
union all
select cte3.dte, cte3.inp, cte3.outp, result = cte3.Result + cte2.inp-cte2.outp, cte3.seq+1 from cte2 join cte3 on cte2.seq = cte3.seq + 1
)
select *
from cte3
order by seq


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 12/12/2012 :  07:42:05  Show Profile  Reply with Quote
Thanks for your prompt reply, I have copy your code and paste to my visual studio query and run it, it return with error.


quote:
Originally posted by nigelrivett

declare @t table (dte datetime, inp int, outp int)
insert @t select '20121201', 5, 0
insert @t select '20121202', 0, 3
insert @t select '20121202', 4, 0
insert @t select '20121203', 0, 2

;
with cte as (
select *, val = convert(varchar(8),dte,112) + right('0000' + convert(varchar(4),inp),4) + right('0000' + convert(varchar(4),outp),4)
from @t
),
cte2 as (
select *, seq=(select count(*) from cte t2 where t2.val <= t1.val)
from cte t1
)
--select * from cte2
, cte3 as
(
select dte, inp, outp, result = inp-outp, seq from cte2 where seq = 1
union all
select cte3.dte, cte3.inp, cte3.outp, result = cte3.Result + cte2.inp-cte2.outp, cte3.seq+1 from cte2 join cte3 on cte2.seq = cte3.seq + 1
)
select *
from cte3
order by seq


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

Posted - 12/12/2012 :  07:50:43  Show Profile  Reply with Quote
quote:
Originally posted by nigelrivett


Hi nigel,
Result column is fine, but not other columns

dte	                inp	outp	result	seq
2012-12-01 00:00:00.000	5	0	5	1
2012-12-01 00:00:00.000	5	0	2	2
2012-12-01 00:00:00.000	5	0	6	3
2012-12-01 00:00:00.000	5	0	4	4
----------------------------------------------------------------------
Small modification in your query..
declare @t table (dte datetime, inp int, outp int)
insert @t select '20121201', 5, 0
insert @t select '20121202', 0, 3
insert @t select '20121202', 4, 0
insert @t select '20121203', 0, 2
;with cte as (
select *, ROW_NUMBER() OVER( ORDER BY  dte, inp, outp) Val
from @t
),
cte2 as (
select *, seq=(select count(*) from cte t2 where t2.val <= t1.val)
from cte t1
) 
--select * from cte2
, cte3 as
(
select dte, inp, outp, result = inp-outp, seq from cte2 where seq = 1
union all
select cte3.dte, cte3.inp, cte3.outp, result = cte3.Result + cte2.inp-cte2.outp, cte3.seq+1 from cte2 join cte3 on cte2.seq = cte3.seq + 1
)
select cte.dte, cte.inp, cte.outp, cte3.result
from cte3 JOIN cte ON cte3.seq = cte.Val
order by seq


--
Chandu

Edited by - bandi on 12/12/2012 08:05:53
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

Posted - 12/12/2012 :  07:53:41  Show Profile  Reply with Quote
Hi Patrick,
Nigel's code is compatible with MSSQL 2005 also. Which error you are getting? Post error message




--
Chandu
Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 12/13/2012 :  05:51:08  Show Profile  Reply with Quote
Thanks Bandi
I copy your code and paste to my sql query:

declare @t table (dte datetime, inp int, outp int)
insert @t select '20121201', 5, 0
insert @t select '20121202', 0, 3
insert @t select '20121202', 4, 0
insert @t select '20121203', 0, 2
;with cte as (
select *, ROW_NUMBER() OVER( ORDER BY dte, inp, outp) Val
from @t
),
cte2 as (
select *, seq=(select count(*) from cte t2 where t2.val <= t1.val)
from cte t1
)
--select * from cte2
, cte3 as
(
select dte, inp, outp, result = inp-outp, seq from cte2 where seq = 1
union all
select cte3.dte, cte3.inp, cte3.outp, result = cte3.Result + cte2.inp-cte2.outp, cte3.seq+1 from cte2 join cte3 on cte2.seq = cte3.seq + 1
)
select cte.dte, cte.inp, cte.outp, cte3.result
from cte3 JOIN cte ON cte3.seq = cte.Val
order by seq


Reply as Follow:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'with'.
Msg 195, Level 15, State 10, Line 7
'ROW_NUMBER' is not a recognized function name.
Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near ','.


quote:
Originally posted by bandi

Hi Patrick,
Nigel's code is compatible with MSSQL 2005 also. Which error you are getting? Post error message




--
Chandu

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 12/13/2012 :  05:57:53  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Which version of sql server are you running.
Is it running in compatability mode?


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2242 Posts

Posted - 12/13/2012 :  06:25:35  Show Profile  Reply with Quote
run this one to know compatibility level
EXEC sp_dbcmptlevel 'YourDBName'

After that
sp_dbcmptlevel
Sets certain database behaviors to be compatible with the specified version of SQL Server.

Example:
----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO
Note:
Version of SQL Server database can be one of the following:

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005





--
Chandu
Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 12/13/2012 :  09:08:53  Show Profile  Reply with Quote
Thank You Bandi,

Sorry ! My sql server is
8.0.760.0 = SQL Server 2000 SP3 / SP3a 1 (Updated 2003-08-27)



quote:
Originally posted by bandi

run this one to know compatibility level
EXEC sp_dbcmptlevel 'YourDBName'

After that
sp_dbcmptlevel
Sets certain database behaviors to be compatible with the specified version of SQL Server.

Example:
----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO
Note:
Version of SQL Server database can be one of the following:

60 = SQL Server 6.0
65 = SQL Server 6.5
70 = SQL Server 7.0
80 = SQL Server 2000
90 = SQL Server 2005





--
Chandu

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/13/2012 :  17:11:25  Show Profile  Reply with Quote
declare @t table (dte datetime, inp int, outp int)
insert @t select '20121201', 5, 0
insert @t select '20121202', 0, 3
insert @t select '20121202', 4, 0
insert @t select '20121203', 0, 2


Select ID = Identity(int,1,1) , qty = inp - outp,* into #T
from @T t

select t.*,
(select sum(qty)
        from    #T tt
        where  tt.ID  < = t.Id
        )
from #T t
Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 12/13/2012 :  23:08:44  Show Profile  Reply with Quote
Well Done! Sodeep, thank you very much, I still need some time to figure out your code.
how if my existing table name TEST, with data

DATE inp outp
2012-12-01 5 0
2012-12-02 0 3
2012-12-02 4 0
2012-12-03 0 2

After running your code ,It will create a New table TEST-1 with Data

ID QTY DATE inp outp Remain
1 5 2012-12-01 5 0 5
2 -3 2012-12-02 0 3 2
3 4 2012-12-02 4 0 6
4 -2 2012-12-03 0 2 4

Thank you again




quote:
Originally posted by sodeep

declare @t table (dte datetime, inp int, outp int)
insert @t select '20121201', 5, 0
insert @t select '20121202', 0, 3
insert @t select '20121202', 4, 0
insert @t select '20121203', 0, 2


Select ID = Identity(int,1,1) , qty = inp - outp,* into #T
from @T t

select t.*,
(select sum(qty)
        from    #T tt
        where  tt.ID  < = t.Id
        )
from #T t


Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/14/2012 :  10:05:36  Show Profile  Reply with Quote
cool...you are welcome
Go to Top of Page

patrickjao
Starting Member

Indonesia
24 Posts

Posted - 12/17/2012 :  00:10:09  Show Profile  Reply with Quote
Anyone can help ! IF the table has a field name code:

code dte inp outp S
1000 2012-12-01 5 0 5
1000 2012-12-02 0 3 -3
1000 2012-12-02 4 0 4
1000 2012-12-03 0 2 -2
2000 2012-12-02 6 0 6
2000 2012-12-03 0 4 -4
3000 2012-12-02 7 0 7
3000 2012-12-02 0 4 -4

the outout become :
code dte inp outp S balance
1000 2012-12-01 5 0 5 5
1000 2012-12-02 0 3 -3 2
1000 2012-12-02 4 0 4 6
1000 2012-12-03 0 2 -2 4
2000 2012-12-02 6 0 6 6
2000 2012-12-03 0 4 -4 2
3000 2012-12-02 7 0 7 7
3000 2012-12-02 0 4 -4 3


quote:
Originally posted by sodeep

cool...you are welcome

Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 12/17/2012 :  01:02:46  Show Profile  Reply with Quote
quote:
Originally posted by patrickjao

Anyone can help ! IF the table has a field name code:

code dte inp outp S
1000 2012-12-01 5 0 5
1000 2012-12-02 0 3 -3
1000 2012-12-02 4 0 4
1000 2012-12-03 0 2 -2
2000 2012-12-02 6 0 6
2000 2012-12-03 0 4 -4
3000 2012-12-02 7 0 7
3000 2012-12-02 0 4 -4

the outout become :
code dte inp outp S balance
1000 2012-12-01 5 0 5 5
1000 2012-12-02 0 3 -3 2
1000 2012-12-02 4 0 4 6
1000 2012-12-03 0 2 -2 4
2000 2012-12-02 6 0 6 6
2000 2012-12-03 0 4 -4 2
3000 2012-12-02 7 0 7 7
3000 2012-12-02 0 4 -4 3


quote:
Originally posted by sodeep

cool...you are welcome





Is that not what you wanted ?


KH
Time is always against us

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/17/2012 :  08:23:27  Show Profile  Reply with Quote
Do this

declare @t table (code int,dte datetime, inp int, outp int)
insert @t select 1000,'20121201', 5, 0
insert @t select 1000,'20121202', 0, 3
insert @t select 1000,'20121202', 4, 0
insert @t select 1000,'20121203', 0, 2
insert @t select 2000,'2012-12-02',6,0
insert @t select 2000,'2012-12-03',0,4
insert @t select 3000,'2012-12-02',7,0
insert @t select 3000,'2012-12-02',0,4


Select ID = Identity(int,1,1) , qty = inp - outp,* into #T
from @T t

select t.*,
(select sum(qty)
        from    #T tt
        Where tt.code = t.code
        and tt.ID < = t.ID
        )
from #T t
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000