Get number of records between 2 dates

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 2008 Forums

Transact-SQL (2008)

Get number of records between 2 dates

Author

Topic

duncant
Starting Member

18 Posts

Posted - 2011-11-08 : 05:38:17

Bit of a muddle here:
Trying to get get a count of how many records there are between 2 dates:

select
DATEDIFF(day,DateReceived,ReceivedThroughDoor) as NumberOfDays
from my_table where
(DATEDIFF(day,DateReceived,ReceivedThroughDoor) >=1) and
(DATEDIFF(day,DateReceived,ReceivedThroughDoor) <= 20)
and (TheDate between '08/01/2011' and '11/07/2011')

Can anyone help?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 05:44:07

select count(*)
from my_table
where TheDate between '20110108' and '20110711'

but I'm guessing that's not what you mean.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

duncant
Starting Member

18 Posts

Posted - 2011-11-08 : 05:50:18

Yeah I should of elaborated!

I need to count the number of days that have passed between the 2 dates: DateReceived and ReceivedThroughDoor.
If the count(records) is more than 1 and less that 20 then display the total amount of records.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 05:54:38

Still not sure.
You are only interested in rows with TheDate betwen the two dates given.
Then want to check DateReceived and ReceivedThroughDoor

Do you just want the count of rows where the difference is between 1 and 20? Not sure how you can check count(records) to be between 1 and 20 then the total amount (is that another count).

I think your issue is defining the problem rather than coding it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

duncant
Starting Member

18 Posts

Posted - 2011-11-08 : 06:01:00

Pull a single result (number of rows) for records that are less than 20 days old (between DateReceived and ReceivedThroughDoor)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-11-08 : 06:27:31

maybe

select count(*)
from my_table
where TheDate between '20110108' and '20110711'
and datediff(dd,DateReceived,ReceivedThroughDoor) between 1 and 20

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

duncant
Starting Member

18 Posts

Posted - 2011-11-08 : 07:10:06

Tried that, it's looking like its pulling the right results, now I have to account for excluding Saturdays and Sundays from the initial count.
Probably have to account for the weekends first between the start and end date then add to the 20?

duncant
Starting Member

18 Posts

Posted - 2011-11-10 : 06:39:52

Need a little help with this:

How do I piece these 2 queries together?

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '10/01/2011'--01 Oct 2011
SET @EndDate = '10/31/2011'--31 Oct 2011

SELECT count(*) as results
from tbl_complaints where
(FormReceivedThroughDoor between @StartDate and @EndDate)

select
(DATEDIFF(dd, @StartDate, @EndDate) + 1)
-(DATEDIFF(wk, @StartDate, @EndDate) * 2)
-(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources