Group By Week!!! - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Group By Week!!!

Author

Topic

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-03-09 : 16:36:59

Hi All,

I have a SP, where i have a GroupBy clause, which groups my data with dates, and Day, for a given date range.

If the daterange is very large, i would like to run another SP, where the data is grouped by week, not days.

Also I am trying to write one SP, where i can group my data by months.

Can anyone help me in this?
Thanks

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-09 : 16:43:41

[code]SELECT year(yourdate), datepart(wk, yourdate), othercolumns
FROM yourtable
GROUP BY year(yourdate), datepart(wk, yourdate)
ORDER BY 1, 2[/code]

[code]SELECT year(yourdate), month(yourdate), othercolumns
FROM yourtable
GROUP BY year(yourdate), month(yourdate)
ORDER BY 1, 2[/code]

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-03-09 : 17:11:02

Thanks for the reply.
But i messed up with ur idea.

This is my select stmt.
I tried to implement your idea, but failed
Can you please help me in this?

SELECT TOP 100 PERCENT COUNT(dbo.test.CallDate) AS CallCount, convert(varchar(10),dbo.test.CallDate,121) AS CallDate, dbo.GetDayofGivenDate(dbo.test.CallDate) AS [Day]
FROM dbo.test

LEFT OUTER JOIN dbo.view1 ON dbo.test.CallID = dbo.view1.CallID
LEFT OUTER JOIN dbo.view2 ON dbo.test.CallID = dbo.view2.CallID

WHERE (dbo.view1.[ACCOUNT ID] IN (SELECT [account id] FROM dbo.test3 WHERE clientID=(SELECT clientID FROM dbo.Test2 WHERE [contact id]= @contactId)))
OR (dbo.view2.[ACCOUNT ID] IN (SELECT [account id] FROM dbo.test3 WHERE clientID=(SELECT clientID FROM dbo.Test2 WHERE [contact id]= @contactId)))

GROUP BY convert(varchar(10),dbo.test.CallDate,121), dbo.GetDayofGivenDate(dbo.test.CallDate)
Having (convert(varchar(10),dbo.test.CallDate,121) BETWEEN CONVERT(DATETIME, @StartDate, 102)AND CONVERT(DATETIME, @EndDate, 102))
ORDER BY convert(varchar(10),dbo.test.CallDate,121)

-- here i am grouping by date and also day
trying to write a procedure, with the same select stament, but, group by week
Any ideas?
Am i confusing you?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-09 : 17:33:47

--BY WEEK
SELECT TOP 100 PERCENT COUNT(dbo.test.CallDate) AS CallCount, year(dbo.test.CallDate) AS CallYear, datepart(wk, dbo.test.CallDate) AS [Week]
FROM dbo.test

LEFT OUTER JOIN dbo.view1 ON dbo.test.CallID = dbo.view1.CallID
LEFT OUTER JOIN dbo.view2 ON dbo.test.CallID = dbo.view2.CallID

WHERE (dbo.view1.[ACCOUNT ID] IN (SELECT [account id] FROM dbo.test3 WHERE clientID=(SELECT clientID FROM dbo.Test2 WHERE [contact id]= @contactId)))
OR (dbo.view2.[ACCOUNT ID] IN (SELECT [account id] FROM dbo.test3 WHERE clientID=(SELECT clientID FROM dbo.Test2 WHERE [contact id]= @contactId)))
AND (convert(varchar(10),dbo.test.CallDate,121) BETWEEN CONVERT(DATETIME, @StartDate, 102)AND CONVERT(DATETIME, @EndDate, 102))
GROUP BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)
ORDER BY year(dbo.test.CallDate), datepart(wk, dbo.test.CallDate)

--BY MONTH
SELECT TOP 100 PERCENT COUNT(dbo.test.CallDate) AS CallCount, year(dbo.test.CallDate) AS CallYear, month(dbo.test.CallDate) AS [Month]
FROM dbo.test

LEFT OUTER JOIN dbo.view1 ON dbo.test.CallID = dbo.view1.CallID
LEFT OUTER JOIN dbo.view2 ON dbo.test.CallID = dbo.view2.CallID

WHERE (dbo.view1.[ACCOUNT ID] IN (SELECT [account id] FROM dbo.test3 WHERE clientID=(SELECT clientID FROM dbo.Test2 WHERE [contact id]= @contactId)))
OR (dbo.view2.[ACCOUNT ID] IN (SELECT [account id] FROM dbo.test3 WHERE clientID=(SELECT clientID FROM dbo.Test2 WHERE [contact id]= @contactId)))
AND (convert(varchar(10),dbo.test.CallDate,121) BETWEEN CONVERT(DATETIME, @StartDate, 102)AND CONVERT(DATETIME, @EndDate, 102))
GROUP BY year(dbo.test.CallDate), month(dbo.test.CallDate)
ORDER BY year(dbo.test.CallDate), month(dbo.test.CallDate)

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-03-09 : 17:50:14

Thanks, the query worked.
But little confusion.
Output is as
calcount year week
42 2001 32
2 2001 39
1 2001 51
1 2002 17
1 2002 19
106 2002 21
183 2002 22
................
xxx 2007 3

i gave a daterange of 5.1.2006 to 3.1.2007

what is the meaning for this 2001, ..
Please clarify this question, too

Thanks once again

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-09 : 18:08:15

I used the date range code from your query and it's wrong.

So change the where clause to
WHERE [contact id]= @contactId)))
AND (dbo.test.CallDate BETWEEN @StartDate AND @EndDate)

and declare @StartDate and @EndDate as datetime.

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-03-09 : 22:20:09

Thanks for the reply.
I have declared @startDate and @endDate, in my SP
and the WHere clause is also the same
Its not coming clear to me, why is this year parts like this?
Can you please help me to clear this?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-03-09 : 23:55:23

It is not necessary to split the year and week into separate columns if you do not want to. The original post just requested the data be "grouped by week, not days", and this should handle it:

SELECT	dateadd(week, datediff(week, 0, yourdate), 0) as DataWeek,
	othercolumns
FROM	yourtable
GROUP BY dateadd(week, datediff(week, 0, yourdate), 0)
ORDER BY dateadd(week, datediff(week, 0, yourdate), 0)

The same technique will work for month, or quarter, or year, or other ranges.

e4 d5 xd5 Nf6

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-10 : 09:43:41

Also -- you should learn to make use of derived tables to keep your code neater. They should be used instead of those long WHERE IN (SELECT WHERE ...)) expressions, and also when you need to repeat a calculation more than once.

Example, using blindman's code:


SELECT 
  DataWeek, SUM(), SUM(), COUNT(), etc...
FROM
(
  SELECT   
    dateadd(week, datediff(week, 0, yourdate), 0) as DataWeek,
    othercolumns
  FROM
    yourtable
  ) x
GROUP BY DataWeek
ORDER BY DataWeek

Notice how the DateAdd(week(..)) calculation is only done once now, in one spot. Much easier to work with.

- Jeff
http://weblogs.sqlteam.com/JeffS

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-03-10 : 22:52:26

Thanks.. I will try for this

reflex2dotnet
Yak Posting Veteran

99 Posts

Posted - 2007-03-14 : 11:24:56

Thanks I got it worked!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 11:50:43

Instead of having 2 IN's with correlated subquery, try this

LEFT JOIN	(
			SELECT		t2.[Account ID]
			FROM		dbo.test3 AS t3
			INNER JOIN	dbo.Test2 AS t2 ON t2.ClientID = t3.ClientID
			WHERE		t2.[Contact ID] = @ContactID
		) AS x ON x.[Account ID] IN (v1.[Account ID], v2.[Account ID])
WHERE		AND x.[Account ID] IS NOT NULL
		AND t.CallDate >= @StartDate
		AND t.CallDate < DATEADD(DAY, 1, @EndDate)

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources