Query right logic - 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

SQL Server Development (2000)

Query right logic

Author

Topic

Teachme
Starting Member

45 Posts

Posted - 2008-03-03 : 09:35:26

I have a table called Invoice. It has fields such as
Invoicenum duedate Amount and status. The status could be open or close meaning
whether the invoice has been paid or not. What i basically want to achieve is to
find which invoice is delinquent by (1-30days,31-60,61-90,91-120 and >120).
What happens is if there are open invoices for instance in the month of December
and January and the customer made a payment in february for just one month. That payment
goes towards February invoice leaving December and jan open whearas its supposed to be applied
towards December invoice but its a bug in the system. However i want to create a report to
calculate true delinquency. Let me clarify by the following example

Invoicenum duedate amount status
100 20071001 500 o
101 20071101 500 c
102 20071201 500 c
103 20080101 500 o
104 20080201 500 o

Now my query or report shud display the above results as follows

InvoiceNum delinquency Amount
100 61-90 500
103 31-60 500
104 1-30 500

The open and closed invoices might appear in different variations meaning there could be
10 open invoices between 2 closes invoices or anything like that.

In other words I just want to be able to list all the open invoices 30 days apart from each otther.
I just somehow can't get the right logic for it to work in all cases. If i need to
do it thru a stored procedure give any suggestions please. I hope I explained it
clearly if not i'll try to elaborate on it more. thanks for your help.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-03 : 20:24:57

[code]
Create table #invoice ( company char(1) not null,
invoice char(3) not null,
duedate datetime not null,
amount numeric(10,0) not null,
[status] char(1) not null)

Insert Into #invoice ( company,
invoice,
duedate,
amount,
[status] )
SELECT 'A','100', '20071001', 500, 'o' UNION
SELECT 'A','101', '20071101', 500, 'c' UNION
SELECT 'A','102', '20071201', 500, 'c' UNION
SELECT 'A','103', '20080101', 500, 'o' UNION
SELECT 'A','104', '20080201', 500, 'o' UNION
SELECT 'B','100', '20071001', 500, 'c' UNION
SELECT 'B','101', '20071101', 500, 'o' UNION
SELECT 'B','102', '20071201', 500, 'c' UNION
SELECT 'B','103', '20080101', 500, 'o'

Select a.Company,
Invoice,
duedate,
amount,
dpastdue = datediff(d, duedate,maxdate),
pastdue = Case
When datediff(d, duedate,maxdate) <=30 then '0 to 30'
When datediff(d, duedate,maxdate) <=60 then '1 to 30'
When datediff(d, duedate,maxdate) <=90 then '1 to 30'
When datediff(d, duedate,maxdate) >90 then '91 plus'
End

FROM #invoice a inner join (Select Company,dateadd(m,1,max(duedate)) as maxdate --adds a month to the highest invoice date
From #invoice
where [Status] = 'o'
Group by Company ) b
on a.Company = b.Company

Where a.[status] = 'o'

results
--A 100 2007-10-01 00:00:00.000 500 152 91 plus
--A 103 2008-01-01 00:00:00.000 500 60 1 to 30
--A 104 2008-02-01 00:00:00.000 500 29 0 to 30
--B 101 2007-11-01 00:00:00.000 500 92 91 plus
--B 103 2008-01-01 00:00:00.000 500 31 1 to 30

Drop Table #invoice
[/code]

Fix that bug! telling someone they are 90+, when only 30+ can be bad for business...

This code compares the invoice date to 1 month past the highest invoice date for the company. (assumes that March 1 st for a February 1st due date reflects ~30 days, or one statement past due

Poor planning on your part does not constitute an emergency on my part.

Teachme
Starting Member

45 Posts

Posted - 2008-03-04 : 09:09:06

I think u might be on the right track as far as what i want but my results would be slightly different. if below was my data in the table as the way its supposed to be

SELECT 'A','100', '20071001', 500, 'o' UNION
SELECT 'A','101', '20071101', 500, 'c' UNION
SELECT 'A','102', '20071201', 500, 'c' UNION
SELECT 'A','103', '20080101', 500, 'o' UNION
SELECT 'A','104', '20080201', 500, 'o'

my results would be as follows:

--A 102 500 90 plus days
--A 103 500 60 plus days
--A 104 500 1-30 days

because what i'm trying to do is to apply every closed invoice to the first open invoice and so on. i hope it makes it clear. thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-04 : 10:06:01

[code]-- prepare test data
declare @test table (amount int)

Insert @test
select 10 union all
select 20 union all
select 30 union all
select 40 union all
select 50 union all
select 60 union all
select 70 union all
select 80

-- Peso 1
SELECT SUBSTRING(q.[Desc], 3, 8000) AS [Desc],
COUNT(*) [Count of Amount],
SUM(Amount) [Sum of Amount]
FROM (
SELECT CASE
WHEN Amount <= 30 THEN '1#Less than or equal to 30'
WHEN Amount BETWEEN 31 AND 50 THEN '2#31 to 50'
WHEN Amount BETWEEN 51 AND 70 THEN '3#51 to 70'
WHEN Amount BETWEEN 71 AND 100 THEN '4#71 to 100'
WHEN Amount > 100 THEN '5#Over 100'
END AS [Desc],
Amount
FROM @Test
) q
GROUP BY SUBSTRING(q.[Desc], 3, 8000)
ORDER BY MIN(LEFT(q.[Desc], 1))

-- Peso 2
SELECT g.Msg,
SUM(CASE WHEN t.Amount IS NULL THEN 0 ELSE 1 END),
SUM(CASE WHEN t.Amount IS NULL THEN 0 ELSE t.Amount END)
FROM (
SELECT 'Less than or equal to 30' AS Msg, -2147483648 AS minLevel, 30 AS maxLevel UNION ALL
SELECT '31 to 50', 31, 50 UNION ALL
SELECT 'Fifthyone to 70', 51, 70 UNION ALL
SELECT '71 to 100', 71, 100 UNION ALL
SELECT 'Over C', 101, 2147483647
) AS g
LEFT JOIN @Test AS t ON t.Amount BETWEEN g.minLevel AND g.maxLevel
GROUP BY g.Msg
ORDER BY MIN(g.minLevel)[/code]

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

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-04 : 14:02:16

quote:
Originally posted by Teachme

I think u might be on the right track as far as what i want but my results would be slightly different. if below was my data in the table as the way its supposed to be

SELECT 'A','100', '20071001', 500, 'o' UNION
SELECT 'A','101', '20071101', 500, 'c' UNION
SELECT 'A','102', '20071201', 500, 'c' UNION
SELECT 'A','103', '20080101', 500, 'o' UNION
SELECT 'A','104', '20080201', 500, 'o'

my results would be as follows:

--A 102 500 90 plus days
--A 103 500 60 plus days
--A 104 500 1-30 days

because what i'm trying to do is to apply every closed invoice to the first open invoice and so on. i hope it makes it clear. thanks

That is a little different than your first example ;)

Can you apply what Peso demonstrated?

Poor planning on your part does not constitute an emergency on my part.

Teachme
Starting Member

45 Posts

Posted - 2008-03-04 : 14:52:43

I have no idea what pesto is trying to suggest...As far as my example is concerned...i know how to calculate difference in days...what i wana do is to list all the open invoices with their due dates changed temporarily...what i mean is if i have one open invoice in the month of january and then a close invoice in the month of february...the system was supposed to post that payment of february towards january but it doesn't do that..so now january invoice is open but the delinquency is not gona be counted since january...it will be counted since february..because we look at that (Feb) payment as if it was posted towards the month of january so the delinquency would be 1-30days (days since 02/01/08 till todays date). i hope now u understand what i'm trying to achieve. thanks

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-04 : 17:09:46

[code]

Create table #invoice ( company char(1) not null,
invoice char(3) not null,
duedate datetime not null,
amount numeric(10,0) not null,
[status] char(1) not null)

Insert Into #invoice ( company,
invoice,
duedate,
amount,
[status] )
SELECT 'A','100', '20071001', 500, 'o' UNION
SELECT 'A','101', '20071101', 500, 'c' UNION
SELECT 'A','102', '20071201', 500, 'c' UNION
SELECT 'A','103', '20080101', 500, 'o' UNION
SELECT 'A','104', '20080201', 500, 'o' UNION
SELECT 'B','100', '20071001', 500, 'c' UNION
SELECT 'B','101', '20071101', 500, 'o' UNION
SELECT 'B','102', '20071201', 500, 'c' UNION
SELECT 'B','103', '20080101', 500, 'o'

Select main.Company,
main.Invoice,
main.duedate,
main.amount,
Case
When datediff(d, main.duedate,getdate()) <=30 then '0 to 30'
When datediff(d, main.duedate,getdate()) <=60 then '30 to 59'
When datediff(d, main.duedate,getdate()) <=90 then '61 to 90'
When datediff(d, main.duedate,getdate()) >90 then '91 plus'
End as PastDue

FROM (Select Company,Invoice, DueDate,Amount,[Status], (select Count(*)+1 from #invoice b
where b.Invoice < a.invoice
and b.Company = a.Company) as Ct
FROM #invoice a
Group by Company,Invoice, DueDate,Amount,[Status]) main

left join (Select Company,Invoice, DueDate,Amount,[Status], (select Count(*) from #invoice b
where b.Invoice < a.invoice AND a.[Status] = 'c'
and b.Company = a.Company) as Ct
FROM #invoice a
Where a.[Status] = 'c'
Group by Company,Invoice, DueDate,Amount,[Status]) paid

on main.Company = paid.Company and main.ct =paid.ct
Where paid.DueDate is null

--results
--A 102 2007-12-01 00:00:00.000 500 91 plus
--A 103 2008-01-01 00:00:00.000 500 61 to 90
--A 104 2008-02-01 00:00:00.000 500 30 to 59
--B 100 2007-10-01 00:00:00.000 500 91 plus
--B 102 2007-12-01 00:00:00.000 500 91 plus
--B 103 2008-01-01 00:00:00.000 500 61 to 90

Drop Table #invoice

[/code]

That seems to do the trick.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources