Grouping results Date Wise - 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 2005 Forums

Transact-SQL (2005)

Grouping results Date Wise

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2008-09-24 : 04:52:31

I have 2 tables ItemList and Stats. The sample data is as follows :

ItemList

Id    Name
1     Item1
2     Item2
3     Item3
4     Item4

Stats

Id    ItemListId   EntryDateTime                    Type
1     1               2008-09-22 04:47:33.000       0
2     2               2008-09-22 06:27:13.000       0
3     1               2008-09-22 07:14:22.000       1
4     3               2008-09-23 01:41:55.000       0
5     2               2008-09-23 13:47:33.000       1

Type = 0 means 'Viewed' and Type = 1 means 'Clicked'

How do I get results as shown below grouped on the date:

22.09.2008

Id      Name        Viewed      Clicked     
1      Item1         2000          5
2      Item2         2400          4
3      Item3         4500          6
4      Item4         2700          9   

23.09.2008

Id      Name        Viewed      Clicked     
1      Item1         2600          4
2      Item2         2100          8
3      Item3         4800          2
4      Item4         1700          8

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 04:59:05

use above query and do formatting at your front end.

SELECT DATEADD(dd,DATEDIFF(dd,0,EntryDateTime),0),
il.Name,
SUM(CASE WHEN Type=0 THEN 1 ELSE 0 END) AS Viewed,
SUM(CASE WHEN Type=1 THEN 1 ELSE 0 END) AS Clicked        
FROM ItemList il
INNER JOIN Stats s
ON s.ItemListId =il.Id
GROUP BY DATEADD(dd,DATEDIFF(dd,0,EntryDateTime),0),
il.Name

lols
Posting Yak Master

174 Posts

Posted - 2008-09-24 : 05:46:21

thanks so much

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-24 : 06:04:04

Here is another approach if you want to report all items on all days even if there are no views nor clicks.

DECLARE	@ItemList TABLE
	(
		ID INT,
		Name VARCHAR(20)
	)

INSERT	@ItemList
SELECT	1, 'Item1' UNION ALL
SELECT	2, 'Item2' UNION ALL
SELECT	3, 'Item3' UNION ALL
SELECT	4, 'Item4'

DECLARE	@Stats TABLE
	(
		ID INT,
		ItemListID INT,
		EntryDateTime DATETIME,
		Type TINYINT
	)

SET DATEFORMAT YMD

INSERT	@Stats
SELECT	1, 1, '2008-09-22 04:47:33', 0 UNION ALL
SELECT	2, 2, '2008-09-22 06:27:13', 0 UNION ALL
SELECT	3, 1, '2008-09-22 07:14:22', 1 UNION ALL
SELECT	4, 3, '2008-09-23 01:41:55', 0 UNION ALL
SELECT	5, 2, '2008-09-23 13:47:33', 1

-- Peso
SELECT		d.theDate,
		l.ID,
		l.Name,
		SUM(CASE WHEN s.Type = 0 THEN 1 ELSE 0 END) AS Viewed,
		SUM(CASE WHEN s.Type = 1 THEN 1 ELSE 0 END) AS Clicked
FROM		(
			SELECT		DATEADD(DAY, DATEDIFF(DAY, '19000101', EntryDateTime), '19000101') AS theDate
			FROM		@Stats
			GROUP BY	DATEADD(DAY, DATEDIFF(DAY, '19000101', EntryDateTime), '19000101')
		) AS d
CROSS JOIN	@ItemList AS l
LEFT JOIN	(
			SELECT		ItemListID,
					DATEADD(DAY, DATEDIFF(DAY, '19000101', EntryDateTime), '19000101') AS EntryDateTime,
					Type
			FROM		@Stats
		) AS s ON s.ItemListID = l.ID
			AND s.EntryDateTime = d.theDate
GROUP BY	d.theDate,
		l.ID,
		l.Name		
ORDER BY	d.theDate,
		l.ID,
		l.Name

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources