HELP w/ Query please. - 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 2005 Forums

Transact-SQL (2005)

HELP w/ Query please.

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-01-27 : 12:10:18

Hello,
*******************************************
select b.Account as Account, b.accountnum_ as [Account Number], cast(b.credit as decimal(10,2))as [Credit Limit],
cast(b.balance as decimal (10,2)) as Balance,
CASE b.Stopped_ WHEN '0' then 'No' WHEN '1' THEN 'Invoice' WHEN '2' THEN 'Yes' END as [Stop Status],
--b.lastpaiddate as lastpaid--, b.lastpaid as [Last Paid Date]
b.last as lastpaid
from
(
select ctrans.accountnum, ctrans.dataareaid, sum(ctrans.amountcur - ctrans.settleamountcur) as Balance, custtable.name as Account, custtable.blocked as Stopped_,
custtable.accountnum as accountnum_, custtable.creditmax as credit, x.lastpaiddate as last
from custtrans ctrans, custtable
--where ctrans.dataareaid = @DATAAREAID
where ctrans.dataareaid = 'XXX'
--and ctrans.accountnum = @ACCOUNTNUM
and ctrans.accountnum = custtable.accountnum
and ctrans.dataareaid = custtable.dataareaid

group by ctrans.accountnum, ctrans.dataareaid, custtable.name, custtable.blocked, custtable.accountnum,
custtable.creditmax
) b
--START MOD
join simple_intlist_to_tbl ('100000, 100025') i on b.accountnum_ = i.numbers
******************************************************

Now I am interested in joining this query which gives me back a date value I want printed on the original query:

******************************************************
select top(1) ctrans.transdate as lastpaiddate, ctrans.accountnum as acnum, ctrans.dataareaid, ctrans.transdate, ctrans.voucher
from custtrans ctrans
where ctrans.invoice = '' and ctrans.amountcur < 0 and ctrans.dataareaid = 'XXX'
order by transdate desc, voucher desc

********************************************************
I have attempted this but I am getting back NULL values:
******************************************************
select b.Account as Account, b.accountnum_ as [Account Number], cast(b.credit as decimal(10,2))as [Credit Limit],
cast(b.balance as decimal (10,2)) as Balance,
CASE b.Stopped_ WHEN '0' then 'No' WHEN '1' THEN 'Invoice' WHEN '2' THEN 'Yes' END as [Stop Status],
--b.lastpaiddate as lastpaid--, b.lastpaid as [Last Paid Date]
b.last as lastpaid
from
(
select ctrans.accountnum, ctrans.dataareaid, sum(ctrans.amountcur - ctrans.settleamountcur) as Balance, custtable.name as Account, custtable.blocked as Stopped_,
custtable.accountnum as accountnum_, custtable.creditmax as credit, x.lastpaiddate as last
from custtrans ctrans, custtable
--where ctrans.dataareaid = @DATAAREAID
where ctrans.dataareaid = 'XXX'
--and ctrans.accountnum = @ACCOUNTNUM
and ctrans.accountnum = custtable.accountnum
and ctrans.dataareaid = custtable.dataareaid

group by ctrans.accountnum, ctrans.dataareaid, custtable.name, custtable.blocked, custtable.accountnum,
custtable.creditmax
) b
--START MOD
join simple_intlist_to_tbl ('100000, 100025') i on b.accountnum_ = i.numbers

left outer join
(
select top(1) ctrans.transdate as lastpaiddate, ctrans.accountnum as acnum, ctrans.dataareaid, ctrans.transdate, ctrans.voucher
from custtrans ctrans
where ctrans.invoice = '' and ctrans.amountcur < 0 and ctrans.dataareaid = 'XXX'
order by transdate desc, voucher desc
) x
on b.accountnum_ = x.acnum
and b.dataareaid = x.dataareaid

*******************************************************

The first query gives me results like so:

Account Acct Number Credit Limit Balance Stop Status
Name1 100000 5000 0 NO
Name2 100025 2000 200 NO

The second query gives me a result like so (select top(1))

lastpaiddate acnum
1-1-09 100000

I would like to join the tables to include the lastpaiddate after the Stop Status field. I am getting null values when trying this (3rd query):

select b.Account as Account, b.accountnum_ as [Account Number], cast(b.credit as decimal(10,2))as [Credit Limit],
cast(b.balance as decimal (10,2)) as Balance,
CASE b.Stopped_ WHEN '0' then 'No' WHEN '1' THEN 'Invoice' WHEN '2' THEN 'Yes' END as [Stop Status],

x.lastpaiddate
from
(
select ctrans.accountnum, ctrans.dataareaid, sum(ctrans.amountcur - ctrans.settleamountcur) as Balance, custtable.name as Account, custtable.blocked as Stopped_,
custtable.accountnum as accountnum_, custtable.creditmax as credit
from custtrans ctrans, custtable
--where ctrans.dataareaid = @DATAAREAID
where ctrans.dataareaid = 'XXX'
--and ctrans.accountnum = @ACCOUNTNUM
and ctrans.accountnum = custtable.accountnum
and ctrans.dataareaid = custtable.dataareaid

group by ctrans.accountnum, ctrans.dataareaid, custtable.name, custtable.blocked, custtable.accountnum,
custtable.creditmax
) b
--START MOD
join simple_intlist_to_tbl ('100000, 100025') i on b.accountnum_ = i.numbers

left outer join
(
select top(1) ctrans.transdate as lastpaiddate, ctrans.accountnum as acnum, ctrans.dataareaid, ctrans.transdate, ctrans.voucher
from custtrans ctrans
where ctrans.invoice = '' and ctrans.amountcur < 0 and ctrans.dataareaid = 'XXX'
order by transdate desc, voucher desc
) x
on b.accountnum_ = x.acnum
and b.dataareaid = x.dataareaid

***********************************************************

The function simple_intlist_to_tbl is a UDF that goes as follows:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[simple_intlist_to_tbl] (@list nvarchar(MAX))
RETURNS @tbl TABLE (numbers int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int

SELECT @pos = 0, @nextpos = 1

WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (numbers)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END

Any ideas as to how to accomplish this? I'm out of ideas...

Thank you in advance.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 12:18:25

try with outer apply


select b.Account as Account, b.accountnum_ as [Account Number], cast(b.credit as decimal(10,2))as [Credit Limit], 
cast(b.balance as decimal (10,2)) as Balance, 
CASE b.Stopped_ WHEN '0' then 'No' WHEN '1' THEN 'Invoice' WHEN '2' THEN 'Yes' END as [Stop Status],tmp.lastpaiddate
from
( 
select ctrans.accountnum, ctrans.dataareaid, sum(ctrans.amountcur - ctrans.settleamountcur) as Balance, custtable.name as Account, custtable.blocked as Stopped_, 
custtable.accountnum as accountnum_, custtable.creditmax as credit, x.lastpaiddate as last
from custtrans ctrans, custtable
--where ctrans.dataareaid = @DATAAREAID
where ctrans.dataareaid = 'XXX'
--and ctrans.accountnum = @ACCOUNTNUM
and ctrans.accountnum = custtable.accountnum
and ctrans.dataareaid = custtable.dataareaid

group by ctrans.accountnum, ctrans.dataareaid, custtable.name, custtable.blocked, custtable.accountnum, 
custtable.creditmax
) b 
--START MOD
join simple_intlist_to_tbl ('100000, 100025') i on b.accountnum_ = i.numbers
outer apply (
select top(1) ctrans.transdate as lastpaiddate, ctrans.accountnum as acnum, ctrans.dataareaid, ctrans.transdate, ctrans.voucher
from custtrans ctrans
where ctrans.invoice = '' and ctrans.amountcur < 0 and ctrans.dataareaid = 'XXX'
and acnum = b.accountnum_ 
and dataareaid =  b.dataareaid 
order by transdate desc, voucher desc
)tmp

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-01-27 : 12:26:09

viskah,

Thank you for the reply. This returns back dates but not the correct dates coming from the outer apply query.

select top(1) ctrans.transdate as lastpaiddate, ctrans.accountnum as acnum, ctrans.dataareaid, ctrans.transdate, ctrans.voucher
from custtrans ctrans
where ctrans.invoice = '' and ctrans.amountcur < 0 and ctrans.dataareaid = 'XXX' and ctrans.accountnum = '100000'
order by transdate desc, voucher desc

This returns the correct date that I wanted printed for that row:

1-21-08

Another example:

select top(1) ctrans.transdate as lastpaiddate, ctrans.accountnum as acnum, ctrans.dataareaid, ctrans.transdate, ctrans.voucher
from custtrans ctrans
where ctrans.invoice = '' and ctrans.amountcur < 0 and ctrans.dataareaid = 'XXX' and ctrans.accountnum = '100025'
order by transdate desc, voucher desc

This results in: 1-22-08

So I need the results to be:

Account Acct Number Credit Limit Balance Stop_Status lastpaid
Name1 100000 5000 0 NO 1-21-08
Name2 100025 2000 200 NO 1-22-08

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 12:30:41

then it might be problem with your top 1 query. i dont know how your date values are and whats your rule for getting date value is. if you can provide some sample data and explain which date you're looking at, then i might be able to provide you with solution.

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-01-27 : 14:21:16

quote:
Originally posted by visakh16

then it might be problem with your top 1 query. i dont know how your date values are and whats your rule for getting date value is. if you can provide some sample data and explain which date you're looking at, then i might be able to provide you with solution.

The top(1) query is ordered by transdate and voucher. This makes the first row of the query to be the last transdate for the perticular accountnum... which is the field I am interested in.

select top(1) ctrans.transdate as lastpaiddate, ctrans.accountnum as acnum, ctrans.dataareaid, ctrans.transdate, ctrans.voucher
from custtrans ctrans
where ctrans.invoice = '' and ctrans.amountcur < 0 and ctrans.dataareaid = 'XXX'
order by transdate desc, voucher desc

The important fields of custtrans are:

Accountnum transdate dataareaid
100000 1-22-08 XXX
100000 1-25-08 XXX
100025 1-21-08 XXX
100025 2-1-08 XXX

When I select the first record where ctrans.invoice = '' and ctrans.amountcur < 0 and ctrans.dataareaid = 'XXX' and accountnum = '100000' and then order by.......

I will get back this:
100000 1-25-08 XXX

Which is what I am after but now I need to query this with the other select statement.

viskah, is this what you need? Or do you need more examples...?

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-01-27 : 14:43:38

visakh16,

I believe my problem is with the accountnum's:

If I add this to the top(1) query:

select top(1) ctrans.transdate as lastpaiddate, ctrans.accountnum as acnum, ctrans.dataareaid, ctrans.transdate, ctrans.voucher
from custtrans ctrans
where ctrans.invoice = '' and ctrans.amountcur < 0 and ctrans.dataareaid = 'XXX' and ctrans.accountnum = '100000'
order by transdate desc, voucher desc

I get back my desired result. I believe I need to relate the accountnum's to my UDF (simple_intlist_to_tbl ('100000, 100025'))
to be able to process the data the way I am trying to. I'm not quite sure how to nest all of these though for the desired result!

Appreciate all the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources