Number of days between two dates in same column

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

General SQL Server Forums

New to SQL Server Programming

Number of days between two dates in same column

Author

Topic

tendulkar
Starting Member

9 Posts

Posted - 2007-04-17 : 17:42:58

I have a table like this (a small section of the table)

Cu_id | Tr_id | Date
1234 | 1 | 12/3/2006
1234 | 2 | 12/18/2006
1234 | 3 | 1/5/2007
1234 | 4 | 1/9/2007
1234 | 5 | 2/21/2007
9999 | 91 | 1/3/2006
9999 | 81 | 1/10/2006
9999 | 71 | 1/18/2007
9999 | 61 | 2/1/2007

I have to find the number of days between the dates for the same cu_id and add the number as a new column. The new table should look like this.

Cu_id | Tr_id | Date | Days_between
1234 | 1 | 12/3/2006 | 0
1234 | 2 | 12/18/2006 | 15
1234 | 3 | 1/5/2007 | 18
1234 | 4 | 1/9/2007 | 4
1234 | 5 | 2/21/2007 | 43
9999 | 91 | 1/3/2006 | 0
9999 | 81 | 1/10/2006 | 7
9999 | 71 | 1/18/2007 | 8
9999 | 61 | 2/1/2007 | 14

Please let me know how I can find the number of days between two dates in the same column for the same cu_id (customer_id).

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

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

I think you need to do this on a row by row basis.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-17 : 18:07:20

Try this:

DECLARE @Table TABLE( cu_id INT, tr_id INT, date DATETIME)

INSERT @Table
SELECT 1234, 1, '12/3/2006' UNION ALL
SELECT 1234, 2, '12/18/2006' UNION ALL 
SELECT 1234, 3, '1/5/2007' UNION ALL 
SELECT 1234, 4, '1/9/2007' UNION ALL 
SELECT 1234, 5, '2/21/2007' UNION ALL 
SELECT 9999, 91,'1/3/2006' UNION ALL 
SELECT 9999, 81, '1/10/2006' UNION ALL 
SELECT 9999, 71, '1/18/2007' UNION ALL 
SELECT 9999, 61, '2/1/2007'

SELECT 
	*,
	COALESCE(DATEDIFF(DAY, (SELECT MAX(date) FROM @Table WHERE date < a.date AND cu_id = a.cu_id), a.date), 0)
FROM @Table a

Cheers,

-Ryan

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-17 : 18:10:54

You may be able to do it in one query.. what is the PrimaryKey for this table?
I tried something like this:

Declare @t table (CUID int, TRID int, Dateval datetime, Days int, Processed tinyint )
insert into @t
select 1234 , 1 , '12/3/2006' , 0, 0 Union all
select 1234 , 2 , '12/18/2006' , 0, 0 Union all
select 1234 , 3 , '1/5/2007' , 0, 0 Union all
select 1234 , 4 , '1/9/2007' , 0, 0 Union all
select 1234 , 5 , '2/21/2007' , 0, 0 Union all
select 9999 , 91 ,'1/3/2006' , 0, 0 Union all
select 9999 , 81 , '1/10/2006' , 0, 0 Union all
select 9999 , 71 , '1/18/2007' , 0, 0 Union all
select 9999 , 61 , '2/1/2007' , 0, 0

select *
,date3 = datediff(day, (select dateval from @t t3 where t3.cuid = t1.cuid and t3.trid = (select max(trid) from @t t where t.cuid = t1.cuid and t.trid < t1.trid ) ) , dateval)
From @t t1

but since the TD_ID's are not in an increasing order the results are slightly different. So we need to identify some progressingly incresing value/column.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

yumyum113
Starting Member

31 Posts

Posted - 2007-04-17 : 20:12:27

Hi Tendulkar,

Question on the sample output that you have presented

Cu_id | Tr_id | Date | Days_between
1234 | 1 | 12/3/2006 | 0
1234 | 2 | 12/18/2006 | 15
1234 | 3 | 1/5/2007 | 18
1234 | 4 | 1/9/2007 | 4
1234 | 5 | 2/21/2007 | 43
9999 | 91 | 1/3/2006 | 0
9999 | 81 | 1/10/2006 | 7
9999 | 71 | 1/18/2007 | 8 shouldn't this be 373
9999 | 61 | 2/1/2007 | 14

I'm a little confuse on the end date of those first transaction(those that have 0 days as result) of every customer but I hope this helps...

Declare @t table (CUID int, TRID int, Dateval datetime )
insert into @t
select 1234 , 1 , '12/3/2006' Union all
select 1234 , 2 , '12/18/2006' Union all
select 1234 , 3 , '1/5/2007' Union all
select 1234 , 4 , '1/9/2007' Union all
select 1234 , 5 , '2/21/2007' Union all
select 9999 , 91 ,'1/3/2006' Union all
select 9999 , 81 , '1/10/2006' Union all
select 9999 , 71 , '1/18/2007' Union all
select 9999 , 61 , '2/1/2007'

select t1.cuid,t1.trid,min(t2.dateval) ,min(t1.dateval),datediff(d,min(t1.dateval),min(t2.dateval))
from @t t1
join @t t2 on t1.cuid = t2.cuid
and t2.dateval > t1.dateval
group by t1.cuid,t1.trid,t1.dateval
union all /*this part inserts the first transaction as per your desired output*/
select cuid,null,min(dateval),min(dateval),datediff(d,min(dateval),min(dateval))
from @t
group by cuid
order by 1,2

tendulkar
Starting Member

9 Posts

Posted - 2007-04-18 : 09:20:53

quote:
Originally posted by yumyum113

Hi Tendulkar,

Question on the sample output that you have presented

Cu_id | Tr_id | Date | Days_between
1234 | 1 | 12/3/2006 | 0
1234 | 2 | 12/18/2006 | 15
1234 | 3 | 1/5/2007 | 18
1234 | 4 | 1/9/2007 | 4
1234 | 5 | 2/21/2007 | 43
9999 | 91 | 1/3/2006 | 0
9999 | 81 | 1/10/2006 | 7
9999 | 71 | 1/18/2007 | 8 shouldn't this be 373
9999 | 61 | 2/1/2007 | 14

Please read it as 1/18/2006 and 2/1/2006.

tendulkar
Starting Member

9 Posts

Posted - 2007-04-18 : 16:33:32

The dataset I have given as an example is only a small section of the data. The table has 3 columns - CU_ID,TR_ID and Date.

Please let me know how I can use your code for the whole table.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-18 : 16:38:09

Does the table have a primarykey and if so what is it?

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

tendulkar
Starting Member

9 Posts

Posted - 2007-04-18 : 16:49:28

There is no primary key in the table. The table is actually a view created from three other tables.

There is no primary key because each customer(cu_id) can have many transactions(tr_id) and each transaction has a date.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-18 : 18:40:20

Does my query not work?

tendulkar
Starting Member

9 Posts

Posted - 2007-04-18 : 20:21:34

Lamprey,

Your query worked. Please let me know how I can make it work for the full table.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-18 : 20:26:08

quote:
Originally posted by tendulkar

Lamprey,

Your query worked. Please let me know how I can make it work for the full table.

Thanks

just replace the table name and column name used in the query with your actual table / column name.

tendulkar
Starting Member

9 Posts

Posted - 2007-04-19 : 09:35:43

Almost all the SQL suggested here give wrong results.

For example assume these customers (4493,5496,10558,13832):

4493	1011610131307329	2006-10-13 00:00:00.000
4493	1011610132101325	2006-10-13 00:00:00.000
4493	114161014303559	        2006-10-14 00:00:00.000
4493	1141610142001807	2006-10-14 00:00:00.000
5946	1011611142103553	2006-11-14 00:00:00.000
10558	1011612085506058	2006-12-08 00:00:00.000
13832	1011610311601889	2006-10-31 00:00:00.000
13832	113161103160314	        2006-11-03 00:00:00.000
13832	101161106403523	        2006-11-06 00:00:00.000
13832	1011611065503275	2006-11-06 00:00:00.000
13832	1141612141506821	2006-12-14 00:00:00.000

For this dataset, the fourth column (days_between) should look like this

4493	1011610131307329	2006-10-13 00:00:00.000  0
4493	1011610132101325	2006-10-13 00:00:00.000  0
4493	114161014303559	        2006-10-14 00:00:00.000  1
4493	1141610142001807	2006-10-14 00:00:00.000  0
5946	1011611142103553	2006-11-14 00:00:00.000  0
10558	1011612085506058	2006-12-08 00:00:00.000  0
13832	1011610311601889	2006-10-31 00:00:00.000  0
13832	113161103160314	        2006-11-03 00:00:00.000  3
13832	101161106403523	        2006-11-06 00:00:00.000  3
13832	1011611065503275	2006-11-06 00:00:00.000  0
13832	1141612141506821	2006-12-14 00:00:00.000  38

Instead, when I use yumyum's solution, it is deleting a record from the dataset and this is the result I'm getting


4493	NULL	                2006-10-13 00:00:00.000	2006-10-13 00:00:00.000	0
4493	1011610131307329	2006-10-14 00:00:00.000	2006-10-13 00:00:00.000	1
4493	1011610132101325	2006-10-14 00:00:00.000	2006-10-13 00:00:00.000	1
5946	NULL	                2006-11-14 00:00:00.000	2006-11-14 00:00:00.000	0
10558	NULL	                2006-12-08 00:00:00.000	2006-12-08 00:00:00.000	0
13832	NULL	                2006-10-31 00:00:00.000	2006-10-31 00:00:00.000	0
13832	101161106403523	        2006-12-14 00:00:00.000	2006-11-06 00:00:00.000	38
13832	113161103160314	        2006-11-06 00:00:00.000	2006-11-03 00:00:00.000	3
13832	1011610311601889	2006-11-03 00:00:00.000	2006-10-31 00:00:00.000	3
13832	1011611065503275	2006-12-14 00:00:00.000	2006-11-06 00:00:00.000	38

When I use Dinakar's solution, I'm getting this result:

4493	1011610131307329	2006-10-13 00:00:00.000	-1
4493	1011610132101325	2006-10-13 00:00:00.000	0
4493	114161014303559	        2006-10-14 00:00:00.000	NULL
4493	1141610142001807	2006-10-14 00:00:00.000	1
5946	1011611142103553	2006-11-14 00:00:00.000	NULL
10558	1011612085506058	2006-12-08 00:00:00.000	NULL
13832	1011610311601889	2006-10-31 00:00:00.000	-3
13832	113161103160314	        2006-11-03 00:00:00.000	-3
13832	101161106403523	        2006-11-06 00:00:00.000	NULL
13832	1011611065503275	2006-11-06 00:00:00.000	6
13832	1141612141506821	2006-12-14 00:00:00.000	38

When I use Lamprey's solution, I'm getting this result:

4493	1011610131307329	2006-10-13 00:00:00.000	0
4493	1011610132101325	2006-10-13 00:00:00.000	0
4493	114161014303559	        2006-10-14 00:00:00.000	1
4493	1141610142001807	2006-10-14 00:00:00.000	1
5946	1011611142103553	2006-11-14 00:00:00.000	0
10558	1011612085506058	2006-12-08 00:00:00.000	0
13832	1011610311601889	2006-10-31 00:00:00.000	0
13832	113161103160314	        2006-11-03 00:00:00.000	3
13832	101161106403523	        2006-11-06 00:00:00.000	3
13832	1011611065503275	2006-11-06 00:00:00.000	3
13832	1141612141506821	2006-12-14 00:00:00.000	38

This is how I created the table:

create table avg_days(customer_key bigint,transaction_id bigint,dateval datetime)

insert into avg_days(customer_key,transaction_id,dateval)
select distinct A.customer_key,A.transaction_id,B.Calendar_dt 
from header A, date_time B
where A.time_key=B.time_key
order by 1,3

Order by customer_key and dateval is necessary to list the dates in ascending order for a particular customer.
Please let me know the correct solution. Thanks for all your time and efforts. You guys have been of great help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-19 : 14:47:43

I did not notice if you mentioned which version of SQL server you are using, but if it is 2005 the this should work:

WITH Partitioned AS
(
	SELECT *, ROW_NUMBER() OVER (PARTITION BY cu_id, date ORDER BY cu_id, date) AS RowNumber
	FROM @Table
) 
SELECT 
	*,
	CASE 
		WHEN RowNumber > 1 THEN 0
		ELSE COALESCE(DATEDIFF(DAY, (SELECT MAX(date) FROM @Table WHERE date < a.date AND cu_id = a.cu_id), a.date), 0)
	END AS Days_between
FROM Partitioned a

Obviously, adjusting table and column names accordingly.

tendulkar
Starting Member

9 Posts

Posted - 2007-04-19 : 16:01:07

quote:
Originally posted by Lamprey

I did not notice if you mentioned which version of SQL server you are using, but if it is 2005 the this should work:
WITH Partitioned AS
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY cu_id, date ORDER BY cu_id, date) AS RowNumber
FROM @Table
)
SELECT
*,
CASE
WHEN RowNumber > 1 THEN 0
ELSE COALESCE(DATEDIFF(DAY, (SELECT MAX(date) FROM @Table WHERE date < a.date AND cu_id = a.cu_id), a.date), 0)
END AS Days_between
FROM Partitioned a
Obviously, adjusting table and column names accordingly.

Thanks very much Lamprey. It worked.

Can you please tell me how I can write the output of the above statement to a table?

Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources