having a hard time writing this query

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

SQL Server Development (2000)

having a hard time writing this query

Author

Topic

asp_rin
Starting Member

4 Posts

Posted - 2004-09-08 : 17:07:39

ok

table - dbo.ResearchDataReg9
columns that contain the data needed = cliuniqueid, ticdate

cliuniqueid = nvarchar 255 unique client identifier (can and does have repeated records)
ticdate = smalldatetime 4 date that the client came in

1. i need the distinct count of dates for each client
2. then I need a distinct count of cliuniqueid where
ticdate >= 6 display that and
then >= 11 display that and
then >= 16 etc right on down the list...

if need more info just ask
if anyone can help that would be awesome I have been strugling with this for a few days I am new to SQL..

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-08 : 17:23:36

[code]
DECLARE @MyTable TABLE (clientId int, inDate datetime)
INSERT INTO @MyTable VALUES(1, '02/05/04')
INSERT INTO @MyTable VALUES(2, '02/06/04')
INSERT INTO @MyTable VALUES(3, '02/07/04')
INSERT INTO @MyTable VALUES(1, '02/15/04')
INSERT INTO @MyTable VALUES(1, '03/01/04')
INSERT INTO @MyTable VALUES(3, '02/08/04')
INSERT INTO @MyTable VALUES(3, '03/15/04')
INSERT INTO @MyTable VALUES(2, '02/18/04')
INSERT INTO @MyTable VALUES(3, '04/01/04')
INSERT INTO @MyTable VALUES(1, '03/05/04')
INSERT INTO @MyTable VALUES(3, '04/02/04')
INSERT INTO @MyTable VALUES(3, '04/05/04')
INSERT INTO @MyTable VALUES(1, '03/06/04')
INSERT INTO @MyTable VALUES(1, '03/10/04')
INSERT INTO @MyTable VALUES(3, '04/15/04')
INSERT INTO @MyTable VALUES(1, '03/11/04')
INSERT INTO @MyTable VALUES(1, '04/01/04')
INSERT INTO @MyTable VALUES(3, '04/25/04')
INSERT INTO @MyTable VALUES(1, '04/18/04')
INSERT INTO @MyTable VALUES(1, '04/25/04')
INSERT INTO @MyTable VALUES(3, '05/02/04')
INSERT INTO @MyTable VALUES(1, '05/02/04')

Insert Into @MyTable
Select clientId=4, inDate From @myTable where clientId = 3

Select ClientId, count(distinct inDate) From @myTable Group By ClientId

Select
Range = convert(nvarchar,n * 5 + 1) + ' to ' + convert(nvarchar,(n+1) * 5),
Cnt = Count(*)
From
(
Select ClientId, n=(count(distinct inDate)-1)/5 From @myTable Group By ClientId
) as A
Group By N
[/code]

Corey

asp_rin
Starting Member

4 Posts

Posted - 2004-09-08 : 22:00:40

thanks!

I used partial code and created a view with it and used where clauses in my ASP to get the results

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources