count of next consecutive days

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)

count of next consecutive days

Author

Topic

Nitin
Starting Member

6 Posts

Posted - 2004-09-22 : 08:00:40

Hi All ,
I have a table in which there is a field of date type
I want to retrieve the count of records inserted for next consective days.
Can any one write a query for this.
Thanks in advance
Nitin

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 08:16:36

i can guess... though it would help immensly to have table definitions and maybe sample data....

[borrow esp server from spirit]
[esp server]


Select 
	A.<dateCol>,
	Cnt = count(*)
From <yourTable> A
Inner Join <yourTable> B
On A.<dateCol> = B.<dateCol>+1
/***Use following instead if it has a time value***/
--On convert(nvarchar,A.<dateCol>,101) = convert(nvarchar,B.<dateCol>+1,101)
Group By A.<dateCol>

[/esp server]
[/borrow esp server from spirit]

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 08:17:06

well to that you will need a table of dates to join on....

and how does a consecutive date count?

say i insert a record on monday, tuesday, thursday, friday and sunday.
looking from monday, dos record inserted on thursday count as consecutive, or do you want to return only tuesday?

Go with the flow & have fun! Else fight the flow

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 08:18:28

Maybe I didn't plug the esp server in right... Oh well, I guess we will see

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 08:21:38

ROTFL!

Go with the flow & have fun! Else fight the flow

Nitin
Starting Member

6 Posts

Posted - 2004-09-22 : 09:24:01

Hi
I would like to specify my table structure
Tbl_Schedule_Details(Schedule_Detail_Id int,Emp_Id int,Schedule_For_Date Date)
I would like to retrieve count of records of a particular employee who has been consectively scheduled from a particular date
Can any one help me on this
Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 09:30:20

The problem wasn't so much the lack of table structure as the ammbiguous definition of consectively scheduled...

for 9/1/2004 count all on 9/2/2004?
or is it
for 9/1/2004 count all on 9/2/2004, 9/3/2004, 9/4/2004... until there is a skip?

Corey

Nitin
Starting Member

6 Posts

Posted - 2004-09-22 : 09:37:33

Hi Corey,
Thanks for your prompt reply
I think your are getting the problem
for 9/1/2004 count all on 9/2/2004, 9/3/2004, 9/4/2004... until there is a skip?
is what i want
Thanks again
Nitin

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 09:53:43

These should help out a bit:


Declare @myTable table (d datetime)
Insert Into @myTable 
Select '9/1/2004'
Union Select '9/2/2004'
Union Select '9/3/2004'
Union Select '9/4/2004'
Union Select '9/5/2004'
Union Select '9/7/2004'
Union Select '9/8/2004'
Union Select '9/9/2004'
Union Select '9/10/2004'
Union Select '9/11/2004'
Union Select '9/12/2004'
Union Select '9/13/2004'
Union Select '9/14/2004'
Union Select '9/15/2004'
Union Select '9/16/2004'

Declare @d datetime

Select @d = '9/1/2004'

Select 
	cnt = count(*) 
From @myTable Z
Where d < isnull((Select min(A.d) From @myTable A Left Join @myTable B On A.d-1 = B.d Where B.d is null and A.d > @d),(Select max(A.d)+1 From @myTable A))
and d > @d

Select @d = '9/7/2004'

Select 
	cnt = count(*) 
From @myTable Z
Where d < isnull((Select min(A.d) From @myTable A Left Join @myTable B On A.d-1 = B.d Where B.d is null and A.d > @d),(Select max(A.d)+1 From @myTable A))
and d > @d



Select 
	d1, 
	cnt = count(*)
From @myTable Z
Inner Join
	(
	Select 
		d1 = A.d,
		d2 = isnull(B.d,(Select max(A.d) From @myTable A))
	From
		(
		Select d = A.d
		From @myTable A
		Left Join @myTable B
		On A.d-1 = B.d
		Where B.d is null
		) A
	Left Join
		(
		Select d = A.d-1
		From @myTable A
		Left Join @myTable B
		On A.d-1 = B.d
		Where B.d is null
		) B
	On A.d < isnull(B.d,(Select max(A.d) From @myTable A))
	) Y
On Z.d between d1+1 and d2
Group By Y.d1

Corey

objectuser
Starting Member

14 Posts

Posted - 2004-09-22 : 10:58:55

Well, let's say you want the number of times employees where scheduled between a particular date and five days after that. You might do something like this:


select Emp_Id, (
	select count(*)
	from Tbl_Schedule_Details isd
	where isd.Emp_Id = osd.Emp_Id
	and isd.Schedule_For_Date between osd.Schedule_For_Date and dateadd(day, 5, osd.Schedule_For_Date)
) next_count
from Tbl_Schedule_Details osd
where osd.Schedule_For_Date = '2004/09/15'

If you just wanted the number of times since a particular date, you could change it to be:


select Emp_Id, (
	select count(*)
	from Tbl_Schedule_Details isd
	where isd.Emp_Id = osd.Emp_Id
	and isd.Schedule_For_Date > osd.Schedule_For_Date
) next_count
from Tbl_Schedule_Details osd
where osd.Schedule_For_Date = '2004/09/15'

Is this sort of what you are wanting?

Note that since the subquery is correlated, this won't perform very well with a large table.

--Kevin

Nitin
Starting Member

6 Posts

Posted - 2004-09-22 : 11:04:56

Hi Corey,
It was simply great !!!!!
You solved my problem within minutes
Thanks a lot

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-22 : 11:16:01

I finally got the esp server working... (don't force a european plug into an American one

)

Glad I could help!

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-22 : 11:23:54

quote:
Originally posted by Seventhnight
(don't force a european plug into an American one )

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources