Count for the day. - 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

General SQL Server Forums

New to SQL Server Programming

Count for the day.

Author

Topic

jrobin747
Starting Member

48 Posts

Posted - 2013-08-26 : 13:30:42

I have a query that is counting the number of applications gotten for the day.

SELECT (usr.FirstName + '' + usr.LastName)AS [Sales Team Member], stm.UserID,
COUNT (MerApp.ApplicationID)AS [Number of Apps]
FROM MerchantApplication MerApp
INNER JOIN Users usr ON MerApp.assignedTo = usr.UserID
INNER JOIN SalesTeamMembers stm ON usr.UserID = stm.UserID
INNER JOIN SalesTeams satm ON stm.SalesteamID = satm.SalesTeamID
WHERE MerApp.LastUpdateOn BETWEEN @strStartofDays AND @strEndDate AND MerApp.ApplicationStatus=3 AND STM.ActiveStatus=1
GROUP BY MerApp.AssignedTo, usr.LastName, usr.FirstName, stm.UserID, stm.SalesteamID, MerApp.ApplicationStatus, satm.TeamDescription

LastUpdateOn is the days the table is updated
Since it's just a 24hr count I should have to use BETWEEN in my where clause. What is another way to count for 1 day.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-26 : 14:34:56

Are you looking for 24 hour period starting now and counting backward, or some other 24 hour period? If it is the 24 hour period ending now:

WHERE   MerApp.LastUpdateOn > DATEADD(dd,-1,GETDATE()) 
		AND MerApp.LastUpdateOn <= GETDATE()

jrobin747
Starting Member

48 Posts

Posted - 2013-08-26 : 15:57:11

the 24 hour period ending now

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-26 : 16:02:00

quote:
Originally posted by jrobin747

the 24 hour period ending now

Query I posted earlier would do exactly that.

That "DATEADD(dd,-1,GETDATE())" subtracts one day from the current time. You can see what it does if you run this query:

SELECT DATEADD(dd,-1,GETDATE())

So it is saying that pickup all rows for which MerApp.LastUpdateOn is later than yesterday this time and less than or equal to the current time.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-27 : 00:03:41

Check the WHERE condition which is posted by James..
SELECT DATEADD(dd,-1,GETDATE()) StartTime , GETDATE() EndTime -- Which gives exact 24 hours....

>> the 24 hour period ending now
What do you mean by this?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-27 : 01:43:42

the 24 hour period ending now
I think this


WHERE   MerApp.LastUpdateOn >= DATEADD(hh,-24,GETDATE()) 
		AND MerApp.LastUpdateOn < DATEADD(ms,1,GETDATE())

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources