Days betweeen dates 1

[chip12]

I have code that gives me that gives me the customer name,the purchase date, the date the customer paid out the contract completly but never defaulted before paying out.

I am trying to find out the average numers of days between the 1st and 2nd contract purchase, 2nd and 3rd purchase contract etc. thanks for your help.. I will be grouping this by customer. This code should work but gives an aggregate error message. Would diffdate work here?

SELECT dbo_MRC_Merchants.BusinessName AS Merchant,
dbo_CNT_Contracts.FirstFundingDate AS Purchase,
dbo_CNT_Contracts.BalanceZeroDate AS Closed,
dbo_CNT_v_DefaultedDateHistory.DefaultedDate AS Defaulteddate,
avg(dbo_CNT_Contracts.BalanceZeroDate - dbo_CNT_Contracts.FirstFundingDate)

FROM (dbo_MRC_Merchants INNER JOIN dbo_CNT_Contracts ON dbo_MRC_Merchants.MerchantID = dbo_CNT_Contracts.MerchantID)
LEFT JOIN dbo_CNT_v_DefaultedDateHistory ON dbo_CNT_Contracts.ContractID = dbo_CNT_v_DefaultedDateHistory.contractid
WHERE (((dbo_CNT_Contracts.BalanceZeroDate) Is Not Null) AND ((dbo_CNT_v_DefaultedDateHistory.DefaultedDate) Is Null))
Group BY dbo_MRC_Merchants.BusinessName;

 

[chip12]

Can anyone tell me how to convert two different dates to a number format so that I can subtract them in ACCESS?

 

[RoyVidar]

A little secret - dates are stored as numbers - you can just subtract them, format the result as a number, and voila...

To do some experimentation, enter VBE and the immediate pane (hit ctrl+g somewhere in Access), then enter the following expressions, and hit enter on them:

[tt]? date
? format(date, "0")
? format(38539, "mm/dd/yyyy")
? format(date - 15, "0")
? date - 15[/tt]

Roy-Vidar

 

[chip12]

I used Datediff for the answer. It gave me the numbers of days between the purchase and the payoff. However.I have customers who have several purchases over time. I need to find out the average time between purchases

 

[MichaelRed]

Your inquiry is ambigious and the data structure(s) are omitted.

Please find, review and utilize one or more of the several threads and faqs re posting etiquite





MichaelRed

 

[chip12]

Thnaks .. I'll be more clear. I need to to get the average days between dates at selected intervals. I have customers who have open new contracts and paid them off with out defaulting at different dates.

This particular data set has several different open and paid contracts. I used DateDiff to get the days betwen each open and paid contract. What I am looking for is the ability to know the AVERAGE amount of days between each new contract for all merchants in the table at different time periods

I need to know the AVERAGE number of days between the first contract and the second contract, the 2nd and 3rd contract,4 and 5th contract and so on on the entire merchant base

Data sample below

MerchantID contract date payoff date.. days between
12589 03/12/2005 06/12/2005 90
12589 08/12/2005 09/12/2205 30
12589 01/05/2006 05/12/2005 120
12666 04/15/2005 08/12/2005 120
12666 09/12/2005 11/12/2005 60

 

[lespaul]

nowhere in your sample data do you show any AVERAGE NUMBER OF DAYS??

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[chip12]

I don't understand your response. I can run a query using
this AVG(DateDiff("'d"([date1],[date2}) But it only will give me the average of each row. I need the average of the number of days between each new contract broken into intervals between the first and second contract, the 2nd and third contract, the third and 4th contract and so on on the entire base

Output is below it shows the AVERGAGE number of days between new contract broken intointervals of 1st and 2nd contract, 2nd and 3rd and etc....

avg_1st_2nd_cnt avg_2nd_3rd_cnt avg_3rd_4th_cnt
30 (days) 90 (days) 45 (days)

 

[lespaul]

again, I don't see where there's an average. If this is your data:

MerchantID contract date payoff date.. days between
12589 03/12/2005 06/12/2005 90
12589 08/12/2005 09/12/2205 30
12589 01/05/2006 05/12/2005 120
12666 04/15/2005 08/12/2005 120
12666 09/12/2005 11/12/2005 60

Merchant 12589's first contract was from 3/12 - 6/12;
Merchant 12666's first contract was from 4/15 - 8/12;

This results in an AVERAGE of (30 days + 120 days) = 150 days/2 merchant contracts
an AVERAGE FIRST CONTRACT LENGTH of 75 days.

Merchant 12589's second contract began 60 days after the first contract expired.
Merchant 12666's second contract began 30 days after the first contract expired.

This results in an AVERAGE of (60 days + 30 days) = 90 days/2 merchant contracts
an AVERAGE of BETWEEN CONTRACTS of 45 days.

Again, could you explain in a little more detail, what AVERAGE you are trying to find?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[chip12]

You are correct! that is exactly the average interval in days between the the first and 2nd contract purchase, the numbers of days between the 2nd and 3rd contract purchase. I need to run that query against the entire 20,000 records merchant table. I have multiple mercants wih multiple contracts that have repurchased from us at different intervals.

I just need the average number of days between the contract purchases broken down by 1st and 2nd contract, 2nd and 3rd contract,3rd and 4th contract and so on, on the entire merchant table

On the data I gave, I only have the number of days between each contract and the payoff date. When I tried to do an average AVG(DateDiff("d"[date1],[date2])) I get the average between just 1 contract and it's payoff date

I hope this helps

 

[lespaul]

You are going to have to write a function that does this for you. There's no way to have an SQL statement do everything you want.

I would:

1. run a query and get all distinct merchant ids
2. loop through the results of #1 and for each merchant run a query that gets all the contract information.
3. loop through the results of #2 and calculate the number of days between the relevant contracts and fill in an array like this:

MerchantCount NumberOfDays
Between1And2
Between2And3
Between3And4
Between4And5

(you may have to find out how many "Betweens" there are going to be in order to initialize the array correctly. I don't do much programming in Access and I'm not sure of the exact parameters required to create a multi-dimensional array.)

For each merchant that you add to NumberOfDays you need to increment the MerchantCount. Once you have finished looping through all the records, your average will be the NumberOfDays element divided by the MerchantCount.

I'm sure you can get some specific information on the code required in the Microsoft Access VBA Forum (Forum705).


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

Sorry Leslie, here a pure SQL way.
The basic idea is an agregate query based on a ranking query.
Chip, I suppose you already have a query selecting the contracts of interest.
Here an example named, say, qry_CNT_Contracts:
SELECT C.MerchantID, C.FirstFundingDate, C.BalanceZeroDate
FROM dbo_CNT_Contracts AS C LEFT JOIN dbo_CNT_v_DefaultedDateHistory AS V
ON C.ContractID = V.ContractID
WHERE C.BalanceZeroDate Is Not Null AND V.DefaultedDate Is Null;

Now the ranking query named, say, qry__Rank_Contracts:
SELECT A.MerchantID, A.FirstFundingDate AS ContractDate, A.BalanceZeroDate AS PayoffDate
, A.BalanceZeroDate-A.FirstFundingDate AS DaysBetween, Count(*) AS Rank
FROM qry_CNT_Contracts AS A INNER JOIN qry_CNT_Contracts AS B
ON A.MerchantID = B.MerchantID AND A.FirstFundingDate >= B.FirstFundingDate
GROUP BY A.MerchantID, A.FirstFundingDate, A.BalanceZeroDate;

And finally the aggregate one:
SELECT Rank, Avg(DaysBetween) As Average
FROM qry_Rank_Contracts
GROUP BY Rank;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chip12]

Thanks PH I will try that today

 

[lespaul]

you rock!

 

[chip12]

PH I ran this part of the code and it ran fine as long as i added the group by clause, Howver when I aded this


AS A INNER JOIN qry_CNT_Contracts AS B
dbo_CNT_Contracts.MerchantID = B.MerchantID AND MerchantID, dbo_CNT_Contracts.FirstFundingDate >= B.FirstFundingDate
GROUP BY MERCHANT,Purchase, CLOSED;


I received a Syntax error in the From Clause?

SELECT Merchant,Purchase,CLOSED
,Closed-Purchase AS DaysBetween, Count(*) AS Rank

FROM

(SELECT dbo_MRC_Merchants.BusinessName AS Merchant, dbo_MRC_Merchants.MerchantID, dbo_CNT_Contracts.FirstFundingDate AS Purchase, dbo_CNT_Contracts.BalanceZeroDate As Closed , DateDiff("d",[dbo_CNT_Contracts.firstfundingdate],[dbo_CNT_Contracts.BalanceZeroDate]) AS Avg_RePurchase
FROM (dbo_MRC_Merchants INNER JOIN dbo_CNT_Contracts ON dbo_MRC_Merchants.MerchantID = dbo_CNT_Contracts.MerchantID) LEFT JOIN dbo_CNT_v_DefaultedDateHistory ON dbo_CNT_Contracts.ContractID = dbo_CNT_v_DefaultedDateHistory.contractid
WHERE (((dbo_CNT_v_DefaultedDateHistory.DefaultedDate) Is Null) AND ((dbo_CNT_Contracts.BalanceZeroDate) Is Not Null)))

AS A INNER JOIN qry_CNT_Contracts AS B
dbo_CNT_Contracts.MerchantID = B.MerchantID AND MerchantID, dbo_CNT_Contracts.FirstFundingDate >= B.FirstFundingDate
GROUP BY MERCHANT,Purchase, CLOSED;

 

[lespaul]

Your syntax is off, but I'm not sure what you are trying to do, so it's hard to figure out how it needs to be fixed:

SELECT Merchant,Purchase,CLOSED
,Closed-Purchase AS DaysBetween, Count(*) AS Rank 

FROM

(SELECT dbo_MRC_Merchants.BusinessName AS Merchant, dbo_MRC_Merchants.MerchantID, dbo_CNT_Contracts.FirstFundingDate AS Purchase, dbo_CNT_Contracts.BalanceZeroDate As Closed ,  DateDiff("d",[dbo_CNT_Contracts.firstfundingdate],[dbo_CNT_Contracts.BalanceZeroDate]) AS Avg_RePurchase
FROM (dbo_MRC_Merchants INNER JOIN dbo_CNT_Contracts ON dbo_MRC_Merchants.MerchantID = dbo_CNT_Contracts.MerchantID) LEFT JOIN dbo_CNT_v_DefaultedDateHistory ON dbo_CNT_Contracts.ContractID = dbo_CNT_v_DefaultedDateHistory.contractid
WHERE (((dbo_CNT_v_DefaultedDateHistory.DefaultedDate) Is Null) AND ((dbo_CNT_Contracts.BalanceZeroDate) Is Not Null)))

AS A INNER JOIN qry_CNT_Contracts AS B[b]
dbo_CNT_Contracts.MerchantID = B.MerchantID AND [COLOR=red]MerchantID[/color][COLOR=green],[/color] dbo_CNT_Contracts.FirstFundingDate >= B.FirstFundingDate[/b]
GROUP BY  MERCHANT,Purchase, CLOSED;

what is the red part for?  Do you need dbo_CNT_Contracts.MerchantID equal to both B.MerchantID and another merchantID?  the green comma needs to be an AND or an OR.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[chip12]

Lespaul..Thanks fo responing, i was out yesterday.. I will rerun your initial answer and giv you a baetter senario of what is going on later today.. thanks for hanging with me