SQL Help - 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 2008 Forums

Transact-SQL (2008)

SQL Help

Author

Topic

abhishekmadas
Starting Member

19 Posts

Posted - 2011-01-19 : 16:04:59

I have the below query :

SELECT State,
CONVERT(CHAR(4), ActivityDate, 100) + CONVERT(CHAR(2), ActivityDate, 12) as UserTypeMonth
,SUM(ActivityCount) UserTypeCount
FROM [dbo].[AggUserActivity]
WHERE
ActivityDate BETWEEN @StartDate AND @EndDate
GROUP BY State, CONVERT(CHAR(4), ActivityDate, 100) + CONVERT(CHAR(2), ActivityDate, 12)

Following is the Resultset:

Alabama Aug 10 90
Tenessee Aug 10 255
Georgia Sep 10 379
Alabama Sep 10 570
Tenessee Sep 10 1620
Georgia Oct 10 1885
Alabama Oct 10 2272
Tenessee Oct 10 2769

I need to get Georgia Aug 10 0 or the count as 0 for months there is no data.

AggUserActivity Table has the following columns:

ActivityID int identity column.
ID int
State varchar
ActivityCount int
ActivityDate datetime

Thanks.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-19 : 16:51:38

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

SQL has temporal data types. You do not have to treat dates as string; that was COBOL and the 1950's.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

abhishekmadas
Starting Member

19 Posts

Posted - 2011-01-19 : 17:01:02

AggUserActivity Table has the following columns:

ActivityID int identity column.
ID int
State varchar
ActivityCount int
ActivityDate datetime

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-19 : 17:02:52

Something like this?

DECLARE	@Sample TABLE
	(
		StateName VARCHAR(20),
		theDate DATETIME,
		Items INT
	)

INSERT	@Sample
VALUES	('Alabama',  '20100810',   30),
	('Alabama',  '20100811',   60),
	('Tenessee', '20100810',  127),
	('Tenessee', '20100809',  128),
	('Georgia',  '20100910',  379),
	('Alabama',  '20100910',  300),
	('Alabama',  '20100912',  270)

-- Solution here
SELECT		StateName,
		m,
		SUM(Items)
FROM		(
			SELECT		StateName,
					DATEPART(MONTH, theDate) AS m,
					Items
			FROM		@Sample

			UNION ALL

			SELECT		s.StateName,
					m.m,
					0
			FROM		(SELECT DISTINCT StateName FROM @Sample) AS s
			CROSS JOIN	(SELECT DISTINCT DATEPART(MONTH, theDate) AS m FROM @Sample) AS m
		) AS d
GROUP BY	StateName,
		m
ORDER BY	StateName,
		m

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

abhishekmadas
Starting Member

19 Posts

Posted - 2011-01-19 : 17:49:24

Thanks a lot. That worked.

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-20 : 04:04:28

SEE THIS ONE

DECLARE @Sample TABLE
(
StateName VARCHAR(20),
theDate DATETIME,
Items INT
)

INSERT @Sample VALUES ('Alabama', '20100810', 30),
('Alabama', '20100811', 60),
('Tenessee', '20100810', 127),
('Tenessee', '20100809', 128),
('Georgia', '20100910', 379),
('Alabama', '20100910', 300),
('Alabama', '20100912', 270)

SELECT * FROM @Sample

SELECT S.StateName,t.mon,SUM(Items) AS TotItems
FROM @Sample s
JOIN (SELECT StateName, DATEPART(MONTH,theDate) AS mon
FROM @Sample
GROUP BY StateName ,DATEPART(MONTH,theDate)) AS t ON t.mon=DATEPART(MONTH,S.theDate)
AND S.StateName=t.StateName
GROUP BY S.StateName,t.mon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources