show sum as 0 if no records - 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)

show sum as 0 if no records

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-17 : 06:08:06

I have
SELECT affiliatecode,DATEADD(ww,DATEDIFF(ww,0,date),0) AS date,COUNT(*)
FROM messages
WHERE date BETWEEN @startdate AND dateadd(ww,1,@enddate)
GROUP BY affiliatecode,DATEADD(ww,DATEDIFF(ww,0,date),0)

How can I get it to show every week even if there are no records?
(if there are no records it should show 0)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-17 : 06:12:36

SELECT affiliatecode,COALESCE(DATEADD(ww,DATEDIFF(ww,0,date),0),0) AS date,COUNT(*)
FROM messages
WHERE date BETWEEN @startdate AND dateadd(ww,1,@enddate)
GROUP BY affiliatecode,COALESCE(DATEADD(ww,DATEDIFF(ww,0,date),0),0)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-12-17 : 06:15:16

SELECT affiliatecode,ISNULL(DATEADD(ww,DATEDIFF(ww,0,date),0),0) AS date,COUNT(*)
FROM messages
WHERE date BETWEEN @startdate AND dateadd(ww,1,@enddate)
GROUP BY affiliatecode,COALESCE(DATEADD(ww,DATEDIFF(ww,0,date),0),0)

I Struggle For Excellence

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-17 : 06:20:55

that's not working

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 06:23:52

Use an auxiliary week number table.

SELECT		affiliatecode,
		DATEADD(ww, DATEDIFF(ww, 0, date), 0) AS date,
		SUM(yak)
FROM		(
			SELECT	affiliatecode,
				date,
				1 AS yak
			FROM	messages
			WHERE	date BETWEEN @startdate AND dateadd(ww, 1, @enddate)

			UNION ALL

			SELECT		c.affiliatecode,
					DATEADD(DAY, 7 * v.Number, @StartDate),
					0
			FROM		master..spt_values AS v
			CROSS JOIN	(
						SELECT	DISTINCT
							affiliatecode
						FROM	messages
						WHERE	date BETWEEN @startdate AND dateadd(ww, 1, @enddate)
					) AS c
			WHERE		v.type = 'P'
					AND v.Number <= DATEDIFF(WW, @startdate, @enddate)
		) AS d
GROUP BY	affiliatecode,
		DATEADD(ww, DATEDIFF(ww, 0, date), 0)

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-17 : 06:25:25

both are still not showing weks/affilate codes that are 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 06:26:30

[code]
;With CTE(Date)
AS
(SELECT @startdate
UNION ALL
SELECT DATEADD(dd,1,Date)
FROM CTE
WHERE DATEADD(dd,1,Date)<=@enddate
)

SELECT t.affiliatecode,
DATEADD(ww,DATEDIFF(ww,0,t.date),0),
COUNT(*) AS RecCnt
FROM
(SELECT DISTINCT affiliatecode FROM messages)m
CROSS JOIN CTE c
)t
LEFT JOIN messages m
ON m.date BETWEEN @startdate AND dateadd(ww,1,@enddate)
AND m.affiliatecode=t.affiliatecode
GROUP BY t.affiliatecode,DATEADD(ww,DATEDIFF(ww,0,t.date),0)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-17 : 06:29:04

quote:
Originally posted by esthera

both are still not showing weks/affilate codes that are 0

Really?
I am certain of one thing. You haven't even tested the suggestion I posted 12/17/2008 : 06:23:52.
What do you need? Absolute Spoonfeeding (tm)?

DECLARE	@Messages TABLE
	(
		AffiliateCode VARCHAR(20),
		date DATETIME
	)

INSERT	@Messages
SELECT	'A', '2008-01-01' UNION ALL
SELECT	'B', '2008-02-01'

DECLARE	@StartDate DATETIME,
	@EndDate DATETIME

SELECT	@StartDate = '2008-01-01',
	@EndDate = '2008-03-31'

SELECT		affiliatecode,
		DATEADD(ww, DATEDIFF(ww, 0, date), 0) AS date,
		SUM(yak)
FROM		(
			SELECT	affiliatecode,
				date,
				1 AS yak
			FROM	@messages
			WHERE	date BETWEEN @startdate AND dateadd(ww, 1, @enddate)

			UNION ALL

			SELECT		c.affiliatecode,
					DATEADD(DAY, 7 * v.Number, @StartDate),
					0
			FROM		master..spt_values AS v
			CROSS JOIN	(
						SELECT	DISTINCT
							affiliatecode
						FROM	@messages
						WHERE	date BETWEEN @startdate AND dateadd(ww, 1, @enddate)
					) AS c
			WHERE		v.type = 'P'
					AND v.Number <= DATEDIFF(WW, @startdate, @enddate)
		) AS d
GROUP BY	affiliatecode,
		DATEADD(ww, DATEDIFF(ww, 0, date), 0)
ORDER BY	affiliatecode,
		DATEADD(ww, DATEDIFF(ww, 0, date), 0)

My suggestion returns this resultset


affiliatecode	date		(No column name)
A		2007-12-31	1
A		2008-01-07	0
A		2008-01-14	0
A		2008-01-21	0
A		2008-01-28	0
A		2008-02-04	0
A		2008-02-11	0
A		2008-02-18	0
A		2008-02-25	0
A		2008-03-03	0
A		2008-03-10	0
A		2008-03-17	0
A		2008-03-24	0
A		2008-03-31	0
B		2007-12-31	0
B		2008-01-07	0
B		2008-01-14	0
B		2008-01-21	0
B		2008-01-28	1
B		2008-02-04	0
B		2008-02-11	0
B		2008-02-18	0
B		2008-02-25	0
B		2008-03-03	0
B		2008-03-10	0
B		2008-03-17	0
B		2008-03-24	0
B		2008-03-31	0

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-12-17 : 06:40:06

thanks Peso - I didn't see yours before :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources