Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help writing a mssql 2000 script 1

Status
Not open for further replies.
tbscmgi

tbscmgi

MIS
Sep 17, 2003
66
US
I have a number table with an account number and I want to find that account number in a balance table and when I find that account number I want the most recent one by date, if account not found I want to look in the previous month balance table.

Thank for any help I can get.
 
Sort by date Sort by votes
what have you written so far?

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Paul-
I can do the basic SQL never done a procedure.
I create a table with the account number.

Create table test_ANB (number varchar (12),code_status varchar (1),code_branch varchar (3),code_account_type varchar (4))

Insert into test_ANB
select number, code_status , code_branch , code_account_type
from profile_ai where code_status in('1','2') and left(number,2) in ('00','02','10')

This is where I'm stuck.

Thanks
Tony
 
Upvote 0 Downvote
and what if the account number is not found in previous month balance table also? How many months back you want to search? Can you post some simple data and desired result from that data?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
Paul-
Six months of table.

data:
nbr date balance
260942006-07-31USD+0000006782.84
263282006-07-31USD+0000011943.36
301222006-07-31USD+0000006113.35
340992006-07-31USD+0000018162.96

814712006-09-06USD+0000000000.00
664432006-09-06USD+0000000487.00
665162006-09-06USD+0000000307.00
666992006-09-06USD+0000000399.00

result:
number, date, balance


Thanks
Tony
 
Upvote 0 Downvote
Is that what you want your results to look like?
Why is the balance padded with the '0's

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Can you post the structure of both tables. I see a date there and I'm not sure which table that resides in? Is it in the balance table or number table?

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Paul-
The data is the raw data.

table test_ANB:
number varchar (12)
code_status varchar (1)
code_branch varchar (3)
code_account_type varchar (4)

table balance:

accountno varchar (35)
balancedate datetime (8)
balance money (8)

thanks
Tony
 
Upvote 0 Downvote
Does this work for you?

Code: 
INSERT INTO @nbr values(26094)
INSERT INTO @nbr values(26328)
INSERT INTO @nbr values(30122)
INSERT INTO @nbr values(34099)
INSERT INTO @nbr values(81471)
INSERT INTO @nbr values(66443)

INSERT INTO @balance values(26094, '2006-07-31', 6782.84)
INSERT INTO @balance values(26094, '2006-06-30', 5782.84)
INSERT INTO @balance values(26094, '2006-05-30', 2782.84)
INSERT INTO @balance values(26328, '2006-07-31', 1150.84)
INSERT INTO @balance values(26328, '2005-07-31', 6782.84)
INSERT INTO @balance values(81471, '2006-07-31', 9982.84)
INSERT INTO @balance values(81471, '2006-06-30', 42.84)
INSERT INTO @balance values(66443, '2006-07-31', 16782.84)
INSERT INTO @balance values(66443, '2006-07-31', 6782.84)

SELECT b.nbr
       ,b.date
       ,b.balance
FROM @nbr a
 JOIN @balance b
  on a.nbr = b.nbr 
WHERE b.date = (select max(date) from @balance)

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Paul-
That work really well on one balance table, but if one of the account from the number table in the current balance table I need it to check the previous month balance table up to 6 months

I'm looking for the max balance date on each account, also the balance date can be different for each account, some can have a max date 2006-12-31 or 2006-12-15 or 2006-12-05.

Thanks
Tony
 
Upvote 0 Downvote
This should give you the max balance date for each nbr.

Code: 
declare @nbr table ( id int 
                     ,nbr varchar(12))

DECLARE @balance table( id int
                  ,nbr varchar(12)
                 ,date datetime
                 ,balance money)

INSERT INTO @nbr values(1,26094)
INSERT INTO @nbr values(2,26328)
INSERT INTO @nbr values(3,30122)
INSERT INTO @nbr values(4,34099)
INSERT INTO @nbr values(5,81471)
INSERT INTO @nbr values(6,66443)

INSERT INTO @balance values(1, 26094, '2006-07-31', 6782.84)
INSERT INTO @balance values(1,26094, '2006-06-30', 5782.84)
INSERT INTO @balance values(1,26094, '2006-05-30', 2782.84)
INSERT INTO @balance values(2,26328, '2006-07-31', 1150.84)
INSERT INTO @balance values(2,26328, '2005-07-31', 6782.84)
INSERT INTO @balance values(5,81471, '2006-07-31', 9982.84)
INSERT INTO @balance values(5,81471, '2006-06-30', 42.84)
INSERT INTO @balance values(6,66443, '2006-07-31', 16782.84)
INSERT INTO @balance values(6,66443, '2006-07-31', 6782.84)

select b.nbr
       ,max(b.date) as date
from @nbr a join @balance b on a.id = b.id
GROUP BY b.nbr

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
I think your complete select will look something like this.

Code: 
declare @nbr table ( id int 
                     ,nbr varchar(12))

DECLARE @balance table( id int
                  ,nbr varchar(12)
                 ,date datetime
                 ,balance money)

INSERT INTO @nbr values(1,26094)
INSERT INTO @nbr values(2,26328)
INSERT INTO @nbr values(3,30122)
INSERT INTO @nbr values(4,34099)
INSERT INTO @nbr values(5,81471)
INSERT INTO @nbr values(6,66443)

INSERT INTO @balance values(1, 26094, '2006-07-31', 6782.84)
INSERT INTO @balance values(1,26094, '2006-06-30', 5782.84)
INSERT INTO @balance values(1,26094, '2006-05-30', 2782.84)
INSERT INTO @balance values(2,26328, '2006-07-31', 1150.84)
INSERT INTO @balance values(2,26328, '2005-07-31', 6782.84)
INSERT INTO @balance values(5,81471, '2006-07-31', 9982.84)
INSERT INTO @balance values(5,81471, '2006-06-30', 42.84)
INSERT INTO @balance values(6,66443, '2006-07-31', 16782.84)
INSERT INTO @balance values(6,66443, '2006-07-31', 6782.84)

SELECT b.nbr
      ,b.date
      ,a.balance
FROM @balance a
JOIN
(
select b.id
       ,b.nbr
       ,max(b.date) as date
from @nbr a join @balance b on a.id = b.id
GROUP BY b.id,b.nbr
) b
ON a.id = b.id and a.date = b.date

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Pual-
I'm half way there.

The secord script get me the correct record, but I can't get the balance.

Just let you know each balance table have over 2 million records.

Would it be wise merge the six balance table into one temp table?

Thanks
Tony
 
Upvote 0 Downvote
Did you want that account to appear IF there is no record in balance tables for last 6 months?


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP
 
Upvote 0 Downvote
did you look at the third script I added that adds the balance?

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Paul-
Yes, I would like it to appear.

I test the third script and getting this error
The column prefix 'a' does not match with a table name or alias name used in the query.

Thanks
Tony
 
Upvote 0 Downvote
Post your query, you must be missing an alias.

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Paul-
SELECT b.AccountNo
,b.BalanceDate
,c.OpenBalance
FROM balance_2007jan c
JOIN (select b.accountno, max(b.balancedate) as Date
FROM #test_ANB a
JOIN balance_2007jan b
on a.number = b.accountno
GROUP BY b.accountno) b ON a.number = b.accountno and c.balancedate = b.balancedate


Thanks
Tony
 
Upvote 0 Downvote
Here is the problem

Code: 
SELECT  b.AccountNo
       ,b.BalanceDate
       ,c.OpenBalance
FROM balance_2007jan c
JOIN (select b.accountno, max(b.balancedate) as Date
FROM #test_ANB a
 JOIN balance_2007jan b
  on a.number = b.accountno
GROUP BY b.accountno) b ON [b]a[/b].number = b.accountno and c.balancedate = b.balancedate

Change the Bold a to c

Code: 
SELECT  b.AccountNo
       ,b.BalanceDate
       ,c.OpenBalance
FROM balance_2007jan c
JOIN (select b.accountno
        ,max(b.balancedate) as Date
         FROM #test_ANB a
         JOIN balance_2007jan b
         on a.number = b.accountno
        GROUP BY b.accountno) b
  ON c.number = b.accountno and c.balancedate = b.balancedate

- Paul [batman]
- If at first you don't succeed, find out if the loser gets anything.
 
Upvote 0 Downvote
Paul-
I'm having the following error.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'balancedate'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'BalanceDate'.

Tony
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
568
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
1K
Chris Miller
Chris Miller
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
860
SashaBuilder3
SashaBuilder3
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
536
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
770
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top