help with modifying working query (group by month)

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)

help with modifying working query (group by month)

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2008-05-28 : 03:10:11

Hi,

I have the following query which is working fine, but its returning a row for each day.

I'd like to modify it somehow so that we had the same data returned, but 1 row for each month. Can anyone offer some insight ?

Any help is much appreciated !!

thanks once again,

mike123

CREATE PROCEDURE [dbo].[select_Stats_LoginHistory]

(
@numDays int
)

AS SET NOCOUNT ON

SELECT CONVERT(varchar(10),LL.loginDate,112) as loginDate,
COUNT(LL.userID) AS TotalLogins,
COUNT(DISTINCT LL.userID) AS TotalLogins_Unique

FROM tblLogins_Log LL WITH (NOLOCK)
WHERE DateDiff(dd, LL.loginDate, GetDate()) < @numDays
GROUP BY CONVERT(varchar(10),LL.loginDate,112)
ORDER BY loginDate DESC

table structure below

CREATE TABLE [dbo].[tblLogins_Log](
[loginID] [int] IDENTITY(1,1) NOT NULL,
[userID] [int] NULL,
[IP] [varchar](15) NOT NULL,
[loginDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

nr
SQLTeam MVY

12543 Posts

Posted - 2008-05-28 : 03:14:32

SELECT CONVERT(varchar(6),LL.loginDate,112) as loginDate,

GROUP BY CONVERT(varchar(6),LL.loginDate,112)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources