Count dates between - 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)

Count dates between

Author

Topic

PBoy
Starting Member

22 Posts

Posted - 2010-06-21 : 18:34:28

Hi All,

Just wonder if anyone can give me a idea how I may do this?

What Im looking to do is to show how long transaction have been our system for example we have transaction listed below (small sample)

Trans No. Trans Date
400024366 2009-12-23 00:00:00.000
400056932 2009-12-31 00:00:00.000
400027707 2009-12-31 00:00:00.000
400020381 2010-01-11 00:00:00.000
400020417 2010-01-11 00:00:00.000
400020418 2010-01-11 00:00:00.000
400020419 2010-01-11 00:00:00.000
400021367 2010-01-14 00:00:00.000
400021367 2010-01-14 00:00:00.000
400035002 2010-01-21 00:00:00.000
400035004 2010-01-21 00:00:00.000
400055159 2010-01-25 00:00:00.000
400055160 2010-01-25 00:00:00.000
400031684 2010-05-29 00:00:00.000

what I would like to do is a report that shows the transactions group (counted by DISTINCT trans numbers) by how long they have been in the system i.e. 10 Days, 20 Days, 30 days etc

so something like this

------------------------------------------
| 10 Days | 20 Days | 30 Days | 30+ Days |
------------------------------------------
| 3 | 18 | 10 | 1 |
------------------------------------------

Is this possible? Any help would be great

Cheers
Patrick

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-21 : 18:51:43

[CODE]select
sum(case when d.Duration <= 10 then 1 else 0 end) [10 Days],
sum(case when d.Duration <= 20 then 1 else 0 end) [20 Days],
sum(case when d.Duration <= 30 then 1 else 0 end) [30 Days],
sum(case when d.Duration > 30 then 1 else 0 end) [30+ Days]
from
(
select
t.TransNo, DateDiff(Day, t.TransDate, GetDate()) Duration
(
select distinct TransNo, TransDate
from Transactions
) t
) d[/CODE]

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

PBoy
Starting Member

22 Posts

Posted - 2010-06-21 : 19:01:42

HI Bustaz,

Thanks for that looks like it would do the trick I have just tried it and its give me the error below

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near 't'.

Cheers
Patrick

Sachin.Nand

2937 Posts

Posted - 2010-06-21 : 23:27:25

It is a typo error.Just put from keyword marked in red.


select
	sum(case when d.Duration <= 10 then 1 else 0 end) [10 Days],
	sum(case when d.Duration <= 20 then 1 else 0 end) [20 Days],
	sum(case when d.Duration <= 30 then 1 else 0 end) [30 Days],
	sum(case when d.Duration >  30 then 1 else 0 end) [30+ Days]
from
	(
	select
		t.TransNo, DateDiff(Day, t.TransDate, GetDate()) Duration from
		(
		select distinct TransNo, TransDate
		from Transactions
		) t
	) d

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-06-22 : 19:39:32

Thanks for the correction, Idera.

PBoy, if you supply scripts to create the table and data, you'll stand a better chance of getting a response and, clearly, less screw-ups from me.

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

PBoy
Starting Member

22 Posts

Posted - 2010-06-23 : 12:17:19

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 13:48:42

Just add a group by clause shown below



select 
       Type,
	sum(case when d.Duration <= 10 then 1 else 0 end) [10 Days],
	sum(case when d.Duration <= 20 then 1 else 0 end) [20 Days],
	sum(case when d.Duration <= 30 then 1 else 0 end) [30 Days],
	sum(case when d.Duration >  30 then 1 else 0 end) [30+ Days]
from
	(
	select
		t.TransNo, DateDiff(Day, t.TransDate, GetDate()) Duration,Type from
		(
		select distinct TransNo, TransDate,Type from Transactions
		) t
	) d
Group by Type

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources