June 30, 2010 at 1:54 pm
Hi,
I have the following sample table:
cust_num inv_num type inv_date due_date diiference
c000101 A01 I 6/10/10 6/15/10
c000101 A01 P 6/25/10 10
c000101 A02 I 5/10/10 6/10/10
C000101 A02 P 6/15/10 5
Thus the difference is the inv_date - due_date corresponding to a customer having the same inv_num and inv_date is the payment date and due_date is the invoice date.
The total days late is 10 + 5 = 15 for this customer.
However the above is going to be a part of a table value function. Hence the sum need to be calculated as a single valued number corresponing to this customer.
Is there any way to do this. I appreciate any help.
June 30, 2010 at 2:22 pm
Your verbal description is not really telling me what you're trying to do.
Please provide table def, sample data, expected result and what you've tried so far in a ready to use format as described in the first link referenced in my signature. The benfit you'll get is a tested version... 😉
June 30, 2010 at 3:09 pm
OK,
Here is the sample table and script and at the botton I am trying to do what I am trying to explain. If this does not help please let me know. I will try my best any further confusion. I appreciate your help.
create table mytable
(
cust_num varchar(50),
inv_num varchar(50),
type char(1),
inv_date datetime,
due_date datetime,
dayslate int
)
select * from mytable
update mytable
set due_date = null
where type = 'P'
insert into mytable
(cust_num, inv_num, type, inv_date, due_date, dayslate)
select 'c000101', 'A01', 'I', '6/10/10', '6/15/10', '' union all
select 'c000101', 'A01', 'P', '6/25/10', '', 10 union all
select 'c000101', 'A02', 'I', '5/10/10', '6/10/10', '' union all
select 'c000101', 'A02', 'P', '6/15/10', '', 5
update mytable
set due_date = null
where type = 'P'
declare @totaldayslate int,
@cust_num varchar(50)
select @cust_num = 'c00101'
select @totaldayslate = Sum(Datediff(day, b.inv_date, a.due_date)) from mytable a
where a.type = 'I' and a.cust_num = 'c000101'
and 1 = (select 1 from mytable b
where a.cust_num = b.cust_num
and a.inv_num = b.inv_num
and b.type = 'P')
June 30, 2010 at 4:13 pm
Based on your sample data, wouldn't it simply be
SELECT cust_num, SUM(dayslate) AS res
FROM mytable
WHERE [TYPE]='P'
GROUP BY cust_num
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply