due dates - 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)

due dates

Author

Topic

troyz
Starting Member

7 Posts

Posted - 2007-04-18 : 16:54:48

I have a table that has monthly scheduled payments like this:
CUSTOMER DUEDATE PAYMENT STATUS
102115 01/02/2007 59.00 PAID
104583 01/07/2007 59.00 UNPAID
104110 01/09/2007 59.00 PAID
104583 02/07/2007 59.00 UNPAID
104583 03/07/2007 59.00 UNPAID
107672 03/07/2007 59.00 UNPAID

I need to be able to select all unpaid payments based on a date entered, but if exist older unpaid payment for same day of month then use that instead.

Of course I can easily do this for one day:
Select payment,duedate
from table
where duedate='03/07/2007' and status='unpaid'

I need customer number 104583 to return row 01/07/2007 as this is oldest unpaid payment for this customer and not the 02/07/2007 or 03/07/2007.

I don't believe I can use a group by for what I am doing.

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-18 : 17:05:26

One way of doing this is -

Select customer, payment,duedate
from table a
where status='unpaid'
and duedate= (select min(duedate) from table b where b.customer = a.customer and status='unpaid')

troyz
Starting Member

7 Posts

Posted - 2007-04-18 : 17:09:18

Thanks, I will try this

troyz
Starting Member

7 Posts

Posted - 2007-04-18 : 17:22:08

this will only pull all customers oldest unpaid invoices.

The table contains future payments also as these are monthly payment plans. So I need to select a date to process payments for that date.

If today was 3-7-2007 I would enter 3-7-2007 and would need the query to return all unpaid rows with 3-7-2007, but if previous unpaid date exists for a customer due on 3-7-2007 return that row instead.

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-18 : 17:42:21

Try fixing the query by checking the input date in one of the WHERE clause.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-18 : 20:20:55

Is this what you were asking?


declare @Customer varchar(20),@DueDate Datetime

set @Customer = '104583'
set @DueDate = '03/07/2007'

select top 1 a.CUSTOMER, a.DUEDATE, a.PAYMENT,a.Status
from Tb1 a
where a.Status = 'UNPAID'
and a.Customer = @Customer
and a.DueDate <= @DueDate
order by a.DueDate asc

Please explain what this is going to be used for. If this is for any type of accounting purposes, this methodology is not the correct one you should be using.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-18 : 20:30:16

[code]
declare @table table
(
CUSTOMER int,
DUEDATE datetime,
PAYMENT decimal(5,2),
STATUS varchar(6)
)
insert into @table
select 102115, '01/02/2007', 59.00, 'PAID' UNION ALL
select 104583, '01/07/2007', 59.00, 'UNPAID' UNION ALL
select 104110, '01/09/2007', 59.00, 'PAID' UNION ALL
select 104583, '02/07/2007', 59.00, 'UNPAID' UNION ALL
select 104583, '03/07/2007', 59.00, 'UNPAID' UNION ALL
select 107672, '03/07/2007', 59.00, 'UNPAID'

select t.*
from @table t
inner join
(
select CUSTOMER, MIN_DUEDATE = MIN(DUEDATE)
from @table t
where STATUS = 'UNPAID'
group by CUSTOMER
) u
on t.CUSTOMER = u.CUSTOMER
and t.DUEDATE = u.MIN_DUEDATE
/*
CUSTOMER DUEDATE PAYMENT STATUS
----------- ----------- ------- ------
104583 2007-01-07 59.00 UNPAID
107672 2007-03-07 59.00 UNPAID
*/
[/code]

troyz
Starting Member

7 Posts

Posted - 2007-04-19 : 10:57:29

Thanks! That looks exactly what I need I will test it.

troyz
Starting Member

7 Posts

Posted - 2007-04-19 : 16:08:27

Closer, but not correct yet as there are future payments in table also and we are running payments for a particular day entered. Picture 12 month payment plans with an invoice for each month existing in the table. (Great Plains btw, not much I can do about it)

We pull payments based on a day of the month entered, if today is 4-19-2007 the user may enter 4-19-2007 or another day (variable prompted from an existing SP), a search returns all unpaid invoices due on 4-19-2007. These are run through a credit card program and if successfully charged the invoices are marked "Paid". The problem we run into is that a customer may be past due 2 or 3 months, i.e. owed payment from 2-19-2007 or 3-19-2007. We want to pull all day 4-19-2007 but if exist older 19th invoice date use that one instead.

