GroupBy Month,Year-need months without transaction

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)

GroupBy Month,Year-need months without transaction

Author

Topic

gmanojkumar
Starting Member

4 Posts

Posted - 2009-04-15 : 07:02:30

I am trying to write a sql query that will give me the usage details between a start date and end date

My table looks like
UsageTable
User----LogDate
U1------2008-01-03 08:30:00.000
U1------2008-01-13 08:30:00.000
U1------2008-02-12 08:30:00.000
U2------2008-04-13 08:30:00.000

I need a result like this for startdate 01/2008 and enddate 04/2008
User----Month----Year----UsageCount
U1------01-------2008----2
U1------02-------2008----1
U1------03-------2008----0
U1------04-------2008----0
U2------01-------2008----0
U2------02-------2008----0
U2------03-------2008----0
U2------04-------2008----1

I tried grouping using datepart of the logdate from usagetable and joining it with a calendartable that i created but still i am not able to get the desired result.

Any help plz?

svicky9
Posting Yak Master

232 Posts

Posted - 2009-04-15 : 07:31:47

select user, DATEPART(MONTH,GETDATE()) , DATEPART(YEAR,getdate()), UsageCount
from TableName
group by user, DATEPART(MONTH,GETDATE()) , DATEPART(YEAR,getdate())

http://www.sqlserver007.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-15 : 07:36:30

try this once


declare @UsageTable table ([User] varchar(4), LogDate datetime)
insert into @usagetable select 'U1','2008-01-03 08:30:00.000'
insert into @usagetable select 'U1','2008-01-13 08:30:00.000'
insert into @usagetable select 'U1','2008-02-12 08:30:00.000'
insert into @usagetable select 'U2','2008-04-13 08:30:00.000'

declare @minmonth int,@maxmonth int
select @minmonth=month(min(logdate)), @maxmonth = month(max(logdate)) from @usagetable

select distinct [user], [month], [year], 
(select count(logdate) from @usagetable where month(logdate) = s.[month] and [user] = s.[user])
from(  select  [user],@minmonth + number as [month],year(logdate) as [year] ,logdate
from @usagetable l
inner join 
master..spt_values m on m.type ='p'
where number < @maxmonth ) s

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-15 : 07:48:38

select user, DATEPART(MONTH,GETDATE()) , DATEPART(YEAR,getdate()), Count(*) UsageCount
from TableName
group by user, DATEPART(MONTH,GETDATE()) , DATEPART(YEAR,getdate())

gmanojkumar
Starting Member

4 Posts

Posted - 2009-04-15 : 07:50:33

quote:

Originally posted by bklr

try this once


declare @UsageTable table ([User] varchar(4), LogDate datetime)
insert into @usagetable select 'U1','2008-01-03 08:30:00.000'
insert into @usagetable select 'U1','2008-01-13 08:30:00.000'
insert into @usagetable select 'U1','2008-02-12 08:30:00.000'
insert into @usagetable select 'U2','2008-04-13 08:30:00.000'

declare @minmonth int,@maxmonth int
select @minmonth=month(min(logdate)), @maxmonth = month(max(logdate)) from @usagetable

select distinct [user], [month], [year], 
(select count(logdate) from @usagetable where month(logdate) = s.[month] and [user] = s.[user])
from(  select  [user],@minmonth + number as [month],year(logdate) as [year] ,logdate
from @usagetable l
inner join 
master..spt_values m on m.type ='p'
where number < @maxmonth ) s

Thanks for the reply... I am trying to tweak your query to meet my requirement. Basically the startdate and enddate for the query can be from nov 2007 to dec 2008... but the values in the usagetable can be only from jan 2008 to apr 2008. Even in this case i need to show zero values for U1 and U2 for all the remaining months. Any ideas?

Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-15 : 07:52:01

[code]DECLARE @UsageTable TABLE
(
[User] VARCHAR(20),
LogDate DATETIME
)

INSERT @UsageTable
SELECT 'U1', '2008-01-03 08:30:00.000' UNION ALL
SELECT 'U1', '2008-01-13 08:30:00.000' UNION ALL
SELECT 'U1', '2008-02-12 08:30:00.000' UNION ALL
SELECT 'U2', '2008-04-13 08:30:00.000'

DECLARE @StartMonth DATETIME,
@EndMonth DATETIME

SELECT @StartMonth = '2008-01-01',
@EndMonth = '2008-04-01'

;WITH Yak (theMonth, EndMonth)
AS (
SELECT @StartMonth,
@EndMonth

UNION ALL

SELECT DATEADD(MONTH, 1, theMonth),
EndMonth
FROM Yak
WHERE theMonth < EndMonth
)

SELECT u.[User],
CONVERT(CHAR(2), y.theMonth, 101) AS [Month],
DATENAME(YEAR, y.theMonth) AS [Year],
COUNT(ut.[User]) AS UsageCount
FROM Yak AS y
CROSS JOIN (
SELECT [User]
FROM @UsageTable
GROUP BY [User]
) AS u
LEFT JOIN @UsageTable AS ut ON DATEDIFF(MONTH, ut.LogDate, y.theMonth) = 0
AND ut.[User] = u.[User]
GROUP BY u.[User],
CONVERT(CHAR(2), y.theMonth, 101),
DATENAME(YEAR, y.theMonth)
ORDER BY u.[User],
CONVERT(CHAR(2), y.theMonth, 101),
DATENAME(YEAR, y.theMonth)[/code]

E 12°55'05.63"
N 56°04'39.26"

gmanojkumar
Starting Member

4 Posts

Posted - 2009-04-15 : 11:01:44

quote:

Originally posted by Peso

DECLARE	@UsageTable TABLE
	(
		[User] VARCHAR(20),
		LogDate DATETIME
	)

INSERT	@UsageTable
SELECT	'U1', '2008-01-03 08:30:00.000' UNION ALL
SELECT	'U1', '2008-01-13 08:30:00.000' UNION ALL
SELECT	'U1', '2008-02-12 08:30:00.000' UNION ALL
SELECT	'U2', '2008-04-13 08:30:00.000'

DECLARE	@StartMonth DATETIME,
	@EndMonth DATETIME

SELECT	@StartMonth = '2008-01-01',
	@EndMonth = '2008-04-01'

;WITH Yak (theMonth, EndMonth)
AS (
	SELECT	@StartMonth,
		@EndMonth

	UNION ALL

	SELECT	DATEADD(MONTH, 1, theMonth),
		EndMonth
	FROM	Yak
	WHERE	theMonth < EndMonth
)

SELECT		u.[User],
		CONVERT(CHAR(2), y.theMonth, 101) AS [Month],
		DATENAME(YEAR, y.theMonth) AS [Year],
		COUNT(ut.[User]) AS UsageCount
FROM		Yak AS y
CROSS JOIN	(
			SELECT		[User]
			FROM		@UsageTable
			GROUP BY	[User]
		) AS u
LEFT JOIN	@UsageTable AS ut ON DATEDIFF(MONTH, ut.LogDate, y.theMonth) = 0
			AND ut.[User] = u.[User]
GROUP BY	u.[User],
		CONVERT(CHAR(2), y.theMonth, 101),
		DATENAME(YEAR, y.theMonth)
ORDER BY	u.[User],
		CONVERT(CHAR(2), y.theMonth, 101),
		DATENAME(YEAR, y.theMonth)

E 12°55'05.63"
N 56°04'39.26"

Thanks for the excellent reply Peter. This solved my problem. Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources