How do I group on date ? - 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

How do I group on date ?

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-12 : 10:32:11

I have a table with a datetime field and a username. the following gives me a count of the individual users reagrdless of how many entries there are for them.

SELECT COUNT(DISTINCT Log_Username) AS Expr1
FROM tblUserLog

However, I also want to have a total of distinct users for each date - how do I do this ?

TIA

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-12 : 10:40:42

Thanks, but my field contains date and time so it is listing a row and count of 1 for each row. How can extract the date only ? Thisis what I have now

SELECT CONVERT(Log_DateTime, 101) AS Expr2, COUNT(Log_Username) AS Expr1
FROM tblUserLog
GROUP BY CONVERT(Log_DateTime, 101)
ORDER BY CONVERT(Log_DateTime, 101)

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-01-12 : 10:44:41

Sorry , I was not sure of the reply I gave so deleted,

You can use
SELECT Convert(varchar, datetime ,101) AS [Date],Log_Username,Count(Log_Username) AS Expr1 FROM tblUserLog
GROUP BY datetime ,Log_Username
ORDER BY Datetime

Necessity is the mother of all inventions!

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-12 : 10:52:07

Thank you but now it is giving me this

12/1/07 Jines 1
12/1/07 WoodH 1
12/1/07 Jines 1

I want

12/1/07 Jines 2
12/1/07 WoodH 1

sshelper
Posting Yak Master

216 Posts

Posted - 2007-01-12 : 10:56:31

Try it this way:

SELECT CAST(Convert(varchar, datetime ,101) AS DATETIME) AS [Date],Log_Username,Count(Log_Username) AS Expr1 FROM tblUserLog
GROUP BY CAST(Convert(varchar, datetime ,101) AS DATETIME), Log_Username
ORDER BY CAST(Convert(varchar, datetime ,101) AS DATETIME)

SQL Server Helper
http://www.sql-server-helper.com

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-01-12 : 10:57:07

Brend , try this

SELECT Convert(varchar, datetime ,101) AS [Date],Log_Username,Count(Log_Username) AS Expr1 FROM tblUserLog
GROUP BY Convert(varchar, datetime ,101) ,Log_Username
ORDER BY Convert(varchar, datetime ,101)

Necessity is the mother of all inventions!

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-01-12 : 10:57:50

Perfect - many thanks for all the help

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-01-12 : 10:58:48

sshelper that is perfect! I missed the CAST part to get the varchar back to Datetime Format.

Necessity is the mother of all inventions!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 11:03:50

My advice:

Instead of casting things back and forth and repeating formulas over and over, make use of a simple UDF that returns only a true datetime w/o the time, and then group and sort on that.

i.e., here are some simple date UDF's that you can use:

http://weblogs.sqlteam.com/jeffs/archive/2007/01/02/56079.aspx

and then make use of derived tables to keep your code short and consise:


select LogDate, Log_userName, count(*) as Expr1
from  
  (select dbo.DateOnly(Log_DateTime) as LogDate, Log_UserName, Log_DateTime
   from tblUserLog
  ) x
group by LogDate
order by LogDate

That makes your code much clearer as to what you are doing, instead of someone having to try to figure out what all those cast and converts are doing and what date format 101 means and how that might sort.

- Jeff

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-01-12 : 11:12:46

Jeff,
Dont you think you are dependent on remembering the names you need for the conversion.Which means you also need a good memory of the sProc. I would call this complicating things .
1. Remember what you called the sProc for the particular conversion.
2. Creating a inner join in itself which is multiplying your scan on the same table by the number of conversions you need in your results.

Necessity is the mother of all inventions!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 11:18:38

quote:
Originally posted by Vijaykumar_Patil

Jeff,
Dont you think you are dependent on remembering the names you need for the conversion.Which means you also need a good memory of the sProc. I would call this complicating things .
1. Remember what you called the sProc for the particular conversion.
2. Creating a inner join in itself which is multiplying your scan on the same table by the number of conversions you need in your results.

Necessity is the mother of all inventions!

?? Not sure what you are saying. There is no inner join created or multiplying table scan. In fact, less work is done using the derived table because the conversion/time-stripping is done once, as opposed to 3 times.

As for remembering a "sProc for the particular conversion", again, not sure what you are saying ... are you saying you need to remember the name of the *function* used? there no memorizing, you simply see a function called "DateOnly" used, you can probably guess what it does or, if not, look at it's definition in your database. Seeing datetime 101 requires a search of BOL, and, again, it is converting dates to varchar's, which is not the goal here.

Do you really feel that:

select CAST(Convert(varchar, somedate ,101) AS DATETIME)

is easier to read and understand rather than

select dbo.DateOnly(someDate)

??

- Jeff

sshelper
Posting Yak Master

216 Posts

Posted - 2007-01-12 : 11:29:34

Here's a link similar to Jeff's GetDateOnly function:

http://www.sql-server-helper.com/functions/get-date-only.aspx

And applying it to the code earlier:

SELECT [dbo].[ufn_GetDateOnly](Log_DateTime) AS [Log_DateTime], Log_Username,Count(Log_Username) AS Expr1
FROM tblUserLog
GROUP BY [dbo].[ufn_GetDateOnly](Log_DateTime), Log_Username
ORDER BY [dbo].[ufn_GetDateOnly](Log_DateTime)

SQL Server Helper
http://www.sql-server-helper.com

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-12 : 11:53:41

It is much more efficient to get the date only and time only from a datetime column using a combination of dateadd/datediff than using cast.

Convert Datetime to Date Only:

dateadd(dd,datediff(dd,0,MyDate),0)

Convert Datetime to Time Only:

MyDate - dateadd(dd,datediff(dd,0,MyDate),0)

See the links below for more extensive discussions of these subjects.

Finding the Start of Time Periods
One of the most common questions is how to remove the time from a datetime so that you end up with just a date. In other words, change 2006/12/13 02:33:48.347 to 2006/12/13 00:00:00.000. The following links have functions that will find the start of Century, Decade, Year, Quarter, Month, Week, Day, Hour, 30 Minutes, 20 Minutes, 15 Minutes, 10 Minutes , 5 Minutes , x number of Minutes ,Minute , or Second.
Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755

Getting Time Only from DateTime
By convention, a time only column is stored in SQL Server as an offset from 1900-01-01 00:00:00.000. The function on this link will get the time from a datetime value.
Time Only Function: F_TIME_FROM_DATETIME
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358

Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

Vijaykumar_Patil
Posting Yak Master

121 Posts

Posted - 2007-01-12 : 12:47:36

Sorry Jeff I was wrong, I agree with you, I over looked at things. Functions seem to be a better way to get the conversion done. I will start using these functions in my queries too. Thank you for the info and explaining it to me.:-)

Necessity is the mother of all inventions!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-12 : 13:09:48

quote:
Originally posted by sshelper

Here's a link similar to Jeff's GetDateOnly function:

http://www.sql-server-helper.com/functions/get-date-only.aspx

And applying it to the code earlier:

SELECT [dbo].[ufn_GetDateOnly](Log_DateTime) AS [Log_DateTime], Log_Username,Count(Log_Username) AS Expr1
FROM tblUserLog
GROUP BY [dbo].[ufn_GetDateOnly](Log_DateTime), Log_Username
ORDER BY [dbo].[ufn_GetDateOnly](Log_DateTime)

SQL Server Helper
http://www.sql-server-helper.com

I still recommend, in general, doing things once when you can. Call the function once in a derived table, and then sort/group on what is returned. Even though the optimizer most likely will convert this SQL to work the same way (since UDF's must be deterministic), it is usually better to explicitly write it that way anyway and also your code is shorter and easier to read.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources