AR Aging Report - 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 2000 Forums

Transact-SQL (2000)

AR Aging Report

Author

Topic

onetoof
Starting Member

19 Posts

Posted - 2006-03-28 : 18:32:25

Greetings all,

I am currently building an AR Aging report using SQL.

I am having 2 issues.

1) Although I know that building a query that is reliant on user input is discouraged, in this case it would be usefull. Any ideas on how I would do this, or if its even possible?

2)I currently have a column called DSO which has the number of days that an invoice has been past due. How can I get the query to return colums broken into 0-30 days, over 30, over 60, over 90?

I did see somethinf called "IFLT" or "IFGT"...no luck with these.

Any help would be greatly appreciated.

Thanks,
onetoof

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 18:59:27

What is "IFTL" or "IFGT" ?

2)

select Debtor,
       sum(case when DSO <= 30 then Invoice_Amount else 0 end) as [0 - 30 Days],
       sum(case when DSO > 30 and DSO <= 60 then Invoice_Amount else 0 end) as [31 - 60 Days]
from   Invoices
group by Debtor

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 19:04:31

Sorry too early in the morning
IFLT = If Less Than ?
IFGT = If Greater Than ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-29 : 02:29:41

Did you try Tan's code?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-29 : 02:34:38

Probably it is now too late in the night for onetoof.

onetoof
Starting Member

19 Posts

Posted - 2006-03-29 : 11:01:14

Ok..I tried the code suggested above. I get an error message related to 'sum'. I am using DATEDIFF to calculate the number of days.

datediff(dd,convert(varchar,invoice.inv_due_dt,102),convert(varchar,getdate(),102)) as DSO.

Not sure if that makes a difference with regard to the above code. Can I just substitute:

"datediff(dd,convert(varchar,invoice.inv_due_dt,102),convert(varchar,getdate(),102))"

in place of "DSO" in the above suggested script?

Thanks,
onetoof

onetoof
Starting Member

19 Posts

Posted - 2006-03-29 : 11:16:00

Here is what my code looks like. some of this code was inherited from an aging report accessed to Crystal Reports. Bare with me. Thanks. one

SELECT invoice.inv_co, invoice.inv_no,
CONVERT(VARCHAR,invoice.inv_dt,101),
CONVERT(VARCHAR,invoice.inv_paid_dt,101), invoice.inv_paid_amt, invoice.inv_status,
CONVERT(VARCHAR,invoice.inv_due_dt,101), invoice.inv_type, invoice.inv_category, invoice.inv_base_amt, customer.cus_cd,
customer.cus_name, cuscat.cusc_code,
CONVERT(VARCHAR,GETDATE(),102),
DATEDIFF(dd,CONVERT(VARCHAR,invoice.inv_due_dt,102),CONVERT(VARCHAR,GETDATE(),102)) as DSO