This is because the customer cannot pay payment number 12 if they haven't paid payment 10 or 11 and currently payment 12 could be marked paid.

What was created does not work with future payments that exist.

Better Table Example:
CUSTOMER DUEDATE PAYMENT STATUS
102115 03/02/2007 59.00 PAID
102115 04/02/2007 59.00 PAID
102115 05/02/2007 59.00 UNPAID
106222 03/19/2007 59.00 PAID
106222 04/19/2007 59.00 UNPAID
104583 01/19/2007 59.00 UNPAID
104110 01/09/2007 59.00 PAID
104110 02/09/2007 59.00 PAID
104110 03/09/2007 59.00 PAID
104110 04/09/2007 59.00 UNPAID
104110 05/09/2007 59.00 UNPAID
106777 05/09/2007 59.00 UNPAID
104583 02/19/2007 59.00 UNPAID
104583 03/19/2007 59.00 UNPAID
104583 04/19/2007 59.00 UNPAID
104583 05/19/2007 59.00 UNPAID
107672 03/19/2007 59.00 UNPAID
107672 04/19/2007 59.00 UNPAID

The query would return if using date 4-19-2007:
104583 02/19/2007 59.00 UNPAID
106222 04/19/2007 59.00 UNPAID
107672 03/19/2007 59.00 UNPAID

It would not return other dates not equal to the day in the date entered or future dates.

Do I need some kind of Select Case with a temp table to do this?

cvraghu
Posting Yak Master

187 Posts

Posted - 2007-04-19 : 17:14:27

As mentioned before try to use the @inputdate in the queries

troyz
Starting Member

7 Posts

Posted - 2007-04-20 : 09:29:46

I understand @inputdate. I am currently using a variable for the date. What I don't understand is if I enter 4-19-2007 for the variable I need it to return:

104583 02/19/2007 59.00 UNPAID
106222 04/19/2007 59.00 UNPAID
107672 03/19/2007 59.00 UNPAID

In the example 1 customer has no previous unpaid payments so 4-19-2007 is selected, the other 2 have invoices selected for 2-19-2007 and 3-19-2007.

I don't know how to use @inputdate and also return the min(duedate) for a particular day (like the 19th).

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-04-20 : 13:53:17

I see what you're saying now.

You are saying that if you enter a date of 4/19/2007 since the actual day is the 19th, you would only want to return other dates that are on the 19th. The code for this is below using kt query (His was better than mine).



declare @table table
(
	CUSTOMER 	int,
	DUEDATE 	datetime,
	PAYMENT 	decimal(5,2),
	STATUS		varchar(6)
)


insert into @table
select	102115, '01/02/2007', 59.00, 'PAID'		UNION ALL
select	104583, '01/07/2007', 59.00, 'UNPAID'	UNION ALL
select	104110, '01/09/2007', 59.00, 'PAID'		UNION ALL
select	104583, '02/07/2007', 59.00, 'UNPAID'	UNION ALL
select	104583, '03/07/2007', 59.00, 'UNPAID'	UNION ALL
select	107672, '03/07/2007', 59.00, 'UNPAID'   UNION ALL
select	104583, '03/19/2007', 59.00, 'UNPAID'	UNION ALL
select	102115, '01/19/2007', 59.00, 'UNPAID'	UNION ALL
select	111111, '05/19/2007', 59.00, 'UNPAID'	UNION ALL
select	111111, '12/19/2007', 59.00, 'UNPAID'	UNION ALL
select	123456, '03/19/2007', 59.00, 'UNPAID'


declare @InputDate DateTime
set @InputDate = '4/19/2007'

select	t.*
from	@table t
inner join
(
	select	CUSTOMER, MIN_DUEDATE = MIN(DUEDATE)
	from	@table t
	where	STATUS	= 'UNPAID'
	and t.DueDate <= @InputDate
	and day(t.DueDate) = day(@InputDate)
	group by CUSTOMER
) u
on	t.CUSTOMER	= u.CUSTOMER
and	t.DUEDATE	= u.MIN_DUEDATE

troyz
Starting Member

7 Posts

Posted - 2007-04-20 : 16:54:31

Thanks!!!!!!

That does it exactly.

I need to learn my subquerys.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources