Query to catch data dynamic 1

[rotelmak]

I am novice in sql server 2000.

My test table have this columns with this data

cus_id inv_n date_ip debit credit
1 101 10.01.07 100.00
1 101 25.01.07 70.00
1 101 26.01.07 10.00

2 102 11.01.07 200.00
2 102 26.01.07 150.00
2 103 27.01.07 300.00

How can I got with query this resulsts

cus_id inv_n date_ip debit credit total
1 101 10.01.07 100.00 100.00
1 101 25.01.07 70.00 30.00
1 101 26.01.07 10.00 20.00

2 102 11.01.07 200.00 200.00
2 102 26.01.07 150.00 50.00
2 103 27.01.07 300.00 350.00

 

[bborissov]

DECLARE @Temp TABLE (cus_id int, inv_n int, date_ip datetime,   debit numeric(10,2),  credit numeric(10,2))
INSERT INTO @Temp VALUES(1,101,'20070110',100.00,0)
INSERT INTO @Temp VALUES(1,101,'20070125',0,70.00)
INSERT INTO @Temp VALUES(1,101,'20070126',0,10.00)

INSERT INTO @Temp VALUES(2,102,'20070111',200.00,0)
INSERT INTO @Temp VALUES(2,102,'20070126',0,150.00)
INSERT INTO @Temp VALUES(2,103,'20070127',300.00,0)

SELECT cus_id,
       Inv_N,
       date_ip,
       debit,
       credit,
       (debit - Credit + ISNULL((SELECT SUM(debit-credit) FROM @Temp Tmp WHERE Tmp.cus_id  = Tmp2.cus_id AND
                                                                               Tmp.date_ip < Tmp2.date_ip),0)) AS Total
FROM @Temp Tmp2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

Thank you Borislav, but values are varibile and table have thousands records. Is it posibble to do this with data read from table and put in temp table.

 

[monksnake]

That query that bborisov wrote will work for any amount of records.

Just substitute your table name for @Temp and make sure the column names are correct.

[small]"Mom........MEATLOAF!!!! F***!!!!"[/small]
<.

 

[bborissov]

As monksnake said first part of the query was just preparing test data. Just copy and past it in QA, Play with different data and if you satisfied from the results remove data test data preparation part:

DECLARE @Temp TABLE (cus_id int, inv_n int, date_ip datetime,   debit numeric(10,2),  credit numeric(10,2))
INSERT INTO @Temp VALUES(1,101,'20070110',100.00,0)
INSERT INTO @Temp VALUES(1,101,'20070125',0,70.00)
INSERT INTO @Temp VALUES(1,101,'20070126',0,10.00)

INSERT INTO @Temp VALUES(2,102,'20070111',200.00,0)
INSERT INTO @Temp VALUES(2,102,'20070126',0,150.00)
INSERT INTO @Temp VALUES(2,103,'20070127',300.00,0)

And change @Temp to your actual table name in the rest of the query

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

Works super.
Thank you Borislav
Thank you Monksnake

Star from beginer.

 

[rotelmak]

Borislav,
I tested your code in my real table and found bug. If values in field data_ip are equal in all records for cust_id(same date ie 20.01.2007), values in field total are wrong.

Any solution. Please help me

 

[bborissov]

Did you have any primary key in that table?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

No, there is not any primary key

 

[bborissov]

What makes each record unique?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

Table structure is:

work_year - char(4)
work_tran - char(6)
work_recno - integer (not identity)
cus_id - char(9)
inv_n - char(9)
date_ip - datetime
debit - decimal(13,2)
credit - decimal(13,2)

table is indexed on work_year,work_tran,work_recno (uniqe index).

 

[bborissov]

If work_recno is some kind of incrementing field (with your help ) you could use something like that:

SELECT cus_id,
       Inv_N,
       date_ip,
       debit,
       credit,
       (debit - Credit + ISNULL((SELECT SUM(debit-credit)
       FROM @Temp Tmp WHERE Tmp.cus_id  = Tmp2.cus_id AND                                                                        
                         Tmp.date_ip < Tmp2.date_ip AND
                        Tmp.work_recno < Tmp2.work_recno),0))
 AS Total
FROM @Temp Tmp2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

There are two forms for input data:

- In first form user input values for work_year, work_tran and date_ip.

- In second form user input values for cus_id, inv_n, debit, credit.
Can be more records then one in the same work_year, work_tran and date_ip. For each recodrd I add value in the field work_recno begining from 1 (1, 2, 3 etc) and I catch values input in first form (work_year, work_tran, date_ip).

 

[rotelmak]

Borislav ,
It do not work

I got this results for one cust_id (1):
debit credit total work_recno
2007;1;1;101;10.01.2007;100.00; 0.00; 100.00; 1
2007;1,1;101;10.01.2007; 0.00; 70.00; -70.00; 2
2007;1,1;101;10.01.2007; 0.00; 10.00; -10.00; 3

Corect results should be:

debit credit total work_recno
2007;1;1;101;10.01.2007;100.00; 0.00; 100.00 1
2007;1,1;101;10.01.2007; 0.00; 70.00; 30.00 2
2007;1,1;101;10.01.2007; 0.00; 10.00; 20.00 3

 

[bborissov]

DECLARE @Temp TABLE (cus_id int, inv_n int, date_ip datetime,   debit numeric(10,2),  credit numeric(10,2), work_recno int)
INSERT INTO @Temp VALUES(1,101,'20070110',100.00,0,1)
INSERT INTO @Temp VALUES(1,101,'20070110',0,70.00,2)
INSERT INTO @Temp VALUES(1,101,'20070110',0,10.00,3)

INSERT INTO @Temp VALUES(2,102,'20070111',200.00,0,1)
INSERT INTO @Temp VALUES(2,102,'20070126',0,150.00,2)
INSERT INTO @Temp VALUES(2,103,'20070127',300.00,0,3)

SELECT cus_id,
       Inv_N,
       date_ip,
       debit,
       credit,
       (debit - Credit + ISNULL((SELECT SUM(debit-credit) FROM @Temp Tmp WHERE Tmp.cus_id  = Tmp2.cus_id AND
                                                                               (Tmp.date_ip <= Tmp2.date_ip AND Tmp.work_recno <Tmp2.work_recno)),0)) AS Total
FROM @Temp Tmp2

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[rotelmak]

GREAT

Thank you Borislav

one star more