Group By thing - 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)

Group By thing

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2008-03-31 : 10:36:45

I try to do:

DECLARE
@ThisMinute DateTime,
@ThisDay DateTime

SET @ThisMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', CURRENT_TIMESTAMP), '20000101')
SET @ThisDay = DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101')

SELECT
row_number() over (order by MAX(HeadlineDate)) as Number,
COUNT(ArticleID) AS [Count],
MIN(DATEADD(dd, - (DAY(HeadlineDate) - 1), HeadlineDate)) AS HeadlineDate
FROM
dbo.ZMArticle
WHERE
PortalID=0

GROUP BY
MONTH(HeadlineDate), Year(HeadlineDate)
ORDER BY
MAX(HeadlineDate) desC

Which works fine, but when I do:

DECLARE
@ThisMinute DateTime,
@ThisDay DateTime

SET @ThisMinute = DATEADD(MINUTE, DATEDIFF(MINUTE, '20000101', CURRENT_TIMESTAMP), '20000101')
SET @ThisDay = DATEADD(DAY, DATEDIFF(DAY, '20000101', CURRENT_TIMESTAMP), '20000101')

SELECT
row_number() over (order by MAX(HeadlineDate)) as Number,
COUNT(ArticleID) AS [Count],
MIN(DATEADD(dd, - (DAY(HeadlineDate) - 1), HeadlineDate)) AS HeadlineDate
FROM
dbo.ZMArticle
WHERE
PortalID=0
AND
Expiredate <> Null
GROUP BY
MONTH(HeadlineDate), Year(HeadlineDate)
ORDER BY
MAX(HeadlineDate) desC

it doesn't return anything....

How can I change that

The secret to creativity is knowing how to hide your sources. (Einstein)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-03-31 : 10:41:46

For starters, change Expiredate <> Null to Expiredate IS NOT NULL

Jim