SUM (CASE WHEN (DATEDIFF(dd,CONVERT(VARCHAR,invoice.inv_due_dt,102),CONVERT(VARCHAR,GETDATE(),102))<= 30
THEN invoice.inv_base_amt ELSE 0 END) AS [0 - 30 Days],
SUM (CASE WHEN (datediff(dd,CONVERT(VARCHAR,invoice.inv_due_dt,102),CONVERT(VARCHAR,GETDATE(),102)) )> 30
and (DATEDIFF(dd,CONVERT(VARCHAR,invoice.inv_due_dt,102),
CONVERT(varchar,GETDATE(),102))) <= 60 THEN invoice.inv_base_amt ELSE 0 END) AS [31 - 60 Days]

FROM sxacctnotes.dbo.cuscat cuscat, sxacctnotes.dbo.customer customer, sxacctnotes.dbo.invoice invoice

WHERE invoice.inv_cus_cd = customer.cus_cd AND invoice.inv_cus_grp = customer.cus_grp_cd
AND customer.cus_category = cuscat.cusc_code AND ((invoice.inv_status='O') AND (invoice.inv_co='10')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 11:34:22

Is this what you're looking for? you should use the newer JOIN syntax and you should not use SUM for your 0-30 columns unless you are aggregating something which it doesn't look like you want.


SELECT	invoice.inv_co
	,invoice.inv_no
	,cONVERT(VARCHAR,invoice.inv_dt,101)		inv_dt
	,CONVERT(VARCHAR,invoice.inv_paid_dt,101)	inv_paid
	,invoice.inv_paid_amt
	,invoice.inv_status
	,CONVERT(VARCHAR,invoice.inv_due_dt,101)	inv_due_dt
	,invoice.inv_type
	,invoice.inv_category
	,invoice.inv_base_amt
	,customer.cus_cd
	,customer.cus_name
	,cuscat.cusc_code
	,CONVERT(VARCHAR,GETDATE(),102)			curr_date --?	
	,DATEDIFF(dd, invoice.inv_due_dt, GETDATE())	as DSO

	,CASE 
		WHEN DATEDIFF(dd,invoice.inv_due_dt, GETDATE()) <= 30 
		THEN invoice.inv_base_amt 
		ELSE 0 
		END AS [0 - 30 Days]

	,CASE 
		WHEN	datediff(dd,invoice.inv_due_dt, GETDATE()) > 30 
		and	DATEDIFF(dd,invoice.inv_due_dt, GETDATE()) <= 60 
		THEN invoice.inv_base_amt 
		ELSE 0 
		END AS [31 - 60 Days]

FROM	dbo.cuscat cuscat
inner join dbo.customer customer
		on customer.cus_category = cuscat.cusc_code
inner join dbo.invoice invoice
		on invoice.inv_cus_grp = customer.cus_grp_cd 
		and invoice.inv_cus_cd = customer.cus_cd
WHERE	invoice.inv_status = 'O' 
AND	invoice.inv_co = '10'

Be One with the Optimizer
TG

onetoof
Starting Member

19 Posts

Posted - 2006-03-29 : 12:00:44

TG-

This is exactly what I'm looking for!!!
The script works great! Just 1 small questions if you don't mind?

Is there a way that I could set this script up to take user input in place of the "getdate"?
I realize its discouraged to base a query on user input, but in this case it would be useful. Any thoughts on that? suggestions?

Thanks,
one

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 12:07:45

put it into a stored procedure that takes the parameter and usde the parameter instead of getdate()

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 13:10:23

I'm not sure what you are referring to when you say:
>>I realize its discouraged to base a query on user input

That is the whole idea behind input parameters (as spirit1 suggested). I would discourage basing sql code ie: an entire WHERE clause from being passed in, but value substitution is what variables/parameters are all about.

Be One with the Optimizer
TG

jhermiz

3564 Posts

Posted - 2006-03-29 : 15:13:43

Here you go using TG's code:



CREATE PROCEDURE sel_my_proc @MyDate datetime
AS
BEGIN
SET NOCOUNT ON
SELECT	invoice.inv_co
	,invoice.inv_no
	,cONVERT(VARCHAR,invoice.inv_dt,101)		inv_dt
	,CONVERT(VARCHAR,invoice.inv_paid_dt,101)	inv_paid
	,invoice.inv_paid_amt
	,invoice.inv_status
	,CONVERT(VARCHAR,invoice.inv_due_dt,101)	inv_due_dt
	,invoice.inv_type
	,invoice.inv_category
	,invoice.inv_base_amt
	,customer.cus_cd
	,customer.cus_name
	,cuscat.cusc_code
	,CONVERT(VARCHAR,@MyDate,102)			curr_date --?	
	,DATEDIFF(dd, invoice.inv_due_dt, @MyDate)	as DSO

	,CASE 
		WHEN DATEDIFF(dd,invoice.inv_due_dt, @MyDate) <= 30 
		THEN invoice.inv_base_amt 
		ELSE 0 
		END AS [0 - 30 Days]

	,CASE 
		WHEN	datediff(dd,invoice.inv_due_dt, @MyDate) > 30 
		and	DATEDIFF(dd,invoice.inv_due_dt, @MyDate) <= 60 
		THEN invoice.inv_base_amt 
		ELSE 0 
		END AS [31 - 60 Days]

FROM	dbo.cuscat cuscat
inner join dbo.customer customer
		on customer.cus_category = cuscat.cusc_code
inner join dbo.invoice invoice
		on invoice.inv_cus_grp = customer.cus_grp_cd 
		and invoice.inv_cus_cd = customer.cus_cd
WHERE	invoice.inv_status = 'O' 
AND	invoice.inv_co = '10'
SET NOCOUNT OFF
END
GO

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

onetoof
Starting Member

19 Posts

Posted - 2006-03-29 : 16:02:11

When I EXECUTE the procudure I get this error message:

Server: Msg 201, Level 16, State 4, Procedure sel_my_proc, Line 0
Procedure 'sel_my_proc' expects parameter '@MyDate', which was not supplied.

I expected an input box to pop up but I never had the opp to enter a date. Any ideas?

Thanks,
one

jhermiz

3564 Posts

Posted - 2006-03-29 : 16:12:40

You have to supply it:

exec sel_my_proc '1/1/2006'

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

onetoof
Starting Member

19 Posts

Posted - 2006-03-29 : 16:44:34

Is there any way I can integrate an "input box" that prompts the user for the date?

Thanks,
one

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-30 : 00:45:37

quote:
Originally posted by onetoof

Is there any way I can integrate an "input box" that prompts the user for the date?

Thanks,
one

Use Front End application

Madhivanan

Failing to plan is Planning to fail

onetoof
Starting Member

19 Posts

Posted - 2006-03-30 : 16:41:27

What would it take to run the script through excel?
Is that possible?

In Access..no problem, but Access is not as user friendly as Excel. The idea is to make the report usable to most anyone.
Thoughts?
Suggstions?

Thanks,
one

jhermiz

3564 Posts

Posted - 2006-03-30 : 16:56:43

Create either an excel form, or use InputBox(...) function and then either make a dsn connection pointing to that db or use some sort of connection string and connect to the db.
Its really simple you just need to sit down and think about how you want this...
You've got the hard part done...creating an interface or just an input dialog box is super easy.

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-30 : 17:07:14

I'm not recommending this as a solution but in case you don't know, from query analyzer object browser, right click the SP and select "open". You can "fill-in" each input parameter value then click the "execute" button.

I'd go with jhermiz idea(s) though. You probably don't want users poking around QA even if you do control their login privs.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources