| Author |
 Topic  |
|
|
patrickjao
Starting Member
Indonesia
24 Posts |
 Posted - 12/12/2012 : 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
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 12/12/2012 : 05:02:26
|
What is the criteria for Result column
--
Chandu |
 |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/12/2012 : 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
Indonesia
24 Posts |
Posted - 12/12/2012 : 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
Indonesia
24 Posts |
Posted - 12/12/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/12/2012 : 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
Indonesia
24 Posts |
Posted - 12/12/2012 : 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
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 12/12/2012 : 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 |
Edited by - bandi on 12/12/2012 08:05:53 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 12/12/2012 : 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
Indonesia
24 Posts |
Posted - 12/13/2012 : 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
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 12/13/2012 : 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
Flowing Fount of Yak Knowledge
India
1711 Posts |
Posted - 12/13/2012 : 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
Indonesia
24 Posts |
Posted - 12/13/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/13/2012 : 17:11:25
|
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 |
|
|
|
patrickjao
Starting Member
Indonesia
24 Posts |
Posted - 12/13/2012 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/14/2012 : 10:05:36
|
| cool...you are welcome |
|
|
|
patrickjao
Starting Member
Indonesia
24 Posts |
Posted - 12/17/2012 : 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)
Singapore
16769 Posts |
Posted - 12/17/2012 : 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
Time is always against us
|
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/17/2012 : 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 |
|
|
| |
Topic |
|
|
|
How to create a result field
