30, 60, 90+ Days calculation of invoices Totals

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

Analysis Server and Reporting Services (2005)

30, 60, 90+ Days calculation of invoices Totals

Author

Topic

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-27 : 14:21:15

Hi all - am developing my client statement report on my accounts application...

My accounts team need the ability to print off monthly statements for clients. I have done this no probs. I end up with a table of invoices (invoiceid, invoicedate, amount) in my report table. At the bottom of this table, they require the following table:

Current: £amount
30-60 Days: £amount
60-90 Days: £amount
90 + Days: £amount

basically, Say I print the report on 15th Feb. The Current value should be a sum of invoice totals from February. 30-60 Days should be a sum of invoices which were raised in Dec and Jan. 60 - 90 Days should be a sum of invoices raised between Oct and Nov. 90 Days should be a sum of invoices raised before Oct.

I'm assuming I grab the current month from Globals!ExecutionTime but where would I go from there?

Thanks in advance as usual guys :)

Stephen.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-27 : 14:48:06

[code]-- Prepare sample data
DECLARE @Sample TABLE (dt DATETIME, theSum MONEY)

INSERT @Sample
SELECT '20080211', 10 UNION ALL
SELECT '20080210', 20 UNION ALL
SELECT '20080117', 40 UNION ALL
SELECT '20071213', 31 UNION ALL
SELECT '20071130 23:59:12', 30 UNION ALL
SELECT '20071101', 15 UNION ALL
SELECT '20071006', 25 UNION ALL
SELECT '20070906 14:45', 35

DECLARE @FirstOfThisMonth DATETIME

SET @FirstOfThisMonth = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')

SELECT m.DateRange,
SUM(s.theSum) AS Sales
FROM (
SELECT 'Current' AS DateRange,
0 AS Sorting,
@FirstOfThisMonth AS minDate,
DATEADD(DAY, DAY(GETDATE()), @FirstOfThisMonth) AS maxDate

UNION ALL

SELECT '30-60',
1,
DATEADD(MONTH, -2, @FirstOfThisMonth),
@FirstOfThisMonth

UNION ALL

SELECT '60-90',
2,
DATEADD(MONTH, -4, @FirstOfThisMonth),
DATEADD(MONTH, -2, @FirstOfThisMonth)
UNION ALL

SELECT '90+',
3,
'17530101',
DATEADD(MONTH, -4, @FirstOfThisMonth)
) AS m
INNER JOIN @Sample AS s ON s.dt >= m.minDate AND s.dt < m.maxDate
GROUP BY m.DateRange
ORDER BY MIN(m.Sorting)[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-27 : 14:48:56

[code]DateRange Sales
--------- -----
Current 30.00
30-60 71.00
60-90 70.00
90+ 35.00[/code]

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-27 : 15:32:16

Are you asking for a SQL query, or an MDX query?

CODO ERGO SUM

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-27 : 16:42:09

Hi - erm I'm not entirely sure what an MDX query is but I was actually hoping to achieve this in the report itself - not by using another SQL query... Is this possible or should I go with peso's suggestion (thanks peso by the way)
Stephen.

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-27 : 17:32:01

When you declare @sample as a table - is that a temp table? If so - what would be the effect of 2 people running this query for different clients at the same time? Is there any problems?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 00:58:46

@Sample table is only for mimic your environment.
The solution starts with "DECLARE @FirsrtDayOfMonth DATETIME".
Copy and paste that, and replace all instances of @Sample with your real table name.

E 12°55'05.25"
N 56°04'39.16"

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-28 : 04:54:29

Peso - the select to get my invoices and payments is the following:

SELECT invoiceid, invoicedate, cinvoicetotal from invoices where (clientid = @clientid) and (paid = 0)UNION ALL
SELECT paymentcreditid,paymentcreditdate, - amount from paymentcredits where (clientid = @clientid) and (used = 0)

I had:
DECLARE @Sample TABLE (OurID int , dt DATETIME, theSum MONEY)

If I delete the Declare statement, where so I put my SELECT statement?

I have the following at the moment which works:

@clientid int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
DECLARE	@Sample TABLE (OurID int , dt DATETIME, theSum MONEY)

    -- Insert statements for procedure here

--First insert invoices and credits
INSERT	into @Sample
SELECT	invoiceid, invoicedate, cinvoicetotal from invoices where (clientid = @clientid) and (paid = 0)UNION ALL
SELECT	paymentcreditid,paymentcreditdate, - amount from paymentcredits where (clientid = @clientid) and (used = 0)

DECLARE	@FirstOfThisMonth DATETIME

SET	@FirstOfThisMonth = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')


SELECT		m.DateRange,
		isnull(SUM(s.theSum),0) AS Sales
FROM		(
			SELECT	'Current' AS DateRange,
				0 AS Sorting,
				@FirstOfThisMonth AS minDate,
				DATEADD(DAY, DAY(GETDATE()), @FirstOfThisMonth) AS maxDate

			UNION ALL

			SELECT	'30-60',
				1,
				DATEADD(MONTH, -2, @FirstOfThisMonth),
				@FirstOfThisMonth

			UNION ALL

			SELECT	'60-90',
				2,
				DATEADD(MONTH, -4, @FirstOfThisMonth),
				DATEADD(MONTH, -2, @FirstOfThisMonth)
			UNION ALL

			SELECT	'90+',
				3,
				'01-jan-1753',
				DATEADD(MONTH, -4, @FirstOfThisMonth)
		) AS m
LEFT JOIN	@Sample AS s ON s.dt >= m.minDate AND s.dt < m.maxDate
GROUP BY	m.DateRange
ORDER BY	MIN(m.Sorting)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 04:58:06

[code]DECLARE @FirstOfThisMonth DATETIME

SET @FirstOfThisMonth = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101')

SELECT m.DateRange,
SUM(ISNULL(s.theSum, 0)) AS theSales
FROM (
SELECT 'Current' AS DateRange,
0 AS Sorting,
@FirstOfThisMonth AS minDate,
DATEADD(DAY, DAY(GETDATE()), @FirstOfThisMonth) AS maxDate

UNION ALL

SELECT '30-60',
1,
DATEADD(MONTH, -2, @FirstOfThisMonth),
@FirstOfThisMonth

UNION ALL

SELECT '60-90',
2,
DATEADD(MONTH, -4, @FirstOfThisMonth),
DATEADD(MONTH, -2, @FirstOfThisMonth)
UNION ALL

SELECT '90+',
3,
'01-jan-1753',
DATEADD(MONTH, -4, @FirstOfThisMonth)
) AS m
LEFT JOIN (
SELECT invoicedate AS dt,
cinvoicetotal AS theSum
from invoices
where clientid = @clientid
and paid = 0

UNION ALL

SELECT paymentcreditdate AS dt,
-amount AS theSum
from paymentcredits
where clientid = @clientid
and used = 0
) AS s ON s.dt >= m.minDate AND s.dt < m.maxDate
GROUP BY m.DateRange
ORDER BY MIN(m.Sorting)[/code]

E 12°55'05.25"
N 56°04'39.16"

steve_c
Yak Posting Veteran

54 Posts

Posted - 2008-02-28 : 05:07:50

Peso you're a legend!

Thanks for the ultra quick reply!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources