How to create a result field - 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

SQL Server 2008 Forums

Transact-SQL (2008)

How to create a result field

Author

Topic

patrickjao
Starting Member

24 Posts

Posted - 2012-12-12 : 04:42:38

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-12 : 05:02:26

What is the criteria for Result column

--
Chandu

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-12 : 05:09:39

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.

patrickjao
Starting Member

24 Posts

Posted - 2012-12-12 : 05:47:04

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

patrickjao
Starting Member

24 Posts

Posted - 2012-12-12 : 06:13:14

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.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-12 : 07:03:30

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.

patrickjao
Starting Member

24 Posts

Posted - 2012-12-12 : 07:42:05

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-12 : 07:50:43

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-12 : 07:53:41

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

--
Chandu

patrickjao
Starting Member

24 Posts

Posted - 2012-12-13 : 05:51:08

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

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-13 : 05:57:53

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.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-13 : 06:25:35

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

patrickjao
Starting Member

24 Posts

Posted - 2012-12-13 : 09:08:53

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-13 : 17:11:25

[code]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[/code]

patrickjao
Starting Member

24 Posts

Posted - 2012-12-13 : 23:08:44

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-14 : 10:05:36

cool...you are welcome

patrickjao
Starting Member

24 Posts

Posted - 2012-12-17 : 00:10:09

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-12-17 : 01:02:46

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
[spoiler]Time is always against us[/spoiler]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-17 : 08:23:27

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources