SQL Server Forums
Profile | Register | 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
 New Topic  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
2206 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
2206 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
2206 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
2206 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
17601 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  
 New 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.16 seconds. Powered By: Snitz Forums 2000