Record Count check - 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)

Record Count check

Author

Topic

miked1978
Starting Member

25 Posts

Posted - 2008-11-25 : 08:01:03

I'm doing a simple record count which counts the Mondate field:

select Mondate, count (Mondate) as Mondate1
from tablename1
Group By Mondate
Order by Mondate

This table holds 7 weeks of data and is updated each week (Tuesday) which inserts the current week and takes out the oldest week. Each week the record count gets bigger so if the record count is the same as the previous weeks then 99.9% of the time something is wrong. I want to code something in that takes the current week's count and match it to the previous weeks count. If they are the same then I want it to raise a flag or something.

Can someone please help?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-25 : 08:14:31

[code]DECLARE @Sample TABLE
(
monDate DATETIME
)

INSERT @Sample
(
monDate
)
SELECT '20081117' UNION ALL
SELECT '20081117' UNION ALL
SELECT '20081110'

DECLARE @currentWeek DATETIME

SET @currentWeek = '20081122'

-- Simsalabim
DECLARE @previousWeek DATETIME

SELECT @currentWeek = DATEADD(DAY, DATEDIFF(DAY, 0, @currentWeek) / 7 * 7, 0),
@previousWeek = DATEADD(DAY, -7, @currentWeek)

SELECT @currentWeek AS monDate,
SUM(CASE WHEN @currentWeek = monDate THEN 1 ELSE 0 END) AS thisMonDate,
SUM(CASE WHEN @previousWeek = monDate THEN 1 ELSE 0 END) AS previousMonDate
FROM @Sample
WHERE monDate >= @previousWeek
AND monDate < DATEADD(DAY, 1, @currentWeek)[/code]

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

miked1978
Starting Member

25 Posts

Posted - 2008-11-25 : 08:23:50

Peso, thank you. I'm going to try this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources