DateCreated List of Members - 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 2000 Forums

Transact-SQL (2000)

DateCreated List of Members

Author

Topic

adlo
Posting Yak Master

108 Posts

Posted - 2004-11-15 : 11:01:48

I need to do the following complex query.

I have a Member Table with fields
Member_ID int,
DateCreated smalldatetime

I need a resultset containing the Date with the number of members created for that day (MemberCount).

Currently my query looks like this
select CONVERT(CHAR(10),DateCreated,111) as DateCreated,Count(Member_ID) as MemberCount
FROM Member
GROUP BY CONVERT(CHAR(10),DateCreated,111)

The resultset should include all days that exist between the earliest and latest DateCreated of the member table.
If the date does not exist for a spesific day the membercount for that day must be 0.

Thus results should look something like
2004/09/01 - 1 (Earliest date in table)
2004/09/02 - 0
2004/09/03 - 2
2004/09/04 - 0
2004/09/05 - 0
2004/09/06 - 1 (Latest Date in table)

I have a tally table (1-10000)

I would really appreciate it if someone can help me with this.

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-15 : 12:20:22

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTally99(Col1 int IDENTITY(1,1), Col2 char(1))
GO

DECLARE @x int
SELECT @x = 0
WHILE @x < 100
BEGIN
INSERT INTO myTally99(Col2)
SELECT 'x' UNION ALL SELECT 'x' UNION ALL SELECT 'x' UNION ALL SELECT 'x' UNION ALL SELECT 'x' UNION ALL
SELECT 'x' UNION ALL SELECT 'x' UNION ALL SELECT 'x' UNION ALL SELECT 'x' UNION ALL SELECT 'x'
SELECT @x = @x + 1
END
GO
SELECT xxx.OrderDate, COUNT(o.OrderDate)
FROM (
SELECT CONVERT(varchar(10),CONVERT(datetime,Col1 + (SELECT DATEDIFF(d,CONVERT(datetime,1),MIN(OrderDate))
FROM Orders)),102) AS OrderDate
FROM myTally99) AS xxx
LEFT JOIN (SELECT CONVERT(varchar(10),OrderDate,102) AS OrderDate
FROM Orders) AS o
ON xxx.OrderDate = o.OrderDate
GROUP BY xxx.OrderDate
ORDER BY xxx.OrderDate
GO

SET NOCOUNT OFF
DROP TABLE myTally99
GO

[/code]

Brett

8-)

adlo
Posting Yak Master

108 Posts

Posted - 2004-11-15 : 23:18:48

Thanks.

Just added
WHERE myTally99<=(Select DateDiff(d,MIN(OrderDate),Max(DateCreated)) from OrderDate)
to limit to range.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources