average between 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 2000 Forums

Transact-SQL (2000)

average between dates

Author

Topic

chip0312
Starting Member

1 Post

Posted - 2005-07-06 : 15:03:52

Im attempting to find the average time between contract purchases
I need to know the average number of days between the first and second contract purchase, the second and third third and fourt contract and so on . here is a data sample. i need to get the average numbers of days from the first purchase and the 2nd purchase, the 2nd purchase to the 3rd purchase and so on . i used the datediff function to get the days between the initial purchase and the payoff from an Access connected to SQL Server

Merchant MerchantID Purchase Paid days between
xxxxx 1 4/6/1999 10/31/1999 208
xxxxx 1 8/9/1999 12/31/1999 144
xxxxx 1 10/12/2000 1/16/2001 96
xxxxx 1 1/16/2001 2/28/2001 43
xxxxx 1 3/23/2001 7/23/2001 122
xxxxx 1 5/29/2002 12/12/2002 197
xxxxx 1 12/3/2002 6/23/2003 202
xxbgt 2 6/15/1999 11/30/1999 168
xxbgt 2 1/13/2000 5/31/2000 139
xxbgt 2 3/27/2000 8/31/2000 157
xxghy 3 4/18/2000 10/31/2000 196
xxghy 3 2/14/2001 5/31/2001 106
xxghy 3 6/20/2001 1/3/2002 197
xxghy 3 10/17/2001 3/27/2002 161
xbabd 5 3/22/2002 10/7/2002 199
xbabd 5 4/24/2003 9/24/2003 153

This is my initial code

SELECT dbo_MRC_Merchants.BusinessName AS Merchant, dbo_MRC_Merchants.MerchantID, dbo_CNT_Contracts.FirstFundingDate AS Purchase, dbo_CNT_Contracts.BalanceZeroDate AS Closed, dbo_CNT_v_DefaultedDateHistory.DefaultedDate AS Defaulteddate,

DateDiff("d",[dbo_CNT_Contracts.firstfundingdate],[dbo_CNT_Contracts.BalanceZeroDate]) AS days between

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))
GROUP BY dbo_MRC_Merchants.BusinessName, dbo_MRC_Merchants.MerchantID, dbo_CNT_Contracts.FirstFundingDate, dbo_CNT_Contracts.BalanceZeroDate, dbo_CNT_v_DefaultedDateHistory.DefaultedDate
ORDER BY dbo_MRC_Merchants.MerchantID;

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-07-06 : 15:52:20

can you please post desired resultset?

Using this as example:

set nocount on

declare @test table (Merchant char(5), MerchantID int, Purchase datetime, Paid datetime, daysBetween int)
insert into @test
	select 	'xxxxx', 1, '4/6/1999', '10/31/1999', 208 union
	select 	'xxxxx', 1, '8/9/1999', '12/31/1999', 144 union
	select 	'xxxxx', 1, '10/12/2000', '1/16/2001', 96 union
	select 	'xxxxx', 1, '1/16/2001', '2/28/2001', 43 union
	select 	'xxxxx', 1, '3/23/2001', '7/23/2001', 122 union
	select 	'xxxxx', 1, '5/29/2002', '12/12/2002', 197 union
	select 	'xxxxx', 1, '12/3/2002', '6/23/2003', 202 union
	select 	'xxbgt', 2, '6/15/1999', '11/30/1999', 168 union
	select 	'xxbgt', 2, '1/13/2000', '5/31/2000', 139 union
	select 	'xxbgt', 2, '3/27/2000', '8/31/2000', 157 union
	select 	'xxghy', 3, '4/18/2000', '10/31/2000', 196 union
	select 	'xxghy', 3, '2/14/2001', '5/31/2001', 106 union
	select 	'xxghy', 3, '6/20/2001', '1/3/2002', 197 union
	select 	'xxghy', 3, '10/17/2001', '3/27/2002', 161 union
	select 	'xbabd', 5, '3/22/2002', '10/7/2002', 199 union
	select 	'xbabd', 5, '4/24/2003', '9/24/2003', 153

select * from @test

Stellarion
Starting Member

5 Posts

Posted - 2005-07-07 : 15:24:23

This will get you started. If the view you created already were to be called vwMerchant_Info then running the following against it will give you number of days between each set of purchases (between Purchase 1 and 2, 2 and 3, 3 and 4, etc...)

SELECT TOP 100 PERCENT p1.MerchantID, p1.Purchase, MIN(p2.Purchase) nextdate,
DATEDIFF(day, p1.Purchase, MIN(p2.Purchase)) NumberOfDays
FROM vwMerchant_Info p1 JOIN
vwMerchant_Info p2 ON (p1.MerchantID = p2.MerchantID AND p2.Purchase > p1.Purchase)
GROUP BY p1.MerchantID, p1.Purchase
ORDER BY p1.MerchantID, p1.Purchase

The Future is fixed...The Past always changing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